Oracle Clustering Factor

1, Clustering factor (Clustering_factor): is the use of B-tree index to the cost of interval scanning is very important factors, reflecting the random degree of data distribution in the table

2, the cluster Factor calculation method:
① Scan Index
② compares the rowid of a row with the rowid of the previous line, and if the two rowid do not belong to the same block, the cluster factor increases by 1

--The only column selection rate and clustering factor which have the most influence on selecting the optimal query path

3. Experiment Test

CREATE TABLE T1 AS
Select Trunc ((rownum-1)/100) ID,
Rpad (rownum,100) T_pad
From Dba_source
where RowNum <= 10000;
CREATE index t1_idx1 on T1 (ID);
exec dbms_stats.gather_table_stats (user, ' T1 ',method_opt=> ' for all COLUMNS SIZE 1 ', cascade=>true);

CREATE TABLE T2 as
Select mod (rownum,100) ID,
Rpad (rownum,100) T_pad
From Dba_source
where RowNum <= 10000;
Create index t2_idx1 on T2 (ID);
exec dbms_stats.gather_table_stats (user, ' T2 ',method_opt=> ' for all COLUMNS SIZE 1 ', cascade=>true);
Select COUNT (*) CT from t1 where id = 1;
Select COUNT (*) CT from t2 where id = 1;

--Index clustered Factor
Select T.table_name| | '. ' | | I.index_name Idx_name,
I.clustering_factor, T.blocks, t.num_rows
from user_indexes i, User_tables t
where I.T Able_name = T.table_name
and T.table_name in (' T1 ', ' T2 ')
Order by T.table_name, I.index_name;

--Compute the index's clustered factor
Select T.table_name| | '. ' | | I.index_name Idx_name,
I.clustering_factor, T.blocks, t.num_rows
from all_indexes i, All_tables t
where i.tab Le_name = T.table_name
and t.table_name = ' EMPLOYEES '
and T.owner = ' HR '
and i.index_name = ' Emp_department_ix '
Order by T.table_name, I.index_name;

Select department_id, last_name, Blk_no,
Lag (blk_no,1,blk_no) over (order by department_id) PREV_BLK_NO,
Blk_no!= Lag (blk_no,1,blk_no) over (order by department_id)
or rownum = 1
Then ' * * * +1 '
Else nul L
End cluf_ct
from (
Select department_id, last_name,
Dbms_rowid.rowid_block_number (rowid) blk_no
From Hr.employees
where departmeNT_ID is isn't null
ORDER by department_id
);

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/