16.读书笔记收获不止Oracle之 分区索引

来源:互联网 发布:淘宝换货售后卡怎么写 编辑:程序博客网 时间:2021/09/29 03:10

16.读书笔记收获不止Oracle之 分区索引

           分区表索引分为两种:全局索引和局部索引。

           局部所以等同于为每个分区段建分区索引,从user_segment的数据字典中,我们可以观察到表有多少个分区,就有多少个分区索引的segment.

           先建分区表来测试

create table part_tab(id int,col2 int,col3 int)

    partition by range(id)

    (

    partition p1 values lessthan (10000),

    partition p2 values lessthan (20000),

partition p3 values lessthan (30000),

partition p4 values lessthan (40000),

partition p5 values lessthan (50000),

partition p6 values lessthan (60000),

partition p7 values lessthan (70000),

partition p8 values lessthan (80000),

partition p9 values lessthan (90000),

partition p10 values lessthan (100000),

partition p11 values lessthan (maxvalue)

);

插入

SQL>insert into part_tab selectrownum,rownum+1,rownum+2 from dual connect by rownum <= 110000;

Commit;

Create indexidx_par_tab_col2 on part_tab(col2) local;

Create indexidx_par_tab_col3 on part_tab(col3);

进行查看,每个分区就是一个段,11个表分区就是11个段 tablepartition。建立了分区索引后,每个分区索引其实也是一个段,这里显示也是11个段 index partition。全局索引,仅一个段,类型为index.

Col segment_name format a20;

Col partition_name formata20;

查看表分区段

SQL> selectsegment_name,partition_name,segment_type from user_segments wheresegment_name='PART_TAB';

 

SEGMENT_NAME           PARTITION_NAME    SEGMENT_TYPE

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

PART_TAB               P9               TABLE PARTITION

PART_TAB               P8               TABLE PARTITION

PART_TAB               P7               TABLE PARTITION

PART_TAB               P6               TABLE PARTITION

PART_TAB               P5               TABLE PARTITION

PART_TAB               P4               TABLE PARTITION

PART_TAB               P3               TABLE PARTITION

PART_TAB               P2               TABLE PARTITION

PART_TAB               P11              TABLE PARTITION

PART_TAB               P10              TABLE PARTITION

PART_TAB               P1               TABLE PARTITION

查看索引分区段

SQL> selectsegment_name,partition_name,segment_type from user_segments wheresegment_name='IDX_PAR_TAB_COL2';

SEGMENT_NAME           PARTITION_NAME    SEGMENT_TYPE

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

IDX_PAR_TAB_COL2     P1                INDEX PARTITION

IDX_PAR_TAB_COL2     P2                INDEX PARTITION

IDX_PAR_TAB_COL2     P3                INDEX PARTITION

IDX_PAR_TAB_COL2     P4                INDEX PARTITION

IDX_PAR_TAB_COL2     P5                INDEX PARTITION

IDX_PAR_TAB_COL2     P6                INDEX PARTITION

IDX_PAR_TAB_COL2     P7                INDEX PARTITION

IDX_PAR_TAB_COL2     P8                INDEX PARTITION

IDX_PAR_TAB_COL2     P10               INDEX PARTITION

IDX_PAR_TAB_COL2     P11               INDEX PARTITION

IDX_PAR_TAB_COL2     P9                INDEX PARTITION

 

11 rowsselected.

查看全局索引段

SQL> selectsegment_name,partition_name,segment_type from user_segments wheresegment_name='IDX_PAR_TAB_COL3';

SEGMENT_NAME           PARTITION_NAME    SEGMENT_TYPE

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

IDX_PAR_TAB_COL3                         INDEX

接着来看下分区索引实例

1.  分区索引实例

create table norm_tab(idint,col2 int,col3 int);

insert into norm_tab selectrownum,rownum+1,rownum+2 from dual connect by rownum<=110000;

commit;

create index idx_nor_tab_col2 on norm_tab(col2);

create index idx_nor_tab_col3 on norm_tab(col3);

 

对比part_tab 和  norm_tab 的查询。

set autotrace traceonly

set linesize 1000

set timing on

select * from part_tab where col2=8;

Elapsed: 00:00:00.06

 

Execution Plan

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

Plan hash value: 3980401122

 

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

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

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

|   0| SELECT STATEMENT                             |                            |    1 |    39 |    13    (0)|00:00:01 |       |       |

|   1|  PARTITION RANGE ALL                               |                            |    1 |    39 |    13    (0)|00:00:01 |     1 |    11 |

|   2|   TABLE ACCESS BY LOCAL INDEX ROWIDBATCHED| PART_TAB                 |    1 |    39 |    13    (0)|00:00:01 |     1 |    11 |

|*  3|    INDEX RANGE SCAN                                | IDX_PAR_TAB_COL2 |     1 |      |    12       (0)| 00:00:01 |     1|    11 |

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

 

Predicate Information (identified byoperation id):

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

 

   3- access("COL2"=8)

 

Note

-----

   -dynamic statistics used: dynamic sampling (level=2)

 

 

Statistics

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

            11 recursive calls

             0  dbblock gets

           303  consistent gets

            44 physical reads

             0  redosize

           674  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

查看norm_tab

SQL> select * from norm_tab where col2=8;

 

Elapsed: 00:00:00.04

 

Execution Plan

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

Plan hash value: 2321776653

 

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

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

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

|   0| SELECT STATEMENT                    |                          |    1 |    39 |     2  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID BATCHED|NORM_TAB           |    1 |    39 |     2  (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN                         | IDX_NOR_TAB_COL2 |     1 |      |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   2- access("COL2"=8)

 

Note

-----

   -dynamic statistics used: dynamic sampling (level=2)

 

 

Statistics

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

            10 recursive calls

             0  dbblock gets

            69  consistent gets

             4 physical reads

             0  redosize

           678  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

对norm_tab的查询只有69次逻辑读。

针对分区表的查询是普通表的逻辑读的6倍之多。

           分区表的索引等同于查询了11个小索引,小索引虽然体积小但是高速差不多,需要的IO也是一样的。

1.1         查看分区索引

Col clustering_factor format 999;                      

Col INDEX_NAME format a20;

selectindex_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor

From user_ind_partitions where index_name='IDX_PAR_TAB_COL2';

INDEX_NAME              BLEVEL LEAF_BLOCKS   NUM_ROWSDISTINCT_KEYS CLUSTERING_FACTOR

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

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10001             10001                28

IDX_PAR_TAB_COL2       1  21     9999                 9999                          24

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

IDX_PAR_TAB_COL2       1  23   10000             10000                28

 

11 rows selected.

selectindex_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor

From user_ind_statistics where index_name='IDX_NOR_TAB_COL2';

INDEX_NAME               BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR

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

IDX_NOR_TAB_COL2                1 244   110000            110000               299

 

           因此分区表索引的设计是有讲究的,如果设置了分区索引,但是用不到分区条件,性能将继续下降。如果无法加上分区字段的条件,建议不要建分区索引。

1.2         加上分区字段条件

进行查询

select * from part_tab where col2=8 and id=7;

Elapsed: 00:00:00.00

 

Execution Plan

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

Plan hash value: 2640417554

 

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

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

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

|   0| SELECT STATEMENT                             |                            |    2 |    78 |     3    (0)|00:00:01 |       |       |

|   1|  PARTITION RANGE SINGLE                      |                            |    2 |    78 |     3    (0)|00:00:01 |     1 |     1 |

|*  2|   TABLE ACCESS BY LOCAL INDEX ROWIDBATCHED| PART_TAB                 |    2 |    78 |     3    (0)|00:00:01 |     1 |     1 |

|*  3|    INDEX RANGE SCAN                                | IDX_PAR_TAB_COL2 |    11 |      |     2       (0)| 00:00:01 |     1|     1 |

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

 

Predicate Information (identified byoperation id):

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

 

   2- filter("ID"=7)

   3- access("COL2"=8)

 

Note

-----

   -dynamic statistics used: dynamic sampling (level=2)

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

             4  consistent gets

             0 physical reads

             0  redosize

           674  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2  SQL*Netroundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

加上分区条件后,逻辑读直接从303减少到了4.

          

 

 

 

阅读全文
0 0
原创粉丝点击