那么表和索引的顺序是一样的。不过，如果clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。
Oracle 在 dba_indexes 视图中提供一个名为 clustering_factor 的列，通知优化器关于表的行与索引的同步情况。当集簇因子接近数据块的数量时，表的行与索引同步。
列值的选择性、db_block_size、avg_row_len 以及集合基数全都协同工作，帮助优化器决定是使用索引还是使用全表扫描。如果数据列具有高度的选择性和低的 clustering_factor，
则索引扫描通常是最快的执行方法（参见图 2）。
如果多数 SQL 引用了具有高 clustering_factor、大 db_block_size 和小 avg_row_len 的列，则 DBA 有时会周期性地对表的行进行重排序或使用单表集簇来维持行的顺序。
这种方法将所有相邻的行放置在同一数据块中，消除了全表扫描，使查询速度的增加高达 30 倍。
相反，高 clustering_factor 的数值达到表中的行数 (num_rows)，表明这些行的顺序与索引中的顺序不同，索引范围扫描将会需要额外的 I/O。由于 clustering_factor 达到表中的行数，
这些行与索引不同步。
但是，即使列具有高度的选择性，高 clustering_factor 和小 avg_row_len 也会表示列值在表中随机分布，而获取这些行需要额外的 I/O。在此情况下，索引范围扫描会导致大量不必要的 I/O（参见图 3）；
全表扫描则会高效得多。
总而言之，clustering_factor、db_block_size 和 avg_row_len 全都影响优化器有关执行全表扫描或是索引范围扫描的决策，理解优化器如何使用这些统计信息非常重要。
我们已经注意到，每个新版本的优化器都有改进，并且 Oracle Database 10g 提供的最新增强特性在确定执行计划时会考虑外部的影响。Oracle 称此特性为外部成本核算，并包括对 CPU 和 I/O 成本的评估。
创建测试表：
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table t
2 as
3 select * from all_objects
4 where rownum<200;
Table created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select * from user_tables
2 where table_name='T';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- -------------------- ------------------
T DEMO VALID 10 1 255 65536 1 2147483645 YES N 199 6 2 2676 0 79 0 0 1 1
SQL> show parameter db_block
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
SQL> drop table t;
Table dropped.
行长大约80字节，每个数据块大约可放置90行。
SQL> create table t
2 as
3 select * from all_objects
4 where rownum<91;
Table created.
SQL> insert into t
2 select * from t;
90 rows created.
SQL> /
180 rows created.
SQL> /
360 rows created.
SQL> /
720 rows created.
SQL> /
1440 rows created.
SQL> /
2880 rows created.
SQL> /
5760 rows created.
SQL> /
11520 rows created.
插入180000行数据，行数据相同的行分布在不同的块上。
SQL> commit;
Commit complete.
SQL> create index t_ind on t(object_id);
Index created.
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> select * from user_tables
2 where table_name='T';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- -------------------- ------------------
T DEMO VALID 10 1 255 65536 1 2147483645 YES N 23040 309 75 1494 0 77 0 0 1 1
SQL> set linesize 2000
SQL> select * from user_indexes
2 where table_name='T';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOC
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- -------------
T_IND NORMAL SCOTT T TABLE NONUNIQUE DISABLED DEMO 2 255 65536 1 2147483645 10 YES 1 45 90 1
SQL> set timing on
SQL> select * from user_tables
2 where table_name='T';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON CLUSTER_OWNER DEPENDEN COMPRESS DRO
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- -------------------- -------------------- ---------- -------- ----------- --------- --- ------------ - - --- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ---
T DEMO VALID 10 1 255 65536 1 2147483645 YES N 184320 2011 37 839 0 77 0 0 1 1 N ENABLED 184320 25-OCT-06 NO N N NO DEFAULT DISABLED NO NO DISABLED YES DISABLED DISABLED NO
Elapsed: 00:00:00.24
SQL> select * from user_indexes
2 where table_name='T';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE INSTANCES PAR T G S BUFFER_ USE DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ---------------------------------------- ---------------------------------------- --- - - - ------- --- --------------- ----------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- ------------ ------ -------- --- --- ---
T_IND NORMAL SCOTT T TABLE NONUNIQUE DISABLED DEMO 2 255 65536 1 2147483645 10 YES 2 629 90 6 1801 162113 VALID 184320 184320 25-OCT-06 1 1 NO N N N DEFAULT NO NO NO NO NO
Elapsed: 00:00:00.18
聚集因子与表的行数接近，说明数据分布不好。
SQL> set autotrace traceonly;
SQL> select * from t
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:01.62
Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 94633 | 1089 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1229 | 94633 | 1089 (1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | T_IND | 1229 | | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1772 consistent gets
95 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:02.17
SQL> select * from t
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:25.72
Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 94633 | 1089 (1)| 00:00:14 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1229 | 94633 | 1089 (1)| 00:00:14 |
|* 2 | INDEX RANGE SCAN | T_IND | 1229 | | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1772 consistent gets
1608 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
以上可以看到查询用了很长时间
对表中的数据进行重组，重新排序表中的行的物理顺序，与索引健的顺序一致
行重新排序的作用是不可以小看。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。
可以使用以下的工具之一来重新组织表格。
. 使用Oracle的Create Table As Select (CTAS) 语法来拷贝表格
.使用索引聚集
下面分别就这两种方法作试验：
使用Oracle的Create Table As Select (CTAS) 语法来拷贝表格
SQL> set autotrace off
SQL> drop table t3;
drop table t3
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.17
SQL> create table t3
2 as
3 select * from t
4 order by object_id;
Table created.
（使用order by方法重组表数据）
Elapsed: 00:04:00.51
SQL> create index t3_ind on t3(object_id);
Index created.
Elapsed: 00:00:42.16
SQL> analyze table t3 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:24.87
SQL> select * from user_tables
2 where table_name='T3';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON CLUSTER_OWNER DEPENDEN COMPRESS DRO
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- -------------------- -------------------- ---------- -------- ----------- --------- --- ------------ - - --- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ---
T3 DEMO VALID 10 1 255 65536 1 2147483645 YES N 184320 2061 115 854 0 77 0 0 1 1 N ENABLED 184320 25-OCT-06 NO N N NO DEFAULT DISABLED NO NO DISABLED YES DISABLED DISABLED NO
Elapsed: 00:00:00.15
SQL> select * from user_indexes
2 where table_name='T3';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE INSTANCES PAR T G S BUFFER_ USE DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ---------------------------------------- ---------------------------------------- --- - - - ------- --- --------------- ----------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- ------------ ------ -------- --- --- ---
T3_IND NORMAL SCOTT T3 TABLE NONUNIQUE DISABLED DEMO 2 255 65536 1 2147483645 10 YES 1 360 90 4 22 2019 VALID 184320 184320 25-OCT-06 1 1 NO N N N DEFAULT NO NO NO NO NO
Elapsed: 00:00:00.58
SQL> set autotrace traceonly
SQL> select * from t3
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:00.52
Execution Plan
----------------------------------------------------------
Plan hash value: 2495931480
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 94633 | 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1229 | 94633 | 17 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T3_IND | 1229 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
277 recursive calls
0 db block gets
327 consistent gets
13 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.93
SQL> select * from t3
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:00.69
Execution Plan
----------------------------------------------------------
Plan hash value: 2495931480
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 94633 | 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1229 | 94633 | 17 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T3_IND | 1229 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
303 consistent gets
29 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
可以看到表数据物理重组后，查询性能大大的提高。
SQL> set autotrace off
SQL> create table t5
2 as
3 select /*+index(t t_ind)*/ * from t;
（使用索引的方法重组表数据）
Table created.
Elapsed: 00:02:12.74
SQL> analyze table t5 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:20.70
SQL> select * from user_tables
2 where table_name='T5';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON CLUSTER_OWNER DEPENDEN COMPRESS DRO
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- --- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- -------------------- -------------------- ---------- -------- ----------- --------- --- ------------ - - --- ------- -------- --- --- --------------- -------- --- ------------------------------ -------- -------- ---
T5 DEMO VALID 10 1 255 65536 1 2147483645 YES N 184320 2061 115 854 0 77 0 0 1 1 N ENABLED 184320 25-OCT-06 NO N N NO DEFAULT DISABLED NO NO DISABLED YES DISABLED DISABLED NO
Elapsed: 00:00:00.38
SQL> select * from user_indexes
2 where table_name='T5';
no rows selected
Elapsed: 00:00:00.54
SQL> create index t5_ind on t5(object_id);
Index created.
Elapsed: 00:00:49.26
SQL> analyze table t5 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
Elapsed: 00:00:43.25
SQL> select * from user_indexes
2 where table_name='T5';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE INSTANCES PAR T G S BUFFER_ USE DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ---------------------------------------- ---------------------------------------- --- - - - ------- --- --------------- ----------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- ------------ ------ -------- --- --- ---
T5_IND NORMAL SCOTT T5 TABLE NONUNIQUE DISABLED DEMO 2 255 65536 1 2147483645 10 YES 1 360 90 4 22 2019 VALID 184320 184320 25-OCT-06 1 1 NO N N N DEFAULT NO NO NO NO NO
Elapsed: 00:00:00.55
SQL> set autotrace traceonly
SQL> select * from t3
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:01.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2495931480
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 94633 | 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1229 | 94633 | 17 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T3_IND | 1229 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
249 recursive calls
0 db block gets
355 consistent gets
30 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
2048 rows processed
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:01.67
SQL> select * from t3
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:00.73
Execution Plan
----------------------------------------------------------
Plan hash value: 2495931480
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1229 | 94633 | 17 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1229 | 94633 | 17 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T3_IND | 1229 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
303 consistent gets
29 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
使用索引聚集
SQL> set autotrace off
SQL> create cluster t_cluster
2 (object_id number);
Cluster created.
Elapsed: 00:00:00.87
SQL> create index t_cluster_ind on cluster t_cluster;
Index created.
Elapsed: 00:00:00.29
SQL> create table t6
2 cluster t_cluster(object_id)
3 as
4 select * from t
5 where rownum<1;
Table created.
Elapsed: 00:00:00.91
SQL> insert into t6
2 select * from t;
184320 rows created.
Elapsed: 00:05:55.43
SQL> analyze cluster t_cluster compute statisyics;
analyze cluster t_cluster compute statisyics
*
ERROR at line 1:
ORA-00905: missing keyword
Elapsed: 00:00:00.06
SQL> analyze cluster t_cluster compute statistics;
Cluster analyzed.
Elapsed: 00:00:08.10
SQL> select * from user_clusters;
CLUSTER_NAME TABLESPACE_NAME PCT_FREE PCT_USED KEY_SIZE INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS AVG_BLOCKS_PER_KEY CLUST FUNCTION HASHKEYS DEGREE INSTANCES CACHE BUFFER_ SINGLE_TAB DEPENDEN
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------------ ----- --------------- ---------- -------------------- -------------------- ---------- ------- ---------- --------
T_CLUSTER DEMO 10 2 255 65536 1 2147483645 22 INDEX 0 1 1 N DEFAULT N DISABLED
Elapsed: 00:00:06.73
SQL> select * from user_indexes
2 where index_name='T_CLUSTER_IND';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE INSTANCES PAR T G S BUFFER_ USE DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- --------- ---------------------------------------- ---------------------------------------- --- - - - ------- --- --------------- ----------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- ------------ ------ -------- --- --- ---
T_CLUSTER_IND CLUSTER SCOTT T_CLUSTER CLUSTER UNIQUE DISABLED DEMO 2 255 65536 1 2147483645 10 YES 0 1 90 1 22 90 VALID 90 90 25-OCT-06 1 1 NO N N N DEFAULT NO NO NO NO NO
Elapsed: 00:00:00.19
SQL> set autotrace traceonly
SQL> select * from t6
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:00.50
Execution Plan
----------------------------------------------------------
Plan hash value: 1494129050
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2048 | 126K| 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS CLUSTER| T6 | 2048 | 126K| 22 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_CLUSTER_IND | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:18.27
SQL> select * from t6
2 where object_id=29;
2048 rows selected.
Elapsed: 00:00:00.72
Execution Plan
----------------------------------------------------------
Plan hash value: 1494129050
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2048 | 126K| 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS CLUSTER| T6 | 2048 | 126K| 22 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_CLUSTER_IND | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=29)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
161 consistent gets
24 physical reads
0 redo size
30786 bytes sent via SQL*Net to client
1881 bytes received via SQL*Net from client
138 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2048 rows processed
SQL> set autotrace off
由上试验可知，clustering_factor具有很大值时，严重影响数据库查询性能，这可以通过对表中数据物理重组，降低clustering_factor,以消除它的影响。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472903/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9599/viewspace-472903/
965
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
