有的分区表根据业务特点，热点数据只是最近几个月，甚至是当月数据，这时候在其他不活跃分区上建索引就显得浪费存储空间，对有的全局索引来说还会影响性能。oracle 12c中提供了只在部分分区上建索引的功能，不完全索引的出现，完美的解决了这个问题。
建一个分区表
| create table part1 (id int, code int,name varchar2(100)) indexing off partition by range (id) (partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (3000) indexing on ); |
oracle通过关键字indexing作为不完全索引的开关。在这个建表语句中，表级别的indexing默认是on，分区的indexing属性如果没有指定，那么会继承表级别参数。这里的这个建表语句，把表级别indexing关掉，显示指示分区P3 indexing 为on，那么其他三个分区p1,p2的indexing就是off了。这样就可以实现P3分区上建不完全索引了。
该关键字属性可以通过查看dba_tab_partitions的indexing得到
| SYS@cdbtest1(MING)> COL PARTITION_NAME FOR A30 SYS@cdbtest1(MING)> select PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND TABLE_NAME='PART1';
PARTITION_NAME INDE ------------------------------ ---- P1 OFF P2 OFF P3 ON |
可以看到只有P3是显示指定的ON，其他分区集成了表级别的indexing属性。
插入数据
| begin for i in 1 .. 2999 loop insert into part1 values(i,i,'mingshuo'); end loop ; commit; end; /
|
创建全局索引
| create index id_part1_global on part1(id) global indexing full; create index code_part1_global on part1(code) global indexing partial; SYS@cdbtest1(MING)> col INDEX_NAME for a30 SYS@cdbtest1(MING)> select index_name,partitioned,indexing from dba_indexes where index_name in ('ID_PART1_GLOBAL','CODE_PART1_GLOBAL');
INDEX_NAME PAR INDEXIN ------------------------------ --- ------- CODE_PART1_GLOBAL NO PARTIAL ID_PART1_GLOBAL NO FULL |
indexing full是默认的，虽然P1，P2分区indexing是OFF，但是如果在创建索引时指定了indexing full，那么还是会基于所有分区创建索引；
如果指定了indexing partial，那么就会按照分区的indexing属性，选择性的基于indexing为ON的分区上创建索引。
也就是说创建索引时指定的indexing参数优先级更高，会覆盖表定义中的indexing属性。
如果创建索引时不指定那么就按照默认的indexing full来生效，创建索引时指定indexing partial会按照表定义时的indexing属性。
这里，id_part1_global是完全索引，code_part1_global是不完全索引。
对此，利用执行计划印证一下。
| MING@ming(MING)> explain plan for select count(id) from part1;
Explained.
MING@ming(MING)> set line 200 MING@ming(MING)> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2604063405
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FAST FULL SCAN| ID_PART1_GLOBAL | 1 | 13 | 5 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
9 rows selected.
|
id列上是正常的全局索引，所以走了索引快速扫描。
code列上是不完全索引，count(code)看一下执行计划：
先删除之前id列上的索引避免干扰
drop index ID_PART1_GLOBAL;
| MING@ming(MING)> explain plan for select count(code) from part1;
Explained.
MING@ming(MING)> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1564279961
----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 549 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | VIEW | VW_TE_2 | 2 | 26 | 549 (1)| 00:00:01 | | | | 3 | UNION-ALL | | | | | | | | |* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PART1 | 1 | 26 | 1 (0)| 00:00:01 |3 | 3 | |* 5 | INDEX RANGE SCAN | CODE_PART1_GLOBAL | 1 | | 1 (0)| 00:00:01 | | | | 6 | PARTITION RANGE ITERATOR | | 1 | 26 | 548 (1)| 00:00:01 |1 | 2 | |* 7 | TABLE ACCESS FULL | PART1 | 1 | 26 | 548 (1)| 00:00:01 |1 | 2 | -----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter("PART1"."ID">=2000 AND "PART1"."ID"<3000) 5 - access("CODE">2600) 7 - filter("CODE">2600)
10 rows selected.
|
看执行计划就发现，P3分区确实采用了CODE_PART1_GLOBAL索引，其他分区采用全表扫描，两个结果集union all。
再来看分区索引
| drop index CODE_PART1_GLOBAL; create index id_part1_partial on part1(id) local indexing partial; create index code_part1_partial on part1(code) local indexing full;
SYS@cdbtest1(MING)> select index_name,partitioned,indexing from dba_indexes where index_name in ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL');
INDEX_NAME PAR INDEXIN ------------------------------ --- ------- ID_PART1_PARTIAL YES PARTIAL CODE_PART1_PARTIAL YES FULL
SYS@cdbtest1(MING)> col HIGH_VALUE for a15 SYS@cdbtest1(MING)> col PARTITION_NAME for a20 SYS@cdbtest1(MING)> col index_name for a25 SYS@cdbtest1(MING)> set line 150 SYS@cdbtest1(MING)> select INDEX_NAME,HIGH_VALUE,PARTITION_NAME,STATUS,LEAF_BLOCKS,NUM_ROWS from dba_ind_partitions where INDEX_NAME in ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL');
INDEX_NAME HIGH_VALUE PARTITION_NAME STATUS LEAF_BLOCKS NUM_ROWS ------------------------- --------------- -------------------- -------- ----------- ---------- CODE_PART1_PARTIAL 1000 P1 USABLE 3 999 CODE_PART1_PARTIAL 2000 P2 USABLE 3 1000 CODE_PART1_PARTIAL 3000 P3 USABLE 3 1000 ID_PART1_PARTIAL 1000 P1 UNUSABLE 0 0 ID_PART1_PARTIAL 2000 P2 UNUSABLE 0 0 ID_PART1_PARTIAL 3000 P3 USABLE 3 1000
6 rows selected.
|
通过status和num_rows两列可以看到，indexing full确实优先级更高，没有指定indexing的，按照表定义，id列上索引只在P3分区存在，code列索引每个分区都有。
下面也看一下不完全索引在执行计划中的表现：
| MING@ming(MING)> select count(code) from part1 where code<200;
Execution Plan ---------------------------------------------------------- Plan hash value: 186457639
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE ALL| | 1 | 13 | 3 (0)| 00:00:01 | 1 | 3 | |* 3 | INDEX RANGE SCAN | CODE_PART1_PARTIAL | 1 | 13 | 3 (0)| 00:00:01 | 1 | 3 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("CODE"<200)
Note ----- - dynamic statistics used: dynamic sampling (level=2)
MING@ming(MING)> select count(id) from part1 where id<200;
Execution Plan ---------------------------------------------------------- Plan hash value: 1660407118
------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 275 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE SINGLE| | 1 | 13 | 275 (1)| 00:00:01 | 1 | 1 | |* 3 | TABLE ACCESS FULL | PART1 | 1 | 13 | 275 (1)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("ID"<200)
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 6 recursive calls 4 db block gets 92 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
MING@ming(MING)> select count(id) from part1 where id>2900;
Execution Plan ---------------------------------------------------------- Plan hash value: 3675562320
------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE SINGLE| | 1 | 13 | 1 (0)| 00:00:01 | 3 | 3 | |* 3 | INDEX RANGE SCAN | ID_PART1_PARTIAL | 1 | 13 | 1 (0)| 00:00:01 | 3 | 3 | ------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("ID">2900)
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 11 recursive calls 4 db block gets 44 consistent gets 4 physical reads 0 redo size 543 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
MING@ming(MING)> select count(id) from part1 where id>1900;
Execution Plan ---------------------------------------------------------- Plan hash value: 712638347
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 277 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | VIEW | VW_TE_2 | 1422 | 18486 | 277 (1)| 00:00:01 | | | | 3 | UNION-ALL | | | | | | | | | 4 | PARTITION RANGE SINGLE| | 1 | 25 | 1 (0)| 00:00:01 | 3 | 3 | |* 5 | INDEX RANGE SCAN | ID_PART1_PARTIAL | 1 | 25 | 1 (0)| 00:00:01 | 3 | 3 | | 6 | PARTITION RANGE SINGLE| | 1421 | 35525 | 276 (1)| 00:00:01 | 2 | 2 | |* 7 | TABLE ACCESS FULL | PART1 | 1421 | 35525 | 276 (1)| 00:00:01 | 2 | 2 | --------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
5 - access("PART1"."ID">=2000 AND "PART1"."ID"<3000) 7 - filter("PART1"."ID">1900)
|
id>1900的时候，会跨越两个分区，P2走分区扫描，P3走不完全索引，两个结果集union all。
改变表的indexing属性
比如这里我需要P2，P3的indexing属性都是ON，P2定义的时候是OFF，可以通过下面的语句修改：
alter table part1 modify partition P2 indexing on;
实际生产环境下，如果因为之前不了解这种不完全索引，那么建表的时候不会按照我上面实验的建表语句中，表级别indexing设置为OFF，后续如果要用这个新特性的话，就需要去修改分区的indexing，这时候就需要上面的语句，把分区级别修改为OFF。
| MING@ming(MING)> COL PARTITION_NAME FOR A30 MING@ming(MING)> select PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND TABLE_NAME='PART1';
PARTITION_NAME INDE ------------------------------ ---- P1 OFF P2 ON P3 ON
|
这里要注意一下索引的有效性
如果之前将分区indexing从ON修改为OFF，那么之前存在的分区上的不完全索引会失效，换句话说，已经存在的不完全索引的usable或者unusable状态是跟indexing的ON或者OFF对应的。indexing由OFF变为ON，索引从unusable变为usable，indexing由ON变为OFF，索引从usable变为unusable。
对于索引的其他注意的点，回头再专门写一篇介绍。
总结：
1.表级别indexing默认是ON，分区会继承表级别的indexing属性
2.创建索引的时候indexing属性优先级高于表级别的indexing属性
3.对全局索引和本地索引都可以建不完全索引
4.已经存在的不完全索引的usable或者unusable状态是跟indexing的ON或者OFF对应的
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2285384/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31480688/viewspace-2285384/
445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
