with aa as

 (select

         a.agmt_id,

         sum(c.acct_bal) as card_bal, --借记卡期末存款余额

         a.card_open_org,

         a.OPEN_DATE, -- 发卡日期

         a.CARD_NEW_STATUS, -- 卡片状态

         a.cust_magr, -- 客户经理号

         a.cust_no, -- 客户号

         a.corp_org

    from dwf.f_agt_cadb_book_h a

    left outer join (select agmt_id, acct_no

                      from dwf.f_agt_cadb_acct

                     where substr(acct_status, 8, 1) <> '2') b

      on a.master_card_no = b.agmt_id

    left outer join (select agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal

                      from dwf.F_AGT_SAVB_ACCTINFO_H

                     where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

                       and end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

                       and acct_status <> '1'

                       group by  agmt_id

                       ) c  -- 一卡多账号，某一账号睡眠其它未睡 ,卡账务信息表最后交易日期不准

      on b.acct_no = c.agmt_id

   where substr(a.host_card_status, 8, 1) = '0' --卡状态不为注销

     and a.master_card_no is not null --剔除待领卡

     and a.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') /*参数传入 季末日期*/

     and a.end_dt > to_date('2014-03-31', 'YYYY-MM-DD') /*参数传入 季末日期*/

     and c.LAST_TRANS_DATE < to_date( '2014-03-01', 'YYYY-MM-DD') /*参数传入 季末日期*/

     and c.acct_bal is not null

   group by a.agmt_id,

            a.card_open_org,

            a.OPEN_DATE, -- 发卡日期

            a.CARD_NEW_STATUS, -- 卡片状态

            a.cust_magr, -- 客户经理号

            a.cust_no, -- 客户号

            a.corp_org

  having(sum(c.acct_bal) < 10)),

bb as

 (SELECT xx.tran_card_no, count(1) cnt, max(xx.trans_date) max_date

    FROM DWF.F_EVT_CADJ_JOUR xx

   WHERE TRANS_DATE <= to_date('2014-03-31', 'YYYY-MM-DD')

   group by xx.tran_card_no)

select aa.agmt_id, -- 卡号

       'J' AS CARD_T, -- 卡种类

       f.pty_name, -- 客户姓名

       aa.cust_no, -- 客户证件号

       f.mobile_no, -- 客户手机号

       aa.OPEN_DATE, -- 发卡日期

       null as ACTIVEDAY, -- 激活日期

       aa.CARD_NEW_STATUS, -- 卡片状态

       to_date('2014-03-31', 'YYYY-MM-DD') - nvl(bb.MAX_DATE, aa.OPEN_DATE) as sleep_day, -- 睡眠时间

       card_bal, -- 存款余额

       null as cred_limit, -- 授信额度

       bb.cnt, -- 交易次数

       aa.cust_magr, -- 客户经理号

       xx.emp_name, -- 客户经理名称

       aa.card_open_org, -- 所属机构

       bb.MAX_DATE

  from aa

  left join bb

    on aa.agmt_id = bb.tran_card_no

  LEFT JOIN dwm.v_m_pty_emp_info xx --员工表

    ON aa.cust_magr = xx.pty_id

   AND xx.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

   AND xx.end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

  LEFT JOIN dwf.f_pty_table f --当事人主表

    ON aa.cust_no = f.pty_id

   AND aa.corp_org = f.corp_org

   AND f.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

   AND f.end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

   where   bb.MAX_DATE < to_date( '2014-03-01', 'YYYY-MM-DD')

   and aa.card_open_org in (SELECT t.Org_Id

                           FROM b_m_Sys_Branch t

                          WHERE t.Status = 1

                            AND t.Dept_Flag != '2'

                         CONNECT BY  PRIOR t.Id = t.Parent_Id

                          START WITH t.Org_Id =10000) ;

这SQL跑了半天也没出结果,看下执行计划:

                          select * from table(dbms_xplan.display());

Plan hash value: 4046975539

-------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                       | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                |                       |    21 |  5040 |   267K  (2)| 00:53:36 |

|   1 |  NESTED LOOPS OUTER                             |                       |    21 |  5040 |   267K  (2)| 00:53:36 |

|   2 |   NESTED LOOPS OUTER                            |                       |     6 |  1266 |   267K  (2)| 00:53:36 |

|*  3 |    HASH JOIN                                    |                       |     6 |   900 |   267K  (2)| 00:53:36 |

|*  4 |     HASH JOIN                                   |                       |     6 |   660 |   179K  (2)| 00:35:56 |

|   5 |      VIEW                                       | VW_NSO_1              |     6 |   162 |     4  (25)| 00:00:01 |

|   6 |       HASH UNIQUE                               |                       |     6 |   294 |     4  (25)| 00:00:01 |

|*  7 |        FILTER                                   |                       |       |       |            |          |

|*  8 |         CONNECT BY NO FILTERING WITH SW (UNIQUE)|                       |       |       |            |          |

|   9 |          TABLE ACCESS FULL                      | B_M_SYS_BRANCH        |   144 |  2880 |     3   (0)| 00:00:01 |

|  10 |      VIEW                                       |                       |    28 |  2324 |   179K  (2)| 00:35:56 |

|* 11 |       FILTER                                    |                       |       |       |            |          |

|  12 |        HASH GROUP BY                            |                       |    28 |  5908 |   179K  (2)| 00:35:56 |

|* 13 |         HASH JOIN                               |                       |   552 |   113K|   179K  (2)| 00:35:56 |

|* 14 |          HASH JOIN                              |                       |  2835 |   260K|   110K  (3)| 00:22:10 |

|  15 |           VIEW                                  |                       |  2805 |   117K|   107K  (3)| 00:21:32 |

|* 16 |            FILTER                               |                       |       |       |            |          |

|  17 |             HASH GROUP BY                       |                       |  2805 |   142K|   107K  (3)| 00:21:32 |

|* 18 |              TABLE ACCESS FULL                  | F_AGT_SAVB_ACCTINFO_H |  8624K|   427M|   107K  (2)| 00:21:25 |

|* 19 |           TABLE ACCESS FULL                     | F_AGT_CADB_ACCT       | 38498 |  1917K|  3128   (1)| 00:00:38 |

|* 20 |          TABLE ACCESS FULL                      | F_AGT_CADB_BOOK_H     | 61287 |  7002K| 68898   (2)| 00:13:47 |

|  21 |     VIEW                                        |                       | 13290 |   519K| 88273   (2)| 00:17:40 |

|* 22 |      FILTER                                     |                       |       |       |            |          |

|  23 |       HASH GROUP BY                             |                       | 13290 |   337K| 88273   (2)| 00:17:40 |

|* 24 |        TABLE ACCESS FULL                        | F_EVT_CADJ_JOUR       |    12M|   298M| 87422   (1)| 00:17:30 |

|* 25 |    TABLE ACCESS BY INDEX ROWID                  | F_PTY_TABLE           |     1 |    61 |     3   (0)| 00:00:01 |

|* 26 |     INDEX RANGE SCAN                            | SYS_C0061472          |     1 |       |     2   (0)| 00:00:01 |

|  27 |   VIEW PUSHED PREDICATE                         | V_M_PTY_EMP_INFO      |     3 |    87 |     4   (0)| 00:00:01 |

|* 28 |    HASH JOIN OUTER                              |                       |     3 |   153 |   122   (2)| 00:00:02 |

|* 29 |     TABLE ACCESS BY INDEX ROWID                 | F_PTY_EMP_INFO        |     3 |    99 |     4   (0)| 00:00:01 |

|* 30 |      INDEX RANGE SCAN                           | EMP_IDX_002           |     3 |       |     1   (0)| 00:00:01 |

|* 31 |     VIEW                                        |                       | 24404 |   428K|   118   (2)| 00:00:02 |

|* 32 |      HASH JOIN RIGHT OUTER                      |                       | 24404 |  1406K|   118   (2)| 00:00:02 |

|* 33 |       TABLE ACCESS FULL                         | B_M_SYS_BRANCH        |    36 |   504 |     3   (0)| 00:00:01 |

|* 34 |       TABLE ACCESS FULL                         | B_M_SYS_BRANCH        |     1 |    11 |     3   (0)| 00:00:01 |

|  35 |       VIEW                                      |                       | 24404 |  1072K|   115   (2)| 00:00:02 |

|* 36 |        HASH JOIN RIGHT OUTER                    |                       | 24404 |  2049K|   115   (2)| 00:00:02 |

|* 37 |         TABLE ACCESS FULL                       | B_M_SYS_BRANCH        |    36 |   504 |     3   (0)| 00:00:01 |

|* 38 |         TABLE ACCESS FULL                       | B_M_SYS_BRANCH        |     1 |    11 |     3   (0)| 00:00:01 |

|  39 |         VIEW                                    |                       | 24404 |  1715K|   111   (1)| 00:00:02 |

|* 40 |          HASH JOIN RIGHT OUTER                  |                       | 24404 |  1882K|   111   (1)| 00:00:02 |

|* 41 |           TABLE ACCESS FULL                     | B_M_SYS_BRANCH        |    36 |   504 |     3   (0)| 00:00:01 |

|* 42 |           HASH JOIN RIGHT OUTER                 |                       | 24404 |  1549K|   108   (1)| 00:00:02 |

|  43 |            TABLE ACCESS FULL                    | B_M_SYS_BRANCH        |   144 |  1008 |     3   (0)| 00:00:01 |

|* 44 |            HASH JOIN RIGHT OUTER                |                       | 24404 |  1382K|   105   (1)| 00:00:02 |

|  45 |             VIEW                                | M_CBS_TO_DW_ORG       |   141 |  4794 |     8   (0)| 00:00:01 |

|  46 |              UNION-ALL                          |                       |       |       |            |          |

|* 47 |               TABLE ACCESS FULL                 | F_PTY_ORG             |   127 |  2794 |     5   (0)| 00:00:01 |

|* 48 |               TABLE ACCESS FULL                 | B_M_SYS_BRANCH        |    14 |    98 |     3   (0)| 00:00:01 |

|* 49 |             TABLE ACCESS FULL                   | F_PTY_EMP_INFO        | 24404 |   571K|    97   (2)| 00:00:02 |

-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("AA"."AGMT_ID"="BB"."TRAN_CARD_NO")

   4 - access("AA"."CARD_OPEN_ORG"="ORG_ID")

   7 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')

   8 - access("T"."PARENT_ID"=PRIOR "T"."ID")

       filter(TO_NUMBER("T"."ORG_ID")=10000)

  11 - filter(SUM("C"."ACCT_BAL")<10)

  13 - access("A"."MASTER_CARD_NO"="AGMT_ID")

  14 - access("ACCT_NO"="C"."AGMT_ID")

  16 - filter(MAX("LAST_TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              SUM("ACCT_BAL") IS NOT NULL)

  18 - filter("ACCT_STATUS"<>'1' AND "END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  19 - filter(SUBSTR("ACCT_STATUS",8,1)<>'2')

  20 - filter(SUBSTR("A"."HOST_CARD_STATUS",8,1)='0' AND "A"."MASTER_CARD_NO" IS NOT NULL AND

              "A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."START_DT"<=TO_DATE(' 2014-03-31

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  22 - filter(MAX("XX"."TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  24 - filter("TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  25 - filter("F"."END_DT"(+)>TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  26 - access("AA"."CUST_NO"="F"."PTY_ID"(+) AND "AA"."CORP_ORG"="F"."CORP_ORG"(+) AND

              "F"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("F"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "AA"."CORP_ORG"="F"."CORP_ORG"(+))

  28 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND

              "T"."PTY_ID"="B"."PTY_ID"(+))

  29 - filter("T"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "T"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  30 - access("T"."PTY_ID"="AA"."CUST_MAGR")

  31 - filter("B"."PTY_ID"(+)="AA"."CUST_MAGR" AND "B"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00',

              'syyyy-mm-dd hh24:mi:ss'))

  32 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))

  33 - filter("ORG_LEVEL"(+)=2)

  34 - filter("ID"=:B1)

  36 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))

  37 - filter("ORG_LEVEL"(+)=3)

  38 - filter("ID"=:B1)

  40 - access("C"."ORG_ID"="ORG_ID"(+))

  41 - filter("ORG_LEVEL"(+)=4)

  42 - access("B"."ORG_ID"="C"."ORG_ID"(+))

  44 - access("A"."ORG_NO"="B"."PTY_ID"(+))

  47 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  48 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%'

              AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)

  49 - filter("A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

看到了|  27 |   VIEW PUSHED PREDICATE                         | V_M_PTY_EMP_INFO      |     3 |    87 |     4   (0)| 00:00:01 |

谓词推入 而且作为NL的被驱动表 首先HASH 一把

同时看了下表大小情况:

SQL>  WITH t

     AS (SELECT /*+ materialize */

               DISTINCT OBJECT_OWNER, OBJECT_NAME

           FROM (SELECT OBJECT_OWNER, OBJECT_NAME

                   FROM V$SQL_PLAN

                  WHERE SQL_ID = '2pfsvyamsk6yf' AND OBJECT_NAME IS NOT NULL

                 UNION ALL

                 SELECT OBJECT_OWNER, OBJECT_NAME

                   FROM DBA_HIST_SQL_PLAN

                  WHERE SQL_ID = '2pfsvyamsk6yf' AND OBJECT_NAME IS NOT NULL))

SELECT a.owner,

       a.segment_name,

       a.segment_size as MB,

       TRUNC (a.segment_size / 8) block_count

  FROM (  SELECT owner, segment_name , TRUNC (SUM (bytes) / 1024/1024) segment_size

            FROM dba_segments

           WHERE   /*  segment_type LIKE 'TABLE%'

                 AND*/ (OWNER, segment_name) IN

                        (SELECT table_owner, table_name

                           FROM dba_indexes

                          WHERE (owner, index_name) IN (SELECT * FROM t)

                         UNION ALL

                         SELECT * FROM t)

        GROUP BY  (owner, segment_name)) a;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24  

OWNER			       SEGMENT_NAME										 MB BLOCK_COUNT

------------------------------ --------------------------------------------------------------------------------- ---------- -----------

DWF			       F_AGT_SAVB_ACCTINFO_H								       4121	    515

DWF			       F_PTY_TABLE										        176	     22

DWM			       B_M_SYS_BRANCH										  0	      0

DWF			       F_AGT_CADB_BOOK_H								              2739	    342

DWF			       F_AGT_CADB_BOOK_H_IDX1									152	     19

DWF			       F_AGT_CADB_ACCT										128	     16

DWF			       F_PTY_ORG										        0	      0

DWF			       F_PTY_EMP_INFO										  4	      0

DWF			       F_EVT_CADJ_JOUR									       3455	    431

9 rows selected.

创建如下索引；

SELECT xx.tran_card_no, count(1) cnt, max(xx.trans_date) max_date

    FROM DWF.F_EVT_CADJ_JOUR xx

   WHERE TRANS_DATE <= to_date('2014-03-31', 'YYYY-MM-DD')

   group by xx.tran_card_no;

SQL> create index F_EVT_CADJ_JOUR_IDX1 on F_EVT_CADJ_JOUR(tran_card_no,trans_date);

Index created.

select agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal

                      from dwf.F_AGT_SAVB_ACCTINFO_H

                     where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

                       and end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

                       and acct_status <> '1'

                       group by  agmt_id

create index F_AGT_SAVB_ACCTINFO_H_idx4 on F_AGT_SAVB_ACCTINFO_H(agmt_id,last_trans_date,acct_bal,start_dt,end_dt,acct_status);

同时去掉谓词推入:

with aa as

 (select          a.agmt_id,

         sum(c.acct_bal) as card_bal, --借记卡期末存款余额

         a.card_open_org,

         a.OPEN_DATE, -- 发卡日期

         a.CARD_NEW_STATUS, -- 卡片状态

         a.cust_magr, -- 客户经理号

         a.cust_no, -- 客户号

         a.corp_org

    from dwf.f_agt_cadb_book_h a

    left outer join (select agmt_id, acct_no

                      from dwf.f_agt_cadb_acct

                     where substr(acct_status, 8, 1) <> '2') b

      on a.master_card_no = b.agmt_id

    left outer join (select agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal

                      from dwf.F_AGT_SAVB_ACCTINFO_H

                     where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

                       and end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

                       and acct_status <> '1'

                       group by  agmt_id

                       ) c  -- 一卡多账号，某一账号睡眠其它未睡 ,卡账务信息表最后交易日期不准

      on b.acct_no = c.agmt_id

   where substr(a.host_card_status, 8, 1) = '0' --卡状态不为注销

     and a.master_card_no is not null --剔除待领卡

     and a.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD') /*参数传入 季末日期*/

     and a.end_dt > to_date('2014-03-31', 'YYYY-MM-DD') /*参数传入 季末日期*/

     and c.LAST_TRANS_DATE < to_date( '2014-03-01', 'YYYY-MM-DD') /*参数传入 季末日期*/

     and c.acct_bal is not null

   group by a.agmt_id,

            a.card_open_org,

            a.OPEN_DATE, -- 发卡日期

            a.CARD_NEW_STATUS, -- 卡片状态

            a.cust_magr, -- 客户经理号

            a.cust_no, -- 客户号

            a.corp_org

  having(sum(c.acct_bal) < 10)),

bb as

 (SELECT xx.tran_card_no, count(1) cnt, max(xx.trans_date) max_date

    FROM DWF.F_EVT_CADJ_JOUR xx

   WHERE TRANS_DATE <= to_date('2014-03-31', 'YYYY-MM-DD')

   group by xx.tran_card_no)

select /*+  use_hash(AA XX) use_hash(AA F) */   aa.agmt_id, -- 卡号

       'J' AS CARD_T, -- 卡种类

       f.pty_name, -- 客户姓名

       aa.cust_no, -- 客户证件号

       f.mobile_no, -- 客户手机号

       aa.OPEN_DATE, -- 发卡日期

       null as ACTIVEDAY, -- 激活日期

       aa.CARD_NEW_STATUS, -- 卡片状态

       to_date('2014-03-31', 'YYYY-MM-DD') - nvl(bb.MAX_DATE, aa.OPEN_DATE) as sleep_day, -- 睡眠时间

       card_bal, -- 存款余额

       null as cred_limit, -- 授信额度

       bb.cnt, -- 交易次数

       aa.cust_magr, -- 客户经理号

       xx.emp_name, -- 客户经理名称

       aa.card_open_org, -- 所属机构

       bb.MAX_DATE

  from aa

  left join bb

    on aa.agmt_id = bb.tran_card_no

  LEFT JOIN dwm.v_m_pty_emp_info xx --员工表

    ON aa.cust_magr = xx.pty_id

   AND xx.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

   AND xx.end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

  LEFT JOIN dwf.f_pty_table f --当事人主表

    ON aa.cust_no = f.pty_id

   AND aa.corp_org = f.corp_org

   AND f.start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')

   AND f.end_dt > to_date('2014-03-31', 'YYYY-MM-DD')

   where   bb.MAX_DATE < to_date( '2014-03-01', 'YYYY-MM-DD')

   and aa.card_open_org in (SELECT t.Org_Id

                           FROM b_m_Sys_Branch t

                          WHERE t.Status = 1

                            AND t.Dept_Flag != '2'

                         CONNECT BY  PRIOR t.Id = t.Parent_Id

                          START WITH t.Org_Id =10000) ;

Plan hash value: 2910718243

------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                       | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                |                            |     1 |   266 |   156K  (1)| 00:31:14 |

|*  1 |  HASH JOIN OUTER                                |                            |     1 |   266 |   156K  (1)| 00:31:14 |

|*  2 |   HASH JOIN                                     |                            |     1 |   206 |   150K  (1)| 00:30:01 |

|*  3 |    HASH JOIN OUTER                              |                            |     1 |   166 |   131K  (1)| 00:26:18 |

|*  4 |     HASH JOIN                                   |                            |     1 |   110 |   131K  (1)| 00:26:14 |

|   5 |      VIEW                                       |                            |     1 |    83 |   131K  (1)| 00:26:14 |

|*  6 |       FILTER                                    |                            |       |       |            |          |

|   7 |        HASH GROUP BY                            |                            |     1 |   211 |   131K  (1)| 00:26:14 |

|*  8 |         HASH JOIN                               |                            |    27 |  5697 |   131K  (1)| 00:26:14 |

|*  9 |          HASH JOIN                              |                            |  1764 |   161K| 37890   (1)| 00:07:35 |

|  10 |           VIEW                                  |                            |  1746 | 75078 | 33702   (1)| 00:06:45 |

|* 11 |            FILTER                               |                            |       |       |            |          |

|  12 |             HASH GROUP BY                       |                            |  1746 | 90792 | 33702   (1)| 00:06:45 |

|* 13 |              INDEX FAST FULL SCAN               | F_AGT_SAVB_ACCTINFO_H_IDX4 |  1010K|    50M| 33677   (1)| 00:06:45 |

|* 14 |           TABLE ACCESS FULL                     | F_AGT_CADB_ACCT            | 41918 |  2087K|  4188   (1)| 00:00:51 |

|* 15 |          TABLE ACCESS FULL                      | F_AGT_CADB_BOOK_H          |  5278 |   603K| 93269   (1)| 00:18:40 |

|  16 |      VIEW                                       | VW_NSO_1                   |     6 |   162 |     4  (25)| 00:00:01 |

|  17 |       HASH UNIQUE                               |                            |     6 |   294 |     4  (25)| 00:00:01 |

|* 18 |        FILTER                                   |                            |       |       |            |          |

|* 19 |         CONNECT BY NO FILTERING WITH SW (UNIQUE)|                            |       |       |            |          |

|  20 |          TABLE ACCESS FULL                      | B_M_SYS_BRANCH             |   147 |  2940 |     3   (0)| 00:00:01 |

|  21 |     VIEW                                        | V_M_PTY_EMP_INFO           |  7356 |   402K|   275   (1)| 00:00:04 |

|* 22 |      HASH JOIN OUTER                            |                            |  7356 |   366K|   275   (1)| 00:00:04 |

|* 23 |       TABLE ACCESS FULL                         | F_PTY_EMP_INFO             |  7337 |   236K|   128   (1)| 00:00:02 |

|  24 |       VIEW                                      |                            | 25885 |   455K|   148   (1)| 00:00:02 |

|* 25 |        HASH JOIN RIGHT OUTER                    |                            | 25885 |  1491K|   148   (1)| 00:00:02 |

|* 26 |         TABLE ACCESS FULL                       | B_M_SYS_BRANCH             |    23 |   322 |     3   (0)| 00:00:01 |

|* 27 |         TABLE ACCESS FULL                       | B_M_SYS_BRANCH             |     1 |    11 |     3   (0)| 00:00:01 |

|  28 |         VIEW                                    |                            | 25885 |  1137K|   145   (1)| 00:00:02 |

|* 29 |          HASH JOIN RIGHT OUTER                  |                            | 25885 |  2173K|   145   (1)| 00:00:02 |

|* 30 |           TABLE ACCESS FULL                     | B_M_SYS_BRANCH             |   109 |  1526 |     3   (0)| 00:00:01 |

|* 31 |           TABLE ACCESS FULL                     | B_M_SYS_BRANCH             |     1 |    11 |     3   (0)| 00:00:01 |

|  32 |           VIEW                                  |                            | 25885 |  1820K|   142   (1)| 00:00:02 |

|* 33 |            HASH JOIN RIGHT OUTER                |                            | 25885 |  1996K|   142   (1)| 00:00:02 |

|* 34 |             TABLE ACCESS FULL                   | B_M_SYS_BRANCH             |    12 |   168 |     3   (0)| 00:00:01 |

|* 35 |             HASH JOIN RIGHT OUTER               |                            | 25885 |  1643K|   138   (0)| 00:00:02 |

|  36 |              TABLE ACCESS FULL                  | B_M_SYS_BRANCH             |   147 |  1029 |     3   (0)| 00:00:01 |

|* 37 |              HASH JOIN RIGHT OUTER              |                            | 25885 |  1466K|   135   (0)| 00:00:02 |

|  38 |               VIEW                              | M_CBS_TO_DW_ORG            |   143 |  4862 |     8   (0)| 00:00:01 |

|  39 |                UNION-ALL                        |                            |       |       |            |          |

|* 40 |                 TABLE ACCESS FULL               | F_PTY_ORG                  |   129 |  2838 |     5   (0)| 00:00:01 |

|* 41 |                 TABLE ACCESS FULL               | B_M_SYS_BRANCH             |    14 |    98 |     3   (0)| 00:00:01 |

|* 42 |               TABLE ACCESS FULL                 | F_PTY_EMP_INFO             | 25885 |   606K|   127   (0)| 00:00:02 |

|  43 |    VIEW                                         |                            | 14370 |   561K| 18634   (3)| 00:03:44 |

|* 44 |     FILTER                                      |                            |       |       |            |          |

|  45 |      HASH GROUP BY                              |                            | 14370 |   364K| 18634   (3)| 00:03:44 |

|* 46 |       INDEX FAST FULL SCAN                      | F_EVT_CADJ_JOUR_IDX1       |    13M|   328M| 18250   (1)| 00:03:40 |

|* 47 |   TABLE ACCESS FULL                             | F_PTY_TABLE                |   589K|    33M|  6033   (1)| 00:01:13 |

------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("AA"."CORP_ORG"="F"."CORP_ORG"(+) AND "AA"."CUST_NO"="F"."PTY_ID"(+))

   2 - access("AA"."AGMT_ID"="BB"."TRAN_CARD_NO")

   3 - access("AA"."CUST_MAGR"="XX"."PTY_ID"(+))

   4 - access("AA"."CARD_OPEN_ORG"="ORG_ID")

   6 - filter(SUM("C"."ACCT_BAL")<10)

   8 - access("A"."MASTER_CARD_NO"="AGMT_ID")

   9 - access("ACCT_NO"="C"."AGMT_ID")

  11 - filter(MAX("LAST_TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SUM("ACCT_BAL") IS

              NOT NULL)

  13 - filter("END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ACCT_STATUS"<>'1' AND

              "START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  14 - filter(SUBSTR("ACCT_STATUS",8,1)<>'2')

  15 - filter("A"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              SUBSTR("A"."HOST_CARD_STATUS",8,1)='0' AND "A"."MASTER_CARD_NO" IS NOT NULL AND "A"."START_DT"<=TO_DATE(' 2014-03-31

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  18 - filter("T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')

  19 - access("T"."PARENT_ID"=PRIOR "T"."ID")

       filter(TO_NUMBER("T"."ORG_ID")=10000)

  22 - access("T"."START_DT"="B"."START_DT"(+) AND "T"."SOURCE_CODE"="B"."SOURCE_CODE"(+) AND

              "T"."PTY_ID"="B"."PTY_ID"(+))

  23 - filter("T"."END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."START_DT"<=TO_DATE('

              2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  25 - access("FST"."SEC_ORG_ID"="ORG_ID"(+))

  26 - filter("ORG_LEVEL"(+)=2)

  27 - filter("ID"=:B1)

  29 - access("ORG_ID"(+)=NVL("SEC"."THD_ORG_ID","SEC"."ORG_ID"))

  30 - filter("ORG_LEVEL"(+)=3)

  31 - filter("ID"=:B1)

  33 - access("C"."ORG_ID"="ORG_ID"(+))

  34 - filter("ORG_LEVEL"(+)=4)

  35 - access("B"."ORG_ID"="C"."ORG_ID"(+))

  37 - access("A"."ORG_NO"="B"."PTY_ID"(+))

  40 - filter("END_DT"=TO_DATE(' 2999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  41 - filter("ORG_ID" LIKE '%8888%' AND "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL OR "ORG_ID" LIKE '%9999%' AND

              "ORG_ID" IS NOT NULL AND "ORG_ID" IS NOT NULL)

  42 - filter("A"."START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  44 - filter(MAX("XX"."TRANS_DATE")<TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  46 - filter("TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  47 - filter("F"."END_DT"(+)>TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "F"."START_DT"(+)<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

最终SQL16S左右就出结果

最新文章

1. maven 配置问题
2. Guava------------Cache使用方法
3. ASP.NET 上传文件最大值调整
4. Spring security 和 AOP 学习
5. web.config配置aspx页面默认引用的namespace
6. Shell最多支持多少个参数
7. 编写C# Windows服务，用于杀死Zsd.exe进程
8. jenkins 设置权限后管理员登陆提示：Access Denied admin没有Overall/Read权限
9. 卓越网的kindle paperwhite
10. NPM与调试工具的使用
11. extjs每一个组件要设置唯一的ID
12. “无文件”恶意软件的威力：悄无声息一夜之间从ATM机中窃取80万美元
13. Get Remote Computer Install Software
14. UNION的使用方法 （表与表直接数据和在一起的示例）
15. es6 复制对象
16. BZOJ2560串珠子
17. JSP（汇聚页）
18. Node服务端极速搭建 － nvmhome
19. linux下vim命令具体解释
20. Centos网络时好时超时问题解决

热门文章

1. [MongoDB] Introduce to MongoDB
2. swift开发笔记24 解决键盘遮挡输入框 的方法
3. csv操作帮助类
4. webform repeater
5. Jquery插件-Html5图片上传并裁剪
6. div中的内容居中
7. Android开发手记(9) DatePickerDialog 和 TimePickerDialog
8. 引号 shell
9. jQuery Easy UI (适应屏幕分辨率大小)布局(Layout)
10. jquery的几种异步请求，ajax