create index END_DT_IDX1 on F_AGT_BUSINESS_CONTRACT_H(end_dt); SQL> explain plan for select * from F_AGT_BUSINESS_CONTRACT_H t where t.end_dt = date '2999-12-31'; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3544262987 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 238K| 140M| 45269 (1)| 00:09:04 | |* 1 | TABLE ACCESS FULL| F_AGT_BUSINESS_CONTRACT_H | 238K| 140M| 45269 (1)| 00:09:04 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 13 rows selected. 为什么没走索引呢? 1.查看数据分布 SQL> select end_dt,count(*) from F_AGT_BUSINESS_CONTRACT_H group by end_dt having end_dt = date '2999-12-31'; 2 3 END_DT COUNT(*) ---------- ---------- 2999-12-31 246369 总条数: SQL> select count(*) from F_AGT_BUSINESS_CONTRACT_H; COUNT(*) ---------- 1614953 2.查看集群因子: SQL> select index_name,clustering_factor from user_indexes where table_name='F_AGT_BUSINESS_CONTRACT_H' 2 and index_name='END_DT_IDX1'; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- END_DT_IDX1 557965 3.查看表的块数: SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from F_AGT_BUSINESS_CONTRACT_H a 2 where a.end_dt = date '2999-12-31'; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 33459 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from F_AGT_BUSINESS_CONTRACT_H; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 96435 如果clustering factor 接近block 数,说明表的存储和索引存储排序接近,也就是说表中的记录很有序,这样在做index range scan 的时候能,读取少量的data block 就能得到我们想要的数据,代价比较小。如果clustering factor 接近表记录数,说明表的存储和索引排序差异很大,在做index range scan 的时候,会额外读取多个block,因为表记录分散,代价较高。 4. 重建表: CREATE TABLE F_AGT_BUSINESS_CONTRACT_H_1 AS SELECT * FROM F_AGT_BUSINESS_CONTRACT_H ORDER BY end_dt; SQL> create index END_DT_IDX2 on F_AGT_BUSINESS_CONTRACT_H_1(end_dt); BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DWF', tabname => 'F_AGT_BUSINESS_CONTRACT_H_1', estimate_percent => 30, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE); END; 查看此时的集群因子: SQL> select index_name,clustering_factor from user_indexes where table_name='F_AGT_BUSINESS_CONTRACT_H_1'; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- END_DT_IDX2 171023 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from F_AGT_BUSINESS_CONTRACT_H_1; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 161791 此时的集群因子和块数接近; 查看此时需要访问的块数: SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from F_AGT_BUSINESS_CONTRACT_H_1; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 161791 SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from F_AGT_BUSINESS_CONTRACT_H_1 a where a.end_dt>date'2014-03-01' 2 ; COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) --------------------------------------------------- 24971 此时集群因子接近了表的块数 SQL> explain plan for select * from F_AGT_BUSINESS_CONTRACT_H_1 t where t.end_dt = date '2999-12-31'; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1303973883 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1478 | 894K| 163 (0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| F_AGT_BUSINESS_CONTRACT_H_1 | 1478 | 894K| 163 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | END_DT_IDX2 | 1502 | | 6 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 rows selected.
利用集群因子优化
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
上一篇:并行进程问题
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
kubernetes集群部署EFK收集集群日志
K8S日志系统 EFK
Group elasticsearch Elastic EFK -
oracle 集群因子
oracle 集群因子
oracle 集群因子 -
大整数的因子(利用求余)
题目:已知正整数k满足2<=k<=9,现给出长度最大为30位的十进制非负整数c,求所有能整除c的k
#include 程序代码 i++ -
【PSO】基于惯性因子和变异因子的PSO粒子群优化仿真
1.软件版本MATLAB2013b2.本算法理论知识传统的PSO如下:第一,引入惯性因.
matlab 算法 机器学习 变异因子PSO 初始化 -
利用分区优化SQL
SQL> explain plan for select a.so_region_code so_region_code, 2  
数据库调优 SQL调优 dba调优 ocp培训