sql
SELECT
count(0)
FROM
(
SELECT --/*+ view_filter_merging(137)*/
b.C_FIELD1 ZCDM ,
b.GP3ZZHDM ZZHDM,
b.C_FIELD5 KJFL ,
b.N_FIELD2 QZJG ,
b.D_FIELD1 KSRQ ,
b.C_FIELD11 JZRQ,
b.C_FIELD7 ZCLX ,
c.N_FIELD2 CCSL
FROM
T_P_AB_PORT a
right JOIN
(
SELECT
z.GP3ZZHDM ,
z.GP3ZHDM ,
d.C_FIELD1 ,
d.C_FIELD5 ,
d.N_FIELD2 ,
d.D_FIELD1 ,
d.C_FIELD11,
d.C_FIELD7
FROM
(
SELECT DISTINCT
C_PORT_CODE GP3ZHDM,
C_FIELD1 GP3ZZHDM
FROM
T_M_T_PORT_RELA
WHERE
C_RELA_TYPE = 'RELA_BASIC_ZZHCX'
AND C_DATA_IDF = 'RELA_PRO_ACC'
and c_field1 <> '不设子组合'
)
z
RIGHT JOIN
(
SELECT
*
FROM
(
select
case when C_FIELD11 = '永久' then null else to_date(C_FIELD11, 'dd-mon-yy', 'NLS_Date_Language = American') end as jzrq,
C_PORT_CODE ,
C_FIELD1 ,
C_FIELD5 ,
N_FIELD2 ,
D_FIELD1 ,
C_FIELD11 ,
C_FIELD7 ,
C_DATA_IDF ,
D_DEALDATE
from
T_M_T_SYNTH -- index IDX_M_T_SYNTH2
)
WHERE
(
C_FIELD11 = '永久'
OR jzrq > TO_DATE('2024-04-08', 'yyyy-MM-dd')
)
AND D_DEALDATE = TO_DATE('2024-04-08', 'yyyy-MM-dd')
AND C_DATA_IDF = 'ZH_SRC_FORCOU'
)
d
ON
z.GP3ZZHDM = d.C_PORT_CODE
WHERE
z.GP3ZHDM = 'BA0211'
)
b ON a.C_PORT_CODE = b.GP3ZHDM
LEFT JOIN T_M_T_SYNTH c
ON
a.C_PORT_CODE = c.C_PORT_CODE
AND b.C_FIELD1 = c.C_FIELD1
AND c.C_FIELD7 NOT IN ('FUTU', 'CAT')
AND c.C_DATA_IDF = 'ZH_SRC_CWJJCB_HSXT'
WHERE
a.C_PORT_CODE = 'BA0211'
AND a.D_BUILD <= to_date('2024-04-08', 'yyyy-MM-dd')
AND a.D_CLOSE > to_date('2024-04-08', 'yyyy-MM-dd')
AND a.C_DV_PROD_STATE = 'PS4'
)
索引
CREATE INDEX "IDX_M_T_SYNTH" ON "OCP"."T_M_T_SYNTH"("D_DEALDATE" ASC,"C_DATA_IDF" ASC) GLOBAL STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH1" ON "OCP"."T_M_T_SYNTH"("D_DEALDATE" ASC) GLOBAL STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH2" ON "OCP"."T_M_T_SYNTH"("C_PORT_CODE" ASC) GLOBAL STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH3" ON "OCP"."T_M_T_SYNTH"("D_FIELD1" ASC) GLOBAL STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH4" ON "OCP"."T_M_T_SYNTH"("C_DATA_IDF" ASC,"C_FIELD1" ASC) STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH5" ON "OCP"."T_M_T_SYNTH"("C_DATA_IDF" ASC,"C_PORT_CODE" ASC,"D_DEALDATE" ASC) STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH11" ON "OCP"."T_M_T_SYNTH"("C_DATA_IDF" ASC,"C_FIELD1" ASC,"C_FIELD7" ASC,"D_DEALDATE" ASC,"C_FIELD8" ASC) STORAGE(ON "OCP", CLUSTERBTR) ;
CREATE INDEX "IDX_M_T_SYNTH6" ON "OCP"."T_M_T_SYNTH"("C_DATA_IDF" ASC,"D_DEALDATE" ASC,"C_FIELD7" ASC,"C_FIELD11" ASC,"D_FIELD1" ASC) STORAGE(ON "OCP", CLUSTERBTR) ;
执行计划对比
1 #NSET2: [2, 1, 658]
2 #PRJT2: [2, 1, 658]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [2, 1, 658]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #INDEX JOIN LEFT JOIN2: [2, 1, 658] join condition((A.C_PORT_CODE = C.C_PORT_CODE AND NOT(C.C_FIELD7 IN LIST))) ret_null(0)
5 #SLCT2: [1, 1, 658]; (A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var3 AND A.D_CLOSE > var3)
6 #NEST LOOP INDEX JOIN2: [1, 1, 658]
7 #PRJT2: [1, 1, 536]; exp_num(2), is_atom(FALSE)
8 #SLCT2: [1, 1, 536]; Z.GP3ZZHDM = D.C_PORT_CODE
9 #NEST LOOP INNER JOIN2: [1, 1, 536]; [with var]
10 #PRJT2: [1, 1, 192]; exp_num(2), is_atom(FALSE)
11 #DISTINCT: [1, 1, 192]
12 #SLCT2: [1, 1, 192]; T_M_T_PORT_RELA.C_FIELD1 <> '不设子组合'
13 #SSEK2: [1, 1, 192]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZZHCX','RELA_PRO_ACC','BA0211',min),('RELA_BASIC_ZZHCX','RELA_PRO_ACC','BA0211',max))
14 #PRJT2: [1, 1, 344]; exp_num(2), is_atom(FALSE)
15 #PRJT2: [1, 1, 344]; exp_num(2), is_atom(FALSE)
16 #PARALLEL: [1, 1, 344]; scan_type(EQU), key_num(1, 0, 0), simple(0)
17 #SLCT2: [1, 1, 344]; (T_M_T_SYNTH.C_DATA_IDF = 'ZH_SRC_FORCOU' AND (T_M_T_SYNTH.C_FIELD11 = '永久' OR exp_bool_case > var4) AND T_M_T_SYNTH.C_PORT_CODE = var2)
18 #BLKUP2: [1, 1, 344]; IDX_M_T_SYNTH1(T_M_T_SYNTH)
19 #SSEK2: [1, 1, 344]; scan_type(ASC), IDX_M_T_SYNTH1(T_M_T_SYNTH), scan_range[exp11,exp11]
20 #BLKUP2: [1, 1, 48]; IDX_P_AB_PORT(A)
21 #SLCT2: [1, 1, 48]; A.C_PORT_CODE = 'BA0211'
22 #SSEK2: [1, 1, 48]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT as A), scan_range[B.GP3ZHDM,B.GP3ZHDM]
23 #PARALLEL: [1, 1, 96]; scan_type(EQU), key_num(1, 0, 0), simple(0)
24 #BLKUP2: [1, 1, 96]; IDX_M_T_SYNTH4(C)
25 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as C), scan_range[('ZH_SRC_CWJJCB_HSXT',B.C_FIELD1),('ZH_SRC_CWJJCB_HSXT',B.C_FIELD1)]
view_filter_merging=1时才将 C_PORT_CODE条件下放。
1 #NSET2: [1, 1, 658]
2 #PRJT2: [1, 1, 658]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1, 1, 658]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4 #INDEX JOIN LEFT JOIN2: [1, 1, 658] join condition((A.C_PORT_CODE = C.C_PORT_CODE AND NOT(C.C_FIELD7 IN LIST))) ret_null(0)
5 #SLCT2: [1, 1, 658]; (A.C_DV_PROD_STATE = 'PS4' AND A.D_BUILD <= var3 AND A.D_CLOSE > var3)
6 #NEST LOOP INDEX JOIN2: [1, 1, 658]
7 #PRJT2: [1, 1, 536]; exp_num(2), is_atom(FALSE)
8 #SLCT2: [1, 1, 536]; Z.GP3ZZHDM = D.C_PORT_CODE
9 #NEST LOOP INNER JOIN2: [1, 1, 536]; [with var]
10 #PRJT2: [1, 1, 192]; exp_num(2), is_atom(FALSE)
11 #DISTINCT: [1, 1, 192]
12 #SLCT2: [1, 1, 192]; T_M_T_PORT_RELA.C_FIELD1 <> '不设子组合'
13 #SSEK2: [1, 1, 192]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZZHCX','RELA_PRO_ACC','BA0211',min),('RELA_BASIC_ZZHCX','RELA_PRO_ACC','BA0211',max))
14 #PRJT2: [1, 1, 344]; exp_num(2), is_atom(FALSE)
15 #SLCT2: [1, 1, 344]; (DMTEMPVIEW_890686376.C_FIELD11 = '永久' OR DMTEMPVIEW_890686376.JZRQ > var4)
16 #PRJT2: [1, 1, 344]; exp_num(4), is_atom(FALSE)
17 #PARALLEL: [1, 1, 344]; scan_type(EQU), key_num(1, 0, 0), simple(0)
18 #BLKUP2: [1, 1, 344]; IDX_M_T_SYNTH5(T_M_T_SYNTH)
19 #SSEK2: [1, 1, 344]; scan_type(ASC), IDX_M_T_SYNTH5(T_M_T_SYNTH), scan_range[('ZH_SRC_FORCOU',var2,exp11),('ZH_SRC_FORCOU',var2,exp11)]
20 #BLKUP2: [1, 1, 48]; IDX_P_AB_PORT(A)
21 #SLCT2: [1, 1, 48]; A.C_PORT_CODE = 'BA0211'
22 #SSEK2: [1, 1, 48]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT as A), scan_range[B.GP3ZHDM,B.GP3ZHDM]
23 #PARALLEL: [1, 1, 96]; scan_type(EQU), key_num(1, 0, 0), simple(0)
24 #BLKUP2: [1, 1, 96]; IDX_M_T_SYNTH4(C)
25 #SSEK2: [1, 1, 96]; scan_type(ASC), IDX_M_T_SYNTH4(T_M_T_SYNTH as C), scan_range[('ZH_SRC_CWJJCB_HSXT',B.C_FIELD1),('ZH_SRC_CWJJCB_HSXT',B.C_FIELD1)]
oracle执行计划
Plan Hash Value : 2584303792
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | VM_NWVW_1 | 1 | | 22 | 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 206 | 22 | 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 206 | 21 | 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 136 | 14 | 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 79 | 4 | 00:00:01 |
| * 7 | TABLE ACCESS BY INDEX ROWID | T_P_AB_PORT | 1 | 40 | 2 | 00:00:01 |
| * 8 | INDEX UNIQUE SCAN | IDX_P_AB_PORT | 1 | | 1 | 00:00:01 |
| * 9 | INDEX RANGE SCAN | IDX_M_T_PORT_RELA1 | 1 | 39 | 2 | 00:00:01 |
| * 10 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | T_M_T_SYNTH | 1 | 57 | 10 | 00:00:01 |
| * 11 | INDEX RANGE SCAN | IDX_M_T_SYNTH2 | 36 | | 2 | 00:00:01 |
| * 12 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | T_M_T_SYNTH | 1 | 70 | 7 | 00:00:01 |
| * 13 | INDEX RANGE SCAN | IDX_M_T_SYNTH2 | 22 | | 2 | 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 7 - filter("A"."C_DV_PROD_STATE"='PS4' AND "A"."D_CLOSE">TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."D_BUILD"<=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 8 - access("A"."C_PORT_CODE"='BA0211')
* 9 - access("C_PORT_CODE"='BA0211' AND "C_RELA_TYPE"='RELA_BASIC_ZZHCX' AND "C_DATA_IDF"='RELA_PRO_ACC')
* 9 - filter("C_RELA_TYPE"='RELA_BASIC_ZZHCX' AND "C_DATA_IDF"='RELA_PRO_ACC' AND "A"."C_PORT_CODE"="C_PORT_CODE" AND "C_FIELD1"<>'不设子组合')
* 10 - filter("D_DEALDATE"=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("C_FIELD11"='永久' OR CASE "C_FIELD11" WHEN '永久' THEN NULL ELSE
TO_DATE("C_FIELD11",'dd-mon-yy','nls_date_language=''AMERICAN''') END >TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND "C_DATA_IDF"='ZH_SRC_FORCOU')
* 11 - access("C_FIELD1"="C_PORT_CODE")
* 11 - filter("C_PORT_CODE"<>'不设子组合')
* 12 - filter("C_FIELD1"="C"."C_FIELD1"(+) AND "C"."C_DATA_IDF"(+)='ZH_SRC_CWJJCB_HSXT' AND "C"."C_FIELD7"(+)<>'FUTU' AND "C"."C_FIELD7"(+)<>'CAT')
* 13 - access("C"."C_PORT_CODE"(+)='BA0211')