Oracle中的clustering factor
from http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1032431852141
the clustering factor is a measure of how sorted a TABLE is with respect to it's index key.
since you never hit the table, the clustering factor simply didn't matter.
of course both would do the same number of IO's - they both only read the index and the indexes in this case are *identical*
You have to take a query that goes from the index *to the table* to see any effect.
for example:
ops$tkyte%ORA11GR2> create table organized
2 as
3 select *
4 from all_objects
5 order by object_id;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'ORGANIZED' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> alter table organized add constraint organized_pk primary key(object_id);
Table altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table disorganized
2 as
3 select *
4 from organized
5 order by dbms_random.random;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'DISORGANIZED' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> alter table disorganized add constraint disorganized_pk primary key(object_id);
Table altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select i.index_name, i.clustering_factor, t.blocks, t.num_rows
2 from user_indexes i, user_tables t
3 where t.table_name in ('ORGANIZED','DISORGANIZED')
4 and t.table_name = i.table_name
5 /
INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------ ----------------- ---------- ----------
DISORGANIZED_PK 72882 1065 72951
ORGANIZED_PK 1039 1065 72951
so we can see the data is constructed as we desire - the organized table has a 'good' clustering
factor on object id - very near the number of blocks in the table, while the disorganized table has
a 'bad' one - near the number of rows.
now for a query that goes from index to table for every row:
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_monitor.session_trace_enable();
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select /*+ index( organized organized_pk ) */ count(subobject_name) from organized where object_id > 0;
COUNT(SUBOBJECT_NAME)
---------------------
667
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select /*+ index( disorganized disorganized_pk ) */ count(subobject_name) from disorganized where object_id > 0;
COUNT(SUBOBJECT_NAME)
---------------------
667
and tkprof says:
select /*+ index( organized organized_pk ) */ count(subobject_name) from organized where object_id > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.12 0.12 152 1192 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.12 0.12 152 1192 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 931
Number of plan statistics captured: 1
Row Source Operation
---------------------------------------------------
SORT AGGREGATE (cr=1192 pr=152 pw=0 time=124309 us)
TABLE ACCESS BY INDEX ROWID ORGANIZED (cr=1192 pr=152 pw=0 time=98651 us cost=1194 size=510657 card=72951)
INDEX RANGE SCAN ORGANIZED_PK (cr=153 pr=152 pw=0 time=33548 us cost=154 size=0 card=72951)(object id 131875)
if we take 1192 and subtract the 153 IO's we did on the index - we get 1039 - the exact clustering factor for the index...
select /*+ index( disorganized disorganized_pk ) */ count(subobject_name) from disorganized where object_id > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.24 0.24 152 73035 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.24 0.24 152 73035 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 931
Number of plan statistics captured: 1
Row Source Operation
---------------------------------------------------
SORT AGGREGATE (cr=73035 pr=152 pw=0 time=242386 us)
TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=73035 pr=152 pw=0 time=215021 us cost=73058 size=510657 card=72951)
INDEX RANGE SCAN DISORGANIZED_PK (cr=153 pr=152 pw=0 time=38186 us cost=154 size=0 card=72951)(object id 131877)
and 73035-153 = 72882, the exact clustering factor of the disorganized index
