實驗-資料分佈對執行計劃的影響.txt

D:\Documents and Settings\tian>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 5 10:22:14 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/scott@test
已連線。

SQL> create table t1 as select trunc((rownum-1)/100) id,rpad(rownum,100) t_pad
  2  from dba_Source
  3  where rownum<10000;

Table created

SQL> create index t1_idx1 on t1(id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed

SQL> drop table t2;

Table dropped

SQL> create table t2 as select mod(rownum,100) id,rpad(rownum,100) t_pad
  2  from dba_Source
  3  where rownum<10000;

Table created

SQL> create index t2_idx1 on t2(id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);

PL/SQL procedure successfully completed

以上分別通過trunc和mod兩個函式建立了兩張內容相同，但資料排列不一樣的表。
注意一下查詢中兩個表的前十行，ID排列不同。
SQL> select * from t1 where rownum<10;

        ID T_PAD
---------- --------------------------------------------------------------------------------
         0 1
         0 2
         0 3
         0 4
         0 5
         0 6
         0 7
         0 8
         0 9

9 rows selected

SQL> select * from t2 where rownum<10;

        ID T_PAD
---------- --------------------------------------------------------------------------------
         1 1
         2 2
         3 3
         4 4
         5 5
         6 6
         7 7
         8 8
         9 9

9 rows selected

分別在兩個表上進行查詢，選取第二次的結果：
SQL> select * from t1 where id=1;

已選擇100行。

執行計劃
----------------------------------------------------------
Plan hash value: 2623418078

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   100 | 10300 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   100 | 10300 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX1 |   100 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
      12167  bytes sent via SQL*Net to client
        451  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> select * from t2 where id=1;

已選擇100行。

執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 | 10300 |    39   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |   100 | 10300 |    39   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        163  consistent gets
          0  physical reads
          0  redo size
      11845  bytes sent via SQL*Net to client
        451  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

T1是順序排列的，使用索引方式查詢，成本比全表掃描要低；
T2是分散儲存的，使用全表掃描的效率比索引高。

以上實驗提示我們：資料的分佈情況在很大程度上也會影響執行計劃。
通常來說，根據執行查詢的頻率，將資料有序排列，使每次需要獲取的一批資料放在一起，能夠提高效率。

索引的聚簇因子：向優化器表明具有同樣索引值的資料行是不是存放在同一個或連續的一系列資料塊中。
以上兩個表的索引聚簇因子資訊為：

SQL> select t.table_name||'.'||i.index_name idx_name,
  2  i.clustering_factor,t.blocks,t.num_rows
  3  from user_indexes i,user_tables t
  4  where i.table_name=t.table_name
  5  and t.table_name in('T1','T2')
  6  order by t.table_name,i.index_name;

IDX_NAME                                                      CLUSTERING_FACTOR     BLOCKS   NUM_ROWS
------------------------------------------------------------- ----------------- ---------- ----------
T1.T1_IDX1                                                                  152        164       9999
T2.T2_IDX1                                                                 9999        164       9999

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-760705/，如需轉載，請註明出處，否則將追究法律責任。