正常的谓词推入效率
来源：互联网 发布：淘宝没有自然排名 编辑：程序博客网 时间：2024/04/24 16:26
explain plan for with zz as (select t.br_org_name, t.sd_org_name, t.rw_date, t.cust_no, t.cust_name, t.zd_num, t.status, t.sett_acct, t.dr_date, t.xuhao, t.shul , t.sett_acct as agret from M_POS_CUST_SETT t where t.sett_acct not like '621028%' union all select t.br_org_name, t.sd_org_name, t.rw_date, t.cust_no, t.cust_name, t.zd_num, t.status, B.AGMT_ID sett_acct, t.dr_date, t.xuhao, t.shul , t.sett_acct as agret from M_POS_CUST_SETT t left join DWF.F_AGT_CADB_BOOK_H A on t.sett_acct = a.agmt_id and A.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD') AND A.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD') AND A.MASTER_CARD_NO IS NOT NULL -- AND SUBSTR(A.CARD_NEW_STATUS, 8, 1) <> '6' LEFT JOIN DWF.F_AGT_CADB_ACCT ee on ee.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD') AND ee.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD') and A.MASTER_CARD_NO = EE.AGMT_ID LEFT JOIN DWF.F_AGT_SAVB_ACCTINFO_H b on b.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD') AND b.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD') and EE.ACCT_NO = B.AGMT_ID AND EE.ACCT_SEQNO = B.ACCT_SEQNO where t.sett_acct like '621028%' ) select o.FST_ORG_NAME, o.SEC_ORG_NAME, o.THD_ORG_NAME, o.FTH_ORG_NAME, o.ind_no, s.br_org_no, t.BR_ORG_NAME, t.SD_ORG_NAME, t.RW_DATE, t.CUST_NO, t.CUST_NAME, t.ZD_NUM, t.STATUS, t.SETT_ACCT, t.DR_DATE, AVG_BAL, case when s.br_org_no is not null then '正常' else '异常' end sts,t.xuhao,t.shul , s.end_acct_bal, avg_bal/t.shul avg1, s.end_acct_bal/t.shul avg2 from ( SELECT t.BR_ORG_NAME, t.SD_ORG_NAME, t.RW_DATE, t.CUST_NO, t.CUST_NAME, t.ZD_NUM, t.STATUS, t.agret SETT_ACCT, t.DR_DATE, t.xuhao,t.shul , SUM(ACCT_BAL * DAY_CNT) / max(DAY_COUNT) AVG_BAL, sum(end_bal) as end_bal FROM (SELECT t.BR_ORG_NAME, t.SD_ORG_NAME, t.RW_DATE, t.CUST_NO, t.CUST_NAME, t.ZD_NUM, t.STATUS, t.SETT_ACCT, t.DR_DATE, t.xuhao,t.shul , t.agret, XX.AGMT_ID, XX.START_DT, XX.END_DT, XX.ACCT_BAL, case when XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD') then XX.ACCT_BAL else 0 end end_bal, CASE when to_date('2014-01-01', 'YYYY-MM-DD') = to_date('2014-03-31', 'YYYY-MM-DD') then 1 when XX.START_DT < to_date('2014-01-01', 'YYYY-MM-DD') AND XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD') THEN to_date('2014-03-31', 'YYYY-MM-DD') - to_date('2014-01-01', 'YYYY-MM-DD') + 1 WHEN XX.START_DT >= to_date('2014-01-01', 'YYYY-MM-DD') AND XX.END_DT <= to_date('2014-03-31', 'YYYY-MM-DD') THEN XX.END_DT - XX.START_DT WHEN XX.START_DT < to_date('2014-01-01', 'YYYY-MM-DD') THEN XX.END_DT - to_date('2014-01-01', 'YYYY-MM-DD') WHEN XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD') THEN to_date('2014-03-31', 'YYYY-MM-DD') - XX.START_DT + 1 ELSE 0 END AS DAY_CNT, to_date('2014-03-31', 'YYYY-MM-DD') - to_date('2014-01-01', 'YYYY-MM-DD') + 1 AS DAY_COUNT FROM zz t left join DWF.F_AGT_SAVB_ACCTINFO_H XX on t.SETT_ACCT = xx.agmt_id AND ( (XX.START_DT >= to_date('2014-01-01', 'YYYY-MM-DD') AND XX.START_DT <= to_date('2014-03-31', 'YYYY-MM-DD')) or (XX.START_DT < to_date('2014-01-01', 'YYYY-MM-DD') AND XX.END_DT > to_date('2014-03-31', 'YYYY-MM-DD')) OR (XX.END_DT > to_date('2014-01-01', 'YYYY-MM-DD') AND XX.END_DT <= to_date('2014-03-31', 'YYYY-MM-DD'))) and XX.CUR_CD = 'T00CNY' WHERE t.DR_DATE = (select max(a.dr_date) from m_pos_cust_sett a)) t GROUP BY t.BR_ORG_NAME, t.SD_ORG_NAME, t.RW_DATE, t.CUST_NO, t.CUST_NAME, t.ZD_NUM, t.STATUS, t.xuhao, t.shul , t.agret, t.DR_DATE) t left join ( SELECT t.agret AGMT_ID, max(xx.acct_org) as br_org_no, sum(xx.acct_bal) end_acct_bal,t.xuhao FROM zz t left join DWF.F_AGT_SAVB_ACCTINFO_H XX on t.sett_acct = xx.agmt_id WHERE XX.CUR_CD = 'T00CNY' and t.dr_date = (select max(a.dr_date) from m_pos_cust_sett a) AND XX.START_DT <= to_date('2014-03-31', 'YYYY-MM-DD') AND XX.End_Dt > to_date('2014-03-31', 'YYYY-MM-DD') group by t.agret ,t.xuhao) s on t.sett_acct = s.AGMT_IDand t.xuhao = s.xuhao left join V_M_ORG_LEVEL o on s.br_org_no = o.org_id where (s.br_org_no is null or s.br_org_no in (SELECT bb.org_id FROM b_m_sys_branch bb WHERE bb.status = '1' AND bb.dept_flag NOT IN ('2') CONNECT BY PRIOR bb.id = bb.parent_id START WITH bb.org_id = 10000) ) ; select * from table(dbms_xplan.display());SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID0pszsnw1v97nm, child number 1-------------------------------------with zz as (select t.br_org_name,t.sd_org_name,t.rw_date, t.cust_no,t.cust_name, t.zd_num, t.status,t.sett_acct, t.dr_date, t.xuhao, t.shul , t.sett_acct as agret from M_POS_CUST_SETT t wheret.sett_acct not like '621028%'union all select t.br_org_name, t.sd_org_name, t.rw_date, t.cust_no,t.cust_name, t.zd_num, t.status,Plan hash value: 4217052783----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | |5074 |00:00:17.11 | 1577K| 575K| 105 | | | || 1 | TEMP TABLE TRANSFORMATION | | 1 | |5074 |00:00:17.11 | 1577K| 575K| 105 | | | || 2 | LOAD AS SELECT | | 1 | | 0 |00:00:04.60 |353K| 15242 | 105 | 530K| 530K| 530K (0)|| 3 | UNION-ALL | | 1 | |5400 |00:00:04.59 |353K| 15242 | 0 | | | ||* 4 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 2797 |2781 |00:00:00.01 | 91 | 0 | 0 | | | || 5 | VIEW | | 1 | 38905 |2619 |00:00:04.58 |353K| 15242 | 0 | | | || 6 | NESTED LOOPS OUTER | | 1 | 38905 |2619 |00:00:04.58 |353K| 15242 | 0 | | | || 7 | VIEW | | 1 | 12736 |2619 |00:00:03.14 | 91703 | 15242 | 0 | | | ||* 8 | HASH JOIN OUTER | | 1 | 12736 |2619 |00:00:03.13 | 91703 | 15242 | 0 | 1073K| 1073K| 1396K (0)|| 9 | VIEW | | 1 | 2312 |2293 |00:00:02.55 | 76457 | 0 | 0 | | | ||* 10 | HASH JOIN RIGHT OUTER | | 1 | 2312 |2293 |00:00:02.55 | 76457 | 0 | 0 | 37M| 4938K| 38M (0)||* 11 | INDEX RANGE SCAN | F_AGT_CADB_BOOK_H_IDX4 | 1 | 2 | 333K|00:00:02.21 | 76366 | 0 | 0 | | | ||* 12 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 2312 |2293 |00:00:00.01 | 91 | 0 | 0 | | | ||* 13 | TABLE ACCESS FULL | F_AGT_CADB_ACCT | 1 |827K| 605K|00:00:00.28 | 15246 | 15242 | 0 | | | ||* 14 | TABLE ACCESS BY INDEX ROWID | F_AGT_SAVB_ACCTINFO_H | 2619 | 3 |2460 |00:00:01.44 |261K| 0 | 0 | | | ||* 15 | INDEX RANGE SCAN | F_AGT_SAVB_ACCTINFO_H_IDX2 | 2619 | 1 | 253K|00:00:00.16 | 8106 | 0 | 0 | | | ||* 16 | FILTER | | 1 | |5074 |00:00:12.50 | 1223K| 560K| 0 | | | || 17 | NESTED LOOPS OUTER | | 1 | 1 |5074 |00:00:12.44 | 1222K| 560K| 0 | | | ||* 18 | HASH JOIN OUTER | | 1 | 1 |5074 |00:00:12.31 | 1217K| 560K| 0 | 1538K| 1070K| 1437K (0)|| 19 | VIEW | | 1 | 1 |5074 |00:00:04.16 |657K| 105 | 0 | | | || 20 | HASH GROUP BY | | 1 | 1 |5074 |00:00:04.15 |657K| 105 | 0 | 24M| 4065K| 1492K (0)|| 21 | NESTED LOOPS OUTER | | 1 |250K| 94941 |00:00:03.86 |657K| 105 | 0 | | | ||* 22 | VIEW | | 1 | 41702 |5400 |00:00:00.03 |200 | 105 | 0 | | | || 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F46_6CB3C16D | 1 | 41702 |5400 |00:00:00.02 |109 | 105 | 0 | | | || 24 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 91 | 0 | 0 | | | || 25 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 5109 |5074 |00:00:00.01 | 91 | 0 | 0 | | | || 26 | VIEW | | 5400 | 6 | 94736 |00:00:03.80 |656K| 0 | 0 | | | ||* 27 | TABLE ACCESS BY INDEX ROWID | F_AGT_SAVB_ACCTINFO_H | 5400 | 6 | 94736 |00:00:03.76 |656K| 0 | 0 | | | ||* 28 | INDEX RANGE SCAN | SYS_C0054556 | 5400 | 19 | 637K|00:00:00.41 | 18975 | 0 | 0 | | | || 29 | VIEW | | 1 | 1 |4872 |00:00:08.13 |560K| 560K| 0 | | | || 30 | HASH GROUP BY | | 1 | 1 |4872 |00:00:08.13 |560K| 560K| 0 | 1259K| 1259K| 6786K (0)||* 31 | HASH JOIN | | 1 |126K|5195 |00:00:08.11 |560K| 560K| 0 | 1291K| 1291K| 2793K (0)||* 32 | VIEW | | 1 | 41702 |5400 |00:00:00.01 |197 | 0 | 0 | | | || 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F46_6CB3C16D | 1 | 41702 |5400 |00:00:00.01 |106 | 0 | 0 | | | || 34 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 91 | 0 | 0 | | | || 35 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 5109 |5074 |00:00:00.01 | 91 | 0 | 0 | | | ||* 36 | TABLE ACCESS FULL | F_AGT_SAVB_ACCTINFO_H | 1 | 2301K|1287K|00:00:07.44 |560K| 560K| 0 | | | || 37 | VIEW PUSHED PREDICATE | V_M_ORG_LEVEL | 5074 | 1 |4872 |00:00:00.12 | 5085 | 0 | 0 | | | || 38 | NESTED LOOPS OUTER | | 5074 | 1 |4872 |00:00:00.11 | 5085 | 0 | 0 | | | || 39 | NESTED LOOPS OUTER | | 5074 | 1 |4872 |00:00:00.09 | 4440 | 0 | 0 | | | || 40 | NESTED LOOPS OUTER | | 5074 | 1 |4872 |00:00:00.06 | 3462 | 0 | 0 | | | ||* 41 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 5074 | 1 |4872 |00:00:00.04 | 2270 | 0 | 0 | | | ||* 42 | INDEX RANGE SCAN | IND_BRANCH_001 | 5074 | 1 |4872 |00:00:00.02 |652 | 0 | 0 | | | || 43 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 4872 | 1 |4872 |00:00:00.02 | 1192 | 0 | 0 | | | ||* 44 | INDEX RANGE SCAN | IND_BRANCH_002 | 4872 | 1 |4872 |00:00:00.01 |652 | 0 | 0 | | | || 45 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 4872 | 1 |4872 |00:00:00.02 |978 | 0 | 0 | | | ||* 46 | INDEX RANGE SCAN | IND_BRANCH_002 | 4872 | 1 |4872 |00:00:00.01 |652 | 0 | 0 | | | || 47 | TABLE ACCESS BY INDEX ROWID | B_M_SYS_BRANCH | 4872 | 1 | 594 |00:00:00.01 |645 | 0 | 0 | | | ||* 48 | INDEX RANGE SCAN | IND_BRANCH_002 | 4872 | 1 | 594 |00:00:00.01 |377 | 0 | 0 | | | ||* 49 | FILTER | | 143 | | 143 |00:00:00.05 |858 | 0 | 0 | | | ||* 50 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 143 | |7094 |00:00:00.05 |858 | 0 | 0 | | | || 51 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 143 |152 | 21879 |00:00:00.01 |858 | 0 | 0 | | | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."SETT_ACCT" NOT LIKE '621028%') 8 - access("A"."MASTER_CARD_NO"="EE"."AGMT_ID") 10 - access("T"."SETT_ACCT"="A"."AGMT_ID") 11 - access("A"."AGMT_ID" LIKE '621028%' AND "A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter(("A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."AGMT_ID" LIKE '621028%' AND "A"."MASTER_CARD_NO" IS NOT NULL AND "A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 12 - filter("T"."SETT_ACCT" LIKE '621028%') 13 - filter(("EE"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "EE"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 14 - filter(("B"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) 15 - access("EE"."ACCT_NO"="B"."AGMT_ID" AND "EE"."ACCT_SEQNO"="B"."ACCT_SEQNO") 16 - filter(("S"."BR_ORG_NO" IS NULL OR IS NOT NULL)) 18 - access("T"."XUHAO"="S"."XUHAO" AND "T"."SETT_ACCT"="S"."AGMT_ID") 22 - filter("T"."DR_DATE"=) 27 - filter(((("XX"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."START_DT"<TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR ("XX"."START_DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR ("XX"."END_DT">TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."END_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND "XX"."CUR_CD"='T00CNY')) 28 - access("T"."SETT_ACCT"="XX"."AGMT_ID") 31 - access("T"."SETT_ACCT"="XX"."AGMT_ID") 32 - filter("T"."DR_DATE"=) 36 - filter(("XX"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "XX"."CUR_CD"='T00CNY')) 41 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2')) 42 - access("A"."ORG_ID"="S"."BR_ORG_NO") 44 - access("A"."PARENT_ID"="B"."ID") 46 - access("B"."PARENT_ID"="C"."ID") 48 - access("C"."PARENT_ID"="D"."ID") 49 - filter(("BB"."ORG_ID"=:B1 AND "BB"."STATUS"=1 AND "BB"."DEPT_FLAG"<>'2')) 50 - access("BB"."PARENT_ID"=PRIOR NULL) filter(TO_NUMBER("BB"."ORG_ID")=10000)112 rows selected.| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F41_6CB3C16D | 1 | 41702 |5400 |00:00:00.03 |109 | 105 | 0 | | | || 23 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 91 | 0 | 0 | | | || 24 | TABLE ACCESS FULL | M_POS_CUST_SETT | 1 | 5109 |5074 |00:00:00.01 | 91 | 0 | 0 | | | |这里的5400行是怎么计算的呢？with A as (select /*+ materialize */ * from (select t.br_org_name, t.sd_org_name, t.rw_date, t.cust_no, t.cust_name, t.zd_num, t.status, t.sett_acct, t.dr_date, t.xuhao,t.shul , t.sett_acct as agret from M_POS_CUST_SETT t where t.sett_acct not like '621028%' union all select /*+ use_hash(EE B) */ t.br_org_name, t.sd_org_name, t.rw_date, t.cust_no, t.cust_name, t.zd_num, t.status, B.AGMT_ID sett_acct, t.dr_date, t.xuhao,t.shul , t.sett_acct as agret from M_POS_CUST_SETT t left join DWF.F_AGT_CADB_BOOK_H A on t.sett_acct = a.agmt_id and A.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD') AND A.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD') AND A.MASTER_CARD_NO IS NOT NULL -- AND SUBSTR(A.CARD_NEW_STATUS, 8, 1) <> '6' LEFT JOIN DWF.F_AGT_CADB_ACCT ee on ee.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD') AND ee.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD') and A.MASTER_CARD_NO = EE.AGMT_ID LEFT JOIN DWF.F_AGT_SAVB_ACCTINFO_H b on b.START_DT <= TO_DATE('2014-03-31', 'YYYY-MM-DD') AND b.END_DT > TO_DATE('2014-03-31', 'YYYY-MM-DD') and EE.ACCT_NO = B.AGMT_ID AND EE.ACCT_SEQNO = B.ACCT_SEQNO where t.sett_acct like '621028%' ))select count(*) from A where A.DR_DATE = (select max(a.dr_date) from m_pos_cust_sett a) --5400 0 0
- 正常的谓词推入效率
- 谓词推入引发的惨案
- 这里的谓词为什么推入不了?
- 彻底理解谓词推入的利弊
- 再次遇到谓词推入
- 谓词推入一列
- 又见谓词推入
- 谓词推入演示
- Oracle谓词推入
- ORACLE 行转列谓词推入
- VIEW PUSHED PREDICATE(谓词推入)引发的惨剧
- 视图合并和谓词推入
- ORACLE谓词推入初解
- with as 干掉谓词推入
- 《基于ORACLE的SQL优化读书》笔记 星形转换/谓词推入/连接因式分解/表移除
- 强制让SQL走谓词推入
- oracle中的子查询展开和谓词推入
- 22 谓词推入(pushing predicate) --优化主题系列
- 为什么这么多人"黑"阿里？
- 腾讯云安装openvz，快速搭建测试环境
- ubuntu 12.10无法用apt-get安装软件 Err http://us.archive.ubuntu.com quantal-updates/main Sources 404 Not
- 7_字符译码
- 8_指针操作符
- 正常的谓词推入效率
- 9_if判断语句
- Maven教程初级篇01: 简介
- 关于在galssfish中配置jvm参数和gc日志的方法
- 10_else-if语句
- xcode5+ASIHTTPRequest 导入后报ARC错误
- C++-函数重载
- HDU 1166-敌兵布阵（线段树_单点更新）
- RedHat EL 6.4搭建LVS负载均衡
