索引查询要尽可能的避免回表,如不可避免,要关注聚合因子是否过大,聚合因子过大,回表代价高,产生的bufer 多
第一步，构造有序列ｘ在表＂colocated ＂，无序列ｘ在表＂disorganized＂
--构造有序列ｘ在表＂colocated ＂ drop table colocated purge; create table colocated (x int,y varchar2(80)); begin for i in 1 .. 100000 loop insert into colocated(x,y) values (i,rpad(dbms_random.random,75,‘*‘)); end loop; end; / alter table colocated add constraint colocated_pk primary key (x); begin dbms_stats.gather_table_stats( ‘SYS‘, ‘colocated‘, cascade => true); end; /
--disorganized 列x完全无序 drop table disorganized purge; create table disorganized as select x,y from colocated order by y; alter table disorganized add constraint disorganized_pk primary key(x); begin dbms_stats.gather_table_stats(‘SYS‘,‘disorganized‘,cascade=>true); end; /
第二步 比较
第三步 查看clustering_factor
col index_name for A15 select a.table_name, a.index_name, a.blevel, a.leaf_blocks, b.num_rows, b.blocks, a.clustering_factor, trunc(a.clustering_factor / b.num_rows,2) cluster_rate from dba_indexes a, dba_tables b where index_name in(‘COLOCATED_PK‘,‘DISORGANIZED_PK‘) and a.table_name=b.table_name; TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS BLOCKS CLUSTERING_FACTOR CLUSTER_RATE ----------------- --------------- ------ ----------- --------- ------- ------------------ ------------ COLOCATED COLOCATED_PK 1 208 100000 1191 1190 .01 DISORGANIZED DISORGANIZED_PK 1 208 100000 1191 99913 .99