注释：
今天在生产环境发现如下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...;） [在此不做实验了]
|
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>
【源于本人笔记】 若有书写错误，表达错误，请指正...
