Oracle學習----叢集因子(Clustering Factor)

標籤：style   color   sp   檔案   資料   on   div   bs   ad   

1.叢集因子的演算法：通過dbms_rowid.rowid_block_number(rowid)找到記錄對應的block 號。索引中記錄了rowid，因此oracle 就可以根據索引中的rowid來判斷記錄是否是在同一個block 中。舉個例子，比如說索引中有a,b,c,d,e五個記錄，首先比較a,b 是否在同一個block，如果不在同一個block 那麼Clustering Factor +1，然後繼續比較b,c 同理，如果b,c 不在同一個block，那麼Clustering Factor+1，這樣一直進行下去，直到比較了所有的記錄。根據演算法我們就可以知道clustering factor 的值介於block 數和表行數之間。如果clustering factor 接近block 數，說明表的儲存和索引儲存排序接近，也就是說表中的記錄很有序，這樣在做index range scan 的時候能，讀取少量的data block 就能得到我們想要的資料，代價比較小。如果clustering factor 接近表記錄數，說明表的儲存和索引排序差異很大，在做index range scan 的時候，會額外讀取多個block，因為表記錄分散，代價較高。 2.什麼是rowid？

SQL> select rowid from t;

ROWID
------------------
AAASEzAAEAAAAFFAAA

SQL> select length(rowid) from t;

LENGTH(ROWID)
-------------
18

rowid一共18位

最前面6位表示data object number

之後後3位表示datafile number

之後後6位表示datablock number

最後面3位表示row number

 

3.如何根據rowid查詢出來相應的塊號等資訊?

SQL> select
2 rowid,
3 dbms_rowid.rowid_relative_fno(rowid) rel_fno,--返回rowid對應的檔案號
4 dbms_rowid.rowid_block_number(rowid) blockno,--返回rowid所在的塊號
5 dbms_rowid.rowid_row_number(rowid) rowno--返回該行資料在block中的相對位置
6 from t where rownum=1;

ROWID 　　　　　　 　　　　 REL_FNO  BLOCKNO   ROWNO
------------------------  ------------- ------------- -------
AAASEzAAEAAAAFFAAA 　　　　 4 　　　　 325 　　　　 0

 

4.能改叢集因子嗎？重建索引會改變叢集因子嗎？

不會，索引是根據建立時列的值排序建立的，只能create table .. order by 索引列，才能改變叢集因子。

5.叢集因子是如何影響效能的？

假設一個表有1千萬行，只需要返回1w行資料，走索引。

select * from t where rowid<=10000;

走索引要先返回10000個rowid，回表要回10000次

select * from test where rowid=xxxx 假設10000個rowid在10個塊中，那麼回表時，物理回表只有10次耗時10*10ms假設10000個rowid在10000個塊中，那麼回表時，物理回表只有10000次耗時100000*10ms 6.什麼情況下叢集因子不影響效能？1.不回表2.表都在buffer cache中3.返回資料少，主鍵掃描或走唯一索引就跟叢集因子沒有關係 7.叢集因子影響那個索引掃描？index range scan index full scanindex skip scanindex fast full scan跟rowid沒有關係，所以叢集因子不影響 8.反轉建索引會導致叢集因子的增加。

SQL> create table test as select * from dba_objects;

表已建立。

SQL> create index objid on test(object_id);

索引已建立。

SQL> select index_name,clustering_factor from user_indexes where table_name=‘TEST‘;

INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------ -----------------
OBJID 1085

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid))from test;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
1027

SQL> select count(*) from test;

COUNT(*)
----------
72063

反轉建立索引

SQL> alter index objid rebuild reverse;

索引已更改。

SQL> select index_name,clustering_factor from user_indexes where table_name=‘TEST‘;

INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------ -----------------
OBJID 72061

SQL> select count(*) from test;

COUNT(*)
----------
72063

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid))from test;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
1027

 

9.sql實現叢集因子的演算法

WITH T AS
(SELECT OWNER COLUMN_NAME,
LEAD(OWNER, 1, OWNER) OVER(ORDER BY OWNER) NEXT_COLUMN_NAME,
ROWID ROWID_NUM,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_ID,
LEAD(ROWID) OVER(ORDER BY OWNER) NEXT_ROWID_NUM,
LEAD(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),
1,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) OVER(ORDER BY OWNER) NEXT_BLOCK_ID
FROM TEST A
WHERE OWNER IS NOT NULL
ORDER BY OWNER)
SELECT COUNT(*) 記錄數,COUNT(DISTINCT BLOCK_ID) BLOCK_ID_SUM,
SUM(CASE
WHEN T.BLOCK_ID = T.NEXT_BLOCK_ID THEN
0
ELSE
1
END) + 1 叢集因子,
SUM(CASE
WHEN T.COLUMN_NAME = T.NEXT_COLUMN_NAME AND
T.BLOCK_ID <> T.NEXT_BLOCK_ID THEN
1
ELSE
0
END) 值同_塊不同
FROM T;

 

 

 

 

 

 

 

  

Oracle學習----叢集因子(Clustering Factor)