Shulou(Shulou.com)06/01 Report--

--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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.