15.读书笔记收获不止Oracle之 索引高度

我们用实际例子来看下索引的高度是否真的比较低。

构造一系列表T1到T7，记录数从5到500万依次以10倍的差额逐步增大。

SQL>Create table t1 as select rownum as id,rownum+1 as id2from dual connect by level<=5;

Create table t2 as select rownum as id,rownum+1 as id2 from dualconnect by level<=50;

Create table t3 as select rownum as id,rownum+1 as id2 from dualconnect by level<=500;

Create table t4 as select rownum as id,rownum+1 as id2 from dualconnect by level<=5000;

Create table t5 as select rownum as id,rownum+1 as id2 from dualconnect by level<=50000;

Create table t6 as select rownum as id,rownum+1 as id2 from dualconnect by level<=500000;

创建索引：

Create index idx_id_t1 on t1(id);

Create index idx_id_t2 on t2(id);

Create index idx_id_t3 on t3(id);

Create index idx_id_t4 on t4(id);

Create index idx_id_t5 on t5(id);

Create index idx_id_t6 on t6(id);

查看索引大小：

col segment_name format a15;

select segment_name,bytes/1024 from user_segments where segment_namein ('IDX_ID_T1','IDX_ID_T2','IDX_ID_T3','IDX_ID_T4','IDX_ID_T5','IDX_ID_T6');

查看索引高度： BLEVEL=0表示1层

col index_name format a15;

selectindex_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor fromuser_ind_statistics where table_name in ('T1','T2','T3','T4','T5','T6');

INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS

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

CLUSTERING_FACTOR

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

IDX_ID_T6 2 1113 500000 500000

1035

IDX_ID_T5 1 110 50000 50000

101

IDX_ID_T4 1 11 5000 5000

9

INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS

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

CLUSTERING_FACTOR

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

IDX_ID_T3 1 2 500 500

1

IDX_ID_T2 0 1 50 50

1

IDX_ID_T1 0 1 5 5

1

记录相差巨大，但是高度差别却是如此之小。

1. 索引高度较低使用技巧

在这基础上进行测试如下：

Set autotrace traceonly

Set linesize 1000

Set timing on

Select * from t5 where id=10;

Elapsed: 00:00:00.07

Execution Plan

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

Plan hash value: 2977381114

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

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

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

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

| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T5 |1 | 10 | 2(0)| 00:00:01 |

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

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

Predicate Information (identified byoperation id):

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

2- access("ID"=10)

Statistics

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

32recursive calls

0 dbblock gets

52consistent gets

4physical reads

0 redosize

608 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

6sorts (memory)

0sorts (disk)

1 rowsprocessed

在表六中查询：

Select * from t6 where id=10;

Elapsed: 00:00:00.06

Execution Plan

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

Plan hash value: 661597417

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

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

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

| 0| SELECT STATEMENT | | 1 |10 | 4 (0)| 00:00:01 |

| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T6 |1 | 10 | 4(0)| 00:00:01 |

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

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

Predicate Information (identified byoperation id):

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

2- access("ID"=10)

Statistics

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

26recursive calls

0 dbblock gets

52consistent gets

5physical reads

0 redosize

608 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

6sorts (memory)

0sorts (disk)

1 rowsprocessed

发现t5和t6的表记录虽然相差了一个数量级，但是通过索引查询的效率却相差不多。主要是因为他们的BLEVEL 差不多。

2. 删除索引测试

drop index IDX_ID_T6;

Select * from t6 where id=10;

Elapsed: 00:00:00.03

Execution Plan

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

Plan hash value: 1930642322

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

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

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

| 0| SELECT STATEMENT | |1 | 10 | 292(2)| 00:00:01 |

|* 1| TABLE ACCESS FULL| T6 |1 | 10 | 292(2)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

1- filter("ID"=10)

Statistics

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

19recursive calls

0 dbblock gets

1072 consistent gets

0physical reads

0 redosize

604 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

1 rowsprocessed

去掉索引后，逻辑读变的非常明显。产生了1072 次逻辑读。

如果索引的高度为3，查询到一条记录大致需要3到4次IO。如果返回100万条记录，就是100万乘以3或4，就是三四百万的IO数据，如不全表扫描。全表扫描还可以进行读取多个块。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!