今天是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 selected
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';
TABLE_NAME PARTITION_NAME HIGH_VALUE COM PARTITION PARTITION_COUNT STATUS COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- -------------------- --- --------- --------------- -------- ------------------------------ ---------------
EMP PART_5 MAXVALUE NO RANGE 4 VALID DEPTNO 1
EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1
EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1
SQL> select index_name,table_name,partitioning_type from user_part_indexes where table_name='EMP';
no rows selected
SQL> 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 USABLE
EMP RANGE EMP_PART_IDX1 30 USABLE
EMP RANGE EMP_PART_IDX1 40 USABLE
EMP RANGE EMP_PART_IDX1 MAXVALUE USABLE
SQL>
这样就在分区表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 selected
SQL> 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 selected
SQL> 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 indexed
SQL> 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 NO
SQL> 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 index
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 1
EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1
EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1
SQL> 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 NO
EMP RANGE EMP_PART_IDX1 LOCAL 30 USABLE NO
EMP RANGE EMP_PART_IDX1 LOCAL 40 USABLE NO
EMP RANGE EMP_PART_IDX1 LOCAL MAXVALUE USABLE NO
SQL> select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';
no rows selected
SQL> 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 EMP
SQL> 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 NO
EMP RANGE EMP_PART_IDX1 LOCAL 30 USABLE NO
EMP RANGE EMP_PART_IDX1 LOCAL 40 USABLE NO
EMP RANGE EMP_PART_IDX1 LOCAL MAXVALUE USABLE NO
SQL> select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
EMP_P P EMP
SQL> 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 EMP
SQL>
从上面可以知道，在创建唯一索引的时候需要指定分区列，另外先创建索引在创建约束有很到好处，就是当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 1
EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1
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_PART';
TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO
------------------------------ --------- ------------------------------ ------ -------------------- -------- ---
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL MAXVALUE USABLE NO
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 40 USABLE NO
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 30 USABLE NO
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 20 USABLE NO
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 10 USABLE NO
SQL>
维护分区表索引：
添加分区：
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 partition
SQL> 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 USABLE
EMP_GLB_IDX1 PART_IDX_4 USABLE
EMP_GLB_IDX1 PART_IDX_3 USABLE
EMP_GLB_IDX1 PART_IDX_2 USABLE
EMP_GLB_IDX1 PART_IDX_1 USABLE
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
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 YES
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 40 USABLE YES
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 30 USABLE YES
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 20 USABLE YES
EMP_PART RANGE EMP_GLB_IDX1 GLOBAL 10 USABLE YES
SQL>
可见添加分区对现存的索引无任何影响。但新分区却没索引。
总结一下吧。：
| 表级分区操作 | 非分区索引 | 本地分区索引 | 全局分区索引 |
| 添加分区 | 索引不受影响 | 索引不受影响 | 索引不受影响 |
| 拆分分区 | 整个索引标记为unusable | 受拆分操作影响的分区上的索引标记为unusable | 索引的所有分区都标记为unusable |
| 移动分区 | 整个索引标记为unusable | 被移动的分区上的索引被标记为unusable | 索引的所有分区都标记为unusable |
| 交换分区 | 整个索引标记为unusable | 被交换的分区上的索引被标记为unusable | 索引的所有分区都标记为unusable |
| 合并分区 | 整个索引标记为unusable | 受合并操作影响的分区上的索引被标记为unusable | 索引的所有分区都标记为unusable |
| 截断分区 | 整个索引标记为unusable | 索引不受影响 | 索引的所有分区都标记为unusable |
| 删除分区 | 整个索引标记为unusable | 本地分区索引被删除，其余分区索引不受影响 | 索引的所有分区都标记为unusable |
| 令数据只读 | 不可能实现，除非整改表是静态的（表上没有dml操作） | 通过表空间隔离可以令分区级别索引数据只读 | 理论上可以令分区级别索引数据只读，实际上无法实现除非整个表是静态的。 |
以上表格内容摘自《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 1
EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1
EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1
SQL> 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_2
EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3
EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO INDEX_1
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
SQL> 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 USABLE
EMP_IDX1 INDEX_2 USABLE
EMP_IDX1 INDEX_3 USABLE
EMP_IDX1 INDEX_4 USABLE
SQL> select * from emp partition(part_1);
no rows selected
SQL> 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 20
SQL> alter table emp truncate partition part_2;
Table truncated.
SQL> select * from emp partition(part_2);
no rows selected
SQL> SELECT INDEX_NAME, NULL PARTITION_NAME, STATUS
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP'
4 AND PARTITIONED = 'NO'
5 UNION
SELECT 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 USABLE
EMP_IDX1 INDEX_2 USABLE
EMP_IDX1 INDEX_3 USABLE
EMP_IDX1 INDEX_4 USABLE
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';
TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME
------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------
EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2
EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3
EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO INDEX_1
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
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 1
EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1
EMP_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_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 USABLE NO INDEX_GAB_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1
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
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 USABLE
EMP_PART_GLB_IDX1 INDEX_GAB_2 USABLE
EMP_PART_GLB_IDX1 INDEX_GAB_3 USABLE
EMP_PART_GLB_IDX1 INDEX_GAB_4 USABLE
EMP_PART_GLB_IDX1 INDEX_GAB_5 USABLE
SQL> 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 1
EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1
SQL> select * from emp_part partition(part_1);
no rows selected
SQL> 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 20
SQL>
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 UNUSABLE
EMP_PART_GLB_IDX1 INDEX_GAB_2 UNUSABLE
EMP_PART_GLB_IDX1 INDEX_GAB_3 UNUSABLE
EMP_PART_GLB_IDX1 INDEX_GAB_4 UNUSABLE
EMP_PART_GLB_IDX1 INDEX_GAB_5 UNUSABLE
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 500 UNUSABLE NO INDEX_GAB_1
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 UNUSABLE NO INDEX_GAB_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4
SQL>
可以看到在全局索引中截断分区的时候整全局分区索引就会失效。
重建全局索引，有两种办法，一种是直接删除全局索引在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 whole
SQL>
合并分区：
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 10
SQL> 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 20
SQL>
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 1
EMP_PART PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 4 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 4 VALID DEPTNO 1
SQL> 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 20
8 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_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1
EMP_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 1
EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1
SQL> 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_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2000 USABLE NO INDEX_GAB_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1
SQL>
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 10
SQL> 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 20
SQL> 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 1
EMP_PART PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 4 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 4 VALID DEPTNO 1
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
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_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE USABLE NO INDEX_GAB_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1
SQL>
那么对于本地分区索引在合并分区的时候会是怎么样的呢？
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 1
EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1
EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1
SQL> 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_2
EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3
EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO INDEX_1
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
SQL> 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_3
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2
SQL>
可以看到本地索引自动维护。
拆分分区情况是怎么样的呢？
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 1
EMP PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP PART_2 30 NO RANGE 4 VALID DEPTNO 1
EMP PART_1 20 NO RANGE 4 VALID DEPTNO 1
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';
TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME
------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------
EMP RANGE EMP_IDX1 LOCAL 40 USABLE NO INDEX_3
EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO PART_1
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2
SQL>
可见本地分区索引在拆分分区的时候自动维护索引。可是与表格中本地分区索引描述不符，因为我使用的是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 1
EMP_PART PART_4 40 NO RANGE 4 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 4 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 4 VALID DEPTNO 1
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
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_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 USABLE NO INDEX_GAB_4
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE USABLE NO INDEX_GAB_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 USABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 USABLE NO INDEX_GAB_1
SQL> 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 keyword
SQL> 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_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1
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 1
EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1
SQL>
可见全局索引在拆分的时候全失效了。同样使用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 1
EMP_PART PART_4 40 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_3 30 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_2 20 NO RANGE 5 VALID DEPTNO 1
EMP_PART PART_1 10 NO RANGE 5 VALID DEPTNO 1
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_3
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 2500 UNUSABLE NO INDEX_GAB_4
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL MAXVALUE UNUSABLE NO INDEX_GAB_5
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 500 UNUSABLE NO INDEX_GAB_1
EMP_PART RANGE EMP_PART_GLB_IDX1 GLOBAL 1000 UNUSABLE NO INDEX_GAB_2
SQL>
本地索引自动维护：
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 30
SQL> select * from emp partition(part_4);
no rows selected
SQL>
SQL>
SQL>
SQL>
SQL> alter table emp exchange partition part_4 with table emp_text;
Table altered.
SQL> select * from emp_text;
no rows selected
SQL> 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 30
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';
TABLE_NAME PARTITION INDEX_NAME LOCALI HIGH_VALUE STATUS GLO PARTITION_NAME
------------------------------ --------- ------------------------------ ------ -------------------- -------- --- --------------------
EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO PART_1
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
EMP RANGE EMP_IDX1 LOCAL 40 UNUSABLE NO INDEX_3
EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2
SQL> 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_3
EMP RANGE EMP_IDX1 LOCAL 20 USABLE NO PART_1
EMP RANGE EMP_IDX1 LOCAL MAXVALUE USABLE NO INDEX_4
EMP RANGE EMP_IDX1 LOCAL 30 USABLE NO INDEX_2
SQL>
另外还有移动分区。
综上 所示，本地分区索引有比全局分区索引好多好处。
对于索引的维护，说道这吧。
301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
