oracle 分区索引
浏览数：36 /
时间：2015年06月12日
今天是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 索引技术》。
使用中还需要验证。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
