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倍之多。
如果某列的读取频率远高于其他列，那就保证表的排列顺序和这列一致。
原文链接：https://www.f2er.com/oracle/207066.html