CLUSTERING_FACTOR影響執行計劃

SCOTT@ prod1>select count(*) from t2;


  COUNT(*)
----------
     72594
SCOTT@ prod1>select num_rows,blocks from user_tables;
  NUM_ROWS     BLOCKS
---------- ----------
     72594       1059
SCOTT@ prod1>select 72594/1059 from dual;
72594/1059
----------
68.5495751

SCOTT@ prod1>drop table t2;


Table dropped.


SCOTT@ prod1>create table t2 as select * from dba_objects where rownum<69;


Table created.


SCOTT@ prod1>analyze table t2 compute statistics;


Table analyzed.
SCOTT@ prod1>insert into t2 select * from t2;


8832 rows created.
SCOTT@ prod1>commit;


SCOTT@ prod1>create index ind_t2 on t2(object_id);


Index created.
SCOTT@ prod1>analyze table t2 compute statistics for all indexed columns;


Table analyzed.



SCOTT@ prod1>analyze table t2 compute statistics;


Table analyzed.
SCOTT@ prod1>select table_name,num_rows,blocks from user_tables;
T2                                  17664        244


SCOTT@ prod1>select * from t2 where object_id=11;


256 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 | 20224 |    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |   256 | 20224 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=11)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        266  consistent gets
          0  physical reads
          0  redo size
       5155  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed
SCOTT@ prod1>create table t3 as select * from t2 order by object_id;


Table created.


SCOTT@ prod1>create index ind_t3 on t3(object_id);


Index created.


SCOTT@ prod1>analyze table t3 compute statistics;


Table analyzed.


SCOTT@ prod1>select index_name,table_name,clustering_factor from user_indexes where table_name = 'T3';


INDEX_NAME                     TABLE_NAME                     CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
IND_T3                         T3                                           202


SCOTT@ prod1>select table_name,num_rows,blocks from user_tables where table_name = 'T2';


TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T2                                  17664        244


SCOTT@ prod1>select table_name,num_rows,blocks from user_tables where table_name = 'T3';


TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T3                                  17664        212


SCOTT@ prod1>
SCOTT@ prod1>
SCOTT@ prod1>set autotrace traceonly;
SCOTT@ prod1>select * from t3 where object_id=11;
256 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1203691736


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   256 | 20224 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3     |   256 | 20224 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T3 |   256 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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


   2 - access("OBJECT_ID"=11)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         41  consistent gets
          0  physical reads
          0  redo size
      24259  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed
SCOTT@ prod1>select /*+ full(t3) */ * from t3 where object_id = 11;


256 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 4161002650


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   256 | 20224 |    59   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T3   |   256 | 20224 |    59   (0)| 00:00:01 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=11)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        222  consistent gets
          0  physical reads
          0  redo size
       5155  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
         19  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        256  rows processed