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

lfree發表於2013-08-15
Index
[20130812]12c Partial Indexes For Partitioned Tables Part I.txt

參考連結：


更多的是重複作者的測試，加深理解:

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個。善於利用這個特性，既可以減少索引大小，有可以提高檢索速度。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-768589/，如需轉載，請註明出處，否則將追究法律責任。

相關文章