组合索引

来源：互联网发布：江山国色txt下载知轩编辑：程序博客网时间：2024/05/29 04:48
一: 创建的索引1create 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 client578  bytes received via SQL*Net from client  7  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk) 83  rows processed二:创建索引2create 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 client578  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 ,这部分可以过滤大量数据,必须加入到组合索引中,而不是需要去回表在过滤。
0 0