目录
表分区与索引分区
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
表分区的具体作用
Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。
什么时候使用分区表：
表的大小超过2GB。
表中包含历史数据，新的数据被增加都新的分区中。
表分区的优缺点
表分区有以下优点：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：
已经存在的表没有方法可以直接转化为分区表。
分区修剪行为：如果查询首先访问索引，他是否能够修建分区完全取决于查询中的谓词。
分区修剪是数据仓库的一个重要特性。当访问分区表的时候，优化器分析FROM和WHERE子句来消除不必要的分区。
select count(*) from t9 where object_id=10;
select count(*) from t8 where object_id=10;
表分区的实现
列表--list partitioning
| create table emp_list( empno number(4) ,ename varchar2(10) ,job varchar2(9) ,mgr number(4) ,deptno number(2) )partition by list(deptno) (partition p_10 values (10) ,partition p_20 values (20) ,partition p_30 values (30) ,partition p_40 values (40)); |
范围--range partitioning
| create table emp_range ( empno number(4) ,ename varchar2(10) ,job varchar2(9) ,mgr number(4) ,create_date date ) partition by range(create_date) (partition p_20220101 values less than (to_date('2017-01-02','YYYY-MM-DD')) ,partition p_20220102 values less than (to_date('2017-01-03','YYYY-MM-DD')) ,partition p_20220103 values less than (to_date('2017-01-04','YYYY-MM-DD')) ); --也可以 结合maxvalue -- 插入数据 insert into emp_range values(1003,'xiaoming','salas',100,to_date('20160101','YYYYMMDD')); insert into emp_range values(1004,'xiaoming','salas',100,to_date('20170101','YYYYMMDD')); insert into emp_range values(1005,'xiaoming','salas',100,to_date('20170102','YYYYMMDD')); insert into emp_range values(1007,'xiaoming','salas',100,to_date('20170103','YYYYMMDD')); -- 根据分区名查询该分区下数据 select * from emp_range partition(p_20220103); -- 根据值所在的分区，查询该分区的所有数据 select * from emp_range partition for(to_date('20170103','YYYYMMDD')); -- 清空对应的分区，但其他分区数据不做改变 alter table emp_range truncate partition (p_20220103); -- 如果被修改的字段是分区判定的字段，那么可能需要开启行偏移 alter table emp_range enable row movement; update emp_range set create_date= to_date('20170102','YYYYMMDD') where empno=1007; -- 添加一个分区 alter table emp_range add partition p_20220103 values less than(to_date('2017-01-04','YYYY-MM-DD')); -- 删除一个分区 alter table emp_range drop partition p_20220103; |
哈希--hash partitioning
| /*对于无法有效划分范围的表*/ create table emp_hash( empno number(4) ,ename varchar2(10) ,job varchar2(9) ,mgr number(4) ,hirdate date ,sal number(7,2) ,comm number(7,2) ,deptno number(2) ,create_date date )partition by hash(empno)( partition part01, partition part02， partition part03， partition part04); |
组合分区（子分区）
| create table emp_range_list( empno number(4) ,ename varchar2(10) ,job varchar2(9) ,mgr number(4) ,hirdate date ,sal number(7,2) ,comm number(7,2) ,deptno number(2) ,create_date date ) partition by range(hirdate) subpartition by list(deptno) ( partition p1 values less than(to_date('1921-01-01','YYYY-MM-DD')) (subpartition p1A values (10), subpartition p1B values (20), subpartition p1C values (30), subpartition p1D values (40) ), partition p2 values less than(to_date('1922-01-01','YYYY-MM-DD')) (subpartition p1A values (10), subpartition p1B values (20), subpartition p1C values (30), subpartition p1D values (40) ) ) |
自动扩展分区
| -- 按天自动添加分区 create table emp_interval_day ( id number ,time_col date )partition by range(time_col) interval (numtodsinterval(1,'day'))( partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd')) ) ; -- 按月自动添加分区 create table emp_interval_month ( id number ,time_col date )partition by range(time_col) interval (numtoyminterval(1,'month'))( partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd')) ) ; -- 按年分区 create table emp_interval_year ( id number ,time_col date )partition by range(time_col) interval (numtoyminterval(1,'year'))( partition p_day_1 values less than (to_date('2018-01-01','yyyy-mm-dd')) ) ; |
显示数据库相关的分区信息
| --显示数据库所有分区表的信息： |
分区索引
对索引分区有以下两种方法：
按表分区的方式对索引分区：这也称为本地索引（local index）。每个表分区都有一个索引分区，而且这个索引分区只会对这个表分区中的数据进行索引。同一索引分区中的所有条目都指向一个表分区，同一表分区中的所有行都会放在一个索引分区中。
按区间或散列对索引分区：这也称为全局分区索引（globally partitioned index）。索引可以按区间分区，一个索引分区可以指向任何表分区。
本地索引分为两类：
本地前缀索引（local prefixed index）：在这类索引中，分区键在索引定义的前几列上。
本地非前缀索引（local nonprefixed index）：这些索引不以表的分区键作为它的前几列。索引可能包含表的分区键列，也可能不包含。
分区索引特点
表的DML操作无需rebuild索引
可以很方便的管理数据
全局索引特点
表的dml操作导致索引无效
分区修剪行为
如果查询首先访问索引，他是否能够修建分区完全取决于查询中的谓词。
--示例
| /*创建测试环境，验证分区修建行为*/ ---创建测试表空间 create tablespace test_partitioned_tablespace_p1 logging datafile '/data/u01/app/oracle/oradata/orcl/test_partitioned_tablespace_p1' size 40M autoextend on next 100M maxsize unlimited extent management local segment space management auto; create tablespace test_partitioned_tablespace_p2 logging datafile '/data/u01/app/oracle/oradata/orcl/test_partitioned_tablespace_p2' size 40M autoextend on next 100M maxsize unlimited extent management local segment space management auto; --创建测试表环境 CREATE TABLE partitioned_table ( a int, b int, data char(20) ) PARTITION BY RANGE (a) ( PARTITION part_1 VALUES LESS THAN(2) tablespace test_partitioned_tablespace_p1, PARTITION part_2 VALUES LESS THAN(3) tablespace test_partitioned_tablespace_p2); --创建本地前缀索引 create index local_prefixed on partitioned_table (a,b) local; --创建本地非前缀索引 create index local_nonprefixed on partitioned_table (b) local; --创建数据 insert into partitioned_table select mod(rownum-1,2)+1, rownum, 'x' from dual connect by level <= 70000; -- 生成直方图 begin dbms_stats.gather_table_stats( user,'PARTITIONED_TABLE',cascade=>TRUE ); end; --将p2放于离线状态，模拟介质故障 alter tablespace test_partitioned_tablespace_p2 offline; select * from partitioned_table where a = 1 and b = 1;
-- 通过返回结果我们知道，p2是离线状态的表空间；但数据还是能正常访问； select * from partitioned_table where b = 1; -- 然而，使用本地非前置索引却失败；PSTOP为2，说明在使用本地非前缀索引的时候必须考虑part_2，但是表空间已经离线所以失败。 --删除前置索引 drop index local_prefixed; select * from partitioned_table where a = 1 and b = 1; -- 发现使用了非前缀索引，而且也能进行分区修剪，说明本地非前缀索引也可以进行分区修剪。 |
本地索引和唯一约束
如果在分区表上想使本地索引来支撑UNIQUE约束或者PRIMARY KEY约束，那么必须将分区键加到这个约束中；这是本地索引的最大限制，Oracle只能保证索引分区内部不能保证跨分区的唯一性。
如果你使用一个TIMESTAMP字段作为分区键，并且在使用一个本地分区索引的ID上有一个主键，那么Oracle会转而利用全局索引来保证唯一性。
| create table partitioned ( timestamp date, id int, constraint partitioned_pk primary key(id) --id有主键 ) PARTITION BY RANGE (timestamp) --分区键为timestamp ( PARTITION part_1 VALUES LESS THAN( to_date('01/01/2014','dd/mm/yyyy') ) , PARTITION part_2 VALUES LESS THAN( to_date('01/01/2015','dd/mm/yyyy') ) ) |
全局索引
与本地索引不同，全局索引只有一类，那就是前缀全局索引（prefixed global index）。
全局索引必须使用以他的起始列作为分区键。
索引中不能包含表的分区列。
| -- 创建全局索引分区测试表 create table global_partitioned_table ( timestamp date, id int ) partition by range (timestamp) --timestamp作为分区键 ( partition part_1 values less than ( to_date('01/01/2014','dd/mm/yyyy') ) , partition part_2 values less than ( to_date('01/01/2015','dd/mm/yyyy') ) ); --创建全局索引 create index global_partitioned_index on global_partitioned_table(id) GLOBAL partition by range(id) ( partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) ); -- 添加主键约束 alter table global_partitioned_table add constraint global_partitioned_pk primary key(id); -- 约束依赖于该全局索引，如果要删除该全局索引必须先删除约束。 |
误区--分区索引性能高于全局索引
| --搭建测试环境 create table part_1(id int,name varchar2(1000)) partition by range(id) ( partition p1 values less than(1000), partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000), partition p5 values less than(5000), partition p6 values less than(6000), partition p7 values less than(7000), partition p8 values less than(8000), partition p9 values less than(9000), partition p10 values less than(10000), partition p11 values less than(11000), partition p12 values less than(12000), partition p13 values less than(13000), partition p14 values less than(14000), partition p15 values less than(15000), partition p16 values less than(16000), partition pmax values less than(maxvalue) ); insert into part_1 select t.OBJECT_ID as id,t.OWNER|| t.OBJECT_NAME|| t.SUBOBJECT_NAME|| t.OBJECT_ID|| t.DATA_OBJECT_ID|| t.OBJECT_TYPE|| t.CREATED|| t.LAST_DDL_TIME|| t.TIMESTAMP|| t.status|| t.TEMPORARY|| t.GENERATED|| t.SECONDARY|| t.namespace|| t.EDITION_NAME as name from dba_objects t;
create table part_2(id int,name varchar2(1000)) partition by range(id) ( partition p1 values less than(1000), partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000), partition p5 values less than(5000), partition p6 values less than(6000), partition p7 values less than(7000), partition p8 values less than(8000), partition p9 values less than(9000), partition p10 values less than(10000), partition p11 values less than(11000), partition p12 values less than(12000), partition p13 values less than(13000), partition p14 values less than(14000), partition p15 values less than(15000), partition p16 values less than(16000), partition pmax values less than(maxvalue) ); insert into part_2 select * from part_1; create index part_1_idx on part_1 (id) local; -- part_1 建立本地索引 create index part_2_idx -- part_2建立全局索引 on part_2(id) GLOBAL partition by range(id) ( partition part_1 values less than(30000), partition part_2 values less than (MAXVALUE) ); begin dbms_stats.gather_table_stats( user,'PART_1',cascade=>TRUE ); end; begin dbms_stats.gather_table_stats( user,'PART_2',cascade=>TRUE ); end; --查询结果 select /*+ part_1 part_1_idx */ id from part_1 where id >10000 and id <30000;
select /*+ part_2 part_2_idx */ id from part_2 where id >10000 and id <30000;
--结论 分区索引性能不一定比全局索引高 |
总结
1.分区索引的目的在于数据的管理而非性能。
2.一个分区表上如果经常有DDL操作，将会导致全局索引无效，需要对索引重建，此时
创建分区索引更加适合。
1007
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
