[20130812]12c Partial Indexes For Partitioned Tables Part I.txt

参考链接：
http://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/

更多的是重复作者的测试，加深理解:

1.测试环境:

SQL> @ver

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.测试建立：
create table pink_floyd (id number, status varchar2(6), name varchar2(30))
INDEXING OFF
partition by range (id)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001) INDEXING OFF,
partition pf3 values less than (maxvalue) INDEXING ON);

--说明：利用indexing off可以定义在那个分区建立索引，前面的INDEXING OFF表示缺省参数，这样pf1分区上相当于INDEXING OFF。

SQL> insert into pink_floyd select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000;
insert into pink_floyd select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level <= 3000000
            *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation
--报错，估计机器配置没有作者的好。修改如下：

SQL> insert into pink_floyd
select rownum, 'CLOSED', 'DAVID BOWIE' from
(select 1 from dual connect by level <= 3000) a,
(select 1 from dual connect by level<=1000) b;

3000000 rows created.

SQL> commit;
Commit complete.

SQL> update pink_floyd set status = 'OPEN' where id > 2000000 and mod(id,10000)=0;
100 rows updated.

SQL> commit ;
Commit complete.

SQL> create index pink_floyd_status_i on pink_floyd(status);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'PINK_FLOYD', estimate_percent=>null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATUS SIZE 5');
PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';

INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN

------------------------------ ---------- ----------- -------

PINK_FLOYD_STATUS_I               3000000        9203 FULL

3.开始测试1：

SQL> set autot traceonly ;

SQL> select * from pink_floyd where status = 'OPEN';

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3984357026

----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |                     |   100 |  2500 |     4   (0)| 00:00:01 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |   100 |  2500 |     4   (0)| 00:00:01 | ROWID | ROWID |

|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     3   (0)| 00:00:01 |       |       |

----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("STATUS"='OPEN')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        104  consistent gets

          0  physical reads

          0  redo size

       3315  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)

        100  rows processed

SQL> select * from pink_floyd where status = 'OPEN' and id > 2000001;

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4284033138

----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |                     |    99 |  2475 |     4   (0)| 00:00:01 |       |       |

|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |    99 |  2475 |     4   (0)| 00:00:01 |     3 |     3 |

|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     3   (0)| 00:00:01 |       |       |

----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID">2000001)

   2 - access("STATUS"='OPEN')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        104  consistent gets

          0  physical reads

          0  redo size

       1641  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)

        100  rows processed

--可以发现逻辑读一样，实际上我们仅仅需要扫描pf3分区。

4.建立部分索引看看：

SQL> drop index pink_floyd_status_i;

Index dropped.

--使用关键字indexing partial就可以部分索引。

SQL> create index pink_floyd_status_i on pink_floyd(status) indexing partial;

Index created.

SQL> set autot off

SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';

INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN

------------------------------ ---------- ----------- -------

PINK_FLOYD_STATUS_I               1000000        3068 PARTIAL

--这样就实现仅仅对分区pf3的status建立索引，其他分区不需要建立索引。

SQL> select * from pink_floyd where status = 'OPEN' and id >= 2000001;

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4284033138

----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |                     |   100 |  2500 |     4   (0)| 00:00:01 |       |       |

|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |   100 |  2500 |     4   (0)| 00:00:01 |     3 |     3 |

|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |    33 |       |     3   (0)| 00:00:01 |       |       |

----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("ID">=2000001)

   2 - access("STATUS"='OPEN')

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        104  consistent gets

          0  physical reads

          0  redo size

       1641  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)

        100  rows processed

SQL> select * from pink_floyd where status = 'OPEN';

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 4221394394

------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                             |                     |   100 |  2500 |  2475   (1)| 00:00:01 |       |       |

|   1 |  VIEW                                        | VW_TE_2             |    99 |  3465 |  2475   (1)| 00:00:01 |       |       |

|   2 |   UNION-ALL                                  |                     |       |       |            |          |       |       |

|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PINK_FLOYD          |    33 |   825 |     4   (0)| 00:00:01 | ROWID | ROWID |

|*  4 |     INDEX RANGE SCAN                         | PINK_FLOYD_STATUS_I |   100 |       |     3   (0)| 00:00:01 |       |       |

|   5 |    PARTITION RANGE ITERATOR                  |                     |    66 |  1650 |  2471   (1)| 00:00:01 |     1 |     2 |

|*  6 |     TABLE ACCESS FULL                        | PINK_FLOYD          |    66 |  1650 |  2471   (1)| 00:00:01 |     1 |     2 |

------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("PINK_FLOYD"."ID">=2000001 OR "PINK_FLOYD"."ID" IS NULL)

   4 - access("STATUS"='OPEN')

   6 - filter("STATUS"='OPEN')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       8333  consistent gets

          0  physical reads

          0  redo size

       1641  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)

        100  rows processed

--这样查询，分区pf1与pf2全表扫描，而pf3使用索引。
--另外从逻辑读上select * from pink_floyd where status = 'OPEN' and id >= 2000001;依旧是104，没有改进。


5.再看看下面的例子：

SQL> drop table PINK_FLOYD purge ;
Table dropped.

create table pink_floyd (id number, status varchar2(6), name varchar2(30))
indexing off
partition by range (id) subpartition by list(status)
subpartition template
(subpartition closed values ('CLOSED') indexing off, subpartition open values ('OPEN') indexing on)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001),
partition pf3 values less than (maxvalue))
enable row movement;

--分区采用组合：range+list。仅仅对status的字段建立索引，其他不建立。

SQL> select subpartition_position, subpartition_name, num_rows, indexing from dba_tab_subpartitions where table_name = 'PINK_FLOYD';

SUBPARTITION_POSITION SUBPARTITION_NAME      NUM_ROWS IND

--------------------- -------------------- ---------- ---

                    1 PF1_CLOSED                      OFF

                    2 PF1_OPEN                        ON

                    1 PF2_CLOSED                      OFF

                    2 PF2_OPEN                        ON

                    1 PF3_CLOSED                      OFF

                    2 PF3_OPEN                        ON

6 rows selected.

SQL> insert into pink_floyd
select rownum, 'CLOSED', 'DAVID BOWIE' from
(select 1 from dual connect by level <= 3000) a,
(select 1 from dual connect by level<=1000) b;

3000000 rows created.

SQL> commit;
Commit complete.

SQL> update pink_floyd set status = 'OPEN' where id > 2000000 and mod(id,10000)=0;
100 rows updated.

SQL> commit ;
Commit complete.

SQL> create index pink_floyd_status_i on pink_floyd(status) indexing partial;
Index created.

SQL> select index_name, num_rows, leaf_blocks, indexing from dba_indexes where index_name = 'PINK_FLOYD_STATUS_I';

INDEX_NAME                       NUM_ROWS LEAF_BLOCKS INDEXIN

------------------------------ ---------- ----------- -------

PINK_FLOYD_STATUS_I                   100           1 PARTIAL

--仅仅100行在索引中，

SQL> select * from pink_floyd where status = 'OPEN';

100 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3984357026

----------------------------------------------------------------------------------------------------------------------------------

| 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| PINK_FLOYD          |   100 |  3500 |     2   (0)| 00:00:01 | ROWID | ROWID |

|*  2 |   INDEX RANGE SCAN                         | PINK_FLOYD_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

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       3315  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)

        100  rows processed

--这样逻辑读仅仅4个。善于利用这个特性，既可以减少索引大小，有可以提高检索速度。