一: 创建的索引1 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 SQL> 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; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 83 rows selected. Execution Plan ---------------------------------------------------------- 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) Statistics ---------------------------------------------------------- 3066 recursive calls 0 db block gets 49583 consistent gets 0 physical reads 0 redo size 5735 bytes sent via SQL*Net to client 578 bytes received via SQL*Net from client 7 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 83 rows processed 二:创建索引2 create index f_agt_comr_intdist_h_idx2 on f_agt_comr_intdist_h(agmt_id,intt,payable_int_amt) tablespace EDWFIDXTBS nologging SQL> select /*+index(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; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 83 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4236917927 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1483K| 192M| 22M (1)| 74:59:22 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 1483K| 192M| 22M (1)| 74:59:22 | | 3 | NESTED LOOPS OUTER | | 1483K| 192M| 22M (1)| 74:59:21 | |* 4 | TABLE ACCESS FULL | F_AGT_LONC_BASIC_INFO_H | 615 | 48585 | 4303 (2)| 00:00:52 | |* 5 | TABLE ACCESS BY INDEX ROWID| F_AGT_COMR_INTDIST_H | 2412 | 134K| 42200 (1)| 00:08:27 | |* 6 | INDEX RANGE SCAN | F_AGT_COMR_INTDIST_H_IDX2 | 47300 | | 233 (1)| 00:00:03 | ------------------------------------------------------------------------------------------------------------ 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 - 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')) 6 - access("AA"."LOAN_ACCT_NO"="BB"."AGMT_ID"(+)) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 3066 recursive calls 0 db block gets 121492 consistent gets 0 physical reads 0 redo size 5735 bytes sent via SQL*Net to client 578 bytes received via SQL*Net from client 7 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 83 rows processed 结论: 由于2014-01-01<=DW_DATA_DT<=2014-02-10 ,这部分可以过滤大量数据,必须加入到组合索引中,而不是需要去回表在过滤。
组合索引
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
上一篇:|| 连接运算符
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
left join 后用 on 还是 where?
sql语法
SQL sql 结果集 -
mysql left join 无法用到主键索引 left join 索引不生效
索引类型主键索引唯一索引普通索引组合索引全文索引索引采用的数据结构B+树Hash索引的匹配方法全值匹配 (3个字段组合索引查3个字段)匹配最左前缀 (3个字段组合索引查2个字段) “最左匹配”原则,存储引擎不能使用范围条件右边的索引列。mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索
left join 索引失效无条件 mysql like 多个条件 一个项目部署多个节点会导致锁失效么 前缀表达式变成树 数据