索引学习五--物理组织对索引访问的影响

1. 创建2个测试表，一个表的数据按照顺序存放，另一个随机存放。

sys@TEST>createtable colocated(x int,y varchar2(80));
 
Table created.
 
sys@TEST>begin
  2  for i in 1 .. 100000
  3  loop
  4  insert into colocated(x,y)
  5  values(i,rpad(dbms_random.random,75,'*'));
  6  end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
sys@TEST>altertable colocated
  2  add constraint colocated_pk
  3  primary key(x);
 
Table altered.
 
sys@TEST>execdbms_stats.gather_table_stats(user,'COLOCATED',cascade=>true);
 
PL/SQL procedure successfully completed.
 
sys@TEST>createtable disorganized
  2  as select x,y
  3  from colocated
  4  order by y;
 
Table created.
 
sys@TEST>altertable disorganized
  2  add constraints disorganized_pk
  3  primary key(x);
 
Table altered.

sys@TEST>execdbms_stats.gather_table_stats(user,'DISORGANIZED',cascade=>true);
 
PL/SQL procedure successfully completed.

2.分别对这两个表进行条件查询。

sys@TEST>setautotrace traceonly statistics;
sys@TEST>select* from colocated where x between 20000 and 40000;
 
20001 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2482  consistent gets
          0  physical reads
          0  redo size
    1805701  bytes sent via SQL*Net to client
      15166  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      20001  rows processed
 
sys@TEST>select* from  DISORGANIZED where x between 20000 and 40000;
 
20001 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2518  consistent gets
          0  physical reads
          0  redo size
    1805701  bytes sent via SQL*Net to client
      15166  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      20001  rows processed

发现2个查询的consistent gets差不多，查看执行计划，

sys@TEST>select* from colocated where x between 20000 and 40000;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=116 Card=20002 Bytes
          =1600160)
 
   1    0   TABLE ACCESS (FULL) OF 'COLOCATED' (Cost=116 Card=20002 By
          tes=1600160)
 

sys@TEST>select* from  DISORGANIZED where x between 20000 and 40000;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=116 Card=20002 Bytes
          =1600160)
 
   1    0   TABLE ACCESS (FULL) OF 'DISORGANIZED' (Cost=116 Card=20002
           Bytes=1600160)
 

原来均使用了全表扫描，强制使用索引进行查询：

sys@TEST>select/*+ index(colocated colocated_pk) */* from colocated where x between 20000 and 40000;
 
20001 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2899  consistent gets
          0  physical reads
          0  redo size
    1805701  bytes sent via SQL*Net to client
      15166  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      20001  rows processed