Index optimization series three aggregation factors
--colocated table has a certain physical order according to the x column
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 table data is completely disorganized according to x columns
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
---The performance difference between the two is significant
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,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 |
---------------------------------------------------------------------------------------------------------
---Look at the aggregation factor and see the real reason.
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
