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

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

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

            32 recursive calls

             0  dbblock gets

            52 consistent gets

             4 physical reads

             0  redosize

           608  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             6 sorts (memory)

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

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

            26 recursive calls

             0  dbblock gets

            52 consistent gets

             5 physical reads

             0  redosize

           608  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             6 sorts (memory)

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

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

            19 recursive calls

             0  dbblock gets

      1072  consistent gets

             0 physical reads

             0  redosize

           604  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             5 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

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

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