sql
select count(0) from (select distinct cc.*,
to_char(a1.D_DEALDATE, 'yyyy-MM-dd') D_DEALDATE,
a1.C_DATA_IDF,
trim(a1.C_FIELD7) ZQFL,
trim(a1.C_FIELD8) C_FIELD8,
trim(to_char(a1.D_FIELD1, 'yyyy-MM-dd')) DQR,
trim(a1.C_FIELD11) RATE,
trim(a1.C_FIELD12) JXFS,
trim(a2.C_FIELD8) ZQFLCSZNAME
from (select c.C_PORT_CODE,
c.C_FIELD1,
c.N_AMOUNT,
c.C_SEC_NAME,
nvl(trim(c1.N_AMOUNT), 0) QYRCCSL
from (select c.C_PORT_CODE, c.C_FIELD1, c.N_AMOUNT, s.C_SEC_NAME
from T_M_T_ZQCC index IDX_M_T_ZQCC1 c
left join T_M_T_SECINFO index IDX_M_T_SECINFO1 s
on c.C_SEC_CODE = s.C_SEC_MKT_CODE
and c.C_SEC_VAR_CODE = s.C_SEC_VAR_CODE
and c.C_MKT_CODE = s.C_MKT_CODE
and s.N_CHECK_STATE = 1
where c.C_DATA_IDF = 'CC_SRC_GP3_VIEW'
and c.D_STOCK = to_date('2024-04-08', 'yyyy-MM-dd')
and c.C_PORT_CODE = 'BI0111'
and c.N_CHECK_STATE =1-- '1'
and c.C_FIELD2 NOT IN ('MPLIV', 'PPLIV')
and c.C_SEC_VAR_CODE LIKE 'ZQ%') c
left join (select C_PORT_CODE, C_FIELD1, N_AMOUNT
from T_M_T_ZQCC index IDX_M_T_ZQCC1
where C_DATA_IDF = 'CC_SRC_GP3_VIEW'
and D_STOCK = to_date('2024-04-07', 'yyyy-MM-dd')
and C_PORT_CODE = 'BI0111'
and N_CHECK_STATE = '1'
and C_FIELD2 NOT IN ('MPLIV', 'PPLIV')
and C_SEC_VAR_CODE LIKE 'ZQ%') c1
on c.C_PORT_CODE = c1.C_PORT_CODE
and c.C_FIELD1 = c1.C_FIELD1) cc
left join (select D_DEALDATE,
C_DATA_IDF,
C_FIELD7,
C_FIELD8,
D_FIELD1,
C_FIELD11,
C_FIELD12,
C_FIELD1,
'OCR\' || C_FIELD7 || '\' || C_FIELD8 AS TEMP
from T_M_T_SECDATA
where D_DEALDATE = to_date('2024-04-08', 'yyyy-MM-dd')
and C_DATA_IDF = 'ZSJ_SRC_ZQFL_GP3'
and C_FIELD7 in ('AIBN', 'APBN', 'TVAS')
and N_CHECK_STATE = 1
union all
select D_DEALDATE,
C_DATA_IDF,
C_FIELD7,
C_FIELD8,
D_FIELD1,
C_FIELD11,
C_FIELD12,
C_FIELD1,
'OCR\' || C_FIELD7 || '\' || C_FIELD8 AS TEMP
from T_M_T_SECDATA
where D_DEALDATE = to_date('2024-04-08', 'yyyy-MM-dd')-1
and C_DATA_IDF = 'ZSJ_SRC_ZQFL_GP3'
and C_FIELD7 in ('AIBN', 'APBN', 'TVAS')
and N_CHECK_STATE = 1
union all
select D_DEALDATE,
C_DATA_IDF,
C_FIELD7,
C_FIELD8,
D_FIELD1,
C_FIELD11,
C_FIELD12,
C_FIELD1,
null AS TEMP
from T_M_T_SECDATA
where D_DEALDATE = to_date('2024-04-08', 'yyyy-MM-dd')
and C_DATA_IDF = 'ZSJ_SRC_GDLLB_GP3'
and C_FIELD8 = 'FIX'
and N_CHECK_STATE = 1
union all
select D_DEALDATE,
C_DATA_IDF,
C_FIELD7,
C_FIELD8,
D_FIELD1,
C_FIELD11,
C_FIELD12,
C_FIELD1,
null AS TEMP
from T_M_T_SECDATA
where D_DEALDATE = to_date('2024-04-08', 'yyyy-MM-dd')-1
and C_DATA_IDF = 'ZSJ_SRC_GDLLB_GP3'
and C_FIELD8 = 'FIX'
and N_CHECK_STATE = 1
) a1
on cc.C_FIELD1 = a1.C_FIELD1
left join T_M_T_SECDATA a2
on a2.C_FIELD7 = a1.temp
and a1.D_DEALDATE = a2.D_DEALDATE
and a2.N_CHECK_STATE = '1'
and a2.C_DATA_IDF = 'ZSJ_SRC_ZQFLCSZ_GP3') tmp_count
CREATE INDEX "IDX_M_T_ZQCC1" ON "OCP"."T_M_T_ZQCC"("C_DATA_IDF" ASC,"D_STOCK" ASC,"C_PORT_CODE" ASC,"N_CHECK_STATE" ASC,"C_FIELD1" ASC,"C_SEC_VAR_CODE" ASC) STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SECDATA4" ON "OCP"."T_M_T_SECDATA"("D_DEALDATE" ,"C_DATA_IDF" ASC,"N_CHECK_STATE" ASC,"C_FIELD7" ASC ) online parallel 16
N_CHECK_STATE 为字符串类型，传值为数值类型。
执行计划
1 #NSET2: [6, 1, 1120]
2 #PRJT2: [6, 1, 1120]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [6, 1, 1120]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #PRJT2: [6, 1, 1120]; exp_num(0), is_atom(FALSE)
5 #DISTINCT: [6, 1, 1120]
6 #PRJT2: [5, 3, 1120]; exp_num(13), is_atom(FALSE)
7 #INDEX JOIN LEFT JOIN2: [5, 3, 1120] ret_null(0)
8 #HASH LEFT JOIN2: [4, 3, 1120]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(CC.C_FIELD1=A1.C_FIELD1)
9 #PRJT2: [1, 1, 758]; exp_num(5), is_atom(FALSE)
10 #NEST LOOP LEFT JOIN2: [1, 1, 758]; join condition((C.C_PORT_CODE = C1.C_PORT_CODE AND C.C_FIELD1 = C1.C_FIELD1))[with var] partition_keys_num(0) ret_null(0)
11 #PRJT2: [1, 1, 427]; exp_num(4), is_atom(FALSE)
12 #INDEX JOIN LEFT JOIN2: [1, 1, 427] join condition((C.C_SEC_VAR_CODE = S.C_SEC_VAR_CODE AND exp_cast(S.N_CHECK_STATE) = 1)) ret_null(0)
13 #PARALLEL: [1, 1, 427]; scan_type(EQU), key_num(1, 0, 0), simple(0)
14 #HASH RIGHT SEMI JOIN2: [1, 1, 427]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_891038964.colname=C.C_FIELD2) KEY_NULL_EQU(0)
15 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
16 #BLKUP2: [1, 1, 427]; IDX_M_T_ZQCC1(C)
17 #SLCT2: [1, 1, 427]; (C.C_SEC_VAR_CODE >= 'ZQ' AND C.C_SEC_VAR_CODE < 'ZR' AND exp_cast(C.N_CHECK_STATE) = 1)
18 #SSEK2: [1, 1, 427]; scan_type(ASC), IDX_M_T_ZQCC1(T_M_T_ZQCC as C), scan_range[('CC_SRC_GP3_VIEW',exp11,'BI0111',min,min,min),('CC_SRC_GP3_VIEW',exp11,'BI0111',max,max,max))
19 #BLKUP2: [1, 1, 96]; IDX_M_T_SECINFO1(S)
20 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SECINFO1(T_M_T_SECINFO as S), scan_range[(C.C_SEC_CODE,C.C_MKT_CODE),(C.C_SEC_CODE,C.C_MKT_CODE)]
21 #PRJT2: [1, 1, 331]; exp_num(3), is_atom(FALSE)
22 #PARALLEL: [1, 1, 331]; scan_type(EQU), key_num(1, 0, 0), simple(0)
23 #HASH RIGHT SEMI JOIN2: [1, 1, 331]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_891038965.colname=T_M_T_ZQCC.C_FIELD2) KEY_NULL_EQU(0)
24 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
25 #BLKUP2: [1, 1, 331]; IDX_M_T_ZQCC1(T_M_T_ZQCC)
26 #SLCT2: [1, 1, 331]; (T_M_T_ZQCC.C_SEC_VAR_CODE >= 'ZQ' AND T_M_T_ZQCC.C_SEC_VAR_CODE < 'ZR' AND exp_cast(T_M_T_ZQCC.N_CHECK_STATE) = 1 AND T_M_T_ZQCC.C_PORT_CODE = var6 AND T_M_T_ZQCC.C_FIELD1 = var7)
27 #SSEK2: [1, 1, 331]; scan_type(ASC), IDX_M_T_ZQCC1(T_M_T_ZQCC), scan_range[('CC_SRC_GP3_VIEW',exp11,'BI0111',min,min,min),('CC_SRC_GP3_VIEW',exp11,'BI0111',max,max,max))
28 #PRJT2: [2, 4, 362]; exp_num(9), is_atom(FALSE)
29 #UNION ALL: [2, 4, 362]
30 #PRJT2: [1, 3, 362]; exp_num(9), is_atom(FALSE)
31 #UNION ALL: [1, 3, 362]
32 #PRJT2: [1, 2, 362]; exp_num(9), is_atom(FALSE)
33 #UNION ALL: [1, 2, 362]
34 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
35 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA4(T_M_T_SECDATA)
36 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD7 IN LIST AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
37 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA4(T_M_T_SECDATA), scan_range[(exp11,'ZSJ_SRC_ZQFL_GP3',min,min),(exp11,'ZSJ_SRC_ZQFL_GP3',max,max))
38 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
39 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA4(T_M_T_SECDATA)
40 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD7 IN LIST AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
41 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA4(T_M_T_SECDATA), scan_range[(exp11-exp_cast(1),'ZSJ_SRC_ZQFL_GP3',min,min),(exp11-exp_cast(1),'ZSJ_SRC_ZQFL_GP3',max,max))
42 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
43 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD8 = 'FIX' AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
44 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA(T_M_T_SECDATA)
45 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA(T_M_T_SECDATA), scan_range[(exp11,'ZSJ_SRC_GDLLB_GP3'),(exp11,'ZSJ_SRC_GDLLB_GP3')]
46 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
47 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD8 = 'FIX' AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
48 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA(T_M_T_SECDATA)
49 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA(T_M_T_SECDATA), scan_range[(exp11-exp_cast(1),'ZSJ_SRC_GDLLB_GP3'),(exp11-exp_cast(1),'ZSJ_SRC_GDLLB_GP3')]
50 #BLKUP2: [1, 1, 157]; IDX_M_T_SECDATA4(A2)
51 #SSEK2: [1, 1, 157]; scan_type(ASC), IDX_M_T_SECDATA4(T_M_T_SECDATA as A2), scan_range[(A1.D_DEALDATE,'ZSJ_SRC_ZQFLCSZ_GP3','1',A1.TEMP),(A1.D_DEALDATE,'ZSJ_SRC_ZQFLCSZ_GP3','1',A1.TEMP)]
由于类型不匹配即使等值也无法在SSEK时定位到。
将1修改为'1'后的执行计划
1 #NSET2: [6, 1, 1120]
2 #PRJT2: [6, 1, 1120]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [6, 1, 1120]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #PRJT2: [6, 1, 1120]; exp_num(0), is_atom(FALSE)
5 #DISTINCT: [6, 1, 1120]
6 #PRJT2: [5, 3, 1120]; exp_num(13), is_atom(FALSE)
7 #INDEX JOIN LEFT JOIN2: [5, 3, 1120] ret_null(0)
8 #HASH LEFT JOIN2: [4, 3, 1120]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(CC.C_FIELD1=A1.C_FIELD1)
9 #PRJT2: [1, 1, 758]; exp_num(5), is_atom(FALSE)
10 #NEST LOOP LEFT JOIN2: [1, 1, 758]; join condition((C.C_PORT_CODE = C1.C_PORT_CODE AND C.C_FIELD1 = C1.C_FIELD1))[with var] partition_keys_num(0) ret_null(0)
11 #PRJT2: [1, 1, 427]; exp_num(4), is_atom(FALSE)
12 #INDEX JOIN LEFT JOIN2: [1, 1, 427] join condition((C.C_SEC_VAR_CODE = S.C_SEC_VAR_CODE AND exp_cast(S.N_CHECK_STATE) = 1)) ret_null(0)
13 #PARALLEL: [1, 1, 427]; scan_type(EQU), key_num(1, 0, 0), simple(0)
14 #HASH RIGHT SEMI JOIN2: [1, 1, 427]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_891037856.colname=C.C_FIELD2) KEY_NULL_EQU(0)
15 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
16 #BLKUP2: [1, 1, 427]; IDX_M_T_ZQCC1(C)
17 #SLCT2: [1, 1, 427]; (C.C_SEC_VAR_CODE >= 'ZQ' AND C.C_SEC_VAR_CODE < 'ZR')
18 #SSEK2: [1, 1, 427]; scan_type(ASC), IDX_M_T_ZQCC1(T_M_T_ZQCC as C), scan_range[('CC_SRC_GP3_VIEW',exp11,'BI0111','1',min,min),('CC_SRC_GP3_VIEW',exp11,'BI0111','1',max,max))
19 #BLKUP2: [1, 1, 96]; IDX_M_T_SECINFO1(S)
20 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SECINFO1(T_M_T_SECINFO as S), scan_range[(C.C_SEC_CODE,C.C_MKT_CODE),(C.C_SEC_CODE,C.C_MKT_CODE)]
21 #PRJT2: [1, 1, 331]; exp_num(3), is_atom(FALSE)
22 #PARALLEL: [1, 1, 331]; scan_type(EQU), key_num(1, 0, 0), simple(0)
23 #HASH RIGHT SEMI JOIN2: [1, 1, 331]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_891037857.colname=T_M_T_ZQCC.C_FIELD2) KEY_NULL_EQU(0)
24 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
25 #BLKUP2: [1, 1, 331]; IDX_M_T_ZQCC1(T_M_T_ZQCC)
26 #SLCT2: [1, 1, 331]; T_M_T_ZQCC.C_PORT_CODE = var6
27 #SSEK2: [1, 1, 331]; scan_type(ASC), IDX_M_T_ZQCC1(T_M_T_ZQCC), scan_range[('CC_SRC_GP3_VIEW',exp11,'BI0111','1',var7,'ZQ'),('CC_SRC_GP3_VIEW',exp11,'BI0111','1',var7,'ZR'))
28 #PRJT2: [2, 4, 362]; exp_num(9), is_atom(FALSE)
29 #UNION ALL: [2, 4, 362]
30 #PRJT2: [1, 3, 362]; exp_num(9), is_atom(FALSE)
31 #UNION ALL: [1, 3, 362]
32 #PRJT2: [1, 2, 362]; exp_num(9), is_atom(FALSE)
33 #UNION ALL: [1, 2, 362]
34 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
35 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA4(T_M_T_SECDATA)
36 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD7 IN LIST AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
37 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA4(T_M_T_SECDATA), scan_range[(exp11,'ZSJ_SRC_ZQFL_GP3',min,min),(exp11,'ZSJ_SRC_ZQFL_GP3',max,max))
38 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
39 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA4(T_M_T_SECDATA)
40 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD7 IN LIST AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
41 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA4(T_M_T_SECDATA), scan_range[(exp11-exp_cast(1),'ZSJ_SRC_ZQFL_GP3',min,min),(exp11-exp_cast(1),'ZSJ_SRC_ZQFL_GP3',max,max))
42 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
43 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD8 = 'FIX' AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
44 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA(T_M_T_SECDATA)
45 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA(T_M_T_SECDATA), scan_range[(exp11,'ZSJ_SRC_GDLLB_GP3'),(exp11,'ZSJ_SRC_GDLLB_GP3')]
46 #PRJT2: [1, 1, 362]; exp_num(9), is_atom(FALSE)
47 #SLCT2: [1, 1, 362]; (T_M_T_SECDATA.C_FIELD8 = 'FIX' AND exp_cast(T_M_T_SECDATA.N_CHECK_STATE) = 1)
48 #BLKUP2: [1, 1, 362]; IDX_M_T_SECDATA(T_M_T_SECDATA)
49 #SSEK2: [1, 1, 362]; scan_type(ASC), IDX_M_T_SECDATA(T_M_T_SECDATA), scan_range[(exp11-exp_cast(1),'ZSJ_SRC_GDLLB_GP3'),(exp11-exp_cast(1),'ZSJ_SRC_GDLLB_GP3')]
50 #BLKUP2: [1, 1, 157]; IDX_M_T_SECDATA4(A2)
51 #SSEK2: [1, 1, 157]; scan_type(ASC), IDX_M_T_SECDATA4(T_M_T_SECDATA as A2), scan_range[(A1.D_DEALDATE,'ZSJ_SRC_ZQFLCSZ_GP3','1',A1.TEMP),(A1.D_DEALDATE,'ZSJ_SRC_ZQFLCSZ_GP3','1',A1.TEMP)]
修改类型后在索引定位时可以匹配到。
