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,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,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,col3 int);

insert into norm_tab selectrownum,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

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

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

| 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

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

11recursive calls

0 dbblock gets

303 consistent gets

44physical reads

0 redosize

674 bytes sent via sql*Net to client

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1 rowsprocessed

查看norm_tab

sql> select * from norm_tab where col2=8;

Elapsed: 00:00:00.04

Execution Plan

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

Plan hash value: 2321776653

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

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

| 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

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

10recursive calls

0 dbblock gets

69 consistent gets

4physical reads

0 redosize

678 bytes sent via sql*Net to client

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (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 10001 10001 28

IDX_PAR_TAB_COL2 1 21 9999 9999 24

IDX_PAR_TAB_COL2 1 23 10000 10000 28

11 rows selected.

selectindex_name,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

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

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

| 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

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

1recursive calls

0 dbblock gets

4 consistent gets

0physical 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

0sorts (memory)

0sorts (disk)

1 rowsprocessed

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

原文链接：https://www.f2er.com/oracle/207074.html

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

1. 分区索引实例

1.1查看分区索引

1.2加上分区字段条件

猜你在找的Oracle相关文章

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