Clustering factor is a property of the index, the clustering factor actually relates to the spread of similar indexed column values within data blocks in the table. A lower clustering factor indicates that the individual rows are concentrated within fewer blocks in the table. Conversely, a high clustering factor indicates that the individual rows are scattered more randomly across blocks in the table. Therefore, a high clustering factor means that it costs more to use a range scan to fetch rows by rowid, because more blocks in the table need to be visited to return the data.
A quick demonstration about that.
create table t1 as select * from all_objects order by object_id;
create table t2 as select * from all_objects order by object_name,owner;
create index t1_ind on t1(object_id);
create index t2_ind on t2(object_id);
EXEC dbms_stats.gather_table_stats( ownname=>USER,tabname=>'T1',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
cascade=>TRUE);
EXEC dbms_stats.gather_table_stats( ownname=>USER,tabname=>'T2',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
cascade=>TRUE);
scott@10G> SELECT tab.table_name, ind.index_name, tab.blocks,tab.num_rows,ind.clustering_factor
2 FROM user_tables tab,
3 user_indexes ind
4 WHERE tab.table_name = ind.table_name
5 and tab.table_name IN ('T1','T2');
TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR
---------- ---------- ------ -------- -----------------
T1 T1_IND 726 51393 706
T2 T2_IND 726 51394 26438
scott@10G> SELECT /*+ index(t1,t1_ind) */ *
2 FROM T1
3 WHERE object_id BETWEEN 2 AND 20000;
19394 rows selected.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15804 | 1435K| 257 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15804 | 1435K| 257 (1)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | T1_IND | 15804 | | 38 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
559 consistent gets
0 physical reads
0 redo size
775325 bytes sent via SQL*Net to client
1815 bytes received via SQL*Net from client
131 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19394 rows processed
scott@10G> SELECT /*+ index(t2,t2_ind) */ *
2 FROM T2
3 WHERE object_id BETWEEN 2 AND 20000;
19394 rows selected.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15803 | 1435K| 8187 (1)| 00:01:39 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 15803 | 1435K| 8187 (1)| 00:01:39 |
|* 2 | INDEX RANGE SCAN | T2_IND | 15803 | | 38 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10088 consistent gets
0 physical reads
0 redo size
775325 bytes sent via SQL*Net to client
1815 bytes received via SQL*Net from client
131 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19394 rows processed
No comments:
Post a Comment