nvl函数使外连接无法转换为内连接案例
编写：李晓萌
sql
SELECT
count(0)
FROM
(
SELECT
z.GP3ZZHDM ZHDM ,
a.C_PORT_NAME ZHMC,
c.C_SEC_CODE ZQDM ,
c.N_AMOUNT ,
c.C_FIELD5
FROM
T_P_AB_PORT a
LEFT JOIN
(
SELECT DISTINCT
GP3ZHDM,
GP3ZZHDM
FROM
T_OCP_ETL_PORT_MAP
WHERE
GP3ZZHDM != '不设子组合'
AND GP3ZHDM != GP3ZZHDM
)
z
ON
a.C_PORT_CODE = z.GP3ZHDM
LEFT JOIN T_M_T_ZQCC c
ON
z.GP3ZZHDM = c.C_PORT_CODE
AND c.D_STOCK = TO_DATE('2024-03-21', 'yyyy-MM-dd')
AND c.C_FIELD4 = 'AD1'
AND c.C_FIELD1 = 'TRES'
AND c.C_FIELD2 = 'PROPRE'
AND c.N_AMOUNT != 0
AND
(
c.C_PORT_CODE LIKE 'F%'
OR c.C_PORT_CODE LIKE 'B%'
OR c.C_PORT_CODE LIKE 'C%'
OR c.C_PORT_CODE LIKE 'E%'
)
AND c.C_PORT_CODE NOT LIKE '%11'
AND c.C_DATA_IDF = 'CC_SRC_GP3CJ_VIEW'
JOIN T_M_T_SYNTH b
ON
c.C_PORT_CODE = b.C_FIELD1
AND b.C_DATA_IDF = 'ZH_SRC_GP3CJ_GP3'
LEFT JOIN T_M_T_SYNTH e
ON
b.C_FIELD1 = e.C_PORT_CODE
AND e.C_DATA_IDF = 'ZH_SRC_GP3ZHHJ_GP3'
AND
(
(
e.C_PORT_CODE LIKE 'B%'
AND e.C_FIELD7 IN ('TRDINS', 'TKINS')
)
OR
(
e.C_PORT_CODE LIKE 'C%'
AND e.C_FIELD7 = 'TKINS'
)
OR
(
(
e.C_PORT_CODE LIKE 'E%'
OR e.C_PORT_CODE LIKE 'F%'
)
AND e.C_FIELD7 IN ('KTWJZZ', 'TKMFF', 'TKMMF')
)
)
WHERE
a.C_PORT_CODE = 'FO1K11'
AND a.D_BUILD <= TO_DATE('2024-03-21', 'yyyy-MM-dd')
AND a.D_CLOSE > TO_DATE('2024-03-21', 'yyyy-MM-dd')
AND a.C_DV_PROD_STATE = 'PS4'
AND nvl(c.C_SEC_CODE, '0') != '0'
)
t
FULL JOIN
(
SELECT
GP3ZZHDM,
N_AMOUNT,
CASE WHEN TITLE = 'N_FIELD1' THEN '未到期内部拆入应计利息' WHEN TITLE = 'N_FIELD2' THEN '未到期内部拆出应计利息' WHEN TITLE = 'N_FIELD3' THEN '未到期内部拆入本金' WHEN TITLE = 'N_FIELD4' THEN '未到期内部拆出本金' ELSE '' END AS C_FIELD5
FROM
(
SELECT
GP3ZZHDM,
N_AMOUNT,
TITLE
FROM
(
SELECT
GP3ZZHDM ,
sum(N_FIELD1) N_FIELD1,
sum(N_FIELD2) N_FIELD2,
sum(N_FIELD3) N_FIELD3,
sum(N_FIELD4) N_FIELD4
FROM
(
SELECT
i.GP3ZZHDM,
l.N_FIELD1,
l.N_FIELD2,
l.N_FIELD3,
l.N_FIELD4
FROM
(
SELECT DISTINCT
GP3ZZHDM
FROM
T_OCP_ETL_PORT_MAP
WHERE
GP3ZHDM = 'FO1K11'
AND GP3ZZHDM != '不设子组合'
AND GP3ZHDM != GP3ZZHDM
)
i
RIGHT JOIN
(
SELECT DISTINCT
(C_FIELD8) ,
C_PORT_CODE,
C_FIELD3
FROM
T_M_T_SYNTH
WHERE
C_FIELD1 = '1'
AND
(
C_PORT_CODE LIKE 'F%'
OR C_PORT_CODE LIKE 'B%'
OR C_PORT_CODE LIKE 'C%'
OR C_PORT_CODE LIKE 'E%'
)
AND C_DATA_IDF = 'ZH_SRC_ZJZHB_OMAS'
)
k
ON
i.GP3ZZHDM = k.C_PORT_CODE
RIGHT JOIN T_M_T_SYNTH l
ON
k.C_FIELD8 = l.C_FIELD7
AND k.C_FIELD3 = l.C_FIELD8
AND l.D_DEALDATE = TO_DATE('2024-03-21', 'yyyy-MM-dd')
AND l.C_DATA_IDF = 'ZH_SRC_O32ZJCJ_O32'
WHERE
NVL(i.GP3ZZHDM, '0') != '0'
)
GROUP BY
GP3ZZHDM
)
UNPIVOT (N_AMOUNT FOR title IN (N_FIELD1, N_FIELD2, N_FIELD3, N_FIELD4))
)
)
n ON t.ZHDM = n.GP3ZZHDM
AND t.C_FIELD5 = n.C_FIELD5;
执行计划
1 #NSET2: [7380, 1, 1558]
2 #PIPE2: [7380, 1, 1558]
3 #PRJT2: [189, 1, 1558]; exp_num(1), is_atom(FALSE)
4 #AAGR2: [189, 1, 1558]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
5 #HASH FULL JOIN2: [189, 4, 1558]; key_num(2), mix_aagr(0), mix_dist(0) KEY(N.GP3ZZHDM=T.ZHDM AND N.C_FIELD5=T.C_FIELD5)
6 #PRJT2: [6, 1, 625]; exp_num(2), is_atom(FALSE)
7 #PRJT2: [6, 1, 625]; exp_num(2), is_atom(FALSE)
8 #PRJT2: [6, 1, 625]; exp_num(2), is_atom(FALSE)
9 #SLCT2: [6, 1, 625]; NOT(DMTEMPVIEW_894870894.N_AMOUNT IS NULL)
10 #PRJT2: [6, 4, 625]; exp_num(3), is_atom(FALSE)
11 #UNION ALL: [6, 4, 625]
12 #PRJT2: [4, 3, 625]; exp_num(3), is_atom(FALSE)
13 #UNION ALL: [4, 3, 625]
14 #PRJT2: [2, 2, 625]; exp_num(3), is_atom(FALSE)
15 #UNION ALL: [2, 2, 625]
16 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
17 #HEAP TABLE SCAN: [1, 1, 625]; table_no(0)
18 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
19 #HEAP TABLE SCAN: [1, 1, 625]; table_no(0)
20 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
21 #HEAP TABLE SCAN: [1, 1, 625]; table_no(0)
22 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
23 #HEAP TABLE SCAN: [1, 1, 625]; table_no(0)
24 #PRJT2: [182, 4, 933]; exp_num(2), is_atom(FALSE)
25 #UNION FOR OR2: [182, 4, 933]; key_num(6), outer_join(L)
26 #UNION FOR OR2: [136, 3, 933]; key_num(6), outer_join(-)
27 #UNION FOR OR2: [90, 2, 933]; key_num(6), outer_join(-)
28 #SLCT2: [44, 1, 933]; C.C_PORT_CODE = B.C_FIELD1
29 #NEST LOOP INDEX JOIN2: [44, 1, 933]
30 #SLCT2: [44, 1, 825]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var8 AND A.D_CLOSE > var8 AND exp11 <> '0' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D')
31 #HASH2 INNER JOIN: [44, 1, 825]; KEY_NUM(1); KEY(E.C_PORT_CODE=C.C_PORT_CODE) KEY_NULL_EQU(0)
32 #PARALLEL: [1, 1, 156]; scan_type(EQU), key_num(1, 0, 0), simple(0)
33 #SLCT2: [1, 1, 156]; (E.C_FIELD7 = 'TKINS' AND E.C_PORT_CODE >= 'C' AND E.C_PORT_CODE < 'D')
34 #BLKUP2: [1, 1, 156]; IDX_M_T_SYNTH4(E)
35 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as E), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min),('ZH_SRC_GP3ZHHJ_GP3',max))
36 #UNION FOR OR2: [42, 11377, 669]; key_num(4), outer_join(L)
37 #UNION FOR OR2: [30, 3, 669]; key_num(4), outer_join(-)
38 #UNION FOR OR2: [19, 2, 669]; key_num(4), outer_join(-)
39 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
40 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
41 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
42 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND C.N_AMOUNT <> var9 AND NOT(C.C_PORT_CODE LIKE '%11'))
43 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
44 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
45 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
46 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
47 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
48 #DISTINCT: [2, 11374, 96]
49 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
50 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
51 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
52 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
53 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
54 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND C.N_AMOUNT <> var10 AND NOT(C.C_PORT_CODE LIKE '%11'))
55 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
56 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
57 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
58 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
59 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
60 #DISTINCT: [2, 11374, 96]
61 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
62 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
63 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
64 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
65 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
66 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.N_AMOUNT <> var11 AND NOT(C.C_PORT_CODE LIKE '%11'))
67 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
68 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
69 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
70 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
71 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
72 #DISTINCT: [2, 11374, 96]
73 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
74 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
75 #HASH RIGHT JOIN2: [9, 11374, 669]; key_num(1), ret_null(0), KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
76 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
77 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AMOUNT <> var12 AND NOT(C.C_PORT_CODE LIKE '%11'))
78 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
79 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
80 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
81 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
82 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
83 #DISTINCT: [2, 11374, 96]
84 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
85 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
86 #PARALLEL: [1, 1, 96]; scan_type(EQU), key_num(1, 0, 0), simple(0)
87 #SLCT2: [1, 1, 96]; (B.C_FIELD1 >= 'C' AND B.C_FIELD1 < 'D')
88 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as B), scan_range[('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE),('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE)]
89 #SLCT2: [44, 1, 933]; C.C_PORT_CODE = B.C_FIELD1
90 #NEST LOOP INDEX JOIN2: [44, 1, 933]
91 #SLCT2: [44, 1, 825]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var13 AND A.D_CLOSE > var13 AND exp11 <> '0' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C')
92 #HASH2 INNER JOIN: [44, 1, 825]; KEY_NUM(1); KEY(E.C_PORT_CODE=C.C_PORT_CODE) KEY_NULL_EQU(0)
93 #PARALLEL: [1, 1, 156]; scan_type(EQU), key_num(1, 0, 0), simple(0)
94 #HASH RIGHT SEMI JOIN2: [1, 1, 156]; n_keys(1) KEY(DMTEMPVIEW_894871004.colname=E.C_FIELD7) KEY_NULL_EQU(0)
95 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
96 #SLCT2: [1, 1, 156]; (E.C_PORT_CODE >= 'B' AND E.C_PORT_CODE < 'C')
97 #BLKUP2: [1, 1, 156]; IDX_M_T_SYNTH4(E)
98 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as E), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min),('ZH_SRC_GP3ZHHJ_GP3',max))
99 #UNION FOR OR2: [42, 11377, 669]; key_num(4), outer_join(L)
100 #UNION FOR OR2: [30, 3, 669]; key_num(4), outer_join(-)
101 #UNION FOR OR2: [19, 2, 669]; key_num(4), outer_join(-)
102 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
103 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
104 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
105 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND C.N_AMOUNT <> var14 AND NOT(C.C_PORT_CODE LIKE '%11'))
106 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
107 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
108 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
109 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
110 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
111 #DISTINCT: [2, 11374, 96]
112 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
113 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
114 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
115 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
116 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
117 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND C.N_AMOUNT <> var15 AND NOT(C.C_PORT_CODE LIKE '%11'))
118 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
119 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
120 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
121 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
122 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
123 #DISTINCT: [2, 11374, 96]
124 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
125 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
126 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
127 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
128 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
129 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.N_AMOUNT <> var16 AND NOT(C.C_PORT_CODE LIKE '%11'))
130 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
131 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
132 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
133 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
134 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
135 #DISTINCT: [2, 11374, 96]
136 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
137 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
138 #HASH RIGHT JOIN2: [9, 11374, 669]; key_num(1), ret_null(0), KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
139 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
140 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AMOUNT <> var17 AND NOT(C.C_PORT_CODE LIKE '%11'))
141 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
142 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
143 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
144 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
145 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
146 #DISTINCT: [2, 11374, 96]
147 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
148 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
149 #PARALLEL: [1, 1, 96]; scan_type(EQU), key_num(1, 0, 0), simple(0)
150 #SLCT2: [1, 1, 96]; (B.C_FIELD1 >= 'B' AND B.C_FIELD1 < 'C')
151 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as B), scan_range[('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE),('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE)]
152 #SLCT2: [44, 1, 933]; C.C_PORT_CODE = B.C_FIELD1
153 #NEST LOOP INDEX JOIN2: [44, 1, 933]
154 #SLCT2: [44, 1, 825]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var18 AND A.D_CLOSE > var18 AND exp11 <> '0' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G')
155 #HASH2 INNER JOIN: [44, 1, 825]; KEY_NUM(1); KEY(E.C_PORT_CODE=C.C_PORT_CODE) KEY_NULL_EQU(0)
156 #PARALLEL: [1, 1, 156]; scan_type(EQU), key_num(1, 0, 0), simple(0)
157 #HASH RIGHT SEMI JOIN2: [1, 1, 156]; n_keys(1) KEY(DMTEMPVIEW_894871005.colname=E.C_FIELD7) KEY_NULL_EQU(0)
158 #CONST VALUE LIST: [1, 3, 48]; row_num(3), col_num(1),
159 #SLCT2: [1, 1, 156]; (E.C_PORT_CODE >= 'F' AND E.C_PORT_CODE < 'G')
160 #BLKUP2: [1, 1, 156]; IDX_M_T_SYNTH4(E)
161 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as E), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min),('ZH_SRC_GP3ZHHJ_GP3',max))
162 #UNION FOR OR2: [42, 11377, 669]; key_num(4), outer_join(L)
163 #UNION FOR OR2: [30, 3, 669]; key_num(4), outer_join(-)
164 #UNION FOR OR2: [19, 2, 669]; key_num(4), outer_join(-)
165 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
166 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
167 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
168 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND C.N_AMOUNT <> var19 AND NOT(C.C_PORT_CODE LIKE '%11'))
169 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
170 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
171 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
172 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
173 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
174 #DISTINCT: [2, 11374, 96]
175 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
176 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
177 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
178 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
179 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
180 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND C.N_AMOUNT <> var20 AND NOT(C.C_PORT_CODE LIKE '%11'))
181 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
182 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
183 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
184 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
185 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
186 #DISTINCT: [2, 11374, 96]
187 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
188 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
189 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
190 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
191 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
192 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.N_AMOUNT <> var21 AND NOT(C.C_PORT_CODE LIKE '%11'))
193 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
194 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
195 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
196 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
197 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
198 #DISTINCT: [2, 11374, 96]
199 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
200 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
201 #HASH RIGHT JOIN2: [9, 11374, 669]; key_num(1), ret_null(0), KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
202 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
203 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AMOUNT <> var22 AND NOT(C.C_PORT_CODE LIKE '%11'))
204 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
205 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
206 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
207 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
208 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
209 #DISTINCT: [2, 11374, 96]
210 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
211 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
212 #PARALLEL: [1, 1, 96]; scan_type(EQU), key_num(1, 0, 0), simple(0)
213 #SLCT2: [1, 1, 96]; (B.C_FIELD1 >= 'F' AND B.C_FIELD1 < 'G')
214 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as B), scan_range[('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE),('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE)]
215 #INDEX JOIN LEFT JOIN2: [45, 1, 777] join condition((E.C_PORT_CODE >= 'E' AND E.C_PORT_CODE < 'F' AND B.C_FIELD1 = E.C_PORT_CODE)) ret_null(0)
216 #SLCT2: [44, 1, 777]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var23 AND A.D_CLOSE > var23 AND exp11 <> '0')
217 #HASH2 INNER JOIN: [44, 1, 777]; KEY_NUM(1); KEY(B.C_FIELD1=C.C_PORT_CODE) KEY_NULL_EQU(0)
218 #PARALLEL: [1, 1091, 108]; scan_type(EQU), key_num(1, 0, 0), simple(0)
219 #SSEK2: [1, 958, 108]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as B), scan_range[('ZH_SRC_GP3CJ_GP3',min),('ZH_SRC_GP3CJ_GP3',max))
220 #UNION FOR OR2: [42, 11377, 669]; key_num(4), outer_join(L)
221 #UNION FOR OR2: [30, 3, 669]; key_num(4), outer_join(-)
222 #UNION FOR OR2: [19, 2, 669]; key_num(4), outer_join(-)
223 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
224 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
225 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
226 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND C.N_AMOUNT <> var24 AND NOT(C.C_PORT_CODE LIKE '%11'))
227 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
228 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
229 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
230 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
231 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
232 #DISTINCT: [2, 11374, 96]
233 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
234 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
235 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
236 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
237 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
238 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND C.N_AMOUNT <> var25 AND NOT(C.C_PORT_CODE LIKE '%11'))
239 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
240 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
241 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
242 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
243 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
244 #DISTINCT: [2, 11374, 96]
245 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
246 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
247 #SLCT2: [9, 1, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
248 #HASH2 INNER JOIN: [9, 1, 669]; KEY_NUM(1); KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
249 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
250 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.N_AMOUNT <> var26 AND NOT(C.C_PORT_CODE LIKE '%11'))
251 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
252 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
253 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
254 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
255 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
256 #DISTINCT: [2, 11374, 96]
257 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
258 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
259 #HASH RIGHT JOIN2: [9, 11374, 669]; key_num(1), ret_null(0), KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
260 #PARALLEL: [1, 1, 391]; scan_type(EQU), key_num(1, 0, 0), simple(0)
261 #SLCT2: [1, 1, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AMOUNT <> var27 AND NOT(C.C_PORT_CODE LIKE '%11'))
262 #BLKUP2: [1, 1, 391]; IDX_M_T_ZQCC(C)
263 #SSEK2: [1, 1, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC as C), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
264 #HASH LEFT JOIN2: [7, 11374, 278]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
265 #CSCN2: [1, 8827, 182]; INDEX33584323(T_P_AB_PORT as A)
266 #PRJT2: [2, 11374, 96]; exp_num(2), is_atom(FALSE)
267 #DISTINCT: [2, 11374, 96]
268 #SLCT2: [1, 11374, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
269 #CSCN2: [1, 12812, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
270 #PARALLEL: [1, 1, 96]; scan_type(EQU), key_num(1, 0, 0), simple(0)
271 #BLKUP2: [1, 1, 96]; IDX_M_T_SYNTH6(E)
272 #SLCT2: [1, 1, 96]; E.C_FIELD7 IN LIST
273 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH as E), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min,min,min,min),('ZH_SRC_GP3ZHHJ_GP3',max,max,max,max))
274 #HEAP TABLE: [7190, 1, 625]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
275 #PRJT2: [7190, 1, 625]; exp_num(5), is_atom(FALSE)
276 #HAGR2: [7190, 1, 625]; grp_num(1), sfun_num(4), distinct_flag[0,0,0,0]; slave_empty(0) keys(DMTEMPVIEW_894870886.GP3ZZHDM)
277 #PRJT2: [4425, 13877798, 625]; exp_num(5), is_atom(FALSE)
278 #SLCT2: [4425, 13877798, 625]; exp11 <> '0'
279 #HASH RIGHT JOIN2: [3939, 14608209, 625]; key_num(2), ret_null(0),join condition((L.C_DATA_IDF = 'ZH_SRC_O32ZJCJ_O32' AND L.D_DEALDATE = exp11)) KEY(K.C_FIELD8=L.C_FIELD7 AND K.C_FIELD3=L.C_FIELD8)
280 #HASH LEFT JOIN2: [5, 1, 348]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(K.C_PORT_CODE=I.GP3ZZHDM)
281 #PRJT2: [4, 1, 252]; exp_num(3), is_atom(FALSE)
282 #DISTINCT: [4, 1, 252]
283 #UNION FOR OR2: [3, 4, 252]; key_num(1), outer_join(-)
284 #UNION FOR OR2: [2, 3, 252]; key_num(1), outer_join(-)
285 #UNION FOR OR2: [1, 2, 252]; key_num(1), outer_join(-)
286 #PARALLEL: [1, 1, 252]; scan_type(EQU), key_num(1, 0, 0), simple(0)
287 #SLCT2: [1, 1, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'F' AND T_M_T_SYNTH.C_PORT_CODE < 'G')
288 #BLKUP2: [1, 1, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
289 #SSEK2: [1, 1, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,max,max))
290 #PARALLEL: [1, 1, 252]; scan_type(EQU), key_num(1, 0, 0), simple(0)
291 #SLCT2: [1, 1, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'B' AND T_M_T_SYNTH.C_PORT_CODE < 'C')
292 #BLKUP2: [1, 1, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
293 #SSEK2: [1, 1, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,max,max))
294 #PARALLEL: [1, 1, 252]; scan_type(EQU), key_num(1, 0, 0), simple(0)
295 #SLCT2: [1, 1, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'C' AND T_M_T_SYNTH.C_PORT_CODE < 'D')
296 #BLKUP2: [1, 1, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
297 #SSEK2: [1, 1, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,max,max))
298 #PARALLEL: [1, 1, 252]; scan_type(EQU), key_num(1, 0, 0), simple(0)
299 #SLCT2: [1, 1, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'E' AND T_M_T_SYNTH.C_PORT_CODE < 'F')
300 #BLKUP2: [1, 1, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
301 #SSEK2: [1, 1, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,max,max))
302 #PRJT2: [1, 2, 96]; exp_num(1), is_atom(FALSE)
303 #DISTINCT: [1, 2, 96]
304 #SLCT2: [1, 2, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
305 #BLKUP2: [1, 2, 96]; IDX_OCP_ET_PORT_MAP3(T_OCP_ETL_PORT_MAP)
306 #SSEK2: [1, 2, 96]; scan_type(ASC), IDX_OCP_ET_PORT_MAP3(T_OCP_ETL_PORT_MAP), scan_range[('FO1K11',min),('FO1K11',max))
307 #PARALLEL: [2361, 14608209, 277]; scan_type(FULL), key_num(0, 0, 0), simple(0)
308 #CSCN2: [2361, 14608209, 277]; INDEX33585627(T_M_T_SYNTH as L)
autotrace
LINEID COUNT(0)
---------- --------------------
1 0
1 #NSET2: [7380, 1->1, 1558]
2 #PIPE2: [7380, 1->1, 1558]
3 #PRJT2: [189, 1->1, 1558]; exp_num(1), is_atom(FALSE)
4 #AAGR2: [189, 1->1, 1558]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
5 #HASH FULL JOIN2: [189, 4, 1558]; key_num(2); col_num(0); mix_aagr(0);mix_dist(0); MEM_USED(0KB), DISK_USED(0KB) KEY(N.GP3ZZHDM=T.ZHDM AND N.C_FIELD5=T.C_FIELD5)
6 #PRJT2: [6, 1, 625]; exp_num(2), is_atom(FALSE)
7 #PRJT2: [6, 1, 625]; exp_num(2), is_atom(FALSE)
8 #PRJT2: [6, 1, 625]; exp_num(2), is_atom(FALSE)
9 #SLCT2: [6, 1, 625]; NOT(DMTEMPVIEW_894303458.N_AMOUNT IS NULL)
10 #PRJT2: [6, 4, 625]; exp_num(3), is_atom(FALSE)
11 #UNION ALL: [6, 4, 625]
12 #PRJT2: [4, 3, 625]; exp_num(3), is_atom(FALSE)
13 #UNION ALL: [4, 3, 625]
14 #PRJT2: [2, 2, 625]; exp_num(3), is_atom(FALSE)
15 #UNION ALL: [2, 2, 625]
16 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
17 #HEAP TABLE SCAN: [1, 1->0, 625]; table_no(0),
18 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
19 #HEAP TABLE SCAN: [1, 1->0, 625]; table_no(0),
20 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
21 #HEAP TABLE SCAN: [1, 1->0, 625]; table_no(0),
22 #PRJT2: [1, 1, 625]; exp_num(3), is_atom(FALSE)
23 #HEAP TABLE SCAN: [1, 1->0, 625]; table_no(0),
24 #PRJT2: [182, 4, 933]; exp_num(2), is_atom(FALSE)
25 #UNION FOR OR2: [182, 4, 933]; key_num(6)
26 #UNION FOR OR2: [136, 3, 933]; key_num(6)
27 #UNION FOR OR2: [90, 2, 933]; key_num(6)
28 #SLCT2: [44, 1, 933]; C.C_PORT_CODE = B.C_FIELD1
29 #NEST LOOP INDEX JOIN2: [44, 1, 933]
30 #SLCT2: [44, 1->0, 825]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var8 AND A.D_CLOSE > var8 AND exp11 <> '0' AND C.C_PORT_CODE >= 'C
' AND C.C_PORT_CODE < 'D')
31 #HASH2 INNER JOIN: [44, 1->98, 825]; KEY_NUM(1), MEM_USED(12527KB), DISK_USED(0KB) KEY(E.C_PORT_CODE=C.C_PORT_CODE) KEY_NULL_EQU(0)
32 #PARALLEL: [1, 1->129, 156]; scan_type(EQU)
33 #SLCT2: [1, 1->129, 156]; (E.C_FIELD7 = 'TKINS' AND E.C_PORT_CODE >= 'C' AND E.C_PORT_CODE < 'D')
34 #BLKUP2: [1, 1->27111, 156]; IDX_M_T_SYNTH4(T_M_T_SYNTH)
35 #SSEK2: [1, 1->27111, 156]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min),('ZH_SRC_GP3ZHHJ_GP3',max))
36 #UNION FOR OR2: [42, 11377->13864, 669]; key_num(4)
37 #UNION FOR OR2: [30, 3->633, 669]; key_num(4)
38 #UNION FOR OR2: [19, 2->535, 669]; key_num(4)
39 #SLCT2: [9, 1->4, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
40 #HASH2 INNER JOIN: [9, 1->4, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
41 #PARALLEL: [1, 1->19, 391]; scan_type(EQU)
42 #SLCT2: [1, 1->19, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND
C.N_AMOUNT <> var9 AND NOT(C.C_PORT_CODE LIKE '%11'))
43 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
44 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
45 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z
.GP3ZHDM)
46 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
47 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
48 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
49 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
50 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
51 #SLCT2: [9, 1->531, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
52 #HASH2 INNER JOIN: [9, 1->531, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
53 #PARALLEL: [1, 1->531, 391]; scan_type(EQU)
54 #SLCT2: [1, 1->531, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND
C.N_AMOUNT <> var10 AND NOT(C.C_PORT_CODE LIKE '%11'))
55 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
56 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
57 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z
.GP3ZHDM)
58 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
59 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
60 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
61 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
62 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
63 #SLCT2: [9, 1->98, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
64 #HASH2 INNER JOIN: [9, 1->98, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
65 #PARALLEL: [1, 1->98, 391]; scan_type(EQU)
66 #SLCT2: [1, 1->98, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.
N_AMOUNT <> var11 AND NOT(C.C_PORT_CODE LIKE '%11'))
67 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
68 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
69 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.G
P3ZHDM)
70 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
71 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
72 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
73 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
74 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
75 #HASH RIGHT JOIN2: [9, 11374->13521, 669]; key_num(1); col_num(11); MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
76 #PARALLEL: [1, 1->60, 391]; scan_type(EQU)
77 #SLCT2: [1, 1->60, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AM
OUNT <> var12 AND NOT(C.C_PORT_CODE LIKE '%11'))
78 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
79 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
80 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZH
DM)
81 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
82 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
83 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
84 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
85 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
86 #PARALLEL: [1, 1, 96]; scan_type(EQU)
87 #SLCT2: [1, 1, 96]; (B.C_FIELD1 >= 'C' AND B.C_FIELD1 < 'D')
88 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE),('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE)]
89 #SLCT2: [44, 1, 933]; C.C_PORT_CODE = B.C_FIELD1
90 #NEST LOOP INDEX JOIN2: [44, 1, 933]
91 #SLCT2: [44, 1->0, 825]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var13 AND A.D_CLOSE > var13 AND exp11 <> '0' AND C.C_PORT_CODE >=
'B' AND C.C_PORT_CODE < 'C')
92 #HASH2 INNER JOIN: [44, 1->531, 825]; KEY_NUM(1), MEM_USED(12527KB), DISK_USED(0KB) KEY(E.C_PORT_CODE=C.C_PORT_CODE) KEY_NULL_EQU(0)
93 #PARALLEL: [1, 1->1185, 156]; scan_type(EQU)
94 #HASH RIGHT SEMI JOIN2: [1, 1->1185, 156]; key_num(1), MEM_USED(225KB), DISK_USED(0KB) KEY(DMTEMPVIEW_894303603.colname=E.C_FIELD7) KEY_NULL_EQU(0)
95 #CONST VALUE LIST: [1, 2->2, 48]; row_num(2), col_num(1),
96 #SLCT2: [1, 1->2880, 156]; (E.C_PORT_CODE >= 'B' AND E.C_PORT_CODE < 'C')
97 #BLKUP2: [1, 1->27111, 156]; IDX_M_T_SYNTH4(T_M_T_SYNTH)
98 #SSEK2: [1, 1->27111, 156]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min),('ZH_SRC_GP3ZHHJ_GP3',max))
99 #UNION FOR OR2: [42, 11377->13864, 669]; key_num(4)
100 #UNION FOR OR2: [30, 3->633, 669]; key_num(4)
101 #UNION FOR OR2: [19, 2->535, 669]; key_num(4)
102 #SLCT2: [9, 1->4, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
103 #HASH2 INNER JOIN: [9, 1->4, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
104 #PARALLEL: [1, 1->19, 391]; scan_type(EQU)
105 #SLCT2: [1, 1->19, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND
C.N_AMOUNT <> var14 AND NOT(C.C_PORT_CODE LIKE '%11'))
106 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
107 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
108 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z
.GP3ZHDM)
109 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
110 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
111 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
112 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
113 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
114 #SLCT2: [9, 1->531, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
115 #HASH2 INNER JOIN: [9, 1->531, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
116 #PARALLEL: [1, 1->531, 391]; scan_type(EQU)
117 #SLCT2: [1, 1->531, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND
C.N_AMOUNT <> var15 AND NOT(C.C_PORT_CODE LIKE '%11'))
118 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
119 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
120 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z
.GP3ZHDM)
121 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
122 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
123 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
124 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
125 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
126 #SLCT2: [9, 1->98, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
127 #HASH2 INNER JOIN: [9, 1->98, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
128 #PARALLEL: [1, 1->98, 391]; scan_type(EQU)
129 #SLCT2: [1, 1->98, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.
N_AMOUNT <> var16 AND NOT(C.C_PORT_CODE LIKE '%11'))
130 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
131 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
132 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.G
P3ZHDM)
133 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
134 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
135 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
136 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
137 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
138 #HASH RIGHT JOIN2: [9, 11374->13521, 669]; key_num(1); col_num(11); MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
139 #PARALLEL: [1, 1->60, 391]; scan_type(EQU)
140 #SLCT2: [1, 1->60, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AM
OUNT <> var17 AND NOT(C.C_PORT_CODE LIKE '%11'))
141 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
142 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
143 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZH
DM)
144 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
145 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
146 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
147 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
148 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
149 #PARALLEL: [1, 1, 96]; scan_type(EQU)
150 #SLCT2: [1, 1, 96]; (B.C_FIELD1 >= 'B' AND B.C_FIELD1 < 'C')
151 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE),('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE)]
152 #SLCT2: [44, 1, 933]; C.C_PORT_CODE = B.C_FIELD1
153 #NEST LOOP INDEX JOIN2: [44, 1, 933]
154 #SLCT2: [44, 1, 825]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var18 AND A.D_CLOSE > var18 AND exp11 <> '0' AND C.C_PORT_CODE >= 'F' A
ND C.C_PORT_CODE < 'G')
155 #HASH2 INNER JOIN: [44, 1->0, 825]; KEY_NUM(1), MEM_USED(12527KB), DISK_USED(0KB) KEY(E.C_PORT_CODE=C.C_PORT_CODE) KEY_NULL_EQU(0)
156 #PARALLEL: [1, 1->68, 156]; scan_type(EQU)
157 #HASH RIGHT SEMI JOIN2: [1, 1->68, 156]; key_num(1), MEM_USED(225KB), DISK_USED(0KB) KEY(DMTEMPVIEW_894303604.colname=E.C_FIELD7) KEY_NULL_EQU(0)
158 #CONST VALUE LIST: [1, 3->3, 48]; row_num(3), col_num(1),
159 #SLCT2: [1, 1->1975, 156]; (E.C_PORT_CODE >= 'F' AND E.C_PORT_CODE < 'G')
160 #BLKUP2: [1, 1->27111, 156]; IDX_M_T_SYNTH4(T_M_T_SYNTH)
161 #SSEK2: [1, 1->27111, 156]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min),('ZH_SRC_GP3ZHHJ_GP3',max))
162 #UNION FOR OR2: [42, 11377->13864, 669]; key_num(4)
163 #UNION FOR OR2: [30, 3->633, 669]; key_num(4)
164 #UNION FOR OR2: [19, 2->535, 669]; key_num(4)
165 #SLCT2: [9, 1->4, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
166 #HASH2 INNER JOIN: [9, 1->4, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
167 #PARALLEL: [1, 1->19, 391]; scan_type(EQU)
168 #SLCT2: [1, 1->19, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND C.
N_AMOUNT <> var19 AND NOT(C.C_PORT_CODE LIKE '%11'))
169 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
170 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
171 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.G
P3ZHDM)
172 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
173 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
174 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
175 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
176 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
177 #SLCT2: [9, 1->531, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
178 #HASH2 INNER JOIN: [9, 1->531, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
179 #PARALLEL: [1, 1->531, 391]; scan_type(EQU)
180 #SLCT2: [1, 1->531, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND C
.N_AMOUNT <> var20 AND NOT(C.C_PORT_CODE LIKE '%11'))
181 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
182 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
183 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.G
P3ZHDM)
184 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
185 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
186 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
187 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
188 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
189 #SLCT2: [9, 1->98, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
190 #HASH2 INNER JOIN: [9, 1->98, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
191 #PARALLEL: [1, 1->98, 391]; scan_type(EQU)
192 #SLCT2: [1, 1->98, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.N_
AMOUNT <> var21 AND NOT(C.C_PORT_CODE LIKE '%11'))
193 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
194 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
195 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3
ZHDM)
196 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
197 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
198 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
199 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
200 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
201 #HASH RIGHT JOIN2: [9, 11374->13521, 669]; key_num(1); col_num(11); MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
202 #PARALLEL: [1, 1->60, 391]; scan_type(EQU)
203 #SLCT2: [1, 1->60, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AMOU
NT <> var22 AND NOT(C.C_PORT_CODE LIKE '%11'))
204 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
205 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
206 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZHDM
)
207 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
208 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
209 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
210 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
211 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
212 #PARALLEL: [1, 1, 96]; scan_type(EQU)
213 #SLCT2: [1, 1, 96]; (B.C_FIELD1 >= 'F' AND B.C_FIELD1 < 'G')
214 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE),('ZH_SRC_GP3CJ_GP3',E.C_PORT_CODE)]
215 #INDEX JOIN LEFT JOIN2: [45, 1, 777]: col_num(7); join condition((E.C_PORT_CODE >= 'E' AND E.C_PORT_CODE < 'F' AND B.C_FIELD1 = E.C_PORT_CODE)) ret_null(0)
216 #SLCT2: [44, 1->0, 777]; (A.C_PORT_CODE = 'FO1K11' AND A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var23 AND A.D_CLOSE > var23 AND exp11 <> '0')
217 #HASH2 INNER JOIN: [44, 1->610, 777]; KEY_NUM(1), MEM_USED(12527KB), DISK_USED(0KB) KEY(B.C_FIELD1=C.C_PORT_CODE) KEY_NULL_EQU(0)
218 #PARALLEL: [1, 1077->926, 108]; scan_type(EQU)
219 #SSEK2: [1, 958->926, 108]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3CJ_GP3',min),('ZH_SRC_GP3CJ_GP3',max))
220 #UNION FOR OR2: [42, 11377->13864, 669]; key_num(4)
221 #UNION FOR OR2: [30, 3->633, 669]; key_num(4)
222 #UNION FOR OR2: [19, 2->535, 669]; key_num(4)
223 #SLCT2: [9, 1->4, 669]; (Z.GP3ZZHDM >= 'F' AND Z.GP3ZZHDM < 'G')
224 #HASH2 INNER JOIN: [9, 1->4, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
225 #PARALLEL: [1, 1->19, 391]; scan_type(EQU)
226 #SLCT2: [1, 1->19, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'F' AND C.C_PORT_CODE < 'G' AND C.N_AM
OUNT <> var24 AND NOT(C.C_PORT_CODE LIKE '%11'))
227 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
228 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
229 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZH
DM)
230 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
231 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
232 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
233 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
234 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
235 #SLCT2: [9, 1->531, 669]; (Z.GP3ZZHDM >= 'B' AND Z.GP3ZZHDM < 'C')
236 #HASH2 INNER JOIN: [9, 1->531, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
237 #PARALLEL: [1, 1->531, 391]; scan_type(EQU)
238 #SLCT2: [1, 1->531, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'B' AND C.C_PORT_CODE < 'C' AND C.N_A
MOUNT <> var25 AND NOT(C.C_PORT_CODE LIKE '%11'))
239 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
240 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
241 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZH
DM)
242 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
243 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
244 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
245 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
246 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
247 #SLCT2: [9, 1->98, 669]; (Z.GP3ZZHDM >= 'C' AND Z.GP3ZZHDM < 'D')
248 #HASH2 INNER JOIN: [9, 1->98, 669]; KEY_NUM(1), MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM) KEY_NULL_EQU(0)
249 #PARALLEL: [1, 1->98, 391]; scan_type(EQU)
250 #SLCT2: [1, 1->98, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'C' AND C.C_PORT_CODE < 'D' AND C.N_AMOU
NT <> var26 AND NOT(C.C_PORT_CODE LIKE '%11'))
251 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
252 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
253 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZHDM
)
254 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
255 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
256 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
257 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
258 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
259 #HASH RIGHT JOIN2: [9, 11374->13521, 669]; key_num(1); col_num(11); MEM_USED(12783KB), DISK_USED(0KB) KEY(C.C_PORT_CODE=Z.GP3ZZHDM)
260 #PARALLEL: [1, 1->60, 391]; scan_type(EQU)
261 #SLCT2: [1, 1->60, 391]; (C.C_FIELD4 = 'AD1' AND C.C_FIELD1 = 'TRES' AND C.C_FIELD2 = 'PROPRE' AND C.C_PORT_CODE >= 'E' AND C.C_PORT_CODE < 'F' AND C.N_AMOUNT <
> var27 AND NOT(C.C_PORT_CODE LIKE '%11'))
262 #BLKUP2: [1, 1->708, 391]; IDX_M_T_ZQCC(T_M_T_ZQCC)
263 #SSEK2: [1, 1->708, 391]; scan_type(ASC), IDX_M_T_ZQCC(T_M_T_ZQCC), scan_range[(exp11,'CC_SRC_GP3CJ_VIEW'),(exp11,'CC_SRC_GP3CJ_VIEW')]
264 #HASH LEFT JOIN2: [7, 11374->13488, 278]; key_num(1); col_num(7); partition_keys_num(0); mix(0); MEM_USED(12340KB), DISK_USED(0KB) KEY(A.C_PORT_CODE=Z.GP3ZHDM)
265 #CSCN2: [1, 8827->8846, 182]; INDEX33584323(T_P_AB_PORT)
266 #PRJT2: [2, 11374->6047, 96]; exp_num(2), is_atom(FALSE)
267 #DISTINCT: [2, 11374->6047, 96], MEM_USED(9050KB), DISK_USED(0KB)
268 #SLCT2: [1, 11374->9972, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
269 #CSCN2: [1, 12812->12824, 96]; INDEX33560179(T_OCP_ETL_PORT_MAP)
270 #PARALLEL: [1, 1, 96]; scan_type(EQU)
271 #BLKUP2: [1, 1, 96]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
272 #SLCT2: [1, 1, 96]; E.C_FIELD7 IN LIST
273 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_GP3ZHHJ_GP3',min,min,min,min),('ZH_SRC_GP3ZHHJ_GP3',max,max,max,max))
274 #HEAP TABLE: [7190, 1, 625]; table_no(0) full(FALSE), mpp_full(0) autoid(FALSE) sites(-)
275 #PRJT2: [7190, 1, 625]; exp_num(5), is_atom(FALSE)
276 #HAGR2: [7190, 1, 625]; grp_num(1), sfun_num(4), MEM_USED(0KB), DISK_USED(0KB), distinct_flag[0,0,0,0]; slave_empty(0) keys(DMTEMPVIEW_894303450.GP3ZZHDM)
277 #PRJT2: [4425, 13877798, 625]; exp_num(5), is_atom(FALSE)
278 #SLCT2: [4425, 13877798->0, 625]; exp11 <> '0'
279 #HASH RIGHT JOIN2: [3939, 14608209->16434864, 625]; key_num(2); col_num(5); MEM_USED(12463KB), DISK_USED(0KB); join condition((L.C_DATA_IDF = 'ZH_SRC_O32ZJCJ_O32' AND L.D_D
EALDATE = exp11)) KEY(K.C_FIELD8=L.C_FIELD7 AND K.C_FIELD3=L.C_FIELD8)
280 #HASH LEFT JOIN2: [5, 1->998, 348]; key_num(1); col_num(3); partition_keys_num(0); mix(0); MEM_USED(12463KB), DISK_USED(0KB) KEY(K.C_PORT_CODE=I.GP3ZZHDM)
281 #PRJT2: [4, 1->998, 252]; exp_num(3), is_atom(FALSE)
282 #DISTINCT: [4, 1->998, 252], MEM_USED(46KB), DISK_USED(0KB)
283 #UNION FOR OR2: [3, 4->1000, 252]; key_num(1)
284 #UNION FOR OR2: [2, 3->533, 252]; key_num(1)
285 #UNION FOR OR2: [1, 2->492, 252]; key_num(1)
286 #PARALLEL: [1, 1->145, 252]; scan_type(EQU)
287 #SLCT2: [1, 1->145, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'F' AND T_M_T_SYNTH.C_PORT_CODE < 'G')
288 #BLKUP2: [1, 1->5960, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
289 #SSEK2: [1, 1->5960, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,ma
x,max))
290 #PARALLEL: [1, 1->347, 252]; scan_type(EQU)
291 #SLCT2: [1, 1->347, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'B' AND T_M_T_SYNTH.C_PORT_CODE < 'C')
292 #BLKUP2: [1, 1->5960, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
293 #SSEK2: [1, 1->5960, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,ma
x,max))
294 #PARALLEL: [1, 1->41, 252]; scan_type(EQU)
295 #SLCT2: [1, 1->41, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'C' AND T_M_T_SYNTH.C_PORT_CODE < 'D')
296 #BLKUP2: [1, 1->5960, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
297 #SSEK2: [1, 1->5960, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,max,
max))
298 #PARALLEL: [1, 1->467, 252]; scan_type(EQU)
299 #SLCT2: [1, 1->467, 252]; (T_M_T_SYNTH.C_FIELD1 = '1' AND T_M_T_SYNTH.C_PORT_CODE >= 'E' AND T_M_T_SYNTH.C_PORT_CODE < 'F')
300 #BLKUP2: [1, 1->5960, 252]; IDX_M_T_SYNTH6(T_M_T_SYNTH)
301 #SSEK2: [1, 1->5960, 252]; scan_type(ASC), IDX_M_T_SYNTH6(T_M_T_SYNTH), scan_range[('ZH_SRC_ZJZHB_OMAS',min,min,min,min),('ZH_SRC_ZJZHB_OMAS',max,max,max,ma
x))
302 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
303 #DISTINCT: [1, 1, 96], MEM_USED(0KB), DISK_USED(0KB)
304 #SLCT2: [1, 1->0, 96]; (T_OCP_ETL_PORT_MAP.GP3ZZHDM <> '不设子组合' AND T_OCP_ETL_PORT_MAP.GP3ZHDM <> T_OCP_ETL_PORT_MAP.GP3ZZHDM)
305 #BLKUP2: [1, 2->2, 96]; IDX_OCP_ETL_PORT_MAP1(T_OCP_ETL_PORT_MAP)
306 #SSEK2: [1, 2->2, 96]; scan_type(ASC), IDX_OCP_ETL_PORT_MAP1(T_OCP_ETL_PORT_MAP), scan_range[('FO1K11',min),('FO1K11',max))
307 #PARALLEL: [2361, 14608209->16434864, 277]; scan_type(FULL)
308 #CSCN2: [2361, 14608209->16434864, 277]; INDEX33585627(T_M_T_SYNTH)
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
391535 logical reads
81508 physical reads
0 redo size
138 bytes sent to client
2749 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
83260 io wait time(ms)
109803 exec time(ms)
used time: 00:01:49.348. Execute id is 13571902.
分析可知SLCT过滤掉了所有数据。where条件后使外连接的右表，应该可以过滤掉所有数据，猜测是nvl的原因。果然，改写sql变为内连接就会很快。
sql改写
改写后使用HASH INNER JOIN.
使用hint OUTER_CVT_INNER_PULL_UP_COND_FLAG=1不生效.
oracle执行计划
Plan Hash Value : 3292358869
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 149 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | VW_FOJ_0 | 5 | | 149 | 00:00:01 |
| * 3 | HASH JOIN FULL OUTER | | 5 | 320 | 149 | 00:00:01 |
| 4 | VIEW | | 1 | 39 | 73 | 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 412 | 73 | 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 410 | 72 | 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 296 | 71 | 00:00:01 |
| 8 | NESTED LOOPS | | 4 | 160 | 71 | 00:00:01 |
| * 9 | TABLE ACCESS BY INDEX ROWID BATCHED | T_P_AB_PORT | 1 | 28 | 2 | 00:00:01 |
| * 10 | INDEX RANGE SCAN | IDX_T_P_AB_PORT4 | 1 | | 1 | 00:00:01 |
| 11 | VIEW | | 5 | 60 | 69 | 00:00:01 |
| 12 | SORT UNIQUE | | 5 | 75 | 69 | 00:00:01 |
| * 13 | TABLE ACCESS FULL | T_OCP_ETL_PORT_MAP | 6 | 90 | 68 | 00:00:01 |
| * 14 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | T_M_T_ZQCC | 1 | 256 | 0 | 00:00:01 |
| * 15 | INDEX RANGE SCAN | IDX_M_T_ZQCC | 1 | | 0 | 00:00:01 |
| * 16 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | T_M_T_SYNTH | 1 | 114 | 1 | 00:00:01 |
| * 17 | INDEX SKIP SCAN | IDX_M_T_SYNTH | 1 | | 1 | 00:00:01 |
| 18 | VIEW PUSHED PREDICATE | VW_DCL_C914E416 | 1 | 2 | 1 | 00:00:01 |
| * 19 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | T_M_T_SYNTH | 1 | 51 | 1 | 00:00:01 |
| * 20 | INDEX RANGE SCAN | IDX_M_T_SYNTH2 | 1 | | 1 | 00:00:01 |
| 21 | VIEW | | 4 | 100 | 76 | 00:00:01 |
| * 22 | VIEW | | 4 | 140 | 76 | 00:00:01 |
| 23 | UNPIVOT | | | | | |
| 24 | VIEW | | 1 | 64 | 74 | 00:00:01 |
| 25 | HASH GROUP BY | | 1 | 414 | 74 | 00:00:01 |
| * 26 | FILTER | | | | | |
| * 27 | HASH JOIN OUTER | | 1 | 414 | 73 | 00:00:01 |
| 28 | PARTITION HASH ALL | | 1 | 202 | 2 | 00:00:01 |
| 29 | TABLE ACCESS FULL | T_M_T_SYNTH | 1 | 202 | 2 | 00:00:01 |
| 30 | VIEW | | 1 | 212 | 71 | 00:00:01 |
| * 31 | HASH JOIN OUTER | | 1 | 228 | 71 | 00:00:01 |
| 32 | JOIN FILTER CREATE | :BF0000 | 1 | 216 | 2 | 00:00:01 |
| 33 | VIEW | | 1 | 216 | 2 | 00:00:01 |
| 34 | HASH UNIQUE | | 1 | 330 | 2 | 00:00:01 |
| * 35 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | T_M_T_SYNTH | 1 | 330 | 1 | 00:00:01 |
| * 36 | INDEX SKIP SCAN | IDX_M_T_SYNTH | 1 | | 1 | 00:00:01 |
| 37 | VIEW | | 6 | 72 | 69 | 00:00:01 |
| 38 | HASH UNIQUE | | 6 | 90 | 69 | 00:00:01 |
| 39 | JOIN FILTER USE | :BF0000 | 6 | 90 | 68 | 00:00:01 |
| * 40 | TABLE ACCESS FULL | T_OCP_ETL_PORT_MAP | 6 | 90 | 68 | 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("T"."ZHDM"="N"."GP3ZZHDM" AND "T"."C_FIELD5"="N"."C_FIELD5")
* 9 - filter("A"."D_CLOSE">TO_DATE(' 2024-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."D_BUILD"<=TO_DATE(' 2024-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 10 - access("A"."C_DV_PROD_STATE"='PS4' AND "A"."C_PORT_CODE"='FO1K11')
* 13 - filter("GP3ZHDM"='FO1K11' AND "GP3ZZHDM"<>'不设子组合' AND "GP3ZHDM"<>"GP3ZZHDM" AND "GP3ZZHDM"<>'FO1K11')
* 14 - filter("Z"."GP3ZZHDM"="C"."C_PORT_CODE" AND "C"."C_FIELD4"='AD1' AND "C"."C_FIELD1"='TRES' AND "C"."C_FIELD2"='PROPRE' AND "C"."C_PORT_CODE" NOT LIKE '%11' AND ("C"."C_PORT_CODE" LIKE 'F%' OR
"C"."C_PORT_CODE" LIKE 'B%' OR "C"."C_PORT_CODE" LIKE 'C%' OR "C"."C_PORT_CODE" LIKE 'E%') AND "C"."N_AMOUNT"<>0 AND NVL("C"."C_SEC_CODE",'0')<>'0')
* 15 - access("C"."D_STOCK"=TO_DATE(' 2024-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C"."C_DATA_IDF"='CC_SRC_GP3CJ_VIEW')
* 16 - filter("C"."C_PORT_CODE"="B"."C_FIELD1")
* 17 - access("B"."C_DATA_IDF"='ZH_SRC_GP3CJ_GP3')
* 17 - filter("B"."C_DATA_IDF"='ZH_SRC_GP3CJ_GP3')
* 19 - filter("E"."C_DATA_IDF"='ZH_SRC_GP3ZHHJ_GP3' AND (("E"."C_FIELD7"='KTWJZZ' OR "E"."C_FIELD7"='TKMFF' OR "E"."C_FIELD7"='TKMMF') AND ("E"."C_PORT_CODE" LIKE 'E%' OR "E"."C_PORT_CODE" LIKE 'F%')
OR ("E"."C_FIELD7"='TKINS' OR "E"."C_FIELD7"='TRDINS') AND "E"."C_PORT_CODE" LIKE 'B%' OR "E"."C_FIELD7"='TKINS' AND "E"."C_PORT_CODE" LIKE 'C%'))
* 20 - access("E"."C_PORT_CODE"="B"."C_FIELD1")
* 22 - filter("unpivot_view_027"."N_AMOUNT" IS NOT NULL)
* 26 - filter(NVL("from$_subquery$_020"."GP3ZZHDM",'0')<>'0')
* 27 - access("K"."C_FIELD8"(+)="L"."C_FIELD7" AND "K"."C_FIELD3"(+)="L"."C_FIELD8" AND "L"."D_DEALDATE"=CASE WHEN ("K"."C_FIELD3"(+) IS NOT NULL) THEN TO_DATE(' 2024-03-21 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') ELSE TO_DATE(' 2024-03-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss') END AND "L"."C_DATA_IDF"=CASE WHEN ("K"."C_FIELD3"(+) IS NOT NULL) THEN 'ZH_SRC_O32ZJCJ_O32' ELSE 'ZH_SRC_O32ZJCJ_O32'
END )
* 31 - access("I"."GP3ZZHDM"(+)="K"."C_PORT_CODE")
* 35 - filter("C_FIELD1"='1' AND ("C_PORT_CODE" LIKE 'F%' OR "C_PORT_CODE" LIKE 'B%' OR "C_PORT_CODE" LIKE 'C%' OR "C_PORT_CODE" LIKE 'E%'))
* 36 - access("C_DATA_IDF"='ZH_SRC_ZJZHB_OMAS')
* 36 - filter("C_DATA_IDF"='ZH_SRC_ZJZHB_OMAS')
* 40 - filter("GP3ZHDM"='FO1K11' AND "GP3ZZHDM"<>'不设子组合' AND "GP3ZHDM"<>"GP3ZZHDM" AND "GP3ZZHDM"<>'FO1K11' AND SYS_OP_BLOOM_FILTER(:BF0000,"GP3ZZHDM"))
