Oracle 不走索引
来源：互联网 发布：mac口红 淘宝推荐 编辑：程序博客网 时间：2024/04/29 12:50
explain plan for select * from ( select a.trans_org as org_id, --a.cust_magr, b.emp_name as cust_magr_name, a.cust_name as cust_name, a.cert_no as cert_no, --a.prof_amt as prof_amt, --a.convert_amt as convert_amt , CASE WHEN a.Correct_Flag = '0' THEN a.Prof_Amt WHEN a.Correct_Flag = 'T' THEN -a.Prof_Amt END as prof_amt, CASE WHEN a.Correct_Flag = '0' THEN a.convert_amt WHEN a.Correct_Flag = 'T' THEN -a.convert_amt END as convert_amt, row_number() over(partition by a.handle_no || a.seq_no, a.trans_no order by b.pty_id ) as xx from DWF.F_EVT_FORR_CASH_EXCH a left join dwf.F_AGT_S_PM_ACCOUNT_MGR_H xh on xh.agmt_id like nvl(a.acct_no,'XXXZZZ')||'%' left outer join (select pty_id,emp_name from DWF.F_PTY_EMP_INFO where start_dt <= TO_DATE('2014-06-30','YYYY-MM-DD') and end_dt > TO_DATE('2014-06-30','YYYY-MM-DD')) b -- on a.cust_magr = b.pty_id on nvl(xh.cust_magr,a.cust_magr) = b.pty_id where a.trans_date >= TO_DATE('2014-01-01','YYYY-MM-DD') and a.trans_date <= TO_DATE('2014-06-30','YYYY-MM-DD') and a.correct_flag in ('0','T') ) where xx = 1 ; select * from table(dbms_xplan.display()); Plan hash value: 4228062658 -------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 182M| 20G| | 18M (1)| 61:05:02 ||* 1 | VIEW | | 182M| 20G| | 18M (1)| 61:05:02 ||* 2 | WINDOW SORT PUSHED RANK| | 182M| 27G| 30G| 18M (1)| 61:05:02 ||* 3 | HASH JOIN RIGHT OUTER | | 182M| 27G| | 11M (2)| 39:00:52 ||* 4 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 7817 | 221K| | 143 (3)| 00:00:02 || 5 | VIEW | | 171M| 21G| | 11M (2)| 39:00:24 || 6 | NESTED LOOPS OUTER | | 171M| 19G| | 11M (2)| 39:00:24 ||* 7 | TABLE ACCESS FULL | F_EVT_FORR_CASH_EXCH | 1283 | 110K| | 212 (2)| 00:00:03 ||* 8 | TABLE ACCESS FULL | F_AGT_S_PM_ACCOUNT_MGR_H | 133K| 4050K| | 9121 (2)| 00:01:50 |------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("XX"=1) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "from$_subquery$_004"."HANDLE_NO"||TO_CHAR("from$_subq uery$_004"."SEQ_NO"),"from$_subquery$_004"."TRANS_NO" ORDER BY "PTY_ID")<=1) 3 - access("PTY_ID"(+)=NVL("XH"."CUST_MAGR","A"."CUST_MAGR")) 4 - filter("END_DT"(+)>TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "START_DT"(+)<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 7 - filter("A"."TRANS_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("A"."CORRECT_FLAG"='0' OR "A"."CORRECT_FLAG"='T') AND "A"."TRANS_DATE"<=TO_DATE(' 2014-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 8 - filter("XH"."AGMT_ID"(+) LIKE NVL("A"."ACCT_NO",'XXXZZZ')||'%') 可以看到F_AGT_S_PM_ACCOUNT_MGR_H没有走索引,但是为什么呢？ SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 a.histogram, 6 a.num_buckets 7 from dba_tab_col_statistics a, dba_tables b 8 where a.owner = b.owner 9 and a.table_name = b.table_name 10 and a.owner = 'DWF' 11 and a.table_name = 'F_AGT_S_PM_ACCOUNT_MGR_H'; COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS------------------------------ ---------- ----------- ----------- --------------- -----------CUST_MAGR 2676202 5678 .21 HEIGHT BALANCED 254SHARE_RATE 2676202 441 .02 NONE 1CM_NAME 2676202 5384 .2 NONE 1ORG_NAME 2676202 119 0 NONE 1DW_DATA_DT 2676202 1 0 NONE 1END_DT 2676202 1 0 NONE 1AGMT_ID 2676202 2424320 90.59 HEIGHT BALANCED 254AGMT_MOD_CD 2676202 1 0 NONE 1ORG_NO 2676202 120 0 NONE 1START_DT 2676202 1 0 NONE 110 rows selected.直方图信息也有那索引状态呢？SQL> select index_name,status from dba_indexes where table_name='F_AGT_S_PM_ACCOUNT_MGR_H';INDEX_NAME STATUS------------------------------ --------XIE1_F_ACC_MGR VALID create index XIE1_F_ACC_MGR on F_AGT_S_PM_ACCOUNT_MGR_H (AGMT_ID) tablespace EDWFTBS drop index XIE1_F_ACC_MGR; 重建索引后正常: Plan hash value: 127624381 ---------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 182M| 20G| | 18M (1)| 60:42:50 ||* 1 | VIEW | | 182M| 20G| | 18M (1)| 60:42:50 ||* 2 | WINDOW SORT PUSHED RANK | | 182M| 27G| 30G| 18M (1)| 60:42:50 ||* 3 | HASH JOIN RIGHT OUTER | | 182M| 27G| | 11M (1)| 38:38:39 ||* 4 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 7817 | 221K| | 143 (3)| 00:00:02 || 5 | VIEW | | 171M| 21G| | 11M (1)| 38:38:12 || 6 | NESTED LOOPS OUTER | | 171M| 19G| | 11M (1)| 38:38:12 ||* 7 | TABLE ACCESS FULL | F_EVT_FORR_CASH_EXCH | 1283 | 110K| | 212 (2)| 00:00:03 || 8 | TABLE ACCESS BY INDEX ROWID| F_AGT_S_PM_ACCOUNT_MGR_H | 133K| 4050K| | 9034 (1)| 00:01:49 ||* 9 | INDEX RANGE SCAN | XIE1_F_ACC_MGR | 24136 | | | 120 (1)| 00:00:02 |--------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 0 0
- Oracle 不走索引
- Oracle查询不走索引
- oracle 不走索引的原因
- Oracle不走索引的原因
- Oracle is null不走索引问题
- oracle 不走索引的原因
- Oracle SQL不走索引小记
- 不走索引原因
- Oracle执行计划不走索引的原因总结
- Oracle执行计划不走索引的原因总结
- Oracle执行计划不走索引的原因总结
- Oracle执行计划不走索引的原因总结
- Oracle查询隐式类型转换不走索引
- Oracle执行计划不走索引的原因总结
- Oracle执行计划不走索引的原因总结
- Oracle执行计划不走索引的原因总结
- Oracle执行计划不走索引的原因总结
- ORACLE 不走索引（失效）的原因以及解决办法
- 重载和重写的区别
- 堆排序算法（java实现）
- 如何使iframe自适应高度和宽度
- 用于同步并发程序的高级工具类
- SAP 锁对象 基本概念与基本操作 SE11
- Oracle 不走索引
- GPS 偏移校正(WGS-84) 到(GCJ-02) java版本实现
- 【UVa】10537 Toll! Revisited 最短路
- 什么是单反相机
- 针对xslt一些见解
- 这篇说的是Unity Input。输入控制器
- Target runtime Apache Tomcat v7.0 is not defined.
- android 关于屏幕类型
- iReport Distilled
