利用no_merge优化
来源：互联网 发布：淘宝怎么设置定时开售 编辑：程序博客网 时间：2024/06/10 02:30
SQL> select a.unit3_code 机构编码, 2 a.unit3_name 机构名称, 3 a.dept1_code 部门编码, 4 a.dept1_name 部门名称, 5 a.mgr_code 客户经理编码, 6 a.mgr_name 客户经理名称, 7 b.base 基数 8 from s_pm_mgr_dept_rela a, 9 (select t2.mgr_code, sum(T1.avg * t2.share_rate / 100) / 10000 base 10 from (select acct_no_pk, 11 (SUM(DECODE(T1.DATA_DATE, '20121217', T1.Y_ADD_AF, 0)) - 12 SUM(DECODE(T1.DATA_DATE, '20120101', T1.Y_ADD_AF, 0)) + 13 SUM(DECODE(T1.DATA_DATE, 14 '20120101', 15 T1.ADJUST_AMT_AF, 16 0))) / 17 PKG_UF_BANK.FN_GETDAYS('20120101', '20121217') AVG 18 from t_pm_acct_dtl_af t1 19 where 1 = 1 20 and t1.data_date in ('20120101', '20121217') 21 and acct_flag = 'DEPOSIT' 22 and t1.cur_code = 1 23 and substr(flag, 1, 1) = '1' 24 and substr(flag, 4, 1) = '1' 25 and t1.dept1_code <> '999999999' 26 group by acct_no_pk) t1, 27 (select t3.acct_no_pk as acct_no_pk, 28 NVL(t2.cm_code, nvl(T3.MGR_CODE, 'xxxxxx')) as mgr_code, 29 decode(T1.SHARE_RATE, '', 100, T1.SHARE_RATE) as share_rate 30 from t_pm_acct_dtl t3, 31 bap_cm_account t1, 32 bap_cm_psndoc t2, 33 bap_nctobapdept t4 34 where 1 = 1 35 and t1.account(+) = t3.acct_no_pk 36 and decode(t1.seal_flag(+), '', 'N', t1.seal_flag(+)) = 'N' 37 and replace(t1.effect_date(+), '-') <= '20121217' 38 and replace(decode(t1.seal_date(+), 39 '', 40 pkg_uf_bank.FN_GETNEXTDAY('20121217', 41 'M'), 42 t1.seal_date(+)), 43 '-') > '20121217' 44 and decode(t1.dr(+), '', 0, t1.dr(+)) = 0 45 and t1.pk_cm_psndoc = t2 46 .pk_cm_psndoc(+) 47 and decode(t2.dr(+), '', 0, t2.dr(+)) = 0 48 and t2.pk_dept = t4.pk_nctobapdept(+) 49 and t4.deptcode(+) <> '999999999' 50 and t3.data_date = '20121217' 51 and t3.acct_flag = 'DEPOSIT') t2 52 Where t1.acct_no_pk = t2.acct_no_pk 53 group by t2.mgr_code) b 54 where a.mgr_code = b.mgr_code 55 and a.dept1_code <> '999999999' 56 and a.dept1_code = '18605' 57 and a.unit3_code = '18601' 58 and 1 = 1 59 and a.mgr_code IN ('00798', '00817') 60 order by 1, 3, 5 61 ;Execution Plan----------------------------------------------------------Plan hash value: 1749794338-------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 288 | 11414 (15)| 00:00:33 | | || 1 | SORT GROUP BY | | 1 | 288 | 11414 (15)| 00:00:33 | | ||* 2 | HASH JOIN OUTER | | 1 | 288 | 11413 (15)| 00:00:33 | | ||* 3 | FILTER | | | | | | | || 4 | NESTED LOOPS OUTER | | 1 | 258 | 11408 (15)| 00:00:33 | | || 5 | NESTED LOOPS OUTER | | 1 | 207 | 11407 (15)| 00:00:33 | | || 6 | NESTED LOOPS | | 1 | 139 | 11402 (15)| 00:00:33 | | || 7 | MERGE JOIN CARTESIAN | | 1 | 98 | 11398 (15)| 00:00:33 | | || 8 | INLIST ITERATOR | | | | | | | ||* 9 | TABLE ACCESS BY INDEX ROWID | S_PM_MGR_DEPT_RELA | 1 | 63 | 3 (0)| 00:00:01 | | ||* 10 | INDEX RANGE SCAN | MGR_DEPT_RELA_IDX5 | 1 | | 2 (0)| 00:00:01 | | || 11 | BUFFER SORT | | 93 | 3255 | 11396 (15)| 00:00:33 | | || 12 | VIEW | | 93 | 3255 | 11396 (15)| 00:00:33 | | || 13 | HASH GROUP BY | | 93 | 5487 | 11396 (15)| 00:00:33 | | || 14 | PARTITION LIST INLIST | | 93 | 5487 | 11395 (15)| 00:00:33 |KEY(I) |KEY(I) ||* 15 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 93 | 5487 | 11395 (15)| 00:00:33 |KEY(I) |KEY(I) || 16 | PARTITION LIST SINGLE | | 1 | 41 | 4 (0)| 00:00:01 | KEY | KEY ||* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL | 1 | 41 | 4 (0)| 00:00:01 | 352 | 352 ||* 18 | INDEX RANGE SCAN | ACCT_DTL_IDX1 | 2 | | 2 (0)| 00:00:01 | 352 | 352 ||* 19 | TABLE ACCESS BY INDEX ROWID | BAP_CM_ACCOUNT | 1 | 68 | 5 (0)| 00:00:01 | | ||* 20 | INDEX RANGE SCAN | IDX_BAP_CM_ACCOUNT | 2 | | 2 (0)| 00:00:01 | | ||* 21 | TABLE ACCESS BY INDEX ROWID | BAP_CM_PSNDOC | 1 | 51 | 1 (0)| 00:00:01 | | ||* 22 | INDEX UNIQUE SCAN | PK_BAP_CM_PSNDOC | 1 | | 0 (0)| 00:00:01 | | ||* 23 | TABLE ACCESS FULL | BAP_NCTOBAPDEPT | 609 | 18270 | 4 (0)| 00:00:01 | | |-------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("T2"."PK_DEPT"="T4"."PK_NCTOBAPDEPT"(+)) 3 - filter("A"."MGR_CODE"=NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx')) AND (NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00798' OR NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00817')) 9 - filter("A"."DEPT1_CODE"='18605' AND "A"."DEPT1_CODE"<>'999999999') 10 - access("A"."UNIT3_CODE"='18601' AND ("A"."MGR_CODE"='00798' OR "A"."MGR_CODE"='00817')) 15 - filter("ACCT_FLAG"='DEPOSIT' AND SUBSTR("FLAG",1,1)='1' AND SUBSTR("FLAG",4,1)='1' AND "T1"."DEPT1_CODE"<>'999999999' AND TO_NUMBER("T1"."CUR_CODE")=1) 17 - filter("T3"."ACCT_FLAG"='DEPOSIT') 18 - access("T3"."DATA_DATE"=20121217 AND "T1"."ACCT_NO_PK"="T3"."ACCT_NO_PK") 19 - filter(DECODE("T1"."SEAL_FLAG"(+),'','N',"T1"."SEAL_FLAG"(+))='N' AND REPLACE("T1"."EFFECT_DATE"(+),'-')<='20121217' AND DECODE(TO_CHAR("T1"."DR"(+)),'',0,"T1"."DR"(+))=0 AND REPLACE(DECODE("T1"."SEAL_DATE"(+),'',"PKG_UF_BANK"."FN_GETNEXTDAY"('20121217','M'),"T1"."SEAL_DATE"(+)),'-')>'20121217 ') 20 - access("T1"."ACCOUNT"(+)="T3"."ACCT_NO_PK") 21 - filter(DECODE(TO_CHAR("T2"."DR"(+)),'',0,"T2"."DR"(+))=0) 22 - access("T1"."PK_CM_PSNDOC"="T2"."PK_CM_PSNDOC"(+)) 23 - filter("T4"."DEPTCODE"(+)<>'999999999')Statistics---------------------------------------------------------- 214 recursive calls 0 db block gets 11912102 consistent gets 122578 physical reads 13516 redo size 1064 bytes sent via SQL*Net to client 1625 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 2 rows processedselect count(*) from S_PM_MGR_DEPT_RELA A where ("A"."UNIT3_CODE"='18601' AND ("A"."MGR_CODE"='00798' OR "A"."MGR_CODE"='00817'))and ("A"."DEPT1_CODE"='18605' AND "A"."DEPT1_CODE"<>'999999999');---返回2条记录(select acct_no_pk, 11 (SUM(DECODE(T1.DATA_DATE, '20121217', T1.Y_ADD_AF, 0)) - 12 SUM(DECODE(T1.DATA_DATE, '20120101', T1.Y_ADD_AF, 0)) + 13 SUM(DECODE(T1.DATA_DATE, 14 '20120101', 15 T1.ADJUST_AMT_AF, 16 0))) / 17 PKG_UF_BANK.FN_GETDAYS('20120101', '20121217') AVG 18 from t_pm_acct_dtl_af t1 19 where 1 = 1 20 and t1.data_date in ('20120101', '20121217') 21 and acct_flag = 'DEPOSIT' 22 and t1.cur_code = 1 23 and substr(flag, 1, 1) = '1' 24 and substr(flag, 4, 1) = '1' 25 and t1.dept1_code <> '999999999' 26 group by acct_no_pk) t1 (select t3.acct_no_pk as acct_no_pk, 28 NVL(t2.cm_code, nvl(T3.MGR_CODE, 'xxxxxx')) as mgr_code, 29 decode(T1.SHARE_RATE, '', 100, T1.SHARE_RATE) as share_rate 30 from t_pm_acct_dtl t3, 31 bap_cm_account t1, 32 bap_cm_psndoc t2, 33 bap_nctobapdept t4 34 where 1 = 1 35 and t1.account(+) = t3.acct_no_pk 36 and decode(t1.seal_flag(+), '', 'N', t1.seal_flag(+)) = 'N' 37 and replace(t1.effect_date(+), '-') <= '20121217' 38 and replace(decode(t1.seal_date(+), 39 '', 40 pkg_uf_bank.FN_GETNEXTDAY('20121217', 41 'M'), 42 t1.seal_date(+)), 43 '-') > '20121217' 44 and decode(t1.dr(+), '', 0, t1.dr(+)) = 0 45 and t1.pk_cm_psndoc = t2 46 .pk_cm_psndoc(+) 47 and decode(t2.dr(+), '', 0, t2.dr(+)) = 0 48 and t2.pk_dept = t4.pk_nctobapdept(+) 49 and t4.deptcode(+) <> '999999999' 50 and t3.data_date = '20121217' 51 and t3.acct_flag = 'DEPOSIT') t2| 12 | VIEW | | 93 | 3255 | 11396 (15)| 00:00:33 | | || 13 | HASH GROUP BY | | 93 | 5487 | 11396 (15)| 00:00:33 | | || 14 | PARTITION LIST INLIST | | 93 | 5487 | 11395 (15)| 00:00:33 |KEY(I) |KEY(I) ||* 15 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 93 | 5487 | 11395 (15)| 00:00:33 |KEY(I) |KEY(I) || 16 | PARTITION LIST SINGLE | | 1 | 41 | 4 (0)| 00:00:01 | KEY | KEY ||* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PM_ACCT_DTL | 1 | 41 | 4 (0)| 00:00:01 | 352 | 352 ||* 18 | INDEX RANGE SCAN | ACCT_DTL_IDX1 | 2 | | 2 (0)| 00:00:01 | 352 | 352 ||* 19 | TABLE ACCESS BY INDEX ROWID | BAP_CM_ACCOUNT | 1 | 68 | 5 (0)| 00:00:01 | | ||* 20 | INDEX RANGE SCAN | IDX_BAP_CM_ACCOUNT | 2 | | 2 (0)| 00:00:01 | | ||* 21 | TABLE ACCESS BY INDEX ROWID | BAP_CM_PSNDOC | 1 | 51 | 1 (0)| 00:00:01 | | ||* 22 | INDEX UNIQUE SCAN | PK_BAP_CM_PSNDOC | 1 | | 0 (0)| 00:00:01 | | ||* 23 | TABLE ACCESS FULL | BAP_NCTOBAPDEPT | 609 | 18270 | 4 (0)| 00:00:01 | | |-------------------------------------------------------------------------------------------------------------------------------可以看到t1视图没有展开,但是t2视图确被展开了,尝试让t2视图不展开SQL> select a.unit3_code 机构编码, 2 a.unit3_name 机构名称, 3 a.dept1_code 部门编码, 4 a.dept1_name 部门名称, 5 a.mgr_code 客户经理编码, 6 a.mgr_name 客户经理名称, 7 b.base 基数 8 from s_pm_mgr_dept_rela a, 9 (select /*+ no_merge(t2)*/ t2.mgr_code, sum(T1.avg * t2.share_rate / 100) / 10000 base 10 from (select acct_no_pk, 11 (SUM(DECODE(T1.DATA_DATE, '20121217', T1.Y_ADD_AF, 0)) - 12 SUM(DECODE(T1.DATA_DATE, '20120101', T1.Y_ADD_AF, 0)) + 13 SUM(DECODE(T1.DATA_DATE, 14 '20120101', 15 T1.ADJUST_AMT_AF, 16 0))) / 17 PKG_UF_BANK.FN_GETDAYS('20120101', '20121217') AVG 18 from t_pm_acct_dtl_af t1 19 where 1 = 1 20 and t1.data_date in ('20120101', '20121217') 21 and acct_flag = 'DEPOSIT' 22 and t1.cur_code = 1 23 and substr(flag, 1, 1) = '1' 24 and substr(flag, 4, 1) = '1' 25 and t1.dept1_code <> '999999999' 26 group by acct_no_pk) t1, 27 (select 28 t3.acct_no_pk as acct_no_pk, 29 NVL(t2.cm_code, nvl(T3.MGR_CODE, 'xxxxxx')) as mgr_code, 30 decode(T1.SHARE_RATE, '', 100, T1.SHARE_RATE) as share_rate 31 from t_pm_acct_dtl t3, 32 bap_cm_account t1, 33 bap_cm_psndoc t2, 34 bap_nctobapdept t4 35 where 1 = 1 36 and t1.account(+) = t3.acct_no_pk 37 and decode(t1.seal_flag(+), '', 'N', t1.seal_flag(+)) = 'N' 38 and replace(t1.effect_date(+), '-') <= '20121217' 39 and replace(decode(t1.seal_date(+), 40 '', 41 pkg_uf_bank.FN_GETNEXTDAY('20121217', 42 'M'), 43 t1.seal_date(+)), 44 '-') > '20121217' 45 and decode(t1.dr(+), '', 0, t1.dr(+)) = 0 46 and t1.pk_cm_psndoc = t2 47 .pk_cm_psndoc(+) 48 and decode(t2.dr(+), '', 0, t2.dr(+)) = 0 49 and t2.pk_dept = t4.pk_nctobapdept(+) 50 and t4.deptcode(+) <> '999999999' 51 and t3.data_date = '20121217' 52 and t3.acct_flag = 'DEPOSIT') t2 53 Where t1.acct_no_pk = t2.acct_no_pk 54 group by t2.mgr_code) b 55 where a.mgr_code = b.mgr_code 56 and a.dept1_code <> '999999999' 57 and a.dept1_code = '18605' 58 and a.unit3_code = '18601' 59 and 1 = 1 60 and a.mgr_code IN ('00798', '00817') 61 order by 1, 3, 5 62 ;Execution Plan----------------------------------------------------------Plan hash value: 74438866----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 150 | 19483 (14)| 00:00:57 | | || 1 | SORT GROUP BY | | 1 | 150 | 19483 (14)| 00:00:57 | | ||* 2 | HASH JOIN | | 1 | 150 | 19482 (14)| 00:00:57 | | ||* 3 | HASH JOIN | | 1 | 115 | 8086 (13)| 00:00:24 | | || 4 | INLIST ITERATOR | | | | | | | ||* 5 | TABLE ACCESS BY INDEX ROWID| S_PM_MGR_DEPT_RELA | 1 | 63 | 3 (0)| 00:00:01 | | ||* 6 | INDEX RANGE SCAN | MGR_DEPT_RELA_IDX5 | 1 | | 2 (0)| 00:00:01 | | || 7 | VIEW | | 12519 | 635K| 8082 (13)| 00:00:24 | | ||* 8 | HASH JOIN RIGHT OUTER | | 12519 | 2322K| 8082 (13)| 00:00:24 | | ||* 9 | TABLE ACCESS FULL | BAP_NCTOBAPDEPT | 609 | 18270 | 4 (0)| 00:00:01 | | ||* 10 | FILTER | | | | | | | ||* 11 | HASH JOIN RIGHT OUTER | | 12519 | 1956K| 8077 (13)| 00:00:24 | | ||* 12 | TABLE ACCESS FULL | BAP_CM_PSNDOC | 84 | 4284 | 33 (10)| 00:00:01 | | ||* 13 | HASH JOIN RIGHT OUTER | | 629K| 65M| 8021 (12)| 00:00:24 | | ||* 14 | TABLE ACCESS FULL | BAP_CM_ACCOUNT | 1 | 68 | 5627 (12)| 00:00:17 | | || 15 | PARTITION LIST SINGLE | | 629K| 24M| 2370 (13)| 00:00:07 | KEY | KEY ||* 16 | TABLE ACCESS FULL | T_PM_ACCT_DTL | 629K| 24M| 2370 (13)| 00:00:07 | 352 | 352 || 17 | VIEW | | 93 | 3255 | 11396 (15)| 00:00:33 | | || 18 | HASH GROUP BY | | 93 | 5487 | 11396 (15)| 00:00:33 | | || 19 | PARTITION LIST INLIST | | 93 | 5487 | 11395 (15)| 00:00:33 |KEY(I) |KEY(I) ||* 20 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 93 | 5487 | 11395 (15)| 00:00:33 |KEY(I) |KEY(I) |----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("T1"."ACCT_NO_PK"="T2"."ACCT_NO_PK") 3 - access("A"."MGR_CODE"="T2"."MGR_CODE") 5 - filter("A"."DEPT1_CODE"='18605' AND "A"."DEPT1_CODE"<>'999999999') 6 - access("A"."UNIT3_CODE"='18601' AND ("A"."MGR_CODE"='00798' OR "A"."MGR_CODE"='00817')) 8 - access("T2"."PK_DEPT"="T4"."PK_NCTOBAPDEPT"(+)) 9 - filter("T4"."DEPTCODE"(+)<>'999999999') 10 - filter(NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00798' OR NVL("T2"."CM_CODE",NVL("T3"."MGR_CODE",'xxxxxx'))='00817') 11 - access("T1"."PK_CM_PSNDOC"="T2"."PK_CM_PSNDOC"(+)) 12 - filter(DECODE(TO_CHAR("T2"."DR"(+)),'',0,"T2"."DR"(+))=0) 13 - access("T1"."ACCOUNT"(+)="T3"."ACCT_NO_PK") 14 - filter(DECODE("T1"."SEAL_FLAG"(+),'','N',"T1"."SEAL_FLAG"(+))='N' AND REPLACE("T1"."EFFECT_DATE"(+),'-')<='20121217' AND DECODE(TO_CHAR("T1"."DR"(+)),'',0,"T1"."DR"(+))=0 AND REPLACE(DECODE("T1"."SEAL_DATE"(+),'',"PKG_UF_BANK"."FN_GETNEXTDAY"('20121217','M'),"T1"."SEAL_DATE"(+)),'-')> '20121217') 16 - filter("T3"."ACCT_FLAG"='DEPOSIT') 20 - filter("ACCT_FLAG"='DEPOSIT' AND SUBSTR("FLAG",1,1)='1' AND SUBSTR("FLAG",4,1)='1' AND "T1"."DEPT1_CODE"<>'999999999' AND TO_NUMBER("T1"."CUR_CODE")=1)Statistics---------------------------------------------------------- 154 recursive calls 0 db block gets 193327 consistent gets 13545 physical reads 692 redo size 1064 bytes sent via SQL*Net to client 1649 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed
- 利用no_merge优化
- 利用NO_MERGE解决数据字典视图访问低效
- merge no_merge
- NO_MERGE视图合并案例
- no_merge/merge vs no_unnest/unnest
- index_ffs, leading,merge,no_merge,no_unnest,use_hash
- 利用分区优化SQL
- 利用merge优化
- 利用merge优化
- 利用函数索引优化<>
- 利用集群因子优化
- 利用OWI优化SQL
- 利用ViewHolder优化BaseAdapter
- 利用RunLoop优化tableView
- 利用组合索引优化
- 利用convertView优化ListView性能
- 利用Hierarchy Viewer优化布局
- 利用convertView优化ListView性能
- [转]QWidget QMainWindow QDialog的区别和相互转换
- SOCKET选项和管理（一）
- 编译型语言VS解释型语言
- linux socket模板
- JavaScript表单验证年龄
- 利用no_merge优化
- 怎么就不可以了
- 各种学习资料大全
- CentOS&java环境配置（转）
- 最近的计划吧
- 汉字转拼音
- 检测到在集成的托管管道模式下不适用的 ASP.NET 设置。
- Ubuntu下环境变量的设置
- 魅族 M9 的刷机脚本代码
