经典的标量子查询

来源：互联网发布：华为知乎编辑：程序博客网时间：2024/05/21 07:18
explain plan for ( select 'C10927' as YXJGDM, --银行机构代码\           '20110213000003' as JRXKZH, --金融许可证号           a.SIGN_ORG as NBJGH, --内部机构号           case             when substr(b.product_name, 1, 3) = '如意宝' and                  substr(a.cust_no, 1, 1) = '1' then              '219'             when substr(b.product_name, 1, 3) = '如意宝' and                  substr(a.cust_no, 1, 1) <> '1' then              '218'             else              '40605'           end as MXKMBH, --明细科目编号           c.org_name as YXJGMC, --银行机构名称           case             when substr(b.product_name, 1, 3) = '如意宝' and                  substr(a.cust_no, 1, 1) = '1' then              '个人如意宝存款'             when substr(b.product_name, 1, 3) = '如意宝' and                  substr(a.cust_no, 1, 1) <> '1' then              '单位如意宝存款'             else              '委托理财负债'           end as MXKMMC, --明细科目名称           a.acct_no as LCZH, --理财帐号           a.cust_no as KHTYBH, --客户统一编号           a.cust_name as KHXM, --客户姓名           (case             when length(a.acct_no) = 16 then              (select MASTER_CARD_NO                 from dwf.F_AGT_CADB_BOOK_H                where start_dt <= date '2014-09-30'                  and end_dt > date '2014-09-30'                  and agmt_id = a.acct_no)             else              a.acct_no           end) as GLHQCKZH, --,           b.product_name as LCCPMC, --理财产品名称           b.product_no as HNBSM, --行内标识码           trunc(a.FINANCE_AMT, 0) as FEZS, --份额总数           trunc(a.FINANCE_AMT, 0) as DJFE, --冻结份额           '否' as HLZTZBZ, --再投资标志           PROF_AMT as BQSY, --本期收益           PROF_AMT as LJSY, --累计收益           a.FINANCE_AMT as MRCB, --买入成本           to_char(b.START_DATE, 'yyyymmdd') as BQQSRQ, --本期起始日期           to_char(b.maturity_dt, 'yyyymmdd') as BQDQRQ, --本期到期日期           nvl((case                 when length(a.acct_no) = 16 then                  (select to_char(OPEN_DATE, 'yyyymmdd')                     from dwf.F_AGT_CADB_BOOK_H                    where start_dt <= date '2014-09-30'                      and end_dt > date '2014-09-30'                      and agmt_id = a.acct_no)                 else                  (select to_char(OPEN_DATE, 'yyyymmdd')                     from dwf.F_AGT_SAVB_BASICINFO_H                    where start_dt <= date '2014-09-30'                      and end_dt > date '2014-09-30'                      and agmt_id = a.acct_no)               end),               (select to_char(OPEN_DATE, 'yyyymmdd')                  from dwf.F_AGT_CADB_BOOK_H                 where start_dt <= date '2014-09-30'                   and end_dt > date '2014-09-30'                   and agmt_id = a.acct_no)   ) as KHRQ, --开户日期              to_char(FREEZE_START_DATE, 'yyyymmdd') as SCDHRQ, --上次动户日期就是气息日           to_char(date '2014-09-30', 'yyyymmdd') as CJRQ      from DWF.F_EVT_EXTR_FINANCE_BOOK a      left join dwf.f_extc_finance_para b        on a.product_no = b.product_no      left join dwm.b_m_sys_branch c        on a.SIGN_ORG = c.org_id     where b.freeze_end_date between date '2014-01-01' and date '2014-09-30'       and a.in_acct_date is not null --到帐日就是结息日就是到期日 只统计到期日       and a.VALID_FLAG = '1'       and a.deduct_flag = '1'       and a.acct_flag in ('1', '2')       and a.product_no in (select product_no                              from dwf.f_extc_finance_para                             where PRODUCT_TERM <> 0)); select * from table(dbms_xplan.display());select * from table(dbms_xplan.display());Plan hash value: 3881317398---------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                         | 18473 |  2904K|  7488   (2)| 00:01:30 ||   1 |  TABLE ACCESS BY INDEX ROWID  | F_AGT_CADB_BOOK_H       |     1 |    54 | 72313   (1)| 00:14:28 ||*  2 |   INDEX SKIP SCAN             | F_AGT_CADB_BOOK_H_IDX1  |     2 |       | 72312   (1)| 00:14:28 ||   3 |  TABLE ACCESS BY INDEX ROWID  | F_AGT_CADB_BOOK_H       |     1 |    42 | 72313   (1)| 00:14:28 ||*  4 |   INDEX SKIP SCAN             | F_AGT_CADB_BOOK_H_IDX1  |     2 |       | 72312   (1)| 00:14:28 ||*  5 |   TABLE ACCESS BY INDEX ROWID | F_AGT_SAVB_BASICINFO_H  |     1 |    45 |     6   (0)| 00:00:01 ||*  6 |    INDEX SKIP SCAN            | SYS_C0026015            |     2 |       |     4   (0)| 00:00:01 ||   7 |    TABLE ACCESS BY INDEX ROWID| F_AGT_CADB_BOOK_H       |     1 |    42 | 72313   (1)| 00:14:28 ||*  8 |     INDEX SKIP SCAN           | F_AGT_CADB_BOOK_H_IDX1  |     2 |       | 72312   (1)| 00:14:28 ||*  9 |  HASH JOIN RIGHT SEMI         |                         | 18473 |  2904K|  7488   (2)| 00:01:30 ||* 10 |   TABLE ACCESS FULL           | F_EXTC_FINANCE_PARA     |   804 |  9648 |     9   (0)| 00:00:01 ||* 11 |   HASH JOIN RIGHT OUTER       |                         | 18473 |  2687K|  7478   (2)| 00:01:30 ||  12 |    TABLE ACCESS FULL          | B_M_SYS_BRANCH          |   154 |  3234 |     4   (0)| 00:00:01 ||* 13 |    HASH JOIN                  |                         | 18473 |  2309K|  7474   (2)| 00:01:30 ||* 14 |     TABLE ACCESS FULL         | F_EXTC_FINANCE_PARA     |   286 | 14872 |     9   (0)| 00:00:01 ||* 15 |     TABLE ACCESS FULL         | F_EVT_EXTR_FINANCE_BOOK | 52350 |  3885K|  7464   (2)| 00:01:30 |--------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------    2 - access("AGMT_ID"=:B1 AND "END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))       filter("AGMT_ID"=:B1 AND "END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))   4 - access("AGMT_ID"=:B1 AND "END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))       filter("AGMT_ID"=:B1 AND "END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))   5 - filter("END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))   6 - access("AGMT_ID"=:B1 AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss'))       filter("AGMT_ID"=:B1 AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss'))                 8 - access("AGMT_ID"=:B1 AND "END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))       filter("AGMT_ID"=:B1 AND "END_DT">TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "START_DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                 9 - access("A"."PRODUCT_NO"="PRODUCT_NO")  10 - filter(TO_NUMBER("PRODUCT_TERM")<>0)  11 - access("A"."SIGN_ORG"="C"."ORG_ID"(+))  13 - access("A"."PRODUCT_NO"="B"."PRODUCT_NO")  14 - filter("B"."FREEZE_END_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')               AND "B"."FREEZE_END_DATE"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  15 - filter("A"."IN_ACCT_DATE" IS NOT NULL AND ("A"."ACCT_FLAG"='1' OR "A"."ACCT_FLAG"='2')               AND "A"."DEDUCT_FLAG"='1' AND "A"."VALID_FLAG"='1')
0 0