联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORACLE 12C Partial Global/Local Indexes for Partitioned Tables
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
以前我有个想法，我只想对其中的某个或者某几个分区上创建索引，其他分区不想创建，在12C之前的版本，无论是Local还是Global index，都不能实现该需求，但是从ORACLE 12C开始引进了Partial Global/Local Indexes for Partitioned Tables，解决了该问题，可以在指定的分区上创建本地索引或者全局索引，主要语法是在表或者分区,子分区级别设置[INDEXING { ON | OFF }]
创建测试表
CDB_PDB@CHF> SELECT BANNER FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production PL/SQL Release 12.1.0.1.0 - Production CORE 12.1.0.1.0 Production TNS for 64-bit Windows: Version 12.1.0.1.0 - Production NLSRTL Version 12.1.0.1.0 - Production CDB_PDB@CHF> CREATE TABLE xifenfei_orders ( 2 order_id NUMBER(12), 3 order_address varchar2(100), 4 order_mode VARCHAR2(20)) 5 INDEXING OFF 6 PARTITION BY RANGE (order_id) 7 (PARTITION ord_p1 VALUES LESS THAN (100) INDEXING ON, 8 PARTITION ord_p2 VALUES LESS THAN (200) INDEXING OFF, 9 PARTITION ord_p3 VALUES LESS THAN (300) INDEXING ON, 10 PARTITION ord_p4 VALUES LESS THAN (400), 11 PARTITION ord_p5 VALUES LESS THAN (500)); 表已创建。
创建LOCAL INDEX
CDB_PDB@CHF> CREATE INDEX ind_lox on xifenfei_orders(order_address) LOCAL INDEXING PARTIAL; 索引已创建。 CDB_PDB@CHF> COL INDEX_NAME FOR A10 CDB_PDB@CHF> COL PARTITION_NAME FOR A15 CDB_PDB@CHF> select index_name, partition_name,STATUS 2 from user_ind_partitions 3 where index_name = 'IND_LOX'; INDEX_NAME PARTITION_NAME STATUS ---------- --------------- -------- IND_LOX ORD_P5 UNUSABLE IND_LOX ORD_P4 UNUSABLE IND_LOX ORD_P3 USABLE IND_LOX ORD_P2 UNUSABLE IND_LOX ORD_P1 USABLE --设置INDEXING OFF对应的index 状态为UNUSABLE，分区继承表 CDB_PDB@CHF> select partition_name,indexing from user_tab_partitions where table_name='XIFENFEI_ORDERS'; PARTITION_NAME INDE --------------- ---- ORD_P5 OFF ORD_P4 OFF ORD_P3 ON ORD_P2 OFF ORD_P1 ON --因为segment 延迟，无数据，所以无分区和索引记录 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX'; 未选定行 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS'; 未选定行 CDB_PDB@CHF> begin 2 for i in 1 .. 449 loop 3 insert into xifenfei_orders 4 values 5 (i,'www.xifenfei.com'||i,'惜分飞'||i); 6 end loop; 7 commit; 8 end; 9 / PL/SQL 过程已成功完成。 --插入记录后，分区表有相关记录 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME,blocks FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS'; PARTITION_NAME SEGMENT_NAME BLOCKS --------------- --------------- ---------- ORD_P5 XIFENFEI_ORDERS 1024 ORD_P4 XIFENFEI_ORDERS 1024 ORD_P3 XIFENFEI_ORDERS 1024 ORD_P2 XIFENFEI_ORDERS 1024 ORD_P1 XIFENFEI_ORDERS 1024 --注意:这里只有user_tab_partitions.indexing为on的有记录,也就是说，至于这些分区的索引被创建，其他的未被创建 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX'; PARTITION_NAME SEGMENT_NAME --------------- --------------- ORD_P1 IND_LOX ORD_P3 IND_LOX
分析执行计划
CDB_PDB@CHF> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'XIFENFEI_ORDERS',CASCADE=>TRUE);
PL/SQL 过程已成功完成。
CDB_PDB@CHF> SET AUTOT TRACE
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS WHERE ORDER_ADDRESS='www.xifenfei.com99';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2800545636
------------------------------------------------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
Pstop |
------------------------------------------------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 30 | 40 (0)| 00:00:01 | |
|
| 1 | VIEW | VW_TE_2 | 2 | 24 | 40 (0)| 00:00:01 | |
|
| 2 | UNION-ALL | | | | | | |
|
| 3 | PARTITION RANGE OR | | 1 | 34 | 1 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 34 | 1 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|* 5 | INDEX RANGE SCAN | IND_LOX | 1 | | 1 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
| 6 | PARTITION RANGE OR | | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|* 7 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
------------------------------------------------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("XIFENFEI_ORDERS"."ORDER_ID"<100 OR "XIFENFEI_ORDERS"."ORDER_ID">=200 AND
"XIFENFEI_ORDERS"."ORDER_ID"<300)
5 - access("ORDER_ADDRESS"='www.xifenfei.com99')
7 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND ("XIFENFEI_ORDERS"."ORDER_ID">=300 AND
"XIFENFEI_ORDERS"."ORDER_ID"<500 OR "XIFENFEI_ORDERS"."ORDER_ID"<200 AND "XIFENFEI_ORDERS"."ORDER_ID">=100))
--这里可以看到，当我们没有指定分区范围的时候，显示的执行计划是有分区index的部分直接走index，没有分区index的部分是扫描分区
统计信息
----------------------------------------------------------
34 recursive calls
0 db block gets
120 consistent gets
1 physical reads
0 redo size
347 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com499' and order_id>200 and ord
er_id<300;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3337708912
------------------------------------------------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| P
stop |
------------------------------------------------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 34 | 2 (0)| 00:00:01 | |
|
| 1 | PARTITION RANGE SINGLE | | 1 | 34 | 2 (0)| 00:00:01 | 3 |
3 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 34 | 2 (0)| 00:00:01 | 3 |
3 |
|* 3 | INDEX RANGE SCAN | IND_LOX | 1 | | 1 (0)| 00:00:01 | 3 |
3 |
------------------------------------------------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDER_ID">200)
3 - access("ORDER_ADDRESS"='www.xifenfei.com499')
--指定分区查询，可以明确的看到，该sql直接使用了分区索引
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
347 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com499' and order_id>300 and ord
er_id<400;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2072227240
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 14 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 34 | 14 (0)| 00:00:01 | 4 | 4 |
|* 2 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 14 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDER_ADDRESS"='www.xifenfei.com499' AND "ORDER_ID">300)
--当指定的分区无index之时，直接判断走全表扫描
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
347 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
创建Global index
CDB_PDB@CHF> ALTER INDEX ind_lox INVISIBLE; 索引已更改。 CDB_PDB@CHF> CREATE INDEX IND_G_LOX ON XIFENFEI_ORDERS(ORDER_ADDRESS) Global INDEXING PARTIAL; 索引已创建。 CDB_PDB@CHF> select index_name,indexing from dba_indexes where index_name='IND_G_LOX'; INDEX_NAME INDEXIN ---------- ------- IND_G_LOX PARTIAL
执行计划
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1912382893
------------------------------------------------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart|
Pstop |
------------------------------------------------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 30 | 41 (0)| 00:00:01 | |
|
| 1 | VIEW | VW_TE_2 | 2 | 24 | 41 (0)| 00:00:01 | |
|
| 2 | UNION-ALL | | | | | | |
|
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 34 | 2 (0)| 00:00:01 | ROWID |
ROWID |
|* 4 | INDEX RANGE SCAN | IND_G_LOX | 1 | | 1 (0)| 00:00:01 | |
|
| 5 | PARTITION RANGE OR | | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|* 6 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
------------------------------------------------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."ORDER_ID"<100 OR "T"."ORDER_ID">=200 AND "T"."ORDER_ID"<300)
4 - access("ORDER_ADDRESS"='www.xifenfei.com99')
6 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND ("T"."ORDER_ID">=300 AND "T"."ORDER_ID"<500 OR
"T"."ORDER_ID"<200 AND "T"."ORDER_ID">=100))
--这里可以看到因为没有指定分区范围，该sql在含index的分区使用全局index，在没有index的分区直接使用全表扫描
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
347 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99' and order_id<100;
执行计划
----------------------------------------------------------
Plan hash value: 3717359654
------------------------------------------------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| P
stop |
------------------------------------------------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 | |
|
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 31 | 2 (0)| 00:00:01 | 1 |
1 |
|* 2 | INDEX RANGE SCAN | IND_G_LOX | 1 | | 1 (0)| 00:00:01 | |
|
------------------------------------------------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_ID"<100)
2 - access("ORDER_ADDRESS"='www.xifenfei.com99')
--指定了分区范围，而且该分区又有index，直接使用全局index
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99' and order_id>400
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2072227240
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 14 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 34 | 14 (0)| 00:00:01 | 5 | 5 |
|* 2 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 14 (0)| 00:00:01 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND "ORDER_ID">400)
--指定了分区范围，但是该分区无index，直接使用全表扫描
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
347 bytes sent via SQL*Net to client
533 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
结论
通过测试，证明Partial Global/Local Indexes for Partitioned Tables确实能够实现对部分分区创建index。
1)如果查询条件确定的分区无index，那直接走全表扫描；
2)如果查询条件可以确定的分区范围内有index，会直接使用index(无论是Local还是GLobal)；
3)如果查询条件未确定分区范围(含index和无index分区情况)，那该sql会在有index分区使用index，在没有index区域走全表扫描