Oracle 12C 新特性之表分区部分索引(Partial Indexes)
12c之前没办法在部分或指定的分区上创建索引,12c 版本中引入了Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引。分区上有索引用索引，无索引全表扫，跨多分区中indexing有开有关时有用索引和全表的子集union all。
partial index有两个限制条件:
1、 表是分区表
2、 不能是唯一索引
3、 建表的时候分区指定 INDEXING OFF or ON 或者 alter table 修改
一：indexing 探讨。
-- 创建实验表
CREATE TABLE p_andy
(ID number(10), NAME varchar2(30))
INDEXING OFF
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (200) INDEXING OFF,
PARTITION p2 VALUES LESS THAN (400),
PARTITION p3 VALUES LESS THAN (600),
PARTITION p4 VALUES LESS THAN (800) INDEXING ON,
partition p5 values less than (maxvalue)
);
Table created.
-- 插入数据
begin
for i in 1 .. 1000 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
PL/SQL procedure successfully completed.
-- 查看现在表的分区：
SQL>
col table_name for a25
col partition_name for a25
col HIGH_VALUE for a10
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE INDE
------------------------- ------------------------- ------------------ ------------------------------ ---------- ----
P_ANDY P1 1 USERS 200 OFF
P_ANDY P2 2 USERS 400 OFF
P_ANDY P3 3 USERS 600 OFF
P_ANDY P4 4 USERS 800 ON
P_ANDY P5 5 USERS MAXVALUE OFF
说明：Create table属性indexing的默认值是ON; partition 或sub partition如果没有指定indexing是继承上级属性。
--创建global索引
create index idx_pAndy_id on p_andy(id);
Index created.
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID NO 1 3 FULL
SQL> drop index IDX_PANDY_ID;
Index dropped.
SQL> create index idx_pAndy_id01 on p_andy(id) GLOBAL INDEXING FULL;
Index created.
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID01';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID01 NO 1 3 FULL
SQL> drop index IDX_PANDY_ID01;
Index dropped.
SQL> create index idx_pAndy_id02 on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID02';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID02 NO 0 1 PARTIAL
SQL> drop index IDX_PANDY_ID02;
Index dropped.
说明：Create Index 引入了INDEXING ON|OFF|PARTIAL|FULL值, INDEXING FULL是默认值. 上面创建了global index full和index partial 可以看到部分索引的index leaf block和number of rows都明显减少。
创建LOCAL索引
SQL> create index idx_pAndy_id03 on p_andy(id) local;
Index created.
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID03 YES 0 5 FULL
SQL> drop index idx_pAndy_id03;
Index dropped.
创建LOCAL索引
SQL> create index idx_pAndy_id03 on p_andy(id) local INDEXING PARTIAL;
Index created.
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME INDEX_NAME PAR BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY IDX_PANDY_ID03 YES 0 1 PARTIAL
说明：创建了local index full和index partial 可以看到部分索引的index leaf blocks和number of rows都明显减少.
二： 部分索引执行计划探讨
测试：索引相关的执行计划
-- 查看表索引
col column_name for a30
col index_name for a20
SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = 'P_ANDY';
INDEX_NAME COLUMN_NAME DESC
-------------------- ------------------------------ ----
IDX_PANDY_ID_L ID ASC
IDX_PANDY_ID_G ID ASC
-- 删除所有索引，为了方便下面创建部分索引。
SQL> drop index IDX_PANDY_ID_L ;
Index dropped.
SQL> drop index IDX_PANDY_ID_G;
Index dropped.
-- 查看现在表的分区：
SQL>
col table_name for a25
col partition_name for a25
col HIGH_VALUE for a10
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE INDE
------------------------- ------------------------- ------------------ ------------------------------ ---------- ----
P_ANDY P1 1 USERS 200 OFF
P_ANDY P2 2 USERS 400 OFF
P_ANDY P3 3 USERS 600 ON
P_ANDY P4 4 USERS 800 OFF
P_ANDY P5 5 USERS MAXVALUE OFF
-- 创建全局索引
SQL> create index idx_pAndy_id_G on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 验证是不是部分全局索引
SQL> select index_name,indexing from dba_indexes where index_name='IDX_PANDY_ID_G';
INDEX_NAME INDEXIN
-------------------- -------
IDX_PANDY_ID_G PARTIAL > 说明是部分全局索引
SQL>
col index_name for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID_G';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY IDX_PANDY_ID_G VALID 0 1
-- 改变分区 indexing 属性
SQL> alter table P_ANDY modify partition P3 indexing off;
Table altered.
SQL> alter table P_ANDY modify partition P4 indexing on;
Table altered.
SQL> select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID_G';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY IDX_PANDY_ID_G VALID 0 1
SQL> select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE INDE
------------------------- --------------- ------------------ ------------------------------ ---------- ----
P_ANDY P1 1 USERS 200 OFF
P_ANDY P2 2 USERS 400 OFF
P_ANDY P3 3 USERS 600 OFF
P_ANDY P4 4 USERS 800 ON
P_ANDY P5 5 USERS MAXVALUE OFF
-- 改变分区 indexing 属性
-- 创建local 索引
SQL> create index idx_pAndy_id_L on p_andy(id) local INDEXING PARTIAL;
ORA-01408: such column list already indexed
SQL> create index idx_pAndy_id_L on p_andy(id) local INDEXING PARTIAL invisible;
Index created.
说明：同一字段上不能创建两个相同种类的索引，这里把第二次创建的索引设置为 invisible 。
SQL>
COL INDEX_NAME FOR A25
COL PARTITION_NAME FOR A15
select index_name, partition_name,STATUS from user_ind_partitions where index_name = 'IDX_PANDY_ID_L';
INDEX_NAME PARTITION_NAME STATUS
------------------------- --------------- --------
IDX_PANDY_ID_L P1 UNUSABLE
IDX_PANDY_ID_L P2 UNUSABLE
IDX_PANDY_ID_L P3 UNUSABLE
IDX_PANDY_ID_L P4 USABLE
IDX_PANDY_ID_L P5 UNUSABLE
说明：local index 标记为INDEXING OFF的分区索引状态将显示为UNUSABLE， 标记为INDEXING ON的分区索引状态显示为USABLE。
SQL> set autotrace only on explain;
SQL> select * from p_andy where id>550;
Execution Plan
----------------------------------------------------------
Plan hash value: 4213581160
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 450 | 4500 | 549 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 453 | 13590 | 549 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY | 200 | 2000 | 2 (0)| 00:00:01 | 4 | 4 |
|* 4 | INDEX RANGE SCAN | IDX_PANDY_ID_G | 40 | | 1 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE OR | | 253 | 2530 | 547 (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|* 6 | TABLE ACCESS FULL | P_ANDY | 253 | 2530 | 547 (0)| 00:00:01 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------------------------------------
SQL> select * from p_andy where id=550;
Execution Plan
----------------------------------------------------------
Plan hash value: 2878316774
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 274 (0)| 00:00:01 | |
| 1 | PARTITION RANGE SINGLE| | 1 | 10 | 274 (0)| 00:00:01 | 3 | 3
|* 2 | TABLE ACCESS FULL | P_ANDY | 1 | 10 | 274 (0)| 00:00:01 | 3 | 3
------------------------------------------------------------------------------------------------
SQL> alter index IDX_PANDY_ID_G invisible;
Index altered.
SQL> alter index IDX_PANDY_ID_L visible;
Index altered.
SQL> select * from p_andy where id=770;
Execution Plan
----------------------------------------------------------
Plan hash value: 3981876911
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 | |
| 1 | PARTITION RANGE SINGLE | | 1 | 10 | 2 (0)| 00:00:01 | 4 | 4
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY | 1 | 10 | 2 (0)| 00:00:01 | 4 | 4
|* 3 | INDEX RANGE SCAN | IDX_PANDY_ID_L | 1 | | 1 (0)| 00:00:01 | 4 | 4
--------------------------------------------------------------------------------------------------------------------------
SQL> select * from p_andy where id>770;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 230 | 2300 | 276 (0)| 00:00:01 | | |
| 1 | VIEW | VW_TE_2 | 230 | 6900 | 276 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE | | 29 | 290 | 2 (0)| 00:00:01 | 4 | 4 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY | 29 | 290 | 2 (0)| 00:00:01 | 4 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_PANDY_ID_L | 29 | | 1 (0)| 00:00:01 | 4 | 4 |
| 6 | PARTITION RANGE SINGLE | | 201 | 2010 | 274 (0)| 00:00:01 | 5 | 5 |
|* 7 | TABLE ACCESS FULL | P_ANDY | 201 | 2010 | 274 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------------------------
说明：分区上有索引用索引（也可能存在某种情况不走索引），无索引全表扫，跨多分区中indexing有开有关时有用索引和全表的子集union all。
