索引优化系列三 聚合因子
--colocated表根据x列有一定的物理顺序 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( user,'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( user,'DISORGANIZED',cascade=>true ); end; / set autotrace off alter session set statistics_level=all; set linesize 1000 ---两者性能差异显著 select /*+ index( colocated colocated_pk ) */ * from colocated where x between 20000 and 40000; SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last')); ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 20001 |00:00:00.05 | 2900 | | 1 | TABLE ACCESS BY INDEX ROWID| COLOCATED | 1 | 20002 | 20001 |00:00:00.05 | 2900 | |* 2 | INDEX RANGE SCAN | COLOCATED_PK | 1 | 20002 | 20001 |00:00:00.03 | 1375 | ------------------------------------------------------------------------------------------------------ select /*+ index( disorganized disorganized_pk ) */* from disorganized where x between 20000 and 40000; SELECT * FROM table(dbms_xplan.display_cursor(NULL,'runstats_last')); --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 20001 |00:00:00.09 | 21360 | | 1 | TABLE ACCESS BY INDEX ROWID| DISORGANIZED | 1 | 20002 | 20001 |00:00:00.09 | 21360 | |* 2 | INDEX RANGE SCAN | DISORGANIZED_PK | 1 | 20002 | 20001 |00:00:00.03 | 1375 | --------------------------------------------------------------------------------------------------------- ---看聚合因子,就明白真正的原因了。 select a.index_name, b.num_rows, b.blocks, a.clustering_factor from user_indexes a,user_tables b where index_name in ('COLOCATED_PK','DISORGANIZED_PK' ) and a.table_name = b.table_name; INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR ------------------------------ ---------- ---------- ----------------- COLOCATED_PK 100000 1252 1190 DISORGANIZED_PK 100000 1219 99899 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |