Oracle 12c 新特性 --- 分区表的局部索引
全局的部分索引：只对那些需要索引的分区创建索引，别的分区不会创建。
本地索引：如果对表分区打开索引，则索引分区可用，否则不可用。 您可以通过在索引或索引分区级别指定USABLE / UNUSABLE来覆盖此行为。
这个功能不支持唯一索引，full是默认的模式。
本地索引：如果对表分区打开索引，则索引分区可用，否则不可用。 您可以通过在索引或索引分区级别指定USABLE / UNUSABLE来覆盖此行为。
这个功能不支持唯一索引，full是默认的模式。
CREATE TABLE orders (
order_id NUMBER(12),
order_date DATE CONSTRAINT order_date_nn NOT NULL,
order_mode VARCHAR2(8),
customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT order_mode_lov CHECK (order_mode in ('direct','online')),
CONSTRAINT order_total_min CHECK (order_total >= 0))
INDEXING OFF
PARTITION BY RANGE (ORDER_DATE)
(PARTITION ord_p1 VALUES LESS THAN (TO_DATE('01-MAR-1999','DD-MON-YYYY'))
INDEXING ON,
PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
INDEXING OFF,
PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
INDEXING ON,
PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY')),
PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-MAR-2010','DD-MON-YYYY')));
创建全局索引
CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL)
GLOBAL INDEXING PARTIAL;
SQL> select index_name,index_type,partitioned,orphaned_entries,indexing from user_indexes;
INDEX_NAME INDEX_TYPE PAR ORP INDEXIN
------------------------------ --------------------------- --- --- -------
PK_DEPT NORMAL NO NO FULL
PK_EMP NORMAL NO NO FULL
ORDERS_ORDER_TOTAL_GIDX NORMAL NO NO PARTIAL <----------PARTIAL
SQL> select * from user_ind_partitions ;
no rows selected
下面创建本地分区索引
create index idx_orders_local on orders(order_date) local;
select index_name,partition_name,status from user_ind_partitions ;
SQL> select index_name,partition_name,status from user_ind_partitions ;
INDEX_NAME PARTITION_NAME STATUS
-------------------- -------------------- --------
IDX_ORDERS_LOCAL ORD_P1 USABLE
IDX_ORDERS_LOCAL ORD_P2 USABLE
IDX_ORDERS_LOCAL ORD_P3 USABLE
IDX_ORDERS_LOCAL ORD_P4 USABLE
IDX_ORDERS_LOCAL ORD_P5 USABLE
SELECT table_name,
partition_name,
indexing
FROM user_tab_partitions
ORDER BY 1,2;
TABLE_NAME PARTITION_NAME INDE
-------------------- -------------------- ----
ORDERS ORD_P1 ON
ORDERS ORD_P2 ON
ORDERS ORD_P3 ON
ORDERS ORD_P4 ON
ORDERS ORD_P5 ON
SCOTT@orcl01>ALTER TABLE ORDERS MODIFY PARTITION ORD_P5 INDEXING OFF;
SELECT table_name,
partition_name,
indexing
FROM user_tab_partitions
ORDER BY 1,2;
TABLE_NAME PARTITION_NAME INDE
-------------------- -------------------- ----
ORDERS ORD_P1 ON
ORDERS ORD_P2 ON
ORDERS ORD_P3 ON
ORDERS ORD_P4 ON
ORDERS ORD_P5 OFF
SQL> drop index IDX_ORDERS_LOCAL ;
添加索引部分子句可以将索引创建为部分索引。非索引的分区被标记为不可用。
SQL> create index IDX_ORDERS_LOCAL on orders(order_date) local indexing partial; <----------PARTIAL
Index created.
SQL> select index_name,partition_name,status from user_ind_partitions ;
INDEX_NAME PARTITION_NAME STATUS
-------------------- -------------------- --------
IDX_ORDERS_LOCAL ORD_P1 USABLE
IDX_ORDERS_LOCAL ORD_P2 USABLE
IDX_ORDERS_LOCAL ORD_P3 USABLE
IDX_ORDERS_LOCAL ORD_P4 USABLE
IDX_ORDERS_LOCAL ORD_P5 UNUSABLE
SCOTT@orcl01>ALTER TABLE ORDERS MODIFY PARTITION ORD_P5 INDEXING ON;
COTT@orcl01>ALTER TABLE ORDERS MODIFY PARTITION ORD_P4 INDEXING OFF;
SELECT table_name,
partition_name,
indexing
FROM user_tab_partitions
ORDER BY 1,2;
TABLE_NAME PARTITION_NAME INDE
-------------------- -------------------- ----
ORDERS ORD_P1 ON
ORDERS ORD_P2 ON
ORDERS ORD_P3 ON
ORDERS ORD_P4 OFF
ORDERS ORD_P5 ON <------------------
SCOTT@orcl01>select index_name,partition_name,status from user_ind_partitions ;
INDEX_NAME PARTITION_NAME STATUS
----------------------------------------------------------- -------------------- ----------
IDX_ORDERS_LOCAL ORD_P1 USABLE
IDX_ORDERS_LOCAL ORD_P2 USABLE
IDX_ORDERS_LOCAL ORD_P3 USABLE
IDX_ORDERS_LOCAL ORD_P4 UNUSABLE
IDX_ORDERS_LOCAL ORD_P5 USABLE <------------------效果：不可用的分区索引为全表扫描。 可用的分区索引 走索引
SCOTT@orcl01>SELECT * FROM ORDERS PARTITION(ORD_P4) where order_total=5 ; <-----PARTITION(ORD_P4)
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3905487950
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 93 | 3 (0)| 00:00:01 | 4 | 4 |
|* 2 | TABLE ACCESS FULL | ORDERS | 1 | 93 | 3 (0)| 00:00:01 | 4 | 4 | <-----全表
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDER_TOTAL"=5)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
920 bytes sent via SQL*Net to client
414 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SCOTT@orcl01>SELECT * FROM ORDERS PARTITION(ORD_P1) where order_total=5 ; <-----PARTITION(ORD_P1)
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1855914208
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 1 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 93 | 1 (0)| 00:00:01 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | ORDERS_ORDER_TOTAL_GIDX | 1 | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_TOTAL"=5)
filter(TBL$OR$IDX$PART$NUM("ORDERS",0,1,0,ROWID)=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
44 recursive calls
8 db block gets
41 consistent gets
0 physical reads
1096 redo size
920 bytes sent via SQL*Net to client
414 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
参考文档
http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT205
http://docs.oracle.com/database/121/VLDBG/GUID-569F94D0-E6E5-45BB-9626-5506DE18FF00.htm#VLDBG00403
https://oracle-base.com/articles/12c/partial-indexes-for-partitioned-tables-12cr1
http://docs.oracle.com/database/121/CNCPT/schemaob.htm#CNCPT89298