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。