21.读书笔记收获不止Oracle之 索引回表效率

21.读书笔记收获不止Oracle之 索引回表效率

如果一直要执行回表，我们就需要关注回表的效率了。

1.  示例

SQL> create table t_colocated (id number,col2varchar2(100));

begin

    for i in 1 .. 100000

    loop

               insert into t_colocated(id,col2)

               values (i,rpad(dbms_random.random,95,'*'));

    end loop;

    end;

    /

SQL> alter table t_colocated add constraint pk_t_colocatedprimary key(id);

SQL>create table t_disorganized 

    as

    select id,col2 fromt_colocated

    order by col2;

SQL> alter table t_disorganized add constraint pk_t_disorgprimary key(id);

在t_colocated 表中，表的数据基本依据id从1 到100000顺序插入的。id列上的索引存放的数据也是按1 到 100000顺序插入的。表和索引两者的排序顺序相似度很高，我们称之为聚合因子比较低。

表t_disorganized 表，依据col2这个插入记录为随机值的列来排序的，表和索引两者之间的排列顺序相似度差异明显，称之为聚合因子比较高。

           通过数据字典来判断聚合因子情况。

Set linesize 1000

Col index_name format a15;

selectindex_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor

    from user_ind_statisticswhere table_name in ('T_COLOCATED','T_DISORGANIZED');

INDEX_NAME        BLEVEL LEAF_BLOCKS          NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR

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

PK_T_DISORG              1        208         100000         100000     99913

PK_T_COLOCATED               1         208         100000         100000      1469

其中clustering_factor表名有都少临近的索引条目直到不同的数据块。

 

如果聚合因子很大，10行索引条目对应的数据块的10行记录，分布在10个不同的数据块里。那么就要访问多个数据块，回表查询的性能就很低。

1.1         性能跟踪

之前使用如下查看执行计划

SQL> set linesize 1000

SQL> set autotrace traceonly;

现在使用如下来进行性能跟踪：

Alter session set statistics_level=all
SQL> select * fromtable(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

然后执行如下查询：

SQL>select /*+index(t)*/ * from t_colocated t whereid>=20000 and id<=40000;

执行查看执行计划

SQL> select * fromtable(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

PLAN_TABLE_OUTPUT

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

SQL_ID      b29s020c6tbyd,child number 0

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

select /*+index(t)*/ * from t_colocated twhere id>=20000 and id<=40000

 

Plan hash value: 1513619617

 

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

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

 

| Id | Operation                               | Name           | Starts | E-Rows |

 A-Rows |  A-Time   | Buffers | Reads          |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

|   0| SELECT STATEMENT                    |                        |          1 |       |

 20001 |00:00:00.03 |      2985 |     38 |

 

|   1|  TABLE ACCESS BY INDEX ROWID BATCHED|T_COLOCATED    |          1 |  17857 |

 20001 |00:00:00.03 |      2985 |    38 |

 

|*  2|   INDEX RANGE SCAN                         | PK_T_COLOCATED |             1 |  17857 |

 

PLAN_TABLE_OUTPUT

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

 20001 |00:00:00.01 |      1374 |    38 |

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

   2- access("ID">=20000 AND "ID"<=40000)

 

 

PLAN_TABLE_OUTPUT

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

Note

-----

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

 

 

23 rows selected.

 

继续观察 t_disorganized 表的查询。

SQL>select /*+index(t)*/ * from t_disorganized t whereid>=20000 and id<=40000;

select * fromtable(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

PLAN_TABLE_OUTPUT

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

SQL_ID      1gr3svkfdtcax,child number 0

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

select /*+index(t)*/ *from t_disorganized t where id>=20000 and

id<=40000

 

Plan hash value: 3927524887

 

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

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

 

| Id | Operation                               | Name           | Starts | E-Rows |

 

PLAN_TABLE_OUTPUT

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

 A-Rows |  A-Time   | Buffers | Reads          |

 

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

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

 

|   0| SELECT STATEMENT                    |                        |          1 |       |

 20001 |00:00:00.07 |    21363 |     47 |

 

|   1|  TABLE ACCESS BY INDEX ROWID BATCHED|T_DISORGANIZED |             1 | 20002 |

 20001 |00:00:00.07 |    21363|     47 |

 

 

PLAN_TABLE_OUTPUT

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

|*  2|   INDEX RANGE SCAN                         | PK_T_DISORG    |             1 | 20002 |

 20001 |00:00:00.03 |      1374 |    47 |

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

   2- access("ID">=20000 AND "ID"<=40000)

 

PLAN_TABLE_OUTPUT

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

 

 

20 rows selected.

 

可以看到逻辑读从2985增加到了21363多。

同样大小的同样大小的索引，记录数相同，执行的同样的语句，因为聚合因子的差异，性能差异达到10倍之多。

如果某列的读取频率远高于其他列，那就保证表的排列顺序和这列一致。