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,'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,'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倍之多。

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

（编辑：李大同）

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