sql
SELECT
port.C_PORT_CODE ,
port.jjmc ,
port.c_rela_content,
A.D_SELL ,
(
select
ta.d_hday
from
(
select
TRUNC(to_date('2024-04-03', 'yyyy-MM-dd'), 'yyyy') + ROWNUM - 20 as d_hday,
'TA' c_hday_code
from
dual connect by rowNum <= 406
)
ta
left join t_p_bi_hday_sub b
on
ta.d_hday = b.d_hday
and ta.c_hday_code = b.c_hday_code
where
ta.d_hday > A.D_sell
and b.c_date_type is null
and rownum = 1
)
D_SELL1 ,
A.D_CONFIRM ,
a.c_ds_code ,
a.c_data_idf ,
a.n_sell_amount,
a.n_sell_money ,
jz.n_dwjz ,
jz.n_amount
from
(
select distinct
R.C_PORT_CODE ,
R.C_FIELD4 JJMC,
r.c_rela_content
from
T_M_T_PORT_RELA r
where
R.C_FIELD5 NOT IN ('*', '6', '7', '8', '9')
AND R.d_FIELD1 <= TO_DATE('2024-04-03', 'yyyy-MM-dd')
AND R.C_DATA_IDF = 'RELA_PRO_TA'
AND R.C_RELA_TYPE = 'RELA_BASIC_TAXTCS'
)
port
left join T_M_T_CPXS A
on
a.c_port_code_os = port.c_port_code
and A.D_CONFIRM = TO_DATE('2024-04-08', 'yyyy-MM-dd')
and A.C_DS_CODE in ('036')
AND A.C_DATA_IDF = 'CPXS_SRC_TAJYSQ'
and NOT EXISTS
(
SELECT
1
FROM
T_M_T_CPXS X
WHERE
X.C_DATA_IDF = 'CPXS_SRC_TAJYQR'
AND X.C_FIELD4 = '2'
and x.C_DS_CODE in ('03', '13', '02', '16')
AND A.C_FIELD4 = X.C_FIELD7
)
left join
(
select
c_port_code,
n_amount ,
n_dwjz
from
(
select
c_port_code,
n_amount ,
n_dwjz ,
row_number() over(partition by c_port_code order by D_FIELD1 desc) rn
from
T_M_T_FUNDNAV
where
C_DATA_IDF = 'ZHJZ_SRC_TAXT'
and D_FIELD1 in (TO_DATE('2024-04-02', 'yyyy-MM-dd'), TO_DATE('2024-04-03', 'yyyy-MM-dd'))
)
t
where
rn = 1
)
jz
on
a.c_port_code = jz.c_port_code
DM执行计划
1 #NSET2: [939, 59, 470]
2 #PIPE2: [939, 59, 470]
3 #PIPE2: [384, 59, 470]
4 #PRJT2: [384, 59, 470]; exp_num(12), is_atom(FALSE)
5 #HASH LEFT JOIN2: [384, 59, 470]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.C_PORT_CODE=JZ.C_PORT_CODE)
6 #INDEX JOIN LEFT JOIN2: [1, 59, 301] join condition((A.C_DS_CODE = '036' AND A.C_PORT_CODE_OS = PORT.C_PORT_CODE AND SSS)) ret_null(0)
7 #PRJT2: [1, 1, 301]; exp_num(3), is_atom(FALSE)
8 #DISTINCT: [1, 1, 301]
9 #HASH RIGHT SEMI JOIN2: [1, 1, 301]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_892544421.colname=R.C_FIELD5) KEY_NULL_EQU(0)
10 #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1),
11 #SLCT2: [1, 1, 301]; R.D_FIELD1 <= var7
12 #BLKUP2: [1, 6, 301]; IDX_M_T_PORT_RELA(R)
13 #SSEK2: [1, 6, 301]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_TAXTCS','RELA_PRO_TA'),('RELA_BASIC_TAXTCS','RELA_PRO_TA')]
14 #PARALLEL: [1, 59, 61]; scan_type(EQU), key_num(1, 0, 0), simple(0)
15 #BLKUP2: [1, 59, 61]; IDX_M_T_CPXS1(A)
16 #SSEK2: [1, 59, 61]; scan_type(ASC), IDX_M_T_CPXS1(T_M_T_CPXS as A), scan_range[(exp11,'CPXS_SRC_TAJYSQ'),(exp11,'CPXS_SRC_TAJYSQ')]
17 #PRJT2: [382, 232, 169]; exp_num(3), is_atom(FALSE)
18 #SLCT2: [382, 232, 169]; T.RN = var8
19 #PRJT2: [381, 9282, 169]; exp_num(4), is_atom(FALSE)
20 #AFUN: [381, 9282, 169]; afun_num(1); partition_num(1)[T_M_T_FUNDNAV.C_PORT_CODE]; order_num(1)[T_M_T_FUNDNAV.D_FIELD1]
21 #SORT3: [381, 9282, 169]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
22 #HASH RIGHT SEMI JOIN2: [381, 9282, 169]; n_keys(1) KEY(DMTEMPVIEW_892544418.colname=T_M_T_FUNDNAV.D_FIELD1) KEY_NULL_EQU(0)
23 #CONST VALUE LIST: [1, 2, 13]; row_num(2), col_num(1),
24 #PARALLEL: [358, 261405, 169]; scan_type(FULL), key_num(0, 0, 0), simple(0)
25 #SLCT2: [358, 261405, 169]; T_M_T_FUNDNAV.C_DATA_IDF = 'ZHJZ_SRC_TAXT'
26 #CSCN2: [358, 2406692, 169]; INDEX33586972(T_M_T_FUNDNAV)
27 #SPL2: [1, 1, 1]; key_num(1), spool_num(1), is_atom(TRUE), has_var(1), sites(-)
28 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(TRUE)
29 #RNSK: [1, 1, 1]; rownum = exp_cast(1)
30 #SLCT2: [1, 5, 1]; B.C_DATE_TYPE IS NULL
31 #NEST LOOP LEFT JOIN2: [1, 234, 1]; join condition((TA.D_HDAY = B.D_HDAY AND TA.C_HDAY_CODE = B.C_HDAY_CODE)) partition_keys_num(0) ret_null(0)
32 #SLCT2: [1, 1, 1]; TA.D_HDAY > var6
33 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
34 #RN: [1, 1, 1]
35 #HIERARCHICAL QUERY: [1, 1, 1]; KEY_NUM(0);
36 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
37 #SLCT2: [1, 1, 0]; rownum <= var12
38 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
39 #CSCN2: [1, 2819, 109]; INDEX33585846(T_P_BI_HDAY_SUB as B)
40 #SPL2: [554, 1, 493]; key_num(5), spool_num(0), is_atom(FALSE), has_var(1), sites(-)
41 #PRJT2: [554, 1, 493]; exp_num(5), is_atom(FALSE)
42 #HASH2 INNER JOIN: [554, 1, 493]; KEY_NUM(1); KEY(X.C_FIELD7=A.C_FIELD4) KEY_NULL_EQU(0)
43 #PARALLEL: [1, 1, 192]; scan_type(FULL), key_num(0, 0, 0), simple(0)
44 #HASH RIGHT SEMI JOIN2: [1, 1, 192]; n_keys(1) KEY(DMTEMPVIEW_892544422.colname=X.C_DS_CODE) KEY_NULL_EQU(0)
45 #CONST VALUE LIST: [1, 4, 48]; row_num(4), col_num(1),
46 #BLKUP2: [1, 4, 192]; IDX_M_T_CPXS3(X)
47 #SSEK2: [1, 4, 192]; scan_type(ASC), IDX_M_T_CPXS3(T_M_T_CPXS as X), scan_range[('CPXS_SRC_TAJYQR','2'),('CPXS_SRC_TAJYQR','2')]
48 #NEST LOOP LEFT JOIN2: [538, 153520, 301]; partition_keys_num(0) ret_null(0)
49 #PRJT2: [1, 1, 301]; exp_num(3), is_atom(FALSE)
50 #DISTINCT: [1, 1, 301]
51 #HASH RIGHT SEMI JOIN2: [1, 1, 301]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_892544423.colname=R.C_FIELD5) KEY_NULL_EQU(0)
52 #CONST VALUE LIST: [1, 5, 48]; row_num(5), col_num(1),
53 #SLCT2: [1, 1, 301]; R.D_FIELD1 <= var13
54 #BLKUP2: [1, 6, 301]; IDX_M_T_PORT_RELA(R)
55 #SSEK2: [1, 6, 301]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_TAXTCS','RELA_PRO_TA'),('RELA_BASIC_TAXTCS','RELA_PRO_TA')]
56 #PARALLEL: [26, 153520, 338]; scan_type(FULL), key_num(0, 0, 0), simple(0)
57 #CSCN2: [26, 153520, 338]; INDEX33582389(T_M_T_CPXS as A)
DM autotrace
1 #NSET2: [838, 57->458, 470]
2 #PIPE2: [838, 57->458, 470]
3 #PIPE2: [284, 57->458, 470]
4 #PRJT2: [283, 57->458, 470]; exp_num(12), is_atom(FALSE)
5 #HASH LEFT JOIN2: [283, 57->458, 470]; key_num(1); col_num(11); partition_keys_num(0); mix(0); MEM_USED(12911KB), DISK_USED(0KB) KEY(A.C_PORT_CODE
=JZ.C_PORT_CODE)
6 #INDEX JOIN LEFT JOIN2: [1, 57->458, 301]: col_num(10); join condition((A.C_DS_CODE = '036' AND A.C_PORT_CODE_OS = PORT.C_PORT_CODE AND SSS)) re
t_null(0)
7 #PRJT2: [1, 1->369, 301]; exp_num(3), is_atom(FALSE)
8 #DISTINCT: [1, 1->369, 301], MEM_USED(8829KB), DISK_USED(0KB)
9 #HASH RIGHT SEMI JOIN2: [1, 1->369, 301]; key_num(1) (ANTI), MEM_USED(225KB), DISK_USED(0KB) KEY(DMTEMPVIEW_898876976.colname=R.C_FIELD5)
KEY_NULL_EQU(0)
10 #CONST VALUE LIST: [1, 5->5, 48]; row_num(5), col_num(1),
11 #SLCT2: [1, 1->873, 301]; R.D_FIELD1 <= var7
12 #BLKUP2: [1, 6->880, 301]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
13 #SSEK2: [1, 6->880, 301]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_TAXTCS','RELA_PRO_TA'),('RELA_
BASIC_TAXTCS','RELA_PRO_TA')]
14 #PARALLEL: [1, 57->178596, 61]; scan_type(EQU)
15 #BLKUP2: [1, 57->178596, 61]; IDX_M_T_CPXS1(T_M_T_CPXS)
16 #SSEK2: [1, 57->178596, 61]; scan_type(ASC), IDX_M_T_CPXS1(T_M_T_CPXS), scan_range[(exp11,'CPXS_SRC_TAJYSQ'),(exp11,'CPXS_SRC_TAJYSQ')]
17 #PRJT2: [281, 139->375, 169]; exp_num(3), is_atom(FALSE)
18 #SLCT2: [281, 139->375, 169]; T.RN = var8
19 #PRJT2: [281, 5562->750, 169]; exp_num(4), is_atom(FALSE)
20 #AFUN: [281, 5562->750, 169]; afun_num(1)
21 #SORT3: [281, 5562->750, 169]; key_num(2), is_distinct(FALSE), is_adaptive(0), MEM_USED(30720KB), DISK_USED(0KB)
22 #HASH RIGHT SEMI JOIN2: [281, 5562->750, 169]; key_num(1), MEM_USED(225KB), DISK_USED(0KB) KEY(DMTEMPVIEW_898876973.colname=T_M_T_FUND
NAV.D_FIELD1) KEY_NULL_EQU(0)
23 #CONST VALUE LIST: [1, 2->2, 13]; row_num(2), col_num(1),
24 #PARALLEL: [265, 178210->17946, 169]; scan_type(FULL)
25 #SLCT2: [265, 178210->17946, 169]; T_M_T_FUNDNAV.C_DATA_IDF = 'ZHJZ_SRC_TAXT'
26 #CSCN2: [265, 1781305->1781305, 169]; INDEX33586972(T_M_T_FUNDNAV)
27 #SPL2: [1, 1, 1]; key_num(1), spool_num(1), has_variable(1), sites(-)
28 #PRJT2: [1, 1->115, 1]; exp_num(1), is_atom(TRUE)
29 #RNSK: [1, 1->115, 1];
30 #SLCT2: [1, 5->115, 1]; B.C_DATE_TYPE IS NULL
31 #NEST LOOP LEFT JOIN2: [1, 234->575, 1]: col_num(2); partition_keys_num(0); join condition((TA.D_HDAY = B.D_HDAY AND TA.C_HDAY_CODE = B.C_HD
AY_CODE)) ret_null(0)
32 #SLCT2: [1, 1->575, 1]; TA.D_HDAY > var6
33 #PRJT2: [1, 1->152828, 1]; exp_num(2), is_atom(FALSE)
34 #RN: [1, 1->152828, 1]
35 #HIERARCHICAL QUERY: [1, 1->152828, 1]; key_num(0)
36 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
37 #SLCT2: [1, 1->458, 0]; rownum <= var12
38 #CSCN2: [1, 1->458, 0]; SYSINDEXSYSDUAL2(SYSDUAL2)
39 #CSCN2: [1, 2819->1620925, 109]; INDEX33585846(T_P_BI_HDAY_SUB)
40 #SPL2: [554, 57, 493]; key_num(5), spool_num(0), has_variable(1), sites(-)
41 #PRJT2: [554, 57->2214, 493]; exp_num(5), is_atom(FALSE)
42 #HASH2 INNER JOIN: [554, 57->2214, 493]; KEY_NUM(1), MEM_USED(12975KB), DISK_USED(0KB) KEY(X.C_FIELD7=A.C_FIELD4) KEY_NULL_EQU(0)
43 #PARALLEL: [1, 1->6, 192]; scan_type(FULL)
44 #HASH RIGHT SEMI JOIN2: [1, 1->6, 192]; key_num(1), MEM_USED(450KB), DISK_USED(0KB) KEY(DMTEMPVIEW_898876977.colname=X.C_DS_CODE) KEY_NULL_EQU
(0)
45 #CONST VALUE LIST: [1, 4->8, 48]; row_num(4), col_num(1),
46 #BLKUP2: [1, 5->6, 192]; IDX_M_T_CPXS3(T_M_T_CPXS)
47 #SSEK2: [1, 5->6, 192]; scan_type(ASC), IDX_M_T_CPXS3(T_M_T_CPXS), scan_range[('CPXS_SRC_TAJYQR','2'),('CPXS_SRC_TAJYQR','2')]
48 #NEST LOOP LEFT JOIN2: [538, 153520->56648880, 301]: col_num(5); partition_keys_num(0) ret_null(0)
49 #PRJT2: [1, 1->369, 301]; exp_num(3), is_atom(FALSE)
50 #DISTINCT: [1, 1->369, 301], MEM_USED(8829KB), DISK_USED(0KB)
51 #HASH RIGHT SEMI JOIN2: [1, 1->369, 301]; key_num(1) (ANTI), MEM_USED(225KB), DISK_USED(0KB) KEY(DMTEMPVIEW_898876978.colname=R.C_FIELD5)
KEY_NULL_EQU(0)
52 #CONST VALUE LIST: [1, 5->5, 48]; row_num(5), col_num(1),
53 #SLCT2: [1, 1->873, 301]; R.D_FIELD1 <= var13
54 #BLKUP2: [1, 6->880, 301]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
55 #SSEK2: [1, 6->880, 301]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_TAXTCS','RELA_PRO_TA'),('RELA_
BASIC_TAXTCS','RELA_PRO_TA')]
56 #PARALLEL: [26, 153520->56648880, 338]; scan_type(FULL)
57 #CSCN2: [26, 153520->56648880, 338]; INDEX33582389(T_M_T_CPXS)
SPL2 2 0% 28 40 1 0 0 0 0 0
CONSTV 5 0% 27 10 6 0 0 0 0 0
CONSTV 13 0% 26 23 3 0 0 0 0 0
DLCK 16 0% 25 0 3 0 0 0 0 0
PIPE2 73 0% 24 2 919 0 0 0 0 0
PRJT2 91 0% 23 17 752 0 0 0 0 0
PIPE2 132 0% 22 3 919 0 0 0 0 0
PRJT2 207 0% 21 19 1502 0 0 0 0 0
PRJT2 217 0% 20 7 740 0 0 0 0 0
SLCT2 542 0% 19 18 1127 0 0 0 0 0
HRS2 642 0% 18 9 1250 225 0 5 0 0
AFUN 890 0% 17 20 1502 0 0 0 0 0
SLCT2 979 0% 16 11 1755 0 0 0 0 0
SORT3 1205 0% 15 21 1502 2048 0 0 0 0
NSET2 1222 0% 14 1 461 0 0 0 0 0
SSEK2 2502 0% 13 13 881 0 0 0 0 0
DIST 2748 0% 12 8 740 8829 0 369 0 0
BLKUP2 3363 0% 11 12 1762 0 0 0 0 0
HLO2 5170 0% 10 5 1294 12911 0 28 0 455
PLL 5644 0% 9 24 36266 0 0 0 0 0
HRS2 5818 0% 8 22 18701 225 0 2 0 0
PLL 53470 0.04% 7 14 357930 0 0 0 0 0
SSEK2 252489 0.19% 6 16 178965 0 0 0 0 0
BLKUP2 416553 0.31% 5 15 357930 0 0 0 0 0
SLCT2 1446218 1.09% 4 25 2425384 0 0 0 0 0
PRJT2 3279689 2.47% 3 4 918 0 0 0 0 0
CSCN2 5385506 4.06% 2 26 2407065 0 0 0 0 0
IJLO2 121892866 91.82% 1 6 179794 0 0 0 0 0
ORACLE执行计划
Plan Hash Value :
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 259 | 15365 | 00:00:02 |
| * 1 | COUNT STOPKEY | | | | | |
| * 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 24 | 3 | 00:00:01 |
| * 4 | VIEW | | 1 | 10 | 2 | 00:00:01 |
| 5 | COUNT | | | | | |
| * 6 | CONNECT BY WITHOUT FILTERING | | | | | |
| 7 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T_P_BI_HDAY_SUB | 1 | 14 | 1 | 00:00:01 |
| * 9 | INDEX UNIQUE SCAN | IDX_P_BI_HDAY_SUB | 1 | | 0 | 00:00:01 |
| * 10 | HASH JOIN OUTER | | 1 | 259 | 15362 | 00:00:02 |
| * 11 | HASH JOIN OUTER | | 1 | 208 | 7724 | 00:00:01 |
| 12 | VIEW | | 1 | 116 | 5 | 00:00:01 |
| 13 | HASH UNIQUE | | 1 | 42 | 5 | 00:00:01 |
| * 14 | TABLE ACCESS BY INDEX ROWID BATCHED | T_M_T_PORT_RELA | 1 | 42 | 4 | 00:00:01 |
| * 15 | INDEX RANGE SCAN | IDX_M_T_PORT_RELA | 5 | | 3 | 00:00:01 |
| 16 | VIEW | VW_DCL_42344769 | 15 | 1380 | 7719 | 00:00:01 |
| * 17 | HASH JOIN ANTI | | 15 | 1485 | 7719 | 00:00:01 |
| * 18 | TABLE ACCESS BY INDEX ROWID BATCHED | T_M_T_CPXS | 23 | 1403 | 14 | 00:00:01 |
| * 19 | INDEX RANGE SCAN | IDX_M_T_CPXS1 | 409 | | 5 | 00:00:01 |
| * 20 | TABLE ACCESS FULL | T_M_T_CPXS | 1 | 38 | 7705 | 00:00:01 |
| * 21 | VIEW | | 152 | 7752 | 7638 | 00:00:01 |
| * 22 | WINDOW SORT PUSHED RANK | | 152 | 5776 | 7638 | 00:00:01 |
| 23 | PARTITION RANGE ALL | | 152 | 5776 | 7637 | 00:00:01 |
| * 24 | TABLE ACCESS FULL | T_M_T_FUNDNAV | 152 | 5776 | 7637 | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ROWNUM=1)
* 2 - filter("B"."C_DATE_TYPE" IS NULL)
* 4 - filter(INTERNAL_FUNCTION("TA"."D_HDAY")>:B1)
* 6 - filter(ROWNUM<=406)
* 9 - access("TA"."C_HDAY_CODE"="B"."C_HDAY_CODE"(+) AND "B"."D_HDAY"(+)=INTERNAL_FUNCTION("TA"."D_HDAY"))
* 9 - filter("B"."D_HDAY"(+)>:B1)
* 10 - access("ITEM_2"="C_PORT_CODE"(+))
* 11 - access("ITEM_0"(+)="PORT"."C_PORT_CODE")
* 14 - filter("R"."C_FIELD5"<>'6' AND "R"."C_FIELD5"<>'9' AND "R"."D_FIELD1"<=TO_DATE(' 2024-04-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "R"."C_FIELD5"<>'7' AND "R"."C_FIELD5"<>'8' AND
"R"."C_FIELD5"<>'*')
* 15 - access("R"."C_RELA_TYPE"='RELA_BASIC_TAXTCS' AND "R"."C_DATA_IDF"='RELA_PRO_TA')
* 17 - access("A"."C_FIELD4"="X"."C_FIELD7")
* 18 - filter("A"."C_DS_CODE"='036')
* 19 - access("A"."D_CONFIRM"=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."C_DATA_IDF"='CPXS_SRC_TAJYSQ')
* 20 - filter("X"."C_FIELD4"='2' AND "X"."C_FIELD7" IS NOT NULL AND "X"."C_DATA_IDF"='CPXS_SRC_TAJYQR' AND ("X"."C_DS_CODE"='02' OR "X"."C_DS_CODE"='03' OR "X"."C_DS_CODE"='13' OR
"X"."C_DS_CODE"='16'))
* 21 - filter("RN"(+)=1)
* 22 - filter(ROW_NUMBER() OVER ( PARTITION BY "C_PORT_CODE" ORDER BY INTERNAL_FUNCTION("D_FIELD1") DESC )<=1)
* 24 - filter("C_DATA_IDF"='ZHJZ_SRC_TAXT' AND ("D_FIELD1"=TO_DATE(' 2024-04-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "D_FIELD1"=TO_DATE(' 2024-04-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
解决方案
NOT EXISTS 的问题，右孩子条件里放。
