聚集因子 cluster factor

一:what

 1: 聚集因子 是索引数据顺序和表顺序的一致性程度
 2: 取值范围为:最小数据块数,最大为行数,值越小越好!

二:why

 1:聚集因子会影响sql的执行效率
 2:比如表结构,数据量,索引都一样,生产和测试环境sql执行效率大不一致,
    这种情况下就可能是聚集因子不一致导致的

三:how

drop table t_test;
create table t_test(t_id number(20),t_num number(20));
insert into t_test 
select level,dbms_random.random from dual connect by level <= 1000000;
create index t_test_idx on t_test(t_id);

drop table t_test2;
create table t_test2(t_id number(20),t_num number(20));
insert into t_test2 
select * from t_test order by dbms_random.random;
create index t_test2_idx on t_test2(t_id);

explain plan for select * from t_test where t_id between 2000 and 9000;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1773029164
--------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  8315 |   211K|    48   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST     |  8315 |   211K|    48   (0)|
|*  2 |   INDEX RANGE SCAN          | T_TEST_IDX |  8315 |       |    24   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T_ID">=2000 AND "T_ID"<=9000)
Note
-----
   - dynamic sampling used for this statement (level=2)
18 rows selected


--执行计划
explain plan for select * from t_test2 where t_id between 2000 and 9000;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1680898452
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  6904 |   175K|   668   (3)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| T_TEST2 |  6904 |   175K|   668   (3)| 00:00:09 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T_ID">=2000 AND "T_ID"<=9000)
Note
-----
   - dynamic sampling used for this statement (level=2)
17 rows selected



--查看聚集因子
SELECT index_name,table_name,blevel,leaf_blocks,clustering_factor,num_rows
  FROM user_indexes
 WHERE lower(table_name) LIKE 't_test%';

INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ----------------- ----------
T_TEST_IDX                     T_TEST                                  2        2226              2401    1000000
T_TEST2_IDX                    T_TEST2                                 2        2226            999612    1000000

分析:
1:一个索引范围扫描获取数据,另一个全表扫描
2:cost 一个48,另外一个668,性能提升14倍!

阅读更多
上一篇count大揭秘
下一篇sql语句执行顺序
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭