实验-数据分布对执行计划的影响.txt-CSDN博客

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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-760705/