集群因子 Oracle,Oracle学习----集群因子(Clustering Factor)

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;