12c新特性之Partial Indexes for Partitioned Tables

该新特性简单的说就是可以只针对分区表中的某一个分区建立索引，测试如下
1. 创建测试数据

SQL> 
SQL> create table partialtest (id number, status varchar2(6), name varchar2(30))
  2  INDEXING OFF
  3  partition by range (id)
  4  (partition pt1 values less than (10001),
  5  partition pt2 values less than (20001) INDEXING OFF,
  6  partition pt3 values less than (maxvalue) INDEXING ON);
Table created.
SQL> 
SQL> 
SQL> 
SQL> insert into partialtest select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 30000;
30000 rows created.
SQL> commit;
Commit complete.
SQL> 
SQL> 
SQL> update partialtest set status = 'OPEN' where id > 20000 and mod(id,100)=0;
100 rows updated.
SQL> 
SQL> commit;
Commit complete.
SQL>

2. 创建一般索引

SQL> 
SQL> create index idx_pt_status on partialtest(status);
Index created.
SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'PARTIALTEST', estimate_percent=>null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATUS SIZE 5');
PL/SQL procedure successfully completed.
SQL> 
SQL> 
SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'IDX_PT_STATUS';
INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN
------------------------------ ---------- ----------- -------
IDX_PT_STATUS                       30000          92 FULL
SQL> 
----三万行数据均在索引IDX_PT_STATUS中，默认的索引将包含表的所有分区，不会受表参数indexing的影响
SQL> 
SQL> select * from PARTIALTEST where status = 'OPEN';
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2909882285
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PARTIALTEST | 100 | 2400 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_PT_STATUS | 100 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("STATUS"='OPEN')

Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 52 consistent gets
 0 physical reads
 0 redo size
 4479 bytes sent via SQL*Net to client
 618 bytes received via SQL*Net from client
 8 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 100 rows processed
SQL> 


SQL> 
SQL> select * from PARTIALTEST where status = 'OPEN' and id > 20001;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2847963183
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2376 | 2 (0)| 00:00:01 | | |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PARTIALTEST | 99 | 2376 | 2 (0)| 00:00:01 | 3 | 3 |
|* 2 | INDEX RANGE SCAN | IDX_PT_STATUS | 100 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("ID">20001)
 2 - access("STATUS"='OPEN')

Statistics
----------------------------------------------------------
 6 recursive calls
 0 db block gets
 66 consistent gets
 0 physical reads
 0 redo size
 2787 bytes sent via SQL*Net to client
 618 bytes received via SQL*Net from client
 8 SQL*Net roundtrips to/from client
 2 sorts (memory)
 0 sorts (disk)
 100 rows processed
SQL>

3. 创建局部索引

SQL> 
SQL> drop index IDX_PT_STATUS;
Index dropped.
SQL> 

SQL> 
SQL> create index pt_status_i on partialtest(status) indexing partial;
Index created.
SQL> 
SQL> 

SQL> 
SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PT_STATUS_I';
INDEX_NAME       NUM_ROWS LEAF_BLOCKS INDEXIN
-------------- ---------- ----------- -------
PT_STATUS_I         10000          31 PARTIAL
SQL> 
SQL> 
---partial表示这个索引是一个局部索引，这里索引值包含了10000行数据，因为只有pt3是有indexing on属性的
SQL> 
SQL> select count(*) from partialtest partition(pt3);
  COUNT(*)
----------
     10000
SQL> 
SQL> 

SQL> 
SQL> 
SQL> select * from partialtest where status = 'OPEN' and id >= 20001;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2716186415
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |             |   100 |  2400 |     2   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PARTIALTEST |   100 |  2400 |     2   (0)| 00:00:01 |     3 |     3 |
|*  2 |   INDEX RANGE SCAN                         | PT_STATUS_I |    33 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">=20001)
   2 - access("STATUS"='OPEN')

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         66  consistent gets
          7  physical reads
          0  redo size
       2787  bytes sent via SQL*Net to client
        618  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed
SQL> 
SQL> 
---id>20001刚好落在pt3这个分区，所以会选择使用索引
SQL> 
SQL> 
SQL> select * from partialtest where status = 'OPEN';
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2354871924
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |             |   113 |  2712 |   549   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2     |   114 |  3990 |   549   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |             |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PARTIALTEST |   113 |  2712 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                         | PT_STATUS_I |    33 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE ITERATOR                  |             |     1 |    24 |   547   (0)| 00:00:01 |     1 |     2 |
|*  6 |     TABLE ACCESS FULL                        | PARTIALTEST |     1 |    24 |   547   (0)| 00:00:01 |     1 |     2 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("PARTIALTEST"."ID">=20001 OR "PARTIALTEST"."ID" IS NULL)
   4 - access("STATUS"='OPEN')
   6 - filter("STATUS"='OPEN')
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

Statistics
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        936  consistent gets
          0  physical reads
          0  redo size
       2787  bytes sent via SQL*Net to client
        618  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        100  rows processed
SQL> 
SQL>

当检索的数据跨越多个分区时，如果索引只存在与部分分区，其他分区未建立索引，那么未建立索引的分区将采用全表扫描，建立索引的分区采用索引扫描的方式
 
4. 创建子分区

SQL> 
SQL> drop table partialtest purge;
Table dropped.
SQL> 
SQL> 
SQL> create table partialtest (id number, status varchar2(6), name varchar2(30))
  2  indexing off
  3  partition by range (id) subpartition by list(status)
  4  subpartition template
  5  (subpartition closed values ('CLOSED') indexing off, 
  6  subpartition open values ('OPEN') indexing on)
  7  (partition pt1 values less than (10001),
  8  partition pt2 values less than (20001),
  9  partition pt3 values less than (maxvalue))
 10  enable row movement;
Table created.
SQL> 
SQL> 
SQL> 
SQL> select subpartition_position, subpartition_name, num_rows, indexing from dba_tab_subpartitions where table_name = 'PARTIALTEST';
SUBPARTITION_POSITION SUBPARTITION_NAME                NUM_ROWS IND
--------------------- ------------------------------ ---------- ---
                    1 PT1_CLOSED                                OFF
                    2 PT1_OPEN                                  ON
                    1 PT2_CLOSED                                OFF
                    2 PT2_OPEN                                  ON
                    1 PT3_CLOSED                                OFF
                    2 PT3_OPEN                                  ON
6 rows selected.
SQL> 
SQL> 
SQL> 
SQL> insert into partialtest select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 30000;
30000 rows created.
SQL> commit;
Commit complete.
SQL> update partialtest set status = 'OPEN' where id > 20000 and mod(id,100)=0;
100 rows updated.
SQL> commit;
Commit complete.
SQL> 
SQL> 
SQL> 
SQL> create index pt_status_i on partialtest(status) indexing partial;
Index created.
SQL> 
SQL> col index_name for a30
SQL> 
SQL> 
SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PT_STATUS_I';
INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN
------------------------------ ---------- ----------- -------
PT_STATUS_I                           100           1 PARTIAL
SQL> 
SQL> 

SQL> 
SQL> select * from partialtest where status = 'OPEN';
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2505187443
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |             |   100 |  3500 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PARTIALTEST |   100 |  3500 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | PT_STATUS_I |   100 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='OPEN')
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         87  consistent gets
          0  physical reads
          0  redo size
       4479  bytes sent via SQL*Net to client
        618  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed
SQL> 
SQL> 

通过设计子分区，并在数据量较少的子分区打开indexing属性，对性能有明显的提升