优化大型复杂SQL
来源：互联网 发布：linux wireshark 编辑：程序博客网 时间：2024/05/01 14:49
with aa as (select a.agmt_id, sum(c.acct_bal) as card_bal, --借记卡期末存款余额 a.card_open_org, a.OPEN_DATE, -- 发卡日期 a.CARD_NEW_STATUS, -- 卡片状态 a.cust_magr, -- 客户经理号 a.cust_no, -- 客户号 a.corp_org from dwf.f_agt_cadb_book_h a left outer join (select agmt_id, acct_no from dwf.f_agt_cadb_acct where substr(acct_status, 8, 1) <> '2') b on a.master_card_no = b.agmt_id left outer join (select agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal from dwf.F_AGT_SAVB_ACCTINFO_H where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') and end_dt > to_date('2014-03-31', 'YYYY-MM-DD') and acct_status <> '1' group by agmt_id ) c -- 一卡多账号，某一账号睡眠其它未睡 ,卡账务信息表最后交易日期不准 on b.acct_no = c.agmt_id where substr(a.host_card_status, 8, 1) = '0' --卡状态不为注销 and a.master_card_no is not null --剔除待领卡 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 c.LAST_TRANS_DATE < to_date( '2014-03-01', 'YYYY-MM-DD') /*参数传入 季末日期*/ and c.acct_bal is not null group by a.agmt_id, a.card_open_org, a.OPEN_DATE, -- 发卡日期 a.CARD_NEW_STATUS, -- 卡片状态 a.cust_magr, -- 客户经理号 a.cust_no, -- 客户号 a.corp_org having(sum(c.acct_bal) < 10)),bb as (SELECT xx.tran_card_no, count(1) cnt, max(xx.trans_date) max_date FROM DWF.F_EVT_CADJ_JOUR xx WHERE TRANS_DATE <= to_date('2014-03-31', 'YYYY-MM-DD') group by xx.tran_card_no)select aa.agmt_id, -- 卡号 'J' AS CARD_T, -- 卡种类 f.pty_name, -- 客户姓名 aa.cust_no, -- 客户证件号 f.mobile_no, -- 客户手机号 aa.OPEN_DATE, -- 发卡日期 null as ACTIVEDAY, -- 激活日期 aa.CARD_NEW_STATUS, -- 卡片状态 to_date('2014-03-31', 'YYYY-MM-DD') - nvl(bb.MAX_DATE, aa.OPEN_DATE) as sleep_day, -- 睡眠时间 card_bal, -- 存款余额 null as cred_limit, -- 授信额度 bb.cnt, -- 交易次数 aa.cust_magr, -- 客户经理号 xx.emp_name, -- 客户经理名称 aa.card_open_org, -- 所属机构 bb.MAX_DATE from aa left join bb on aa.agmt_id = bb.tran_card_no LEFT JOIN dwm.v_m_pty_emp_info xx --员工表 ON aa.cust_magr = xx.pty_id AND xx.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') AND xx.end_dt > to_date('2014-03-31', 'YYYY-MM-DD') LEFT JOIN dwf.f_pty_table f --当事人主表 ON aa.cust_no = f.pty_id AND aa.corp_org = f.corp_org AND f.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') AND f.end_dt > to_date('2014-03-31', 'YYYY-MM-DD') where bb.MAX_DATE < to_date( '2014-03-01', 'YYYY-MM-DD') and aa.card_open_org in (SELECT t.Org_Id FROM b_m_Sys_Branch t WHERE t.Status = 1 AND t.Dept_Flag != '2' CONNECT BY PRIOR t.Id = t.Parent_Id START WITH t.Org_Id =10000) ;这SQL跑了半天也没出结果,看下执行计划: select * from table(dbms_xplan.display());Plan hash value: 4046975539 -------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 21 | 5040 | 267K (2)| 00:53:36 || 1 | NESTED LOOPS OUTER | | 21 | 5040 | 267K (2)| 00:53:36 || 2 | NESTED LOOPS OUTER | | 6 | 1266 | 267K (2)| 00:53:36 ||* 3 | HASH JOIN | | 6 | 900 | 267K (2)| 00:53:36 ||* 4 | HASH JOIN | | 6 | 660 | 179K (2)| 00:35:56 || 5 | VIEW | VW_NSO_1 | 6 | 162 | 4 (25)| 00:00:01 || 6 | HASH UNIQUE | | 6 | 294 | 4 (25)| 00:00:01 ||* 7 | FILTER | | | | | ||* 8 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | || 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 144 | 2880 | 3 (0)| 00:00:01 || 10 | VIEW | | 28 | 2324 | 179K (2)| 00:35:56 ||* 11 | FILTER | | | | | || 12 | HASH GROUP BY | | 28 | 5908 | 179K (2)| 00:35:56 ||* 13 | HASH JOIN | | 552 | 113K| 179K (2)| 00:35:56 ||* 14 | HASH JOIN | | 2835 | 260K| 110K (3)| 00:22:10 || 15 | VIEW | | 2805 | 117K| 107K (3)| 00:21:32 ||* 16 | FILTER | | | | | || 17 | HASH GROUP BY | | 2805 | 142K| 107K (3)| 00:21:32 ||* 18 | TABLE ACCESS FULL | F_AGT_SAVB_ACCTINFO_H | 8624K| 427M| 107K (2)| 00:21:25 ||* 19 | TABLE ACCESS FULL | F_AGT_CADB_ACCT | 38498 | 1917K| 3128 (1)| 00:00:38 ||* 20 | TABLE ACCESS FULL | F_AGT_CADB_BOOK_H | 61287 | 7002K| 68898 (2)| 00:13:47 || 21 | VIEW | | 13290 | 519K| 88273 (2)| 00:17:40 ||* 22 | FILTER | | | | | || 23 | HASH GROUP BY | | 13290 | 337K| 88273 (2)| 00:17:40 ||* 24 | TABLE ACCESS FULL | F_EVT_CADJ_JOUR | 12M| 298M| 87422 (1)| 00:17:30 ||* 25 | TABLE ACCESS BY INDEX ROWID | F_PTY_TABLE | 1 | 61 | 3 (0)| 00:00:01 ||* 26 | INDEX RANGE SCAN | SYS_C0061472 | 1 | | 2 (0)| 00:00:01 || 27 | VIEW PUSHED PREDICATE | V_M_PTY_EMP_INFO | 3 | 87 | 4 (0)| 00:00:01 ||* 28 | HASH JOIN OUTER | | 3 | 153 | 122 (2)| 00:00:02 ||* 29 | TABLE ACCESS BY INDEX ROWID | F_PTY_EMP_INFO | 3 | 99 | 4 (0)| 00:00:01 ||* 30 | INDEX RANGE SCAN | EMP_IDX_002 | 3 | | 1 (0)| 00:00:01 ||* 31 | VIEW | | 24404 | 428K| 118 (2)| 00:00:02 ||* 32 | HASH JOIN RIGHT OUTER | | 24404 | 1406K| 118 (2)| 00:00:02 ||* 33 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 36 | 504 | 3 (0)| 00:00:01 ||* 34 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | 3 (0)| 00:00:01 || 35 | VIEW | | 24404 | 1072K| 115 (2)| 00:00:02 ||* 36 | HASH JOIN RIGHT OUTER | | 24404 | 2049K| 115 (2)| 00:00:02 ||* 37 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 36 | 504 | 3 (0)| 00:00:01 ||* 38 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | 3 (0)| 00:00:01 || 39 | VIEW | | 24404 | 1715K| 111 (1)| 00:00:02 ||* 40 | HASH JOIN RIGHT OUTER | | 24404 | 1882K| 111 (1)| 00:00:02 ||* 41 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 36 | 504 | 3 (0)| 00:00:01 ||* 42 | HASH JOIN RIGHT OUTER | | 24404 | 1549K| 108 (1)| 00:00:02 || 43 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 144 | 1008 | 3 (0)| 00:00:01 ||* 44 | HASH JOIN RIGHT OUTER | | 24404 | 1382K| 105 (1)| 00:00:02 || 45 | VIEW | M_CBS_TO_DW_ORG | 141 | 4794 | 8 (0)| 00:00:01 || 46 | UNION-ALL | | | | | ||* 47 | TABLE ACCESS FULL | F_PTY_ORG | 127 | 2794 | 5 (0)| 00:00:01 ||* 48 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 14 | 98 | 3 (0)| 00:00:01 ||* 49 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 24404 | 571K| 97 (2)| 00:00:02 |------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("AA"."AGMT_ID"="BB"."TRAN_CARD_NO") 4 - access("AA"."CARD_OPEN_ORG"="ORG_ID") 7 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2') 8 - access("T"."PARENT_ID"=PRIOR "T"."ID") filter(TO_NUMBER("T"."ORG_ID")=10000) 11 - filter(SUM("C"."ACCT_BAL")<10) 13 - access("A"."MASTER_CARD_NO"="AGMT_ID") 14 - access("ACCT_NO"="C"."AGMT_ID") 16 - filter(MAX("LAST_TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SUM("ACCT_BAL") IS NOT NULL) 18 - filter("ACCT_STATUS"<>'1' AND "END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 19 - filter(SUBSTR("ACCT_STATUS",8,1)<>'2') 20 - filter(SUBSTR("A"."HOST_CARD_STATUS",8,1)='0' AND "A"."MASTER_CARD_NO" IS NOT NULL 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')) 22 - filter(MAX("XX"."TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 24 - filter("TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 25 - filter("F"."END_DT"(+)>TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 26 - access("AA"."CUST_NO"="F"."PTY_ID"(+) AND "AA"."CORP_ORG"="F"."CORP_ORG"(+) AND "F"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("F"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."CORP_ORG"="F"."CORP_ORG"(+)) 28 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND "T"."PTY_ID"="B"."PTY_ID"(+)) 29 - filter("T"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 30 - access("T"."PTY_ID"="AA"."CUST_MAGR") 31 - filter("B"."PTY_ID"(+)="AA"."CUST_MAGR" AND "B"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 32 - access("FST"."SEC_ORG_ID"="ORG_ID"(+)) 33 - filter("ORG_LEVEL"(+)=2) 34 - filter("ID"=:B1) 36 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID")) 37 - filter("ORG_LEVEL"(+)=3) 38 - filter("ID"=:B1) 40 - access("C"."ORG_ID"="ORG_ID"(+)) 41 - filter("ORG_LEVEL"(+)=4) 42 - access("B"."ORG_ID"="C"."ORG_ID"(+)) 44 - access("A"."ORG_NO"="B"."PTY_ID"(+)) 47 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 48 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL) 49 - filter("A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))看到了| 27 | VIEW PUSHED PREDICATE | V_M_PTY_EMP_INFO | 3 | 87 | 4 (0)| 00:00:01 |谓词推入 而且作为NL的被驱动表 首先HASH 一把同时看了下表大小情况:SQL> WITH t AS (SELECT /*+ materialize */ DISTINCT OBJECT_OWNER, OBJECT_NAME FROM (SELECT OBJECT_OWNER, OBJECT_NAME FROM V$SQL_PLAN WHERE SQL_ID = '2pfsvyamsk6yf' AND OBJECT_NAME IS NOT NULL UNION ALL SELECT OBJECT_OWNER, OBJECT_NAME FROM DBA_HIST_SQL_PLAN WHERE SQL_ID = '2pfsvyamsk6yf' AND OBJECT_NAME IS NOT NULL))SELECT a.owner, a.segment_name, a.segment_size as MB, TRUNC (a.segment_size / 8) block_count FROM ( SELECT owner, segment_name , TRUNC (SUM (bytes) / 1024/1024) segment_size FROM dba_segments WHERE /* segment_type LIKE 'TABLE%' AND*/ (OWNER, segment_name) IN (SELECT table_owner, table_name FROM dba_indexes WHERE (owner, index_name) IN (SELECT * FROM t) UNION ALL SELECT * FROM t) GROUP BY (owner, segment_name)) a; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 OWNER SEGMENT_NAME MB BLOCK_COUNT------------------------------ --------------------------------------------------------------------------------- ---------- -----------DWF F_AGT_SAVB_ACCTINFO_H 4121 515DWF F_PTY_TABLE 176 22DWM B_M_SYS_BRANCH 0 0DWF F_AGT_CADB_BOOK_H 2739 342DWF F_AGT_CADB_BOOK_H_IDX1152 19DWF F_AGT_CADB_ACCT128 16DWF F_PTY_ORG 0 0DWF F_PTY_EMP_INFO 4 0DWF F_EVT_CADJ_JOUR 3455 4319 rows selected.创建如下索引；SELECT xx.tran_card_no, count(1) cnt, max(xx.trans_date) max_date FROM DWF.F_EVT_CADJ_JOUR xx WHERE TRANS_DATE <= to_date('2014-03-31', 'YYYY-MM-DD') group by xx.tran_card_no;SQL> create index F_EVT_CADJ_JOUR_IDX1 on F_EVT_CADJ_JOUR(tran_card_no,trans_date);Index created.select agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal from dwf.F_AGT_SAVB_ACCTINFO_H where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') and end_dt > to_date('2014-03-31', 'YYYY-MM-DD') and acct_status <> '1' group by agmt_id create index F_AGT_SAVB_ACCTINFO_H_idx4 on F_AGT_SAVB_ACCTINFO_H(agmt_id,last_trans_date,acct_bal,start_dt,end_dt,acct_status);同时去掉谓词推入:with aa as (select a.agmt_id, sum(c.acct_bal) as card_bal, --借记卡期末存款余额 a.card_open_org, a.OPEN_DATE, -- 发卡日期 a.CARD_NEW_STATUS, -- 卡片状态 a.cust_magr, -- 客户经理号 a.cust_no, -- 客户号 a.corp_org from dwf.f_agt_cadb_book_h a left outer join (select agmt_id, acct_no from dwf.f_agt_cadb_acct where substr(acct_status, 8, 1) <> '2') b on a.master_card_no = b.agmt_id left outer join (select agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal from dwf.F_AGT_SAVB_ACCTINFO_H where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') and end_dt > to_date('2014-03-31', 'YYYY-MM-DD') and acct_status <> '1' group by agmt_id ) c -- 一卡多账号，某一账号睡眠其它未睡 ,卡账务信息表最后交易日期不准 on b.acct_no = c.agmt_id where substr(a.host_card_status, 8, 1) = '0' --卡状态不为注销 and a.master_card_no is not null --剔除待领卡 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 c.LAST_TRANS_DATE < to_date( '2014-03-01', 'YYYY-MM-DD') /*参数传入 季末日期*/ and c.acct_bal is not null group by a.agmt_id, a.card_open_org, a.OPEN_DATE, -- 发卡日期 a.CARD_NEW_STATUS, -- 卡片状态 a.cust_magr, -- 客户经理号 a.cust_no, -- 客户号 a.corp_org having(sum(c.acct_bal) < 10)),bb as (SELECT xx.tran_card_no, count(1) cnt, max(xx.trans_date) max_date FROM DWF.F_EVT_CADJ_JOUR xx WHERE TRANS_DATE <= to_date('2014-03-31', 'YYYY-MM-DD') group by xx.tran_card_no) select /*+ use_hash(AA XX) use_hash(AA F) */ aa.agmt_id, -- 卡号 'J' AS CARD_T, -- 卡种类 f.pty_name, -- 客户姓名 aa.cust_no, -- 客户证件号 f.mobile_no, -- 客户手机号 aa.OPEN_DATE, -- 发卡日期 null as ACTIVEDAY, -- 激活日期 aa.CARD_NEW_STATUS, -- 卡片状态 to_date('2014-03-31', 'YYYY-MM-DD') - nvl(bb.MAX_DATE, aa.OPEN_DATE) as sleep_day, -- 睡眠时间 card_bal, -- 存款余额 null as cred_limit, -- 授信额度 bb.cnt, -- 交易次数 aa.cust_magr, -- 客户经理号 xx.emp_name, -- 客户经理名称 aa.card_open_org, -- 所属机构 bb.MAX_DATE from aa left join bb on aa.agmt_id = bb.tran_card_no LEFT JOIN dwm.v_m_pty_emp_info xx --员工表 ON aa.cust_magr = xx.pty_id AND xx.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') AND xx.end_dt > to_date('2014-03-31', 'YYYY-MM-DD') LEFT JOIN dwf.f_pty_table f --当事人主表 ON aa.cust_no = f.pty_id AND aa.corp_org = f.corp_org AND f.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') AND f.end_dt > to_date('2014-03-31', 'YYYY-MM-DD') where bb.MAX_DATE < to_date( '2014-03-01', 'YYYY-MM-DD') and aa.card_open_org in (SELECT t.Org_Id FROM b_m_Sys_Branch t WHERE t.Status = 1 AND t.Dept_Flag != '2' CONNECT BY PRIOR t.Id = t.Parent_Id START WITH t.Org_Id =10000) ;Plan hash value: 2910718243 ------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 266 | 156K (1)| 00:31:14 ||* 1 | HASH JOIN OUTER | | 1 | 266 | 156K (1)| 00:31:14 ||* 2 | HASH JOIN | | 1 | 206 | 150K (1)| 00:30:01 ||* 3 | HASH JOIN OUTER | | 1 | 166 | 131K (1)| 00:26:18 ||* 4 | HASH JOIN | | 1 | 110 | 131K (1)| 00:26:14 || 5 | VIEW | | 1 | 83 | 131K (1)| 00:26:14 ||* 6 | FILTER | | | | | || 7 | HASH GROUP BY | | 1 | 211 | 131K (1)| 00:26:14 ||* 8 | HASH JOIN | | 27 | 5697 | 131K (1)| 00:26:14 ||* 9 | HASH JOIN | | 1764 | 161K| 37890 (1)| 00:07:35 || 10 | VIEW | | 1746 | 75078 | 33702 (1)| 00:06:45 ||* 11 | FILTER | | | | | || 12 | HASH GROUP BY | | 1746 | 90792 | 33702 (1)| 00:06:45 ||* 13 | INDEX FAST FULL SCAN | F_AGT_SAVB_ACCTINFO_H_IDX4 | 1010K| 50M| 33677 (1)| 00:06:45 ||* 14 | TABLE ACCESS FULL | F_AGT_CADB_ACCT | 41918 | 2087K| 4188 (1)| 00:00:51 ||* 15 | TABLE ACCESS FULL | F_AGT_CADB_BOOK_H | 5278 | 603K| 93269 (1)| 00:18:40 || 16 | VIEW | VW_NSO_1 | 6 | 162 | 4 (25)| 00:00:01 || 17 | HASH UNIQUE | | 6 | 294 | 4 (25)| 00:00:01 ||* 18 | FILTER | | | | | ||* 19 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | || 20 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 147 | 2940 | 3 (0)| 00:00:01 || 21 | VIEW | V_M_PTY_EMP_INFO | 7356 | 402K| 275 (1)| 00:00:04 ||* 22 | HASH JOIN OUTER | | 7356 | 366K| 275 (1)| 00:00:04 ||* 23 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 7337 | 236K| 128 (1)| 00:00:02 || 24 | VIEW | | 25885 | 455K| 148 (1)| 00:00:02 ||* 25 | HASH JOIN RIGHT OUTER | | 25885 | 1491K| 148 (1)| 00:00:02 ||* 26 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 23 | 322 | 3 (0)| 00:00:01 ||* 27 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | 3 (0)| 00:00:01 || 28 | VIEW | | 25885 | 1137K| 145 (1)| 00:00:02 ||* 29 | HASH JOIN RIGHT OUTER | | 25885 | 2173K| 145 (1)| 00:00:02 ||* 30 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 109 | 1526 | 3 (0)| 00:00:01 ||* 31 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 11 | 3 (0)| 00:00:01 || 32 | VIEW | | 25885 | 1820K| 142 (1)| 00:00:02 ||* 33 | HASH JOIN RIGHT OUTER | | 25885 | 1996K| 142 (1)| 00:00:02 ||* 34 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 12 | 168 | 3 (0)| 00:00:01 ||* 35 | HASH JOIN RIGHT OUTER | | 25885 | 1643K| 138 (0)| 00:00:02 || 36 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 147 | 1029 | 3 (0)| 00:00:01 ||* 37 | HASH JOIN RIGHT OUTER | | 25885 | 1466K| 135 (0)| 00:00:02 || 38 | VIEW | M_CBS_TO_DW_ORG | 143 | 4862 | 8 (0)| 00:00:01 || 39 | UNION-ALL | | | | | ||* 40 | TABLE ACCESS FULL | F_PTY_ORG | 129 | 2838 | 5 (0)| 00:00:01 ||* 41 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 14 | 98 | 3 (0)| 00:00:01 ||* 42 | TABLE ACCESS FULL | F_PTY_EMP_INFO | 25885 | 606K| 127 (0)| 00:00:02 || 43 | VIEW | | 14370 | 561K| 18634 (3)| 00:03:44 ||* 44 | FILTER | | | | | || 45 | HASH GROUP BY | | 14370 | 364K| 18634 (3)| 00:03:44 ||* 46 | INDEX FAST FULL SCAN | F_EVT_CADJ_JOUR_IDX1 | 13M| 328M| 18250 (1)| 00:03:40 ||* 47 | TABLE ACCESS FULL | F_PTY_TABLE | 589K| 33M| 6033 (1)| 00:01:13 |------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("AA"."CORP_ORG"="F"."CORP_ORG"(+) AND "AA"."CUST_NO"="F"."PTY_ID"(+)) 2 - access("AA"."AGMT_ID"="BB"."TRAN_CARD_NO") 3 - access("AA"."CUST_MAGR"="XX"."PTY_ID"(+)) 4 - access("AA"."CARD_OPEN_ORG"="ORG_ID") 6 - filter(SUM("C"."ACCT_BAL")<10) 8 - access("A"."MASTER_CARD_NO"="AGMT_ID") 9 - access("ACCT_NO"="C"."AGMT_ID") 11 - filter(MAX("LAST_TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SUM("ACCT_BAL") IS NOT NULL) 13 - filter("END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ACCT_STATUS"<>'1' AND "START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 - filter(SUBSTR("ACCT_STATUS",8,1)<>'2') 15 - filter("A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SUBSTR("A"."HOST_CARD_STATUS",8,1)='0' 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')) 18 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2') 19 - access("T"."PARENT_ID"=PRIOR "T"."ID") filter(TO_NUMBER("T"."ORG_ID")=10000) 22 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND "T"."PTY_ID"="B"."PTY_ID"(+)) 23 - filter("T"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 25 - access("FST"."SEC_ORG_ID"="ORG_ID"(+)) 26 - filter("ORG_LEVEL"(+)=2) 27 - filter("ID"=:B1) 29 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID")) 30 - filter("ORG_LEVEL"(+)=3) 31 - filter("ID"=:B1) 33 - access("C"."ORG_ID"="ORG_ID"(+)) 34 - filter("ORG_LEVEL"(+)=4) 35 - access("B"."ORG_ID"="C"."ORG_ID"(+)) 37 - access("A"."ORG_NO"="B"."PTY_ID"(+)) 40 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 41 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL) 42 - filter("A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 44 - filter(MAX("XX"."TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 46 - filter("TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 47 - filter("F"."END_DT"(+)>TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "F"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))最终SQL16S左右就出结果 0 0
- 优化大型复杂SQL
- 大型数据库的SQL查询优化技巧
- SQL语句太复杂，怎么优化
- 使用Hint优化复杂SQL案例-1
- 使用Hint优化复杂SQL案例-2
- 一个复杂子查询SQL优化
- 大型系统开发sql优化总结（转）
- 一个跨库复杂查询的SQL优化的案例
- 复杂SQL
- 复杂SQL
- 大型网站数据库优化
- 大型数据库优化
- 大型网站优化建议
- 复杂 SQL 应用
- 有些复杂的SQL
- 一个复杂的sql
- SqlServer复杂Sql执行
- 复杂sql 更新
- php 各个扩展的作用
- [IOS] AFNetworking 使用教程
- 户外烧烤配送
- ios7 自定义UINavigationBar UIBarButtonItem 10px的偏移纠正的两种解决方案
- jdbc应用程序连接Oracle rac的URL写法：
- 优化大型复杂SQL
- android 滑动事件冲突解决 Touch事件处理机制
- Scrollview 裁剪问题解决
- boost::asio（1）：封装了定时器类
- 如何对镶嵌数据集进行色彩平衡
- 判断shell字符串是否为空
- foj 2173 floyd+矩阵快速幂
- java中File的delete()方法删除文件失败的原因
- poj 2774 Long Long Message
