索引优化系列三 聚合因子

--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 * 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 * from disorganized  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.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