Oracle union all 不走索引的優化

maohaiqing0304發表於2015-01-06
原文網址 : http://blog.itpub.net/28602568/viewspace-1392132/
Oracle索引優化

標題： Oracle union all 不走索引的優化

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]



註釋： 
    今天在生產環境發現如下sql跑了205s，而實際的業務查出的資料量應該極小【依據表篩選後量很小】；
    那我們看看為啥極小的量會不走索引,會這麼慢？和最終的解決方案。


環境：

AIX
7.1
 >> CPU 64 (16C)
 >> Mem 64
Oracle 11.2.0.3.0
 >>
Mem 40G

表資訊/索引欄位資訊：

TABLE_NAME
NUM_ROWS
COLUMN_NAME
NUM_DISTINCT
INDEX_NAME
ES_FW_STEP 2279 STEP_ID 2279 PK_ES_FW_STEP
ES_FW_STEP 2279 FLOW_ID 300 FLOW_STEP_FK
ES_INS_CUST_INFO 3670 CUST_INS_ID 3670 PK_ES_INS_CUST_INFO
CS_ORDER_DRAFT_APP 21605 ORDER_DRAFT_APP_ID 21605 PK_CS_ORDER_DRAFT_APP
ES_FW_FLOW_INST 137004 FLOW_INST_ID 137004 PK_ES_FW_FLOW_INST
ES_FW_FLOW_INST 137004 FLOW_ID 117 FLOW_FLOW_INST_FK
ES_FW_STEP_INST 289079 FLOW_INST_ID 137888 INST_FLOW_STEP_FK
ES_FW_STEP_INST 289079 STEP_INST_ID 289079 PK_ES_FW_STEP_INST
ES_FW_STEP_INST 289079 STEP_ID 540 STEP_INST_FK
CS_ORDER 644896 RESP_REP 275 IDX_CS_ORDER_RESP_REP
CS_ORDER 644896 SEND_EVA_INFO_ID 613888 IDX_CS_ORDER_SEND_EVA_INFO_ID
CS_ORDER 644896 UNIQ_NO 595904 IDX_CS_ORDER_UNIQ_NO
CS_ORDER 644896 BUSI_CUST_ID 10104 IDX_CS_ORDER_BUSI_CUST_ID
CS_ORDER 644896 ORDER_ID 644896 PK_CS_ORDER
ES_INS_EMP_INFO 1203674 UNIQ_NO 601408 IDX_EIEN_UNIQ_NO
ES_INS_EMP_INFO 1203674 INS_EMP_ID 1203674 PK_ES_INS_EMP_INFO
CS_ORDER_LSRDRAFT_REC 29890808 ID 29890808 PK_CS_ORDER_LSRDRAFT_REC
CS_ORDER_LSRDRAFT_REC 29890808 ORDER_ID 555264 IDX_LSRDRAFT_REC_ORDER_ID
CS_ORDER_LSRDRAFT_REC 29890808 ORDER_LOCAL_STANDARD_REL_ID 9686016 IDX_LOCAL_STANDARD_REL_ID
CS_ORDER_ITEM_DRAFT_REC 43585219 ID 43585219 PK_CS_ORDER_ITEM_DRAFT_REC
CS_ORDER_ITEM_DRAFT_REC 43585219 OI_ID 14394368 IN_CS_ORDER_ITEM_DRAFT_REC
CS_ORDER_ITEM_DRAFT_REC 43585219 ORDER_ID 583936 INDEX_DRAFT_REC_ORDER_ID
ES_INS_REC 83205901 INS_REG_CODE 2821 IDX_INS_REG_CODE
ES_INS_REC 83205901 BUSI_CUST_ID 16806 IDX_ES_REC_BUSI_CUST_ID
ES_INS_REC 83205901 CUST_INS_ID 2815 IDX_CUST_INS_ID
ES_INS_REC 83205901 INS_REC_ID 83205901 PK_ES_INS_REC
ES_INS_REC 83205901 UNIQ_NO 532288 IDX_ES_UNIQ_NO
ES_INS_REC 83205901 CRE_BAT_NO 41612 IDX_CRE_BAT_NO
ES_INS_REC 83205901 INS_EMP_ID 1059328 IDX_INS_EMP_ID
ES_INS_REC
83205901
ORDER_ID
271616
ES_INS_REC_ORDER_ID_LX

 *** 首先看看 最原始的SQL，執行計劃，執行時間，及最消耗的部分！

SQL、執行計劃、執行時間 
SQL> SELECT EI.UNIQ_NO,
  2         EI.EMP_NAME,
  3         EI.ID_CARD,
  4         EC.REG_NO,
  5         EC.REG_NO_NAME,
  6         STEP.STEP_INST_NAME,
  7         STEP.STEP_STATE,
  8         CO.ORDER_ID,
  9         CO.INS_PAY_FEES_WAY AS PAY_WAY,
 10         T.ORDER_DRAFT_APP_ID,
 11         REL.*
 12    FROM CS_ORDER_DRAFT_APP T
 13    JOIN CS_ORDER CO
 14      ON CO.ORDER_ID = T.ORDER_ID
 15    JOIN ES_INS_EMP_INFO EI
 16      ON EI.UNIQ_NO = CO.UNIQ_NO
 17     AND EI.INS_EMP_TYPE = 1
 18    JOIN ES_INS_CUST_INFO EC
 19      ON EC.CUST_INS_ID = CO.INS_CUST_INFO_ID
 20    LEFT JOIN (SELECT INST.FLOW_INST_ID,
 21                      INST.CREATE_NO AS UNIQ_NO,
 22                      INST.ORDER_ID,
 23                      STEP.STEP_INST_NAME,
 24                      STEP.STEP_STATE,
 25                      STEP.STEP_ID,
 26                      STEP.STEP_INST_ORDER,
 27                      ROW_NUMBER() OVER(PARTITION BY STEP.FLOW_INST_ID ORDER BY FW.STEP_ORDER DESC) AS ROWNO
 28                 FROM ES_FW_FLOW_INST INST,
 29                      ES_FW_STEP_INST STEP,
 30                      ES_FW_STEP      FW
 31                WHERE INST.FLOW_INST_ID = STEP.FLOW_INST_ID
 32                  AND STEP.STEP_ID = FW.STEP_ID
 33                  AND FW.IS_VALID = 1
 34                  AND INST.IS_VALID = 1
 35                  AND INST.FLOW_STATE <> 5
 36                  AND INST.FLOW_ID IN (1473, 1474)) STEP
 37      ON EI.UNIQ_NO = STEP.UNIQ_NO
 38     AND STEP.ROWNO = 1
 39    JOIN (SELECT MAX( DECODE(REC.PRODUCT_ID, 4 , REC.PAY_BASE)) AS YALBASEX,
 40                 MAX(DECODE (REC.PRODUCT_ID, 5, REC.PAY_BASE)) AS SHYEBASEX,
 41                 MAX(DECODE (REC.PRODUCT_ID, 6, REC.PAY_BASE)) AS GOSBASEX,
 42                 MAX(DECODE (REC.PRODUCT_ID, 7, REC.PAY_BASE)) AS SHYUBASEX,
 43                 MAX(DECODE (REC.PRODUCT_ID, 8, REC.PAY_BASE)) AS YILBASEX,
 44                 MAX(DECODE (OI.PROD_ID, 4, REL.BASE_APPLY)) AS YALBASEZ,
 45                 MAX(DECODE (OI.PROD_ID, 5, REL.BASE_APPLY)) AS SHYEBASEZ,
 46                 MAX(DECODE (OI.PROD_ID, 6, REL.BASE_APPLY)) AS GOSBASEZ,
 47                 MAX(DECODE (OI.PROD_ID, 7, REL.BASE_APPLY)) AS SHYUBASEZ,
 48                 MAX(DECODE (OI.PROD_ID, 8, REL.BASE_APPLY)) AS YILBASEZ,
 49                 REC.ORDER_ID
 50            FROM CS_ORDER_ITEM_DRAFT_REC OI
 51            JOIN ES_INS_REC REC
 52              ON REC.ORDER_ID = OI.ORDER_ID
 53             AND REC.FAILURE_STATE = 1
 54             AND REC.INS_STATE IN (1, 3)
 55             AND REC.EXECUTE_MON = '201412'
 56            LEFT JOIN CS_ORDER_LSRDRAFT_REC REL
 57              ON OI.CS_ORDER_LSRDRAFT_ID = REL.ORDER_LOCAL_STANDARD_REL_ID
 58             AND REL.BAT_NUM = OI.BAT_NUM
 59           WHERE OI.OI_SOURCE IN (1, 3)
 60             AND OI.BAT_NUM = (SELECT MAX(R.BAT_NO)
 61                                 FROM CS_ORDER_OPR_REC R
 62                                WHERE R.ORDER_ID = OI.ORDER_ID
 63                                  AND R.OPR_TYPE = 1 )
 64             AND OI.PARENT_ID IS NULL
 65             AND OI.PROD_ID IN (4, 5, 6 , 7, 8)
 66             AND (OI.END_DATE IS NULL OR OI.END_DATE >= SYSDATE )
 67           GROUP BY REC.UNIQ_NO, REC.ORDER_ID
 68          UNION all
 69          SELECT MAX (DECODE(R.PRODUCT_ID, 4, R.PAY_BASE)) AS YALBASEX,
 70                 MAX(DECODE (R.PRODUCT_ID, 5, R.PAY_BASE)) AS SHYEBASEX,
 71                 MAX(DECODE (R.PRODUCT_ID, 6, R.PAY_BASE)) AS GOSBASEX,
 72                 MAX(DECODE (R.PRODUCT_ID, 7, R.PAY_BASE)) AS SHYUBASEX,
 73                 MAX(DECODE (R.PRODUCT_ID, 8, R.PAY_BASE)) AS YILBASEX,
 74                 MAX(DECODE (REC.PRODUCT_ID, 4, REC.PAY_BASE)) AS YALBASEZ,
 75                 MAX(DECODE (REC.PRODUCT_ID, 5, REC.PAY_BASE)) AS SHYEBASEZ,
 76                 MAX(DECODE (REC.PRODUCT_ID, 6, REC.PAY_BASE)) AS GOSBASEZ,
 77                 MAX(DECODE (REC.PRODUCT_ID, 7, REC.PAY_BASE)) AS SHYUBASEZ,
 78                 MAX(DECODE (REC.PRODUCT_ID, 8, REC.PAY_BASE)) AS YILBASEZ,
 79                 REC.ORDER_ID
 80            FROM ES_INS_REC REC
 81            JOIN ES_INS_REC R
 82              ON REC.CONFL_SOURCE_ID = R.INS_REC_ID
 83           WHERE R.INS_STATE = 3
 84             AND REC.INS_STATE = 1
 85             AND REC.FAILURE_STATE = 1
 86           GROUP BY REC.UNIQ_NO, REC.NAT_MON, REC.ORDER_ID) REL
 87      ON T.ORDER_ID = REL.ORDER_ID
 88   WHERE T.APP_TYPE IN (2, 4)
 89     AND T.APP_STATE IN (1, 2)
 90     AND CO.RESP_REP IN (3804274, 3822522, 3852263 , 3804273)
 91     AND EI.UNIQ_NO = '65736' ;

no rows selected

Elapsed: 00: 03:25.88

Execution Plan
----------------------------------------------------------
Plan hash value: 3126206360

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |     1 |   496 |       |  2110K  (2 )| 07: 02:03 |       |       |
|   1 |  NESTED LOOPS                           |                           |       |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                          |                           |     1 |   496 |       |  2110K  (2 )| 07: 02:03 |       |       |
|*  3 |    HASH JOIN OUTER                      |                           |     1 |   432 |       |  2110K  (2 )| 07: 02:03 |       |       |
|   4 |     MERGE JOIN CARTESIAN                |                           |     1 |   222 |       |  2110K  (2 )| 07: 02:02 |       |       |
|   5 |      NESTED LOOPS                       |                           |       |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                      |                           |     1 |   185 |       |  2110K  (2 )| 07: 02:02 |       |       |
|*  7 |        HASH JOIN                         |                           |     1 |   160 |       |  2110K  (2 )| 07: 02:02 |       |       |
|*  8 |         TABLE ACCESS FULL               | CS_ORDER_DRAFT_APP        |    29 |   493 |       |    36   (3 )| 00: 00:01 |       |       |
|   9 |         VIEW                            |                           | 37970 |  5302 K|       |  2110K  (2 )| 07: 02:02 |       |       |
|  10 |          UNION-ALL                       |                           |       |       |       |            |          |       |       |
|  11 |           HASH GROUP BY                 |                           | 33380 |  3031 K|  3720K|   430K  (2 )| 01: 26:05 |       |       |
|* 12 |            HASH JOIN                     |                           | 33380 |  3031 K|       |   429K  (2 )| 01: 25:57 |       |       |
|  13 |             NESTED LOOPS OUTER           |                           | 16003 |   968 K|       |   411K  (2 )| 01: 22:24 |       |       |
|* 14 |              HASH JOIN                   |                           | 16003 |   750 K|  3000K|   348K  (3 )| 01: 09:45 |       |       |
|  15 |               VIEW                      | VW_SQ_1                   | 98936 |  1835 K|       |  3316   (3 )| 00: 00:40 |       |       |
|  16 |                HASH GROUP BY            |                           | 98936 |  1159 K|  2488K|  3316   (3 )| 00: 00:40 |       |       |
|* 17 |                 TABLE ACCESS FULL       | CS_ORDER_OPR_REC          |   105K|  1232 K|       |  2845   (3 )| 00: 00:35 |       |       |
|* 18 |               TABLE ACCESS FULL         | CS_ORDER_ITEM_DRAFT_REC   |  4910 K|   135M|       |   335K  (3 )| 01:07 :08 |       |       |
|* 19 |              TABLE ACCESS BY INDEX ROWID | CS_ORDER_LSRDRAFT_REC     |     1 |    14 |       |     6   (0 )| 00: 00:01 |       |       |
|* 20 |               INDEX RANGE SCAN          | IDX_LOCAL_STANDARD_REL_ID |     3 |       |       |     2   (0 )| 00:00 :01 |       |       |
|  21 |             PARTITION RANGE SINGLE      |                           |  1219K|    36 M|       | 17740   (2 )| 00: 03:33 |    13 |    13 |
|* 22 |              TABLE ACCESS FULL          | ES_INS_REC                |  1219K|    36 M|       | 17740   (2 )| 00: 03:33 |    13 |    13 |
|  23 |           HASH GROUP BY                 |                           |  4590 |   215 K|       |  1679K  (2 )| 05: 35:57 |       |       |
|* 24 |            HASH JOIN                     |                           |  4590 |   215 K|    96M|  1679K  (2 )| 05: 35:57 |       |       |
|  25 |             PARTITION RANGE ALL         |                           |  2357K|    69 M|       |   786K  (2 )| 02: 37:14 |     1 |    25 |
|* 26 |              TABLE ACCESS FULL          | ES_INS_REC                |  2357K|    69 M|       |   786K  (2 )| 02: 37:14 |     1 |    25 |
|  27 |             PARTITION RANGE ALL         |                           |    75M|  1229 M|       |   782K  (1 )| 02: 36:35 |     1 |    25 |
|* 28 |              TABLE ACCESS FULL          | ES_INS_REC                |    75M|  1229 M|       |   782K  (1 )| 02: 36:35 |     1 |    25 |
|* 29 |        INDEX UNIQUE SCAN                | PK_CS_ORDER               |     1 |       |       |     1   (0 )| 00: 00:01 |       |       |
|* 30 |       TABLE ACCESS BY INDEX ROWID        | CS_ORDER                  |     1 |    25 |       |     2   (0 )| 00: 00:01 |       |       |
|  31 |      BUFFER SORT                         |                           |     1 |    37 |       |  2110K  (2 )| 07: 02:02 |       |       |
|* 32 |       TABLE ACCESS BY INDEX ROWID        | ES_INS_EMP_INFO           |     1 |    37 |       |     4   (0 )| 00: 00:01 |       |       |
|* 33 |        INDEX RANGE SCAN                 | IDX_EIEN_UNIQ_NO          |     2 |       |       |     2   (0 )| 00: 00:01 |       |       |
|* 34 |     VIEW                                |                           |     8 |  1680 |       |    30   (4 )| 00: 00:01 |       |       |
|* 35 |      WINDOW SORT PUSHED RANK             |                           |     8 |   472 |       |    30   (4 )| 00: 00:01 |       |       |
|  36 |       NESTED LOOPS                      |                           |       |       |       |            |          |       |       |
|  37 |        NESTED LOOPS                     |                           |     8 |   472 |       |    29   (0 )| 00: 00:01 |       |       |
|  38 |         NESTED LOOPS                    |                           |     8 |   392 |       |    21   (0 )| 00: 00:01 |       |       |
|  39 |          INLIST ITERATOR                |                           |       |       |       |            |          |       |       |
|* 40 |           TABLE ACCESS BY INDEX ROWID    | ES_FW_FLOW_INST           |     4 |    84 |       |     5   (0 )| 00: 00:01 |       |       |
|* 41 |            INDEX RANGE SCAN             | FLOW_FLOW_INST_FK         |    13 |       |       |     3   (0 )| 00: 00:01 |       |       |
|  42 |          TABLE ACCESS BY INDEX ROWID     | ES_FW_STEP_INST           |     2 |    56 |       |     4   (0 )| 00: 00:01 |       |       |
|* 43 |           INDEX RANGE SCAN              | INST_FLOW_STEP_FK         |     2 |       |       |     2   (0 )| 00: 00:01 |       |       |
|* 44 |         INDEX UNIQUE SCAN               | PK_ES_FW_STEP             |     1 |       |       |     0   (0 )| 00: 00:01 |       |       |
|* 45 |        TABLE ACCESS BY INDEX ROWID       | ES_FW_STEP                |     1 |    10 |       |     1   (0 )| 00: 00:01 |       |       |
|* 46 |    INDEX UNIQUE SCAN                    | PK_ES_INS_CUST_INFO       |     1 |       |       |     0   (0 )| 00: 00:01 |       |       |
|  47 |   TABLE ACCESS BY INDEX ROWID            | ES_INS_CUST_INFO          |     1 |    64 |       |     1   ( 0)| 00 :00: 01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information ( identified by operation id):
---------------------------------------------------

   3 - access("EI"."UNIQ_NO"="STEP"."UNIQ_NO"(+))
   7 - access("T"."ORDER_ID"="REL"."ORDER_ID")
   8 - filter(("T"."APP_TYPE"=2 OR "T"."APP_TYPE"=4 ) AND ("T"."APP_STATE"=1 OR "T"."APP_STATE"= 2))
  12 - access("REC"."ORDER_ID"="OI"."ORDER_ID")
  14 - access("OI"."BAT_NUM"="MAX(R.BAT_NO)" AND "ITEM_1"="OI"."ORDER_ID")
  17 - filter("R"."OPR_TYPE"=1 )
  18 - filter("OI"."PARENT_ID" IS NULL AND ("OI"."OI_SOURCE"= 1 OR "OI"."OI_SOURCE"=3 ) AND ("OI"."END_DATE" IS NULL OR
              "OI"."END_DATE">= SYSDATE@!) AND ("OI"."PROD_ID"= 4 OR "OI"."PROD_ID"=5 OR "OI"."PROD_ID"= 6 OR "OI"."PROD_ID"=7 OR "OI"."PROD_ID"=8 ))
  19 - filter("REL"."BAT_NUM"(+)="OI"."BAT_NUM")
  20 - access("OI"."CS_ORDER_LSRDRAFT_ID"="REL"."ORDER_LOCAL_STANDARD_REL_ID"(+))
  22 - filter("REC"."FAILURE_STATE"=1 AND ("REC"."INS_STATE"=1 OR "REC"."INS_STATE"=3 ) AND "REC"."EXECUTE_MON"= 201412)
  24 - access("REC"."CONFL_SOURCE_ID"="R"."INS_REC_ID")
  26 - filter("REC"."INS_STATE"=1 AND "REC"."FAILURE_STATE"=1)
  28 - filter("R"."INS_STATE"=3)
  29 - access("CO"."ORDER_ID"="T"."ORDER_ID")
  30 - filter("CO"."UNIQ_NO"=65736 AND "CO"."INS_CUST_INFO_ID" IS NOT NULL AND ("CO"."RESP_REP"= 3804273 OR "CO"."RESP_REP"= 3804274
              OR "CO"."RESP_REP"=3822522 OR "CO"."RESP_REP"=3852263))
  32 - filter("EI"."INS_EMP_TYPE"=1)
  33 - access("EI"."UNIQ_NO"=65736 )
  34 - filter("STEP"."ROWNO"(+)=1 AND "STEP"."UNIQ_NO"(+)=65736)
  35 - filter(ROW_NUMBER() OVER ( PARTITION BY "STEP"."FLOW_INST_ID" ORDER BY INTERNAL_FUNCTION("FW"."STEP_ORDER") DESC )<=1 )
  40 - filter("INST"."FLOW_STATE"<>5 AND "INST"."IS_VALID"=1)
  41 - access("INST"."FLOW_ID"=1473 OR "INST"."FLOW_ID"=1474)
  43 - access("INST"."FLOW_INST_ID"="STEP"."FLOW_INST_ID")
  44 - access("STEP"."STEP_ID"="FW"."STEP_ID")
  45 - filter("FW"."IS_VALID"=1)
  46 - access("EC"."CUST_INS_ID"="CO"."INS_CUST_INFO_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          9  db block gets
    7334176  consistent gets
    7027949  physical reads
        144  redo size
       1756  bytes sent via SQL *Net to client
        513  bytes received via SQL *Net from client
          1  SQL *Net roundtrips to/ from client
          0  sorts (memory )
          0  sorts (disk )
          0  rows processed

SQL> 


分析：
 從如上看出最消耗部分是ES_INS_REC表 NUM_ROWS=83205901，總欄位數=90，segment大小=22G
 而實際上（select * from  CS_ORDER_DRAFT_APP T where T.APP_TYPE IN (2, 4) AND T.APP_STATE IN (1, 2))SQL① 篩選後資料應該在20條左右；
 且 SQL① 的order_id欄位和ES_INS_REC.order_id 是關聯條件[見SQL的ON T.ORDER_ID = REL.ORDER_ID部分]；
 所以應該走索引才對或者理解為啥沒有將幾個order_id傳入es_ins_rec表呢； 
 
 從執行計劃中可以看出 SQL執行順序
 1、CS_ORDER_DRAFT_APP T 表
 2、SQL結果集REL別名內層部分
 3、CS_ORDER 部分
 4、SQL結果集STEP別名內層部分

 而我們想要的順序是：
  除ES_INS_REC表關聯後剩下極少資料再通過ES_INS_REC.order_id和ES_INS_REC關聯
 
 那麼是不是 因為執行計劃的順序 在第二步就取【REL別名SQL結果集】，並沒有衡量出order_id後的量很小，引起的es_ins_rec表沒有選擇索引呢？
 如下去掉union all結果集2個sql的任意一個sql，看下執行計劃和效率...


 

SQL、執行計劃、執行時間
SQL> SELECT EI.UNIQ_NO,
  2         EI.EMP_NAME,
  3         EI.ID_CARD,
  4         EC.REG_NO,
  5         EC.REG_NO_NAME,
  6         STEP.STEP_INST_NAME,
  7         STEP.STEP_STATE,
  8         CO.ORDER_ID,
  9         CO.INS_PAY_FEES_WAY AS PAY_WAY,
 10         T.ORDER_DRAFT_APP_ID,
 11         REL.*
 12    FROM CS_ORDER_DRAFT_APP T
 13    JOIN CS_ORDER CO
 14      ON CO.ORDER_ID = T.ORDER_ID
 15    JOIN ES_INS_EMP_INFO EI
 16      ON EI.UNIQ_NO = CO.UNIQ_NO
 17     AND EI.INS_EMP_TYPE = 1
 18    JOIN ES_INS_CUST_INFO EC
 19      ON EC.CUST_INS_ID = CO.INS_CUST_INFO_ID
 20    LEFT JOIN (SELECT INST.FLOW_INST_ID,
 21                      INST.CREATE_NO AS UNIQ_NO,
 22                      INST.ORDER_ID,
 23                      STEP.STEP_INST_NAME,
 24                      STEP.STEP_STATE,
 25                      STEP.STEP_ID,
 26                      STEP.STEP_INST_ORDER,
 27                      ROW_NUMBER() OVER(PARTITION BY STEP.FLOW_INST_ID ORDER BY FW.STEP_ORDER DESC) AS ROWNO
 28                 FROM ES_FW_FLOW_INST INST,
 29                      ES_FW_STEP_INST STEP,
 30                      ES_FW_STEP      FW
 31                WHERE INST.FLOW_INST_ID = STEP.FLOW_INST_ID
 32                  AND STEP.STEP_ID = FW.STEP_ID
 33                  AND FW.IS_VALID = 1
 34                  AND INST.IS_VALID = 1
 35                  AND INST.FLOW_STATE <> 5
 36                  AND INST.FLOW_ID IN (1473, 1474)) STEP
 37      ON EI.UNIQ_NO = STEP.UNIQ_NO
 38     AND STEP.ROWNO = 1
 39    JOIN (SELECT MAX( DECODE(REC.PRODUCT_ID, 4 , REC.PAY_BASE)) AS YALBASEX,
 40                 MAX(DECODE (REC.PRODUCT_ID, 5, REC.PAY_BASE)) AS SHYEBASEX,
 41                 MAX(DECODE (REC.PRODUCT_ID, 6, REC.PAY_BASE)) AS GOSBASEX,
 42                 MAX(DECODE (REC.PRODUCT_ID, 7, REC.PAY_BASE)) AS SHYUBASEX,
 43                 MAX(DECODE (REC.PRODUCT_ID, 8, REC.PAY_BASE)) AS YILBASEX,
 44                 MAX(DECODE (OI.PROD_ID, 4, REL.BASE_APPLY)) AS YALBASEZ,
 45                 MAX(DECODE (OI.PROD_ID, 5, REL.BASE_APPLY)) AS SHYEBASEZ,
 46                 MAX(DECODE (OI.PROD_ID, 6, REL.BASE_APPLY)) AS GOSBASEZ,
 47                 MAX(DECODE (OI.PROD_ID, 7, REL.BASE_APPLY)) AS SHYUBASEZ,
 48                 MAX(DECODE (OI.PROD_ID, 8, REL.BASE_APPLY)) AS YILBASEZ,
 49                 REC.ORDER_ID
 50            FROM CS_ORDER_ITEM_DRAFT_REC OI
 51            JOIN ES_INS_REC REC
 52              ON REC.ORDER_ID = OI.ORDER_ID
 53             AND REC.FAILURE_STATE = 1
 54             AND REC.INS_STATE IN (1, 3)
 55             AND REC.EXECUTE_MON = '201412'
 56            LEFT JOIN CS_ORDER_LSRDRAFT_REC REL
 57              ON OI.CS_ORDER_LSRDRAFT_ID = REL.ORDER_LOCAL_STANDARD_REL_ID
 58             AND REL.BAT_NUM = OI.BAT_NUM
 59           WHERE OI.OI_SOURCE IN (1, 3)
 60             AND OI.BAT_NUM = (SELECT MAX(R.BAT_NO)
 61                                 FROM CS_ORDER_OPR_REC R
 62                                WHERE R.ORDER_ID = OI.ORDER_ID
 63                                  AND R.OPR_TYPE = 1 )
 64             AND OI.PARENT_ID IS NULL
 65             AND OI.PROD_ID IN (4, 5, 6 , 7, 8)
 66             AND (OI.END_DATE IS NULL OR OI.END_DATE >= SYSDATE )
 67           GROUP BY REC.UNIQ_NO, REC.ORDER_ID
 68          /* UNION all
 69             SELECT MAX(DECODE(R.PRODUCT_ID, 4, R.PAY_BASE)) AS YALBASEX,
 70                    MAX(DECODE(R.PRODUCT_ID, 5, R.PAY_BASE)) AS SHYEBASEX,
 71                    MAX(DECODE(R.PRODUCT_ID, 6, R.PAY_BASE)) AS GOSBASEX,
 72                    MAX(DECODE(R.PRODUCT_ID, 7, R.PAY_BASE)) AS SHYUBASEX,
 73                    MAX(DECODE(R.PRODUCT_ID, 8, R.PAY_BASE)) AS YILBASEX,
 74                    MAX(DECODE(REC.PRODUCT_ID, 4, REC.PAY_BASE)) AS YALBASEZ,
 75                    MAX(DECODE(REC.PRODUCT_ID, 5, REC.PAY_BASE)) AS SHYEBASEZ,
 76                    MAX(DECODE(REC.PRODUCT_ID, 6, REC.PAY_BASE)) AS GOSBASEZ,
 77                    MAX(DECODE(REC.PRODUCT_ID, 7, REC.PAY_BASE)) AS SHYUBASEZ,
 78                    MAX(DECODE(REC.PRODUCT_ID, 8, REC.PAY_BASE)) AS YILBASEZ,
 79                    REC.ORDER_ID
 80               FROM ES_INS_REC REC
 81               JOIN ES_INS_REC R
 82                 ON REC.CONFL_SOURCE_ID = R.INS_REC_ID
 83              WHERE
 84              R.INS_STATE = 3
 85          AND REC.INS_STATE = 1
 86          AND REC.FAILURE_STATE = 1
 87              GROUP BY REC.UNIQ_NO, REC.NAT_MON, REC.ORDER_ID */
 88          ) REL
 89      ON T.ORDER_ID = REL.ORDER_ID
 90   WHERE T.APP_TYPE IN (2, 4)
 91     AND T.APP_STATE IN (1, 2)
 92     AND CO.RESP_REP IN (3804274, 3822522, 3852263 , 3804273)
 93     AND EI.UNIQ_NO = '65736' ;

no rows selected

Elapsed: 00: 00:00.17

Execution Plan
----------------------------------------------------------
Plan hash value: 2458233114

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |     1 |   496 |    99   (5 )| 00: 00:02 |       |       |
|   1 |  NESTED LOOPS                           |                           |     1 |   496 |    99   (5 )| 00: 00:02 |       |       |
|*  2 |   HASH JOIN                              |                           |     1 |   353 |    77   (4 )| 00: 00:01 |       |       |
|*  3 |    HASH JOIN OUTER                      |                           |     1 |   336 |    41   (5 )| 00: 00:01 |       |       |
|   4 |     MERGE JOIN CARTESIAN                |                           |     1 |   126 |    10   (0 )| 00: 00:01 |       |       |
|   5 |      NESTED LOOPS                       |                           |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                      |                           |     1 |    89 |     6   (0 )| 00: 00:01 |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID       | CS_ORDER                  |     1 |    25 |     5   (0 )| 00: 00:01 |       |       |
|*  8 |         INDEX RANGE SCAN                | IDX_CS_ORDER_UNIQ_NO      |     1 |       |     3   (0 )| 00: 00:01 |       |       |
|*  9 |        INDEX UNIQUE SCAN                | PK_ES_INS_CUST_INFO       |     1 |       |     0   (0 )| 00: 00:01 |       |       |
|  10 |       TABLE ACCESS BY INDEX ROWID        | ES_INS_CUST_INFO          |     1 |    64 |     1   (0 )| 00: 00:01 |       |       |
|  11 |      BUFFER SORT                         |                           |     1 |    37 |     9   (0 )| 00: 00:01 |       |       |
|* 12 |       TABLE ACCESS BY INDEX ROWID        | ES_INS_EMP_INFO           |     1 |    37 |     4   (0 )| 00: 00:01 |       |       |
|* 13 |        INDEX RANGE SCAN                 | IDX_EIEN_UNIQ_NO          |     2 |       |     2   (0 )| 00: 00:01 |       |       |
|* 14 |     VIEW                                |                           |     8 |  1680 |    30   (4 )| 00: 00:01 |       |       |
|* 15 |      WINDOW SORT PUSHED RANK             |                           |     8 |   472 |    30   (4 )| 00: 00:01 |       |       |
|  16 |       NESTED LOOPS                      |                           |       |       |            |          |       |       |
|  17 |        NESTED LOOPS                     |                           |     8 |   472 |    29   (0 )| 00: 00:01 |       |       |
|  18 |         NESTED LOOPS                    |                           |     8 |   392 |    21   (0 )| 00: 00:01 |       |       |
|  19 |          INLIST ITERATOR                |                           |       |       |            |          |       |       |
|* 20 |           TABLE ACCESS BY INDEX ROWID    | ES_FW_FLOW_INST           |     4 |    84 |     5   (0 )| 00: 00:01 |       |       |
|* 21 |            INDEX RANGE SCAN             | FLOW_FLOW_INST_FK         |    13 |       |     3   ( 0)| 00 :00: 01 |       |       |
|  22 |          TABLE ACCESS BY INDEX ROWID     | ES_FW_STEP_INST           |     2 |    56 |     4   (0 )| 00: 00:01 |       |       |
|* 23 |           INDEX RANGE SCAN              | INST_FLOW_STEP_FK         |     2 |       |     2   (0 )| 00: 00:01 |       |       |
|* 24 |         INDEX UNIQUE SCAN               | PK_ES_FW_STEP             |     1 |       |     0   (0 )| 00: 00:01 |       |       |
|* 25 |        TABLE ACCESS BY INDEX ROWID       | ES_FW_STEP                |     1 |    10 |     1   (0 )| 00: 00:01 |       |       |
|* 26 |    TABLE ACCESS FULL                    | CS_ORDER_DRAFT_APP        |    29 |   493 |    36   ( 3)| 00 :00: 01 |       |       |
|  27 |   VIEW PUSHED PREDICATE                 |                           |     1 |   143 |    22   (5 )| 00: 00:01 |       |       |
|  28 |    SORT GROUP BY                        |                           |     1 |    93 |    22   (5 )| 00: 00:01 |       |       |
|  29 |     NESTED LOOPS OUTER                   |                           |     1 |    93 |    21   (0 )| 00: 00:01 |       |       |
|  30 |      NESTED LOOPS                       |                           |     1 |    79 |    15   (0 )| 00: 00:01 |       |       |
|  31 |       NESTED LOOPS                      |                           |     1 |    48 |    12   (0 )| 00: 00:01 |       |       |
|  32 |        VIEW                             | VW_SQ_1                   |     1 |    19 |     7   (0 )| 00: 00:01 |       |       |
|  33 |         SORT GROUP BY                   |                           |     1 |    12 |     7   (0 )| 00: 00:01 |       |       |
|* 34 |          TABLE ACCESS BY INDEX ROWID     | CS_ORDER_OPR_REC          |     1 |    12 |     7   (0 )| 00: 00:01 |       |       |
|* 35 |           INDEX RANGE SCAN              | IDX_OPR_REC_ORDER_ID      |     3 |       |     3   (0 )| 00: 00:01 |       |       |
|* 36 |        TABLE ACCESS BY INDEX ROWID       | CS_ORDER_ITEM_DRAFT_REC   |     1 |    29 |     5   (0 )| 00: 00:01 |       |       |
|* 37 |         INDEX RANGE SCAN                | INDEX_DRAFT_REC_ORDER_ID  |     8 |       |     2   (0 )| 00: 00:01 |       |       |
|  38 |       PARTITION RANGE SINGLE            |                           |     1 |    31 |     3   (0 )| 00: 00:01 |    13 |    13 |
|* 39 |        TABLE ACCESS BY LOCAL INDEX ROWID| ES_INS_REC                |     1 |    31 |     3   (0 )| 00: 00:01 |    13 |    13 |
|* 40 |         INDEX RANGE SCAN                | ES_INS_REC_ORDER_ID_LX    |     1 |       |     2   (0 )| 00: 00:01 |    13 |    13 |
|* 41 |      TABLE ACCESS BY INDEX ROWID         | CS_ORDER_LSRDRAFT_REC     |     1 |    14 |     6   (0 )| 00: 00:01 |       |       |
|* 42 |       INDEX RANGE SCAN                  | IDX_LOCAL_STANDARD_REL_ID |     3 |       |     2   (0 )| 00: 00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information ( identified by operation id):
---------------------------------------------------

   2 - access("CO"."ORDER_ID"="T"."ORDER_ID")
   3 - access("EI"."UNIQ_NO"="STEP"."UNIQ_NO"(+))
   7 - filter("CO"."INS_CUST_INFO_ID" IS NOT NULL AND ("CO"."RESP_REP"=3804273 OR "CO"."RESP_REP"= 3804274 OR
              "CO"."RESP_REP"= 3822522 OR "CO"."RESP_REP"= 3852263))
   8 - access("CO"."UNIQ_NO"=65736 )
   9 - access("EC"."CUST_INS_ID"="CO"."INS_CUST_INFO_ID")
  12 - filter("EI"."INS_EMP_TYPE"=1)
  13 - access("EI"."UNIQ_NO"=65736 )
  14 - filter("STEP"."ROWNO"(+)=1 AND "STEP"."UNIQ_NO"(+)=65736)
  15 - filter(ROW_NUMBER() OVER ( PARTITION BY "STEP"."FLOW_INST_ID" ORDER BY INTERNAL_FUNCTION("FW"."STEP_ORDER") DESC )<=1 )
  20 - filter("INST"."FLOW_STATE"<>5 AND "INST"."IS_VALID"=1)
  21 - access("INST"."FLOW_ID"=1473 OR "INST"."FLOW_ID"=1474)
  23 - access("INST"."FLOW_INST_ID"="STEP"."FLOW_INST_ID")
  24 - access("STEP"."STEP_ID"="FW"."STEP_ID")
  25 - filter("FW"."IS_VALID"=1)
  26 - filter(("T"."APP_TYPE"=2 OR "T"."APP_TYPE"=4 ) AND ("T"."APP_STATE"=1 OR "T"."APP_STATE"= 2))
  34 - filter("R"."OPR_TYPE"=1 )
  35 - access("R"."ORDER_ID"="T"."ORDER_ID")
  36 - filter("OI"."PARENT_ID" IS NULL AND ("OI"."OI_SOURCE"= 1 OR "OI"."OI_SOURCE"=3 ) AND ("OI"."END_DATE" IS NULL OR
              "OI"."END_DATE">= SYSDATE@!) AND ("OI"."PROD_ID"= 4 OR "OI"."PROD_ID"=5 OR "OI"."PROD_ID"= 6 OR "OI"."PROD_ID"=7 OR
              "OI"."PROD_ID"= 8) AND "OI"."BAT_NUM"="MAX(R.BAT_NO)")
  37 - access("OI"."ORDER_ID"="T"."ORDER_ID")
       filter("ITEM_1"="OI"."ORDER_ID")
  39 - filter("REC"."FAILURE_STATE"=1 AND ("REC"."INS_STATE"=1 OR "REC"."INS_STATE"=3 ) AND "REC"."EXECUTE_MON"= 201412)
  40 - access("REC"."ORDER_ID"="T"."ORDER_ID")
       filter("REC"."ORDER_ID"="OI"."ORDER_ID")
  41 - filter("REL"."BAT_NUM"(+)="OI"."BAT_NUM")
  42 - access("OI"."CS_ORDER_LSRDRAFT_ID"="REL"."ORDER_LOCAL_STANDARD_REL_ID"(+))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        187  consistent gets
          1  physical reads
          0  redo size
       1756  bytes sent via SQL *Net to client
        513  bytes received via SQL *Net from client
          1  SQL *Net roundtrips to/ from client
          2  sorts (memory )
          0  sorts (disk )
          0  rows processed

SQL>

 
  

 結論:
 
 如上SQL結果：正是我們想要的執行順序！ 【除ES_INS_REC表關聯後剩下極少資料再通過ES_INS_REC.order_id和ES_INS_REC關聯】

  那麼這種情況我們針對union all怎麼優化呢？
  方案1、union all 2部分表都加上與CS_ORDER_OPR_REC做關聯，告知oracle，篩選後的資料量極小.用索引關聯取結果.
        由於改動小，且表關聯次數比方案2要少，所以開發採用的方案1  [詳情見如下SQL]
  方案2、拆成2個大SQL，都分別和在將2個大結果集union all  (簡單例子 取得是別名，不是表名)
       （例如：select..T..left join STEP left join ‘union all的前結果集’ where...
              union all
              select..T..left join STEP left join ‘union all的後結果集’ where...;） [在此不做實驗了]




方案1的SQL、執行計劃、執行時間 
SQL> SELECT EI.UNIQ_NO,
  2         EI.EMP_NAME,
  3         EI.ID_CARD,
  4         EC.REG_NO,
  5         EC.REG_NO_NAME,
  6         STEP.STEP_INST_NAME,
  7         STEP.STEP_STATE,
  8         CO.ORDER_ID,
  9         CO.INS_PAY_FEES_WAY AS PAY_WAY,
 10         T.ORDER_DRAFT_APP_ID,
 11         REL.*
 12    FROM CS_ORDER_DRAFT_APP T
 13    JOIN CS_ORDER CO
 14      ON CO.ORDER_ID = T.ORDER_ID
 15    JOIN ES_INS_EMP_INFO EI
 16      ON EI.UNIQ_NO = CO.UNIQ_NO
 17     AND EI.INS_EMP_TYPE = 1
 18    JOIN ES_INS_CUST_INFO EC
 19      ON EC.CUST_INS_ID = CO.INS_CUST_INFO_ID
 20    LEFT JOIN (SELECT INST.FLOW_INST_ID,
 21                      INST.CREATE_NO AS UNIQ_NO,
 22                      INST.ORDER_ID,
 23                      STEP.STEP_INST_NAME,
 24                      STEP.STEP_STATE,
 25                      STEP.STEP_ID,
 26                      STEP.STEP_INST_ORDER,
 27                      ROW_NUMBER() OVER(PARTITION BY STEP.FLOW_INST_ID ORDER BY FW.STEP_ORDER DESC) AS ROWNO
 28                 FROM ES_FW_FLOW_INST INST,
 29                      ES_FW_STEP_INST STEP,
 30                      ES_FW_STEP      FW
 31                WHERE INST.FLOW_INST_ID = STEP.FLOW_INST_ID
 32                  AND STEP.STEP_ID = FW.STEP_ID
 33                  AND FW.IS_VALID = 1
 34                  AND INST.IS_VALID = 1
 35                  AND INST.FLOW_STATE <> 5
 36                  AND INST.FLOW_ID IN (1473, 1474)) STEP
 37      ON EI.UNIQ_NO = STEP.UNIQ_NO
 38     AND STEP.ROWNO = 1
 39    JOIN (SELECT MAX( DECODE(REC.PRODUCT_ID, 4 , REC.PAY_BASE)) AS YALBASEX,
 40                 MAX(DECODE (REC.PRODUCT_ID, 5, REC.PAY_BASE)) AS SHYEBASEX,
 41                 MAX(DECODE (REC.PRODUCT_ID, 6, REC.PAY_BASE)) AS GOSBASEX,
 42                 MAX(DECODE (REC.PRODUCT_ID, 7, REC.PAY_BASE)) AS SHYUBASEX,
 43                 MAX(DECODE (REC.PRODUCT_ID, 8, REC.PAY_BASE)) AS YILBASEX,
 44                 MAX(DECODE (OI.PROD_ID, 4, REL.BASE_APPLY)) AS YALBASEZ,
 45                 MAX(DECODE (OI.PROD_ID, 5, REL.BASE_APPLY)) AS SHYEBASEZ,
 46                 MAX(DECODE (OI.PROD_ID, 6, REL.BASE_APPLY)) AS GOSBASEZ,
 47                 MAX(DECODE (OI.PROD_ID, 7, REL.BASE_APPLY)) AS SHYUBASEZ,
 48                 MAX(DECODE (OI.PROD_ID, 8, REL.BASE_APPLY)) AS YILBASEZ,
 49                 REC.ORDER_ID
 50            FROM CS_ORDER_ITEM_DRAFT_REC OI
 51            JOIN ES_INS_REC REC
 52              ON REC.ORDER_ID = OI.ORDER_ID
 53             AND REC.FAILURE_STATE = 1
 54             AND REC.INS_STATE IN (1, 3)
 55             AND REC.EXECUTE_MON = '201412'
 56            LEFT JOIN CS_ORDER_LSRDRAFT_REC REL
 57              ON OI.CS_ORDER_LSRDRAFT_ID = REL.ORDER_LOCAL_STANDARD_REL_ID
 58             AND REL.BAT_NUM = OI.BAT_NUM
 59           WHERE OI.OI_SOURCE IN (1, 3)
 60             AND OI.BAT_NUM = (SELECT MAX(R.BAT_NO)
 61                                 FROM CS_ORDER_OPR_REC R
 62                                WHERE R.ORDER_ID = OI.ORDER_ID
 63                                  AND R.OPR_TYPE = 1 )
 64             AND OI.PARENT_ID IS NULL
 65             AND OI.PROD_ID IN (4, 5, 6 , 7, 8)
 66             AND (OI.END_DATE IS NULL OR OI.END_DATE >= SYSDATE )
 67             and rec.ORDER_ID in
 68                 ( SELECT order_id
 69                    FROM CS_ORDER_DRAFT_APP T
 70                   where T.APP_TYPE IN (2, 4)
 71                     AND T.APP_STATE IN (1, 2))
 72           GROUP BY REC.UNIQ_NO, REC.ORDER_ID
 73          UNION all
 74          SELECT MAX (DECODE(R.PRODUCT_ID, 4, R.PAY_BASE)) AS YALBASEX,
 75                 MAX(DECODE (R.PRODUCT_ID, 5, R.PAY_BASE)) AS SHYEBASEX,
 76                 MAX(DECODE (R.PRODUCT_ID, 6, R.PAY_BASE)) AS GOSBASEX,
 77                 MAX(DECODE (R.PRODUCT_ID, 7, R.PAY_BASE)) AS SHYUBASEX,
 78                 MAX(DECODE (R.PRODUCT_ID, 8, R.PAY_BASE)) AS YILBASEX,
 79                 MAX(DECODE (REC.PRODUCT_ID, 4, REC.PAY_BASE)) AS YALBASEZ,
 80                 MAX(DECODE (REC.PRODUCT_ID, 5, REC.PAY_BASE)) AS SHYEBASEZ,
 81                 MAX(DECODE (REC.PRODUCT_ID, 6, REC.PAY_BASE)) AS GOSBASEZ,
 82                 MAX(DECODE (REC.PRODUCT_ID, 7, REC.PAY_BASE)) AS SHYUBASEZ,
 83                 MAX(DECODE (REC.PRODUCT_ID, 8, REC.PAY_BASE)) AS YILBASEZ,
 84                 REC.ORDER_ID
 85            FROM ES_INS_REC REC
 86            JOIN ES_INS_REC R
 87              ON REC.CONFL_SOURCE_ID = R.INS_REC_ID
 88           WHERE R.INS_STATE = 3
 89             AND REC.INS_STATE = 1
 90             AND REC.FAILURE_STATE = 1
 91             and rec.ORDER_ID in
 92                 ( SELECT order_id
 93                    FROM CS_ORDER_DRAFT_APP T
 94                   where T.APP_TYPE IN (2, 4)
 95                     AND T.APP_STATE IN (1, 2))
 96           GROUP BY REC.UNIQ_NO, REC.NAT_MON, REC.ORDER_ID) REL
 97      ON T.ORDER_ID = REL.ORDER_ID
 98   WHERE T.APP_TYPE IN (2, 4)
 99     AND T.APP_STATE IN (1, 2)
100     AND CO.RESP_REP IN ( 3804274, 3822522 , 3852263, 3804273)
101     AND EI.UNIQ_NO = '65736';

no rows selected

Elapsed: 00: 00:01.90

Execution Plan
----------------------------------------------------------
Plan hash value: 699188961

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |                           |     1 |   496 |       | 17676   (1 )| 00: 03:33 |       |       |
|   1 |  NESTED LOOPS                                     |                           |       |       |       |            |          |       |       |
|   2 |   NESTED LOOPS                                    |                           |     1 |   496 |       | 17676   (1 )| 00: 03:33 |       |       |
|*  3 |    HASH JOIN OUTER                                |                           |     1 |   432 |       | 17675   (1 )| 00: 03:33 |       |       |
|   4 |     MERGE JOIN CARTESIAN                          |                           |     1 |   222 |       | 17645   (1 )| 00: 03:32 |       |       |
|   5 |      NESTED LOOPS                                 |                           |       |       |       |            |          |       |       |
|   6 |       NESTED LOOPS                                |                           |     1 |   185 |       | 17641   (1 )| 00: 03:32 |       |       |
|*  7 |        HASH JOIN                                   |                           |     1 |   160 |       | 17639   (1 )| 00: 03:32 |       |       |
|   8 |         VIEW                                      |                           |     5 |   715 |       | 17602   (1 )| 00: 03:32 |       |       |
|   9 |          UNION-ALL                                 |                           |       |       |       |            |          |       |       |
|  10 |           HASH GROUP BY                           |                           |     4 |   420 |       |  7192   (2 )| 00: 01:27 |       |       |
|  11 |            NESTED LOOPS OUTER                      |                           |     4 |   420 |       |  7191   (2 )| 00: 01:27 |       |       |
|* 12 |             HASH JOIN                              |                           |     4 |   364 |       |  7172   (2 )| 00: 01:27 |       |       |
|  13 |              NESTED LOOPS                         |                           |       |       |       |            |          |       |       |
|  14 |               NESTED LOOPS                        |                           |  1174 | 84528 |       |  3855   (1 )| 00: 00:47 |       |       |
|  15 |                NESTED LOOPS                       |                           |   140 |  6020 |       |   210   (1 )| 00: 00:03 |       |       |
|* 16 |                 TABLE ACCESS FULL                 | CS_ORDER_DRAFT_APP        |    29 |   348 |       |    36   ( 3)| 00:00 :01 |       |       |
|  17 |                 PARTITION RANGE SINGLE            |                           |     5 |   155 |       |     6   (0 )| 00: 00:01 |    13 |    13 |
|* 18 |                  TABLE ACCESS BY LOCAL INDEX ROWID| ES_INS_REC                |     5 |   155 |       |     6   (0 )| 00: 00:01 |    13 |    13 |
|* 19 |                   INDEX RANGE SCAN                | ES_INS_REC_ORDER_ID_LX    |     6 |       |       |     2   (0 )| 00:00 :01 |    13 |    13 |
|* 20 |                INDEX RANGE SCAN                   | INDEX_DRAFT_REC_ORDER_ID  |    75 |       |       |     2   (0 )| 00:00 :01 |       |       |
|* 21 |               TABLE ACCESS BY INDEX ROWID          | CS_ORDER_ITEM_DRAFT_REC   |     8 |   232 |       |    26   (0 )| 00:00 :01 |       |       |
|  22 |              VIEW                                 | VW_SQ_1                   | 98936 |  1835 K|       |  3316   (3 )| 00: 00:40 |       |       |
|  23 |               HASH GROUP BY                       |                           | 98936 |  1159 K|  2488K|  3316   (3 )| 00: 00:40 |       |       |
|* 24 |                TABLE ACCESS FULL                  | CS_ORDER_OPR_REC          |   105K|  1232K|       |  2845   ( 3)| 00:00 :35 |       |       |
|* 25 |             TABLE ACCESS BY INDEX ROWID            | CS_ORDER_LSRDRAFT_REC     |     1 |    14 |       |     6   ( 0)| 00:00 :01 |       |       |
|* 26 |              INDEX RANGE SCAN                     | IDX_LOCAL_STANDARD_REL_ID |     3 |       |       |     2   (0 )| 00:00 :01 |       |       |
|  27 |           HASH GROUP BY                           |                           |     1 |    60 |       | 10410   (1 )| 00: 02:05 |       |       |
|  28 |            NESTED LOOPS                           |                           |       |       |       |            |          |       |       |
|  29 |             NESTED LOOPS                          |                           |     1 |    60 |       | 10409   (1 )| 00: 02:05 |       |       |
|  30 |              NESTED LOOPS                         |                           |   121 |  5203 |       | 10167   (1 )| 00: 02:03 |       |       |
|* 31 |               TABLE ACCESS FULL                   | CS_ORDER_DRAFT_APP        |    29 |   348 |       |    36   ( 3)| 00:00 :01 |       |       |
|  32 |               PARTITION RANGE ALL                 |                           |     4 |   124 |       |   349   (0 )| 00: 00:05 |     1 |    25 |
|* 33 |                TABLE ACCESS BY LOCAL INDEX ROWID  | ES_INS_REC                |     4 |   124 |       |   349   (0 )| 00: 00:05 |     1 |    25 |
|* 34 |                 INDEX RANGE SCAN                  | ES_INS_REC_ORDER_ID_LX    |   306 |       |       |    50   (0 )| 00:00 :01 |     1 |    25 |
|* 35 |              INDEX UNIQUE SCAN                    | PK_ES_INS_REC             |     1 |       |       |     1   (0 )| 00:00 :01 |       |       |
|* 36 |             TABLE ACCESS BY GLOBAL INDEX ROWID    | ES_INS_REC                |     1 |    17 |       |     2   (0 )| 00: 00:01 | ROWID | ROWID |
|* 37 |         TABLE ACCESS FULL                         | CS_ORDER_DRAFT_APP        |    29 |   493 |       |    36   ( 3)| 00:00 :01 |       |       |
|* 38 |        INDEX UNIQUE SCAN                          | PK_CS_ORDER               |     1 |       |       |     1   (0 )| 00: 00:01 |       |       |
|* 39 |       TABLE ACCESS BY INDEX ROWID                  | CS_ORDER                  |     1 |    25 |       |     2   (0 )| 00: 00:01 |       |       |
|  40 |      BUFFER SORT                                   |                           |     1 |    37 |       | 17643   (1 )| 00: 03:32 |       |       |
|* 41 |       TABLE ACCESS BY INDEX ROWID                  | ES_INS_EMP_INFO           |     1 |    37 |       |     4   ( 0)| 00:00 :01 |       |       |
|* 42 |        INDEX RANGE SCAN                           | IDX_EIEN_UNIQ_NO          |     2 |       |       |     2   (0 )| 00:00 :01 |       |       |
|* 43 |     VIEW                                          |                           |     8 |  1680 |       |    30   (4 )| 00: 00:01 |       |       |
|* 44 |      WINDOW SORT PUSHED RANK                       |                           |     8 |   472 |       |    30   (4 )| 00: 00:01 |       |       |
|  45 |       NESTED LOOPS                                |                           |       |       |       |            |          |       |       |
|  46 |        NESTED LOOPS                               |                           |     8 |   472 |       |    29   (0 )| 00: 00:01 |       |       |
|  47 |         NESTED LOOPS                              |                           |     8 |   392 |       |    21   (0 )| 00: 00:01 |       |       |
|  48 |          INLIST ITERATOR                          |                           |       |       |       |            |          |       |       |
|* 49 |           TABLE ACCESS BY INDEX ROWID              | ES_FW_FLOW_INST           |     4 |    84 |       |     5   ( 0)| 00:00 :01 |       |       |
|* 50 |            INDEX RANGE SCAN                       | FLOW_FLOW_INST_FK         |    13 |       |       |     3   (0 )| 00:00 :01 |       |       |
|  51 |          TABLE ACCESS BY INDEX ROWID               | ES_FW_STEP_INST           |     2 |    56 |       |     4   ( 0)| 00:00 :01 |       |       |
|* 52 |           INDEX RANGE SCAN                        | INST_FLOW_STEP_FK         |     2 |       |       |     2   (0 )| 00:00 :01 |       |       |
|* 53 |         INDEX UNIQUE SCAN                         | PK_ES_FW_STEP             |     1 |       |       |     0   (0 )| 00:00 :01 |       |       |
|* 54 |        TABLE ACCESS BY INDEX ROWID                 | ES_FW_STEP                |     1 |    10 |       |     1   (0 )| 00: 00:01 |       |       |
|* 55 |    INDEX UNIQUE SCAN                              | PK_ES_INS_CUST_INFO       |     1 |       |       |     0   (0 )| 00:00 :01 |       |       |
|  56 |   TABLE ACCESS BY INDEX ROWID                      | ES_INS_CUST_INFO          |     1 |    64 |       |     1   ( 0)| 00:00 :01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information ( identified by operation id):
---------------------------------------------------

   3 - access("EI"."UNIQ_NO"="STEP"."UNIQ_NO"(+))
   7 - access("T"."ORDER_ID"="REL"."ORDER_ID")
  12 - access("OI"."BAT_NUM"="MAX(R.BAT_NO)" AND "ITEM_1"="OI"."ORDER_ID")
  16 - filter(("T"."APP_TYPE"=2 OR "T"."APP_TYPE"=4 ) AND ("T"."APP_STATE"=1 OR "T"."APP_STATE"= 2))
  18 - filter("REC"."FAILURE_STATE"=1 AND ("REC"."INS_STATE"=1 OR "REC"."INS_STATE"=3 ) AND "REC"."EXECUTE_MON"= 201412)
  19 - access("REC"."ORDER_ID"="ORDER_ID")
  20 - access("REC"."ORDER_ID"="OI"."ORDER_ID")
  21 - filter("OI"."PARENT_ID" IS NULL AND ("OI"."OI_SOURCE"= 1 OR "OI"."OI_SOURCE"=3 ) AND ("OI"."END_DATE" IS NULL OR
              "OI"."END_DATE">= SYSDATE@!) AND ("OI"."PROD_ID"= 4 OR "OI"."PROD_ID"=5 OR "OI"."PROD_ID"= 6 OR "OI"."PROD_ID"=7 OR "OI"."PROD_ID"=8 ))
  24 - filter("R"."OPR_TYPE"=1 )
  25 - filter("REL"."BAT_NUM"(+)="OI"."BAT_NUM")
  26 - access("OI"."CS_ORDER_LSRDRAFT_ID"="REL"."ORDER_LOCAL_STANDARD_REL_ID"(+))
  31 - filter(("T"."APP_TYPE"=2 OR "T"."APP_TYPE"=4 ) AND ("T"."APP_STATE"=1 OR "T"."APP_STATE"= 2))
  33 - filter("REC"."INS_STATE"=1 AND "REC"."FAILURE_STATE"=1)
  34 - access("REC"."ORDER_ID"="ORDER_ID")
  35 - access("REC"."CONFL_SOURCE_ID"="R"."INS_REC_ID")
  36 - filter("R"."INS_STATE"=3)
  37 - filter(("T"."APP_TYPE"=2 OR "T"."APP_TYPE"=4 ) AND ("T"."APP_STATE"=1 OR "T"."APP_STATE"= 2))
  38 - access("CO"."ORDER_ID"="T"."ORDER_ID")
  39 - filter("CO"."UNIQ_NO"=65736 AND "CO"."INS_CUST_INFO_ID" IS NOT NULL AND ("CO"."RESP_REP"= 3804273 OR "CO"."RESP_REP"= 3804274 OR
              "CO"."RESP_REP"= 3822522 OR "CO"."RESP_REP"= 3852263))
  41 - filter("EI"."INS_EMP_TYPE"=1)
  42 - access("EI"."UNIQ_NO"=65736 )
  43 - filter("STEP"."ROWNO"(+)=1 AND "STEP"."UNIQ_NO"(+)=65736)
  44 - filter(ROW_NUMBER() OVER ( PARTITION BY "STEP"."FLOW_INST_ID" ORDER BY INTERNAL_FUNCTION("FW"."STEP_ORDER") DESC )<=1 )
  49 - filter("INST"."FLOW_STATE"<>5 AND "INST"."IS_VALID"=1)
  50 - access("INST"."FLOW_ID"=1473 OR "INST"."FLOW_ID"=1474)
  52 - access("INST"."FLOW_INST_ID"="STEP"."FLOW_INST_ID")
  53 - access("STEP"."STEP_ID"="FW"."STEP_ID")
  54 - filter("FW"."IS_VALID"=1)
  55 - access("EC"."CUST_INS_ID"="CO"."INS_CUST_INFO_ID")

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      16628  consistent gets
      12131  physical reads
        260  redo size
       1756  bytes sent via SQL *Net to client
        513  bytes received via SQL *Net from client
          1  SQL *Net roundtrips to/ from client
          0  sorts (memory )
          0  sorts (disk )
          0  rows processed

SQL>




  【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...

此條目發表在   SQL、SQL優化篇  分類目錄。將固定連線加入收藏夾。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1392132/，如需轉載，請註明出處，否則將追究法律責任。

相關文章