创建组合索引SQL从1个多小时到1S的案例
来源：互联网 发布：计算机中的数据是指 编辑：程序博客网 时间：2024/04/27 19:48
select aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no,sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx from dwf.F_AGT_LONC_BASIC_INFO_H aa left join dwf.f_agt_comr_intdist_h bb on aa.loan_acct_no=bb.agmt_id and bb.dw_data_dt>=to_date('2014-01-01','yyyy-mm-dd') and bb.dw_data_dt<=to_date('2014-02-10','yyyy-mm-dd') where aa.start_dt<=to_date('2014-02-10','yyyy-mm-dd') and aa.end_dt>to_date('2014-02-10','yyyy-mm-dd') and aa.trans_cur_cd<>'T00CNY' group by aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no having sum(bb.payable_int_amt)<>0;Plan hash value: 2421779894 ------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1483K| 192M| 3883K (2)| 12:56:39 ||* 1 | FILTER | | | | | || 2 | HASH GROUP BY | | 1483K| 192M| 3883K (2)| 12:56:39 ||* 3 | HASH JOIN OUTER | | 1483K| 192M| 3883K (2)| 12:56:38 ||* 4 | TABLE ACCESS FULL| F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 ||* 5 | TABLE ACCESS FULL| F_AGT_COMR_INTDIST_H | 53M| 2929M| 3878K (2)| 12:55:42 |------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(SUM("BB"."PAYABLE_INT_AMT")<>0) 3 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+)) 4 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."TRANS_CUR_CD"<>'T00CNY') 5 - filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note----- - dynamic sampling used for this statement (level=2) create index f_agt_comr_intdist_h_idx2 on f_agt_comr_intdist_h(agmt_id,intt,payable_int_amt,dw_data_dt) tablespace EDWFIDXTBS nologging parallel 8 select aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no,sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx from dwf.F_AGT_LONC_BASIC_INFO_H aa left join dwf.f_agt_comr_intdist_h bb on aa.loan_acct_no=bb.agmt_id and bb.dw_data_dt>=to_date('2014-01-01','yyyy-mm-dd') and bb.dw_data_dt<=to_date('2014-02-10','yyyy-mm-dd') where aa.start_dt<=to_date('2014-02-10','yyyy-mm-dd') and aa.end_dt>to_date('2014-02-10','yyyy-mm-dd') and aa.trans_cur_cd<>'T00CNY' group by aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no having sum(bb.payable_int_amt)<>0;Plan hash value: 2166463325 ----------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |----------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1483K| 192M| 155K (1)| 00:31:09 | | | || 1 | PX COORDINATOR FORCED SERIAL | | | | | | | | || 2 | PX SEND QC (RANDOM) | :TQ10002 | 1483K| 192M| 155K (1)| 00:31:09 | Q1,02 | P->S | QC (RAND) ||* 3 | FILTER | | | | | | Q1,02 | PCWC | || 4 | HASH GROUP BY | | 1483K| 192M| 155K (1)| 00:31:09 | Q1,02 | PCWP | || 5 | PX RECEIVE | | 1483K| 192M| 155K (1)| 00:31:09 | Q1,02 | PCWP | || 6 | PX SEND HASH | :TQ10001 | 1483K| 192M| 155K (1)| 00:31:09 | Q1,01 | P->P | HASH || 7 | HASH GROUP BY | | 1483K| 192M| 155K (1)| 00:31:09 | Q1,01 | PCWP | || 8 | NESTED LOOPS OUTER | | 1483K| 192M| 155K (1)| 00:31:08 | Q1,01 | PCWP | || 9 | BUFFER SORT | | | | | | Q1,01 | PCWC | || 10 | PX RECEIVE | | | | | | Q1,01 | PCWP | || 11 | PX SEND ROUND-ROBIN| :TQ10000 | | | | | | S->P | RND-ROBIN ||* 12 | TABLE ACCESS FULL | F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 | | | ||* 13 | INDEX RANGE SCAN | F_AGT_COMR_INTDIST_H_IDX2 | 2412 | 134K| 284 (1)| 00:00:04 | Q1,01 | PCWP | |---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter(SUM(SYS_OP_CSR(SYS_OP_MSR(SUM("BB"."PAYABLE_INT_AMT"),SUM("PKG_TOOLS"."CURRCDTRAN"("BB"."INTT","AA"."TRANS_CUR_CD" ,'T00CNY','2014-02-10'))),0))<>0) 12 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."TRANS_CUR_CD"<>'T00CNY') 13 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+) AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note----- - dynamic sampling used for this statement (level=2) alter index f_agt_comr_intdist_h_idx2 noparallel;关闭并行后:Plan hash value: 2676048883 --------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1483K| 192M| 155K (1)| 00:31:09 ||* 1 | FILTER | | | | | || 2 | HASH GROUP BY | | 1483K| 192M| 155K (1)| 00:31:09 || 3 | NESTED LOOPS OUTER| | 1483K| 192M| 155K (1)| 00:31:08 ||* 4 | TABLE ACCESS FULL| F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 ||* 5 | INDEX RANGE SCAN | F_AGT_COMR_INTDIST_H_IDX2 | 2412 | 134K| 284 (1)| 00:00:04 |-------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(SUM("BB"."PAYABLE_INT_AMT")<>0) 4 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."TRANS_CUR_CD"<>'T00CNY') 5 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+) AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note----- - dynamic sampling used for this statement (level=2)大表必须放在被驱动表上,走NL循环.走Index fast full scan呢？explain plan for select /*+ index_ffs(bb F_AGT_COMR_INTDIST_H_IDX2)*/aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no,sum(dwm.pkg_tools.currcdtran(bb.INTT,aa.trans_cur_cd,'T00CNY','2014-02-10')) as jtlx from dwf.F_AGT_LONC_BASIC_INFO_H aa left join dwf.F_AGT_COMR_INTDIST_H bb on aa.loan_acct_no=bb.agmt_id and bb.dw_data_dt>=to_date('2014-01-01','yyyy-mm-dd') and bb.dw_data_dt<=to_date('2014-02-10','yyyy-mm-dd') where aa.start_dt<=to_date('2014-02-10','yyyy-mm-dd') and aa.end_dt>to_date('2014-02-10','yyyy-mm-dd') and aa.trans_cur_cd<>'T00CNY' group by aa.acct_org,aa.loan_acct_no,aa.FUNCTIONARY,aa.cust_no having sum(bb.payable_int_amt)<>0;Plan hash value: 2940667986 -----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1483K| 192M| 1203K (2)| 04:00:44 ||* 1 | FILTER | | | | | || 2 | HASH GROUP BY | | 1483K| 192M| 1203K (2)| 04:00:44 ||* 3 | HASH JOIN OUTER | | 1483K| 192M| 1203K (2)| 04:00:43 ||* 4 | TABLE ACCESS FULL | F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 ||* 5 | INDEX FAST FULL SCAN| F_AGT_COMR_INTDIST_H_IDX2 | 53M| 2929M| 1198K (2)| 03:59:47 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(SUM("BB"."PAYABLE_INT_AMT")<>0) 3 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+)) 4 - filter("AA"."START_DT"<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."END_DT">TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "AA"."TRANS_CUR_CD"<>'T00CNY') 5 - filter("BB"."DW_DATA_DT"(+)<=TO_DATE(' 2014-02-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "BB"."DW_DATA_DT"(+)>=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note----- - dynamic sampling used for this statement (level=2) 0 0
- 创建组合索引SQL从1个多小时到1S的案例
- 从15小时优化到1分钟的SQL
- AnsiString的索引从1开始,靠,害得我浪费了大半个小时
- oracle sql优化案例1（使用组合索引）
- JAVA对SQL SERVER 2000的备份和恢复-从C/S到B/S(1)
- 给定2个数字，n,m,使得从1到n之间的数字组合，他们的和等于m，求所有组合
- 24小时从0到1开发阴阳师小程序
- 24小时从0到1开发阴阳师小程序
- 24小时从0到1开发阴阳师小程序
- 我如何把薪水从 50人民币/天 提升到 100美元/小时的 (1)
- 我如何把薪水从 50人民币/天 提升到 100美元/小时的 (1)
- 1万个小时
- 在20分钟到1个小时内找到你的人生目标
- 在20分钟到1个小时内找到你的人生目标
- 个性签名代码（花了1个多小时从网上搜集以及整理）
- SQL CHARINDEX函数 注意：索引是从1开始的
- 一次300多行SQL从7分15s到24s的SQL历程
- 基于lucene的案例开发：创建索引
- CRC循环校验码c语言实现
- get向前台传值出现乱码解决办法
- 浙大PAT 2-06. 数列求和 (解题思路)
- 网易移动开发组实习 面试
- Ubuntu下读取某个目录下文件时如何设置路径的格式
- 创建组合索引SQL从1个多小时到1S的案例
- char, char*,scanf,printf
- 传说中的ACM大牛
- 风雨20年：我所积累的20条编程经验
- sql union代替or
- ArcGIS For Android 使用谷歌地图服务
- SDK编程笔记 — 计时器篇两个计时器API的讨论
- Android平台实现开机调试system_process
- /*自增运算测试*/ 理解
