oracle聚簇因子對查詢效能的影響

create table t0

(

sid int not null ,

sname varchar2(20)

)

tablespace test;

--迴圈匯入資料

declare

       maxrecords constant int:=100000;

       i int :=1;

   begin

       for i in 1..maxrecords loop

         insert into t0 values(i,'ocpyang'||i);

       end loop;

   dbms_output.put_line(' 成功錄入資料！ ');

   commit;

   end;

/

create table t1

as

select sid,sname from t0 order by sname desc;

create index index_t1 on t1(sid);

create table t2

as

select sid,sname from t0 order by sid  asc;

create index index_t2 on t2(sid asc);

--分析兩張表及其索引

EXEC DBMS_STATS.gather_table_stats(USER, 'T1');

EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

EXEC DBMS_STATS.gather_index_stats(USER, 'INDEX_T1');

EXEC DBMS_STATS.gather_index_stats(USER, 'INDEX_T2');

---比較同一個查詢

set autot traceonly stat;

SELECT * FROM t1 WHERE sid BETWEEN 100 AND 120;

統計資訊

----------------------------------------------------------

         5  recursive calls

         4  db block gets

        15  consistent gets  一致讀

         0  physical reads

       540  redo size

      1240  bytes sent via SQL*Net to client

       530  bytes received via SQL*Net from client

         3  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        21  rows processed

SELECT * FROM t2 WHERE sid BETWEEN 100 AND 120;

統計資訊

----------------------------------------------------------

         6  recursive calls

         4  db block gets

         9  consistent gets  一致讀

         0  physical reads

       540  redo size

      1240  bytes sent via SQL*Net to client

       530  bytes received via SQL*Net from client

         3  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        21  rows processed

set autotrace off;