demo06.sql

Expert Oracle Database Architecture 9i and 10g sql源码

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

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

select * from colocated where x between 20000 and 40000;
select /*+ index( disorganized disorganized_pk ) */* from disorganized
   where x between 20000 and 40000;

select table_name, blocks
from user_tables
where table_name in ( 'COLOCATED', 'DISORGANIZED' );


select * from disorganized where x between 20000 and 40000;


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
/


select count(Y)
from
 (select /*+ INDEX(COLOCATED COLOCATED_PK) */ * from colocated);

select count(Y)
from
 (select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized);

select * from colocated where x between 20000 and 30000;

select * from disorganized where x between 20000 and 30000;