oracle 修改聚簇因子,Oralce索引聚簇因子

最新推荐文章于 2024-01-22 09:43:32 发布

转载最新推荐文章于 2024-01-22 09:43:32 发布 · 205 阅读

文章标签：

#oracle 修改聚簇因子

本文通过创建有序和无序两张表进行SQL查询性能对比实验，分析了聚簇因子对查询性能的影响。实验证明，有序表相较于无序表在相同查询条件下表现出更好的查询性能。

Sql代码

--聚合因子试验准备:

--分别建两张有序和无序的表

CREATE TABLE T_COLOCATED ( ID NUMBER, COL2 VARCHAR2(100) );

BEGIN

FOR I IN 1 .. 100000

LOOP

INSERT INTO T_COLOCATED(ID,COL2)

VALUES (I, RPAD(DBMS_RANDOM.RANDOM,95,'*') );

END LOOP;

END;

ALTER TABLE T_COLOCATED ADD CONSTRAINT PK_T_COLOCATED PRIMARY KEY(ID);

CREATE TABLE T_DISORGANIZED

SELECT ID,COL2

FROM T_COLOCATED

ORDER BY COL2;

ALTER TABLE T_DISORGANIZED ADD CONSTRAINT PK_T_DISORG PRIMARY KEY (ID);

--分别分析两张表的聚合因子层度

SELECT INDEX_NAME,

BLEVEL,

LEAF_BLOCKS,

NUM_ROWS,

DISTINCT_KEYS,

CLUSTERING_FACTOR

FROM USER_IND_STATISTICS

WHERE TABLE_NAME IN( 'T_COLOCATED','T_DISORGANIZED');

INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR

------------------------------ ---------- ----------- ---------- ------------- -----------------

PK_T_COLOCATED 1 208 100000 100000 1469

PK_T_DISORG 1 208 100000 100000 99932

--首先观察有序表的查询性能

执行并比较性能差异

select /*+index(t)*/ * from t_colocated t where id>=20000 and id<=40000;

执行计划

----------------------------------------------------------

Plan hash value: 4204525375

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 21104 | 1339K| 389 (1)| 00:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_COLOCATED | 21104 | 1339K| 389 (1)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | PK_T_COLOCATED | 21104 | | 53 (2)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID">=20000 AND "ID"<=40000)

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

2986 consistent gets

0 physical reads

0 redo size

2293678 bytes sent via SQL*Net to client

15048 bytes received via SQL*Net from client

1335 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20001 rows processed

再观察无序表的查询性能

select /*+index(t)*/ * from t_disorganized t where id>=20000 and id<=40000;

已用时间: 00: 00: 09.75

执行计划

----------------------------------------------------------

Plan hash value: 4204525375

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 21104 | 1339K| 389 (1)| 00:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_COLOCATED | 21104 | 1339K| 389 (1)| 00:00:05 |

|* 2 | INDEX RANGE SCAN | PK_T_COLOCATED | 21104 | | 53 (2)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID">=20000 AND "ID"<=40000)

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

2986 consistent gets

0 physical reads

0 redo size

2293678 bytes sent via SQL*Net to client

15048 bytes received via SQL*Net from client

1335 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

20001 rows processed

--Oracle文档对聚簇因子的解释