oracle 分区索引
来源：互联网 发布：网络教育的意义 编辑：程序博客网 时间：2024/06/06 00:45
今天是2014-01-22，这是最后一篇索引的学习笔记，另外除了之前介绍的索引外，还有函数索引，虚拟列索引。之前我也学习过分区的相关内容，笔记如下：
http://blog.csdn.net/rhys_oracle/article/details/8944705
在线重定义分区：
http://blog.csdn.net/rhys_oracle/article/details/12840861
由于今天准备在打算综合学习一下分区索引，在此记录一下学习笔记。
可以创建本地分区索引、全局分区索引，一般推荐创建本地分区索引，因为维护方便。本地分区索引只适用于分区表，全局分区索引可以是分区表，也可以是非分区表，另外还可以在分区表中创建非分区索引。创建分区表和分区索引的目的就是平衡I/0，提高查询性能减少热块的产生，但是对于数据仓库和oltp类型分区的创建也是需要判断创建的可行性。其中在《编程艺术》这本书中也有详细的介绍。
创建本地分区索引，使用关键字local：
eg；
SQL> select index_name,table_name,partitioning_type from user_part_indexes where table_name='EMP';no rows selectedSQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1SQL> select index_name,table_name,partitioning_type from user_part_indexes where table_name='EMP';no rows selectedSQL> create index emp_part_idx1 on emp(empno)local;Index created.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 b.high_value, 5 status 6 from user_part_indexes a 7 left join user_ind_partitions b 8 on a.index_name = b.index_name 9 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME HIGH_VALUE STATUS------------------------------ --------- ------------------------------ -------------------- --------EMP RANGE EMP_PART_IDX1 20 USABLEEMP RANGE EMP_PART_IDX1 30 USABLEEMP RANGE EMP_PART_IDX1 40 USABLEEMP RANGE EMP_PART_IDX1 MAXVALUE USABLESQL>
这样就在分区表emp中创建了本地分区索引，另外还可以在分区创建索引中指定每个分区索引所在表空间。
另外在创建主键约束的分区索引要注意，一般先创建主键分区索引，在创建主键约束，因为这样在把约束disable的时候才不会删除本地分区索引。
eg：
SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats 8 from user_part_indexes a 9 left join user_ind_partitions b 10 on a.index_name = b.index_name where a.table_name = 'EMP'; 11 no rows selectedSQL> alter table emp add constraint emp_cons_primary primary key(empno);Table altered.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats 8 from user_part_indexes a 9 left join user_ind_partitions b 10 on a.index_name = b.index_name 11 where a.table_name = 'EMP';no rows selectedSQL> create index emp_part_idx1 on emp(empno);create index emp_part_idx1 on emp(empno) *ERROR at line 1:ORA-01408: such column list already indexedSQL> select index_name,index_type,table_name, PARTITIONED from user_indexes where table_name='EMP';INDEX_NAME INDEX_TYPE TABLE_NAME PAR------------------------------ --------------------------- ------------------------------ ---EMP_CONS_PRIMARY NORMAL EMP NOSQL>
SQL> alter table emp drop constraint emp_cons_primary;Table altered.SQL> select index_name,index_type,table_name, PARTITIONED from user_indexes where table_name='EMP';no rows selected
可见在如果先创建主键约束，那么会自动创建非分区索引，当删除或禁用该约束是底层索引会自动删除。再次创建本地分区索引将出现错误。如果先创建本地分区索引，在创建主键约束会是怎么样呢？
eg：
SQL> create unique index emp_part_idx1 on emp(empno)local;create unique index emp_part_idx1 on emp(empno)local *ERROR at line 1:ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE indexSQL> select a.table_name, 2 a.partition_name, 3 a.high_value, a.composite, 4 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1SQL> create unique index emp_part_idx1 on emp(empno,deptno) local;Index created.SQL> SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats 8 from user_part_indexes a 9 left join user_ind_partitions b 10 on a.index_name = b.index_name 11 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO------------------------------ --------- ------------------------------ ------ -------------------- -------- ---EMP RANGE EMP_PART_IDX1 LOCAL 20 USABLE NOEMP RANGE EMP_PART_IDX1 LOCAL 30 USABLE NOEMP RANGE EMP_PART_IDX1 LOCAL 40 USABLE NOEMP RANGE EMP_PART_IDX1 LOCAL MAXVALUE USABLE NOSQL> select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';no rows selectedSQL> alter table emp add constraint emp_p primary key (empno,deptno);Table altered.SQL> select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';CONSTRAINT_NAME C TABLE_NAME------------------------------ - ------------------------------EMP_P P EMPSQL> alter table emp disable constraint emp_p;Table altered.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats from user_part_indexes a 8 9 left join user_ind_partitions b 10 on a.index_name = b.index_name 11 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO------------------------------ --------- ------------------------------ ------ -------------------- -------- ---EMP RANGE EMP_PART_IDX1 LOCAL 20 USABLE NOEMP RANGE EMP_PART_IDX1 LOCAL 30 USABLE NOEMP RANGE EMP_PART_IDX1 LOCAL 40 USABLE NOEMP RANGE EMP_PART_IDX1 LOCAL MAXVALUE USABLE NOSQL> select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';CONSTRAINT_NAME C TABLE_NAME------------------------------ - ------------------------------EMP_P P EMPSQL> select constraint_name,constraint_type,status,table_name from user_constraints where table_name='EMP';CONSTRAINT_NAME C STATUS TABLE_NAME------------------------------ - -------- ------------------------------EMP_P P DISABLED EMPSQL>
从上面可以知道，在创建唯一索引的时候需要指定分区列，另外先创建索引在创建约束有很到好处，就是当disable的时候索引依然存在。往往重建索引消耗太多的资源，产生一些等待问题。
创建全局分区索引：
注意，全局索引只支持范围分区
eg：
SQL> create table emp_part( 2 empno number(4) not null, 3 ename varchar2(10), 4 job varchar2(9), 5 mgr number(4), 6 hiredate date, 7 sal number(7,2), 8 comm number(7,2), 9 deptno number(2) 10 ) 11 partition by range (deptno) 12 ( 13 partition part_1 values less than(10) tablespace test, 14 partition part_2 values less than(20) tablespace test, 15 partition part_3 values less than(30) tablespace test, 16 partition part_4 values less than(40) tablespace test, 17 partition part_5 values less than(maxvalue) 18 );Table created.SQL> SQL> create index emp_glb_idx1 on emp_part(deptno) 2 global 3 partition by range(deptno) 4 ( 5 partition part_idx_1 values less than(10) tablespace test, 6 partition part_idx_2 values less than(20) tablespace test, 7 partition part_idx_3 values less than(30) tablespace test, 8 partition part_idx_4 values less than(40) tablespace test, 9 partition part_idx_5 values less than (maxvalue) tablespace test); 10 Index created.SQL> SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 5 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats 8 from user_part_indexes a 9 left join user_ind_partitions b 10 on a.index_name = b.index_name 11 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO------------------------------ --------- ------------------------------ ------ -------------------- -------- ---EMP_PART RANGE EMP_GLB_IDX1 GLOBAL MAXVALUE USABLE NOEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 40 USABLE NOEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 30 USABLE NOEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 20 USABLE NOEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 10 USABLE NOSQL>
维护分区表索引：
添加分区：
SQL> alter table emp_part add partition part_6 values less than(50);alter table emp_part add partition part_6 values less than(50) *ERROR at line 1:ORA-14074: partition bound must collate higher than that of the last partitionSQL> alter table emp_part drop partition part_5;Table altered.SQL> alter table emp_part add partition part_5 values less than(50);Table altered.SQL> select a.index_name,b.partition_name,B.STATUS from user_indexes a join user_ind_partitions b on a.index_name=b.index_name where A.TABLE_NAME='EMP_PART';INDEX_NAME PARTITION_NAME STATUS------------------------------ -------------------- --------EMP_GLB_IDX1 PART_IDX_5 USABLEEMP_GLB_IDX1 PART_IDX_4 USABLEEMP_GLB_IDX1 PART_IDX_3 USABLEEMP_GLB_IDX1 PART_IDX_2 USABLEEMP_GLB_IDX1 PART_IDX_1 USABLESQL> SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats 8 from user_part_indexes a 9 left join user_ind_partitions b 10 on a.index_name = b.index_name where a.table_name = 'EMP_PART'; 11 TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO------------------------------ --------- ------------------------------ ------ -------------------- -------- ---EMP_PART RANGE EMP_GLB_IDX1 GLOBAL MAXVALUE USABLE YESEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 40 USABLE YESEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 30 USABLE YESEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 20 USABLE YESEMP_PART RANGE EMP_GLB_IDX1 GLOBAL 10 USABLE YESSQL>
可见添加分区对现存的索引无任何影响。但新分区却没索引。
总结一下吧。：
以上表格内容摘自《oracle 索引技术》。
使用中还需要验证。
截断分区操作：
SQL> CREATE index emp_idx1 on emp(empno,deptno) 2 local 3 ( 4 partition index_1 tablespace test, 5 SQL> SQL> CREATE index emp_idx1 on emp(empno,deptno) 2 local 3 ( 4 partition index_1 tablespace test, 5 partition index_2 tablespace test, 6 partition index_3 tablespace test, 7 partition index_4 tablespace test 8 );Index created.SQL> select a.table_name, a.partition_name, 2 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1SQL> select a.table_name, a.partitioning_type, 2 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO INDEX_1EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4SQL> SELECT INDEX_NAME, NULL PARTITION_NAME, STATUS 2 FROM USER_INDEXES 3 WHERE TABLE_NAME = 'EMP' 4 AND PARTITIONED = 'NO' 5 UNION 6 SELECT INDEX_NAME, PARTITION_NAME, STATUS 7 FROM USER_IND_PARTITIONS 8 WHERE INDEX_NAME IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'EMP') 9 10 ORDER BY 1, 2, 3;INDEX_NAME PARTITION_NAME STATUS------------------------------ -------------------- --------EMP_IDX1 INDEX_1 USABLEEMP_IDX1 INDEX_2 USABLEEMP_IDX1 INDEX_3 USABLEEMP_IDX1 INDEX_4 USABLESQL> select * from emp partition(part_1);no rows selectedSQL> select * from emp partition(part_2); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20SQL> alter table emp truncate partition part_2;Table truncated.SQL> select * from emp partition(part_2);no rows selectedSQL> SELECT INDEX_NAME, NULL PARTITION_NAME, STATUS 2 FROM USER_INDEXES 3 WHERE TABLE_NAME = 'EMP' 4 AND PARTITIONED = 'NO' 5 UNIONSELECT INDEX_NAME, PARTITION_NAME, STATUS 6 7 FROM USER_IND_PARTITIONS WHERE INDEX_NAME IN 8 9 (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'EMP') 10 ORDER BY 1, 2, 3;INDEX_NAME PARTITION_NAME STATUS------------------------------ -------------------- --------EMP_IDX1 INDEX_1 USABLEEMP_IDX1 INDEX_2 USABLEEMP_IDX1 INDEX_3 USABLEEMP_IDX1 INDEX_4 USABLESQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO INDEX_1EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 5 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO
可以看到本地分区索引在截断分区的时候对索引无影响。
SQL> CREATE INDEX EMP_PART_GLB_IDX1 ON EMP_PART(EMPNO) 2 GLOBAL 3 PARTITION BY RANGE(EMPNO) 4 ( 5 PARTITION INDEX_GAB_1 VALUES LESS THAN(500) TABLESPACE TEST, 6 PARTITION INDEX_GAB_2 VALUES LESS THAN(1000) TABLESPACE TEST, 7 PARTITION INDEX_GAB_3 VALUES LESS THAN(2000) TABLESPACE TEST, 8 PARTITION INDEX_GAB_4 VALUES LESS THAN(2500) TABLESPACE TEST, 9 PARTITION INDEX_GAB_5 VALUES LESS THAN(MAXVALUE) 10 );Index created.SQL> SQL> SQL> SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE USABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 USABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1SQL> SELECT INDEX_NAME, NULL PARTITION_NAME, STATUS 2 FROM USER_INDEXES 3 WHERE TABLE_NAME = 'EMP_PART' 4 AND PARTITIONED = 'NO' 5 UNION 6 SELECT INDEX_NAME, PARTITION_NAME, STATUS 7 FROM USER_IND_PARTITIONS 8 WHERE INDEX_NAME IN 9 (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'EMP_PART') 10 ORDER BY 1, 2, 3;INDEX_NAME PARTITION_NAME STATUS------------------------------ -------------------- --------EMP_PART_GLB_IDX1 INDEX_GAB_1 USABLEEMP_PART_GLB_IDX1 INDEX_GAB_2 USABLEEMP_PART_GLB_IDX1 INDEX_GAB_3 USABLEEMP_PART_GLB_IDX1 INDEX_GAB_4 USABLEEMP_PART_GLB_IDX1 INDEX_GAB_5 USABLESQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, b.partitioning_type, 5 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 5 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1SQL> select * from emp_part partition(part_1);no rows selectedSQL> select * from emp_part partition(part_3); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20SQL> SQL> alter table emp_part truncate partition part_3;Table truncated.SQL> SELECT INDEX_NAME, NULL PARTITION_NAME, STATUS 2 FROM USER_INDEXES 3 WHERE TABLE_NAME = 'EMP_PART' 4 AND PARTITIONED = 'NO' 5 UNION 6 SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS 7 8 WHERE INDEX_NAME IN 9 (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'EMP_PART') 10 ORDER BY 1, 2, 3;INDEX_NAME PARTITION_NAME STATUS------------------------------ -------------------- --------EMP_PART_GLB_IDX1 INDEX_GAB_1 UNUSABLEEMP_PART_GLB_IDX1 INDEX_GAB_2 UNUSABLEEMP_PART_GLB_IDX1 INDEX_GAB_3 UNUSABLEEMP_PART_GLB_IDX1 INDEX_GAB_4 UNUSABLEEMP_PART_GLB_IDX1 INDEX_GAB_5 UNUSABLESQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 UNUSABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4SQL>
可以看到在全局索引中截断分区的时候整全局分区索引就会失效。
重建全局索引，有两种办法，一种是直接删除全局索引在create index，另外一种是对全局索引中的每个分区进行重建。但是不能对全局索引一下重建。
eg：
SQL> alter index emp_PART_GLB_IDX1 rebuild partition INDEX_GAB_1 parallel(degree 4);Index altered.SQL> alter index emp_PART_GLB_IDX1 rebuild partition INDEX_GAB_2 parallel(degree 4);Index altered.SQL> alter index emp_PART_GLB_IDX1 rebuild partition INDEX_GAB_3 parallel(degree 4);Index altered.SQL> alter index emp_PART_GLB_IDX1 rebuild partition INDEX_GAB_4 parallel(degree 4);Index altered.SQL> alter index emp_PART_GLB_IDX1 rebuild partition INDEX_GAB_5 parallel(degree 4);Index altered.SQL> alter index emp_part_glb_idx1 rebuild parallel(degree 4);alter index emp_part_glb_idx1 rebuild parallel(degree 4) *ERROR at line 1:ORA-14086: a partitioned index may not be rebuilt as a wholeSQL>
合并分区：
QL> select * from emp_part partition(part_2); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10SQL> select * from emp_part partition(part_3); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20SQL> SQL> alter table emp_part merge partitions part_2,part_3 into partition part_3; Table altered.SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 4 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 4 VALID DEPTNO 1SQL> select * from emp_part partition(part_3); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 208 rows selected.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 UNUSABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4
可以看到在合并分区的时候全局索引就失效了，那么可以使用update indexes 直接rebuild索引：
eg：
SQL> SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 5 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, b.global_stats, 7 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE USABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 USABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1SQL> SQL> insert into emp_part select * from scott.emp;14 rows created.SQL> commit;Commit complete.SQL> select * from emp_part partition(part_2); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10SQL> select * from emp_part partition(part_3); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20SQL> alter table emp_part merge partitions part_2,part_3 into partition part_3 update indexes;Table altered.SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 4 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 4 VALID DEPTNO 1SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a left join user_ind_partitions b 10 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 USABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE USABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1SQL>
那么对于本地分区索引在合并分区的时候会是怎么样的呢？
eg：
SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, a.composite, 4 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, b.high_value, 5 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO INDEX_1EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4SQL> alter table emp merge partitions part_1,part_2 into partition part_2;Table altered.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, a.locality, 4 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2SQL>
可以看到本地索引自动维护。
拆分分区情况是怎么样的呢？
eg：
SQL> alter table emp split partition part_2 at (20) into (partition part_1,partition part_2);Table altered.SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, b.status, 7 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO PART_1EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2SQL>
可见本地分区索引在拆分分区的时候自动维护索引。可是与表格中本地分区索引描述不符，因为我使用的是12.2.0.4版本，可能到该版本该进了吧。
对于全局分区索引是怎么样的呢？
eg：
SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 4 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 4 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 4 VALID DEPTNO 1SQL> SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b on a.index_name = b.index_name 11 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 USABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE USABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1SQL> alter table emp_part split partition part_3 at(20) into (partition part_2,part_3);alter table emp_part split partition part_3 at(20) into (partition part_2,part_3) *ERROR at line 1:ORA-14004: missing PARTITION keywordSQL> alter table emp_part split partition part_3 at(20) into (partition part_2,partition part_3);Table altered.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 UNUSABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 5 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1SQL>
可见全局索引在拆分的时候全失效了。同样使用update indexex可以自动rebuild。
交换分区：
对于全局索引失效
eg
:
SQL> SQL> ALTER TABLE EMP_PART EXCHANGE PARTITION PART_4 WITH TABLE EMP_P_TEST;Table altered.SQL> select a.table_name, 2 a.partition_name, 3 a.high_value, 4 a.composite, 5 b.partitioning_type, 6 b.partition_count, 7 b.status, 8 c.column_name, 9 c.column_position 10 from user_tab_partitions a, user_part_tables b, user_part_key_columns c 11 where a.table_name = b.table_name 12 and b.table_name = c.name and b.table_name='EMP_PART';TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------EMP_PART PART_5 MAXVALUE NO RANGE 5 VALID DEPTNO 1EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP_PART';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 UNUSABLE NO INDEX_GAB_3EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2SQL>
本地索引自动维护：
eg：
SQL> select * from emp_text; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30SQL> select * from emp partition(part_4);no rows selectedSQL> SQL> SQL> SQL> SQL> alter table emp exchange partition part_4 with table emp_text;Table altered.SQL> select * from emp_text;no rows selectedSQL> select * from emp partition(part_4); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, 4 a.locality, 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b on a.index_name = b.index_name 11 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO PART_1EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4EMP RANGE EMP_IDX1 LOCAL 40 UNUSABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2SQL> alter index EMP_IDX1 rebuild partition INDEX_3 online;Index altered.SQL> select a.table_name, 2 a.partitioning_type, 3 a.index_name, a.locality, 4 5 b.high_value, 6 b.status, 7 b.global_stats, 8 b.partition_name 9 from user_part_indexes a 10 left join user_ind_partitions b 11 on a.index_name = b.index_name 12 where a.table_name = 'EMP';TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO PART_1EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2SQL>
另外还有移动分区。
综上 所示，本地分区索引有比全局分区索引好多好处。
对于索引的维护，说道这吧。
0 0
- Oracle表分区、索引分区
- ORACLE全局索引、分区索引
- Oracle 分区索引详解
- Oracle 创建索引分区
- Oracle索引分区杂谈
- Oracle分区索引
- Oracle索引分区杂谈
- Oracle分区索引
- Oracle分区与索引
- Oracle 创建索引分区
- ORACLE分区表、分区索引
- oracle 分区索引
- ORACLE分区表、分区索引
- oracle分区与索引
- oracle 分区索引
- oracle 分区表，分区索引
- ORACLE 分区索引
- ORACLE分区表、分区索引
- 基于SSL协议的安全网络通信程序
- poj 2378 Tree Cutting(树形DP，删点使得独立的部分结点数不超过n/2)
- 第二周：构造器（写两个Student类）
- 删除设备例程
- java设计模式之组合模式
- oracle 分区索引
- POJ 1438 混合图定定向为强连通图 双连通
- Java中的一些语法格式
- pyqt4制作简单爬虫
- 基本面试题
- android sqlite 启用外键
- 字符串比较：equals()方法
- 背包问题
- 【Android资料免积分下载】大量教程+工具+源码下载地址汇总
