国内网站域名吗,wordpress 标题图片,融资网站建设重点,郑州妇科医院哪家排名比较好oracle 分区表介绍 Oracle 分区表是一个非常强大的数据库功能#xff0c;可以将一个大的表分割成多个更小、更易管理的块#xff08;分区#xff09;。这种分区结构在处理大规模数据时非常有用#xff0c;因为它能改善性能、简化维护和管理#xff0c;并支持高效的数据存取…oracle 分区表介绍 Oracle 分区表是一个非常强大的数据库功能可以将一个大的表分割成多个更小、更易管理的块分区。这种分区结构在处理大规模数据时非常有用因为它能改善性能、简化维护和管理并支持高效的数据存取 文章目录 oracle 分区表介绍前言范围分区列表分区哈希分区复合分区查询分区分区表的使用场景 前言
分区表Partitioned Table是数据库中一种特殊的表结构它通过将数据分割成多个分区来提高查询和管理性能。每个分区都包含数据的一个子集分区表通常用于存储大量数据通过分区使得操作数据更为高效。分区表的优点包括
提高查询性能对于大数据量的表通过查询特定分区的数据而非整个表来提高查询效率。 提高管理效率分区表可以将数据根据某些条件如时间、范围、列表等分区使得数据的备份、恢复和删除等操作更为方便。 提高并发性分区表可以实现不同分区并行处理提升数据库的并发能力。
分区的类型 范围分区Range Partitioning按范围划分数据例如按日期区分数据。 列表分区List Partitioning按照指定的离散值划分数据比如按地区分。 哈希分区Hash Partitioning通过哈希函数将数据分配到不同的分区中常用于负载均衡。 复合分区Composite Partitioning结合多种分区方式如先按范围分区再按哈希分区。
我们 用soctt 用户的 emp表 和 dept 表 举例
emp表
INSERT INTO EMP VALUES
(7369,SMITH,CLERK,7902,to_date(17-12-1980,dd-mm-yyyy),800,NULL,20);
INSERT INTO EMP VALUES
(7499,ALLEN,SALESMAN,7698,to_date(20-2-1981,dd-mm-yyyy),1600,300,30);
INSERT INTO EMP VALUES
(7521,WARD,SALESMAN,7698,to_date(22-2-1981,dd-mm-yyyy),1250,500,30);
INSERT INTO EMP VALUES
(7566,JONES,MANAGER,7839,to_date(2-4-1981,dd-mm-yyyy),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,MARTIN,SALESMAN,7698,to_date(28-9-1981,dd-mm-yyyy),1250,1400,30);
INSERT INTO EMP VALUES
(7698,BLAKE,MANAGER,7839,to_date(1-5-1981,dd-mm-yyyy),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,CLARK,MANAGER,7839,to_date(9-6-1981,dd-mm-yyyy),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,SCOTT,CLERK,7566,to_date(19-4-1987,dd-mm-yyyy),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,KING,PRESIDENT,NULL,to_date(17-11-1981,dd-mm-yyyy),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,TURNER,SALESMAN,7698,to_date(8-9-1981,dd-mm-yyyy),1500,0,30);
INSERT INTO EMP VALUES
(7876,ADAMS,CLERK,7788,to_date(23-5-1987,dd-mm-yyyy),1100,NULL,20);
INSERT INTO EMP VALUES
(7900,JAMES,CLERK,7698,to_date(3-12-1981,dd-mm-yyyy),950,NULL,30);
INSERT INTO EMP VALUES
(7902,FORD,ANALYST,7566,to_date(3-12-1981,dd-mm-yyyy),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,MILLER,CLERK,7782,to_date(23-1-1982,dd-mm-yyyy),1300,NULL,10);范围分区
解释 表定义emp_partitioned 表和原始的 EMP 表结构相同包含了员工的基本信息字段。
分区方式根据员工的 HIREDATE入职日期列进行分区使用 范围分区Range Partitioning。每个分区包含一个特定年份的数据VALUES LESS THAN 定义了每个分区的上限日期。
分区名称我们为每个分区命名如 p_1980、p_1981 等代表每年的数据。
-- 创建一个基于范围分区的 EMP 表
CREATE TABLE emp_partitioned (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2)
)
PARTITION BY RANGE (hiredate) (PARTITION p_1980 VALUES LESS THAN (TO_DATE(1981-01-01, YYYY-MM-DD)),PARTITION p_1981 VALUES LESS THAN (TO_DATE(1982-01-01, YYYY-MM-DD)),PARTITION p_1982 VALUES LESS THAN (TO_DATE(1983-01-01, YYYY-MM-DD)),PARTITION p_1983 VALUES LESS THAN (TO_DATE(1984-01-01, YYYY-MM-DD)),PARTITION p_1984 VALUES LESS THAN (TO_DATE(1985-01-01, YYYY-MM-DD))
);导入数据 INSERT INTO emp_partitioned
SELECT * FROM EMP where to_char(hiredate,YYYY-MM-DD)1986-01-01查询 emp_partitioned 的 区分p_1982
SELECT *
FROM emp_partitioned PARTITION (p_1982) E
LEFT JOIN DEPT D ON E.EMPNO D.DEPTNO;– 查询 1992 年入职的员工 查询分区表 当表被分区后查询时 Oracle 会自动根据查询条件来裁剪不相关的分区。例如如果查询的是 1982 年的数据Oracle 只会访问 p_1982 分区而不需要扫描所有的分区。
SELECT * FROM emp_partitioned
WHERE hiredate BETWEEN TO_DATE(1982-01-01, YYYY-MM-DD) AND TO_DATE(1982-12-31, YYYY-MM-DD);添加分区
ALTER TABLE emp_partitioned ADD PARTITION p_1985 VALUES LESS THAN (TO_DATE(1986-01-01, YYYY-MM-DD));拆分分区
ALTER TABLE emp_partitioned SPLIT PARTITION p_1981 AT (TO_DATE(1981-06-01, YYYY-MM-DD))
INTO (PARTITION p_1981_1 VALUES LESS THAN (TO_DATE(1981-06-01, YYYY-MM-DD)), PARTITION p_1981_2 VALUES LESS THAN (TO_DATE(1982-01-01, YYYY-MM-DD)));删除分区
ALTER TABLE emp_partitioned DROP PARTITION p_1980;列表分区
假设我们有 EMP 表并希望根据 DEPTNO部门编号对员工进行分区。使用列表分区的好处是可以根据部门编号将员工数据分布到不同的分区中使得每个部门的数据存储在不同的分区里。
CREATE TABLE emp_partitioned_list (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2)
)
PARTITION BY LIST (deptno) (PARTITION p_sales VALUES (10), -- 部门编号为 10 的员工PARTITION p_marketing VALUES (20), -- 部门编号为 20 的员工PARTITION p_research VALUES (30), -- 部门编号为 30 的员工PARTITION p_accounting VALUES (40) -- 部门编号为 40 的员工
);导入数据 INSERT INTO emp_partitioned_list
SELECT * FROM EMP where deptno in (10,20,30,40)添加新的分区 如果我们有一个新的部门编号 50可以添加新的分区
ALTER TABLE emp_partitioned_list ADD PARTITION p_new_dept VALUES (50);哈希分区
假设我们要对 EMP 表进行哈希分区使用员工编号 EMPNO 作为分区键。哈希分区会使用哈希函数将数据均匀地分配到不同的分区中适用于没有明显规律的列。
– 创建一个哈希分区的 EMP 表
分区名称通过 PARTITION p_part1、PARTITION p_part2、PARTITION p_part3 和 PARTITION p_part4你可以自定义每个分区的名称。 分区键该表仍然使用 empno 列作为分区键Oracle 将会基于 empno 字段的哈希值将数据分配到这些分区中。
CREATE TABLE emp_partitioned_hash (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2)
)
PARTITION BY HASH (empno) -- 使用 empno 作为分区键PARTITIONS 4; -- 将表分为 4 个分区
-----
PARTITION BY HASH (empno) -- 使用 empno 作为分区键(PARTITION p_part1, -- 自定义分区名称 p_part1PARTITION p_part2, -- 自定义分区名称 p_part2PARTITION p_part3, -- 自定义分区名称 p_part3PARTITION p_part4 -- 自定义分区名称 p_part4);导入数据
insert into emp_partitioned_hash
select * from emp复合分区
复合分区结合了多种分区方法可以在范围分区的基础上进一步使用哈希分区。这样可以灵活地控制数据的分区。
假设我们有一个 EMP 表我们按员工的入职日期 (HIREDATE) 进行范围分区然后在每个年份的分区内按部门编号 (DEPTNO) 使用哈希分区。以下是如何创建这样的表的示例。。
-- 创建复合分区的 EMP 表
CREATE TABLE EMP_Partition_Range_Hash (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2)
)
PARTITION BY RANGE (hiredate) -- 首先按 HIREDATE 进行范围分区
SUBPARTITION BY HASH (deptno) -- 在每个范围分区内按 DEPTNO 进行哈希分区
(PARTITION p_1980 VALUES LESS THAN (TO_DATE(1981-01-01, YYYY-MM-DD)) -- 1980 年的数据(SUBPARTITION p_1980_1, -- 子分区1SUBPARTITION p_1980_2 -- 子分区2),PARTITION p_1981 VALUES LESS THAN (TO_DATE(1982-01-01, YYYY-MM-DD)) -- 1981 年的数据(SUBPARTITION p_1981_1, -- 子分区1SUBPARTITION p_1981_2 -- 子分区2),PARTITION p_1982 VALUES LESS THAN (TO_DATE(1983-01-01, YYYY-MM-DD)) -- 1982 年的数据(SUBPARTITION p_1982_1, -- 子分区1SUBPARTITION p_1982_2 -- 子分区2)
);以下是一个 复合分区Composite Partitioning示例其中结合了 范围分区Range Partitioning和 列表分区List Partitioning。我们将创建一个 EMP 表首先按员工的 HIREDATE入职日期进行 范围分区然后在每个范围分区内按 DEPTNO部门编号进行 列表分区。
-- 创建一个复合分区的 EMP 表
CREATE TABLE EMP_Partition_Range_List (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2)
)
PARTITION BY RANGE (hiredate) -- 首先按 HIREDATE 进行范围分区
SUBPARTITION BY LIST (deptno) -- 在每个范围分区内按 DEPTNO 进行列表分区
(PARTITION p_1980 VALUES LESS THAN (TO_DATE(1981-01-01, YYYY-MM-DD)) -- 1980 年的数据(SUBPARTITION p_1980_1 VALUES (10), -- 部门 10 的子分区SUBPARTITION p_1980_2 VALUES (20), -- 部门 20 的子分区SUBPARTITION p_1980_3 VALUES (30) -- 部门 30 的子分区),PARTITION p_1981 VALUES LESS THAN (TO_DATE(1982-01-01, YYYY-MM-DD)) -- 1981 年的数据(SUBPARTITION p_1981_1 VALUES (10), -- 部门 10 的子分区SUBPARTITION p_1981_2 VALUES (20), -- 部门 20 的子分区SUBPARTITION p_1981_3 VALUES (30) -- 部门 30 的子分区),PARTITION p_1982 VALUES LESS THAN (TO_DATE(1983-01-01, YYYY-MM-DD)) -- 1982 年的数据(SUBPARTITION p_1982_1 VALUES (10), -- 部门 10 的子分区SUBPARTITION p_1982_2 VALUES (20), -- 部门 20 的子分区SUBPARTITION p_1982_3 VALUES (30) -- 部门 30 的子分区)
);
导入数据
insert into EMP_Partition_Range_hash
select * from emp where deptno in (10,20,30) and to_char(hiredate,yyyy-mm-dd)1984-01-01insert into EMP_Partition_Range_List
select * from emp where deptno in (10,20,30) and to_char(hiredate,yyyy-mm-dd)1984-01-01查询数据
select * from EMP_Partition_Range_hash PARTITION(p_1981)
select * from EMP_Partition_Range_hash SUBPARTITION (p_1980_1)select * from EMP_Partition_Range_List PARTITION(p_1981)
select * from EMP_Partition_Range_List SUBPARTITION (p_1981_1)添加分区 (Add Partition) 假设您希望为现有的复合分区表添加一个新的范围分区。您可以使用 ALTER TABLE 语句来实现这一操作。这里我们为表 EMP_Partition_Range_List 添加一个新的范围分区 p_1983并在该分区内使用部门编号DEPTNO进行子分区。
ALTER TABLE EMP_Partition_Range_List
ADD PARTITION p_1983 VALUES LESS THAN (TO_DATE(1983-01-01, YYYY-MM-DD))
(SUBPARTITION p_1983_1 VALUES (10),SUBPARTITION p_1983_2 VALUES (20),SUBPARTITION p_1983_3 VALUES (30)
);删除分区 (Drop Partition) 如果某个分区已经不再需要或已经包含过期数据可以删除该分区。假设您希望删除 p_1980 分区及其子分区可以使用如下 SQL 语句
ALTER TABLE EMP_Partition_Range_List
DROP PARTITION p_1980;拆分分区 (Split Partition) 当某个分区中的数据量过大时您可以拆分分区以分散负载。假设您希望拆分 p_1981 分区将其拆分为两个新的分区您可以使用以下语句
ALTER TABLE EMP_Partition_Range_List
SPLIT PARTITION p_1981 AT (TO_DATE(1981-06-30, YYYY-MM-DD))
INTO (PARTITION p_1981_1 VALUES LESS THAN (TO_DATE(1981-06-30, YYYY-MM-DD)),PARTITION p_1981_2 VALUES LESS THAN (TO_DATE(1982-01-01, YYYY-MM-DD))
);合并分区 (Merge Partition) 当多个分区的数据量较小且可以合并时可以使用 MERGE PARTITION 语句。假设您希望将 p_1980_1 和 p_1980_2 两个子分区合并为一个新的子分区可以使用如下语句
ALTER TABLE EMP_Partition_Range_List
MERGE PARTITIONS p_1980_1, p_1980_2 INTO PARTITION p_1980;交换分区 (Exchange Partition) 如果您需要将一个分区的数据与一个表的数据交换可以使用 EXCHANGE PARTITION 语句。假设您有一个临时表 new_data_table并且希望将 p_1981_1 的数据与 new_data_table 交换可以使用以下语句
ALTER TABLE EMP_Partition_Range_List
EXCHANGE PARTITION p_1981_1 WITH TABLE new_data_table;修改分区存储属性 如果您需要修改某个分区的存储属性例如修改分区的存储参数可以使用 ALTER TABLE 来修改。例如如果您想要增加分区的 PCTFREE 参数可以使用如下语句
ALTER TABLE EMP_Partition_Range_List
MODIFY PARTITION p_1981 STORAGE (PCTFREE 20);查询分区
select * from user_tab_partitions
select * from DBA_tab_partitions
select * from ALL_tab_partitions
----查看所有分区信息
----这里的表名是您需要查询的表
SELECT table_name, partition_name, partition_position, tablespace_name, high_value
FROM DBA_TAB_PARTITIONS
WHERE table_name EMP_PARTITION_RANGE_LIST;
----查看每个分区的子分区信息
SELECT table_name, partition_name, subpartition_name, subpartition_position
FROM DBA_TAB_SUBPARTITIONS
WHERE table_name EMP_PARTITION_RANGE_LIST;
----查看分区的列和分区键
SELECT column_name, partitioned
FROM DBA_TAB_COLUMNS
WHERE table_name EMP_PARTITION_RANGE_LIST;
----查看某个分区的存储属性
SELECT table_name, partition_name, pct_free, tablespace_name
FROM DBA_TAB_PARTITIONS
WHERE table_name EMP_PARTITION_RANGE_LIST;
----查看表空间中的所有分区
SELECT table_name, partition_name, tablespace_name
FROM DBA_TAB_PARTITIONS
WHERE tablespace_name USERS; -- 这里的 USERS 是指定的表空间名
分区表的使用场景
时间序列数据
对于大规模的时间序列数据分区表是非常适合的。例如日志数据、交易数据等。通常按年份、季度、月份等时间粒度进行分区可以极大地提高查询效率并方便旧数据的归档和删除。 示例
银行交易记录按月份分区查询某个月的交易记录时数据库仅需要扫描该月份的分区。
大规模数据仓库
在数据仓库系统中经常需要处理数TB甚至PB级的数据分区表可以显著提高查询性能并且优化存储管理。通过分区可以根据不同的维度如时间、地区、产品类型等进行数据分区。 示例
数据仓库的销售记录按地区和时间进行复合分区先按时间分区再按地区分区提高查询性能和数据处理效率。 数据归档和历史数据管理
处理大量历史数据时分区表可以通过分区裁剪和快速删除简化数据归档和清理。例如按年份分区的表可以在每年结束时快速归档过去一年的数据。 示例
大型企业的员工记录表按年份分区每年创建一个新的分区过期的员工记录例如离职超过5年的员工可以快速删除。 高并发环境
在处理大量并发查询的环境中分区表能够将负载分布到不同的分区上提高并发查询的响应速度。例如对于一个在线零售商按地区对订单表进行分区可以提高不同地区用户查询的并发性能。 示例
电商平台的订单表按地区分区不同地区的查询可以在独立的分区上并行执行从而提高查询性能。 大规模日志数据存储
日志数据通常按时间顺序增长分区表特别适合存储和管理这类数据。按月或按天分区的日志表能极大提高查询速度并便于清理过时的数据。 示例
Web 服务器的访问日志按天分区每天生成一个新的分区查询某天的访问记录时只需扫描相关的分区。 跨分区查询优化
如果需要在多个分区之间执行查询使用分区表可以显著优化跨分区的查询操作。通过合理的分区策略例如按客户地域、订单类型等分区可以使查询仅集中在少量相关的分区上而不需要遍历整个表。 示例
客户订单表按地区分区查询某个地区所有订单时仅需访问该地区对应的分区。 文章转载自： http://www.morning.gnbtp.cn.gov.cn.gnbtp.cn http://www.morning.cljmx.cn.gov.cn.cljmx.cn http://www.morning.ypjjh.cn.gov.cn.ypjjh.cn http://www.morning.chbcj.cn.gov.cn.chbcj.cn http://www.morning.rtlrz.cn.gov.cn.rtlrz.cn http://www.morning.xkhhy.cn.gov.cn.xkhhy.cn http://www.morning.jfzbk.cn.gov.cn.jfzbk.cn http://www.morning.nwljj.cn.gov.cn.nwljj.cn http://www.morning.qbfqb.cn.gov.cn.qbfqb.cn http://www.morning.hjwzpt.com.gov.cn.hjwzpt.com http://www.morning.kqhlm.cn.gov.cn.kqhlm.cn http://www.morning.kqbzy.cn.gov.cn.kqbzy.cn http://www.morning.nsfxt.cn.gov.cn.nsfxt.cn http://www.morning.ffydh.cn.gov.cn.ffydh.cn http://www.morning.zrmxp.cn.gov.cn.zrmxp.cn http://www.morning.tjsxx.cn.gov.cn.tjsxx.cn http://www.morning.kjgrg.cn.gov.cn.kjgrg.cn http://www.morning.cjrmf.cn.gov.cn.cjrmf.cn http://www.morning.pcqdf.cn.gov.cn.pcqdf.cn http://www.morning.rsqpc.cn.gov.cn.rsqpc.cn http://www.morning.zxzgr.cn.gov.cn.zxzgr.cn http://www.morning.wnkjb.cn.gov.cn.wnkjb.cn http://www.morning.dnqliv.cn.gov.cn.dnqliv.cn http://www.morning.mbfkt.cn.gov.cn.mbfkt.cn http://www.morning.cwjxg.cn.gov.cn.cwjxg.cn http://www.morning.tlyms.cn.gov.cn.tlyms.cn http://www.morning.sgwr.cn.gov.cn.sgwr.cn http://www.morning.nmtyx.cn.gov.cn.nmtyx.cn http://www.morning.fyxr.cn.gov.cn.fyxr.cn http://www.morning.frxsl.cn.gov.cn.frxsl.cn http://www.morning.tdmr.cn.gov.cn.tdmr.cn http://www.morning.ljzgf.cn.gov.cn.ljzgf.cn http://www.morning.yccnj.cn.gov.cn.yccnj.cn http://www.morning.krlsz.cn.gov.cn.krlsz.cn http://www.morning.lmnbp.cn.gov.cn.lmnbp.cn http://www.morning.syssdz.cn.gov.cn.syssdz.cn http://www.morning.spsqr.cn.gov.cn.spsqr.cn http://www.morning.cklld.cn.gov.cn.cklld.cn http://www.morning.thzgd.cn.gov.cn.thzgd.cn http://www.morning.lfsmf.cn.gov.cn.lfsmf.cn http://www.morning.mpsnb.cn.gov.cn.mpsnb.cn http://www.morning.mzcsp.cn.gov.cn.mzcsp.cn http://www.morning.hfytgp.cn.gov.cn.hfytgp.cn http://www.morning.bqwnp.cn.gov.cn.bqwnp.cn http://www.morning.tgydf.cn.gov.cn.tgydf.cn http://www.morning.yqkmd.cn.gov.cn.yqkmd.cn http://www.morning.mdwtm.cn.gov.cn.mdwtm.cn http://www.morning.plnry.cn.gov.cn.plnry.cn http://www.morning.bwgrd.cn.gov.cn.bwgrd.cn http://www.morning.touziyou.cn.gov.cn.touziyou.cn http://www.morning.kndst.cn.gov.cn.kndst.cn http://www.morning.ygxf.cn.gov.cn.ygxf.cn http://www.morning.yzmzp.cn.gov.cn.yzmzp.cn http://www.morning.ltffk.cn.gov.cn.ltffk.cn http://www.morning.jjmrx.cn.gov.cn.jjmrx.cn http://www.morning.qqxmj.cn.gov.cn.qqxmj.cn http://www.morning.pbtdr.cn.gov.cn.pbtdr.cn http://www.morning.qsmch.cn.gov.cn.qsmch.cn http://www.morning.sfdsn.cn.gov.cn.sfdsn.cn http://www.morning.jxfsm.cn.gov.cn.jxfsm.cn http://www.morning.srbmc.cn.gov.cn.srbmc.cn http://www.morning.jhxtm.cn.gov.cn.jhxtm.cn http://www.morning.rqbr.cn.gov.cn.rqbr.cn http://www.morning.jtqxs.cn.gov.cn.jtqxs.cn http://www.morning.qrwjb.cn.gov.cn.qrwjb.cn http://www.morning.nlmm.cn.gov.cn.nlmm.cn http://www.morning.zkrzb.cn.gov.cn.zkrzb.cn http://www.morning.gthgf.cn.gov.cn.gthgf.cn http://www.morning.rmxgk.cn.gov.cn.rmxgk.cn http://www.morning.lfttb.cn.gov.cn.lfttb.cn http://www.morning.jnbsx.cn.gov.cn.jnbsx.cn http://www.morning.mcbqq.cn.gov.cn.mcbqq.cn http://www.morning.haibuli.com.gov.cn.haibuli.com http://www.morning.duqianw.com.gov.cn.duqianw.com http://www.morning.smggx.cn.gov.cn.smggx.cn http://www.morning.gfpyy.cn.gov.cn.gfpyy.cn http://www.morning.mkpkz.cn.gov.cn.mkpkz.cn http://www.morning.ylkkh.cn.gov.cn.ylkkh.cn http://www.morning.nlbhj.cn.gov.cn.nlbhj.cn http://www.morning.pgmyn.cn.gov.cn.pgmyn.cn
