sql
select
t.c_port_code c_port_code,
sum(abs(t.N_jzbdl)) / nvl(
(
select
count(1)
from
T_M_T_FUNDNAV t
join
(
select
p.c_port_code,
case when r.c_rela_content = 'T1' then
(
select
d_hday
from
(
select
d_hday,
rownum rn
from
(
select
d_hday
from
(
select
a.d_hday ,
nvl(b.c_date_type, 'W') c_date_type,
a.c_hday_code
from
(
SELECT
TRUNC(to_date('2024-04-08', 'yyyy-MM-dd'), 'yyyy') + ROWNUM - 10 as d_hday,
'CN' c_hday_code
FROM
DUAL CONNECT BY ROWNUM <= 370
)
a
left join T_P_BI_HDAY_SUB b
on
a.d_hday = b.d_hday and a.c_hday_code = b.c_hday_code
)
T
where
c_date_type = 'W' and d_hday < to_date('2024-04-08', 'yyyy-MM-dd')
order by
d_hday desc
)
)
where
rn = 1
) else to_date('2024-04-08', 'yyyy-MM-dd') end gzDate,
p.d_build ,
p.d_close ,
'W' c_hday_code
from
T_P_AB_PORT p
left join T_M_T_PORT_RELA r
on
p.c_port_code = r.c_port_code
and r.c_rela_type = 'RELA_BASIC_ZHGZPL'
and r.c_data_idf = 'RELA_PRO_ACC'
where
p.c_port_code = 'EM2O11'
union all
select
p.c_port_code ,
decode(r.c_rela_content, 'T1', to_date('2024-04-08', 'yyyy-MM-dd') -1, to_date('2024-04-08', 'yyyy-MM-dd'))gzDate,
p.d_build ,
p.d_close ,
'D' c_hday_code
from
T_P_AB_PORT p
left join T_M_T_PORT_RELA r
on
p.c_port_code = r.c_port_code
and r.c_rela_type = 'RELA_BASIC_ZHGZPL'
and r.c_data_idf = 'RELA_PRO_ACC'
where
p.c_port_code = 'EM2O11'
)
bi_v_n_portvaluationdate on bi_v_n_portvaluationdate.c_port_code = t.c_port_code
where
bi_v_n_portvaluationdate.gzdate <= 30+ t.d_aststat
and bi_v_n_portvaluationdate.gzdate > t.d_aststat
and to_date('2024-04-08', 'yyyy-MM-dd') between bi_v_n_portvaluationdate.d_build and bi_v_n_portvaluationdate.d_close
and bi_v_n_portvaluationdate.c_hday_code = 'D'
)
, 1) lsjzbdl_avg
from
T_M_T_FUNDNAV t
join
(
select
p.c_port_code,
case when r.c_rela_content = 'T1' then
(
select
d_hday
from
(
select
d_hday,
rownum rn
from
(
select
d_hday
from
(
select
a.d_hday ,
nvl(b.c_date_type, 'W') c_date_type,
a.c_hday_code
from
(
SELECT
TRUNC(to_date('2024-04-08', 'yyyy-MM-dd'), 'yyyy') + ROWNUM - 10 as d_hday,
'CN' c_hday_code
FROM
DUAL CONNECT BY ROWNUM <= 370
)
a
left join T_P_BI_HDAY_SUB b
on
a.d_hday = b.d_hday and a.c_hday_code = b.c_hday_code
)
T
where
c_date_type = 'W' and d_hday < to_date('2024-04-08', 'yyyy-MM-dd')
order by
d_hday desc
)
)
where
rn = 1
) else to_date('2024-04-08', 'yyyy-MM-dd') end gzDate,
p.d_build ,
p.d_close ,
'W' c_hday_code
from
T_P_AB_PORT p
left join T_M_T_PORT_RELA r
on
p.c_port_code = r.c_port_code
and r.c_rela_type = 'RELA_BASIC_ZHGZPL'
and r.c_data_idf = 'RELA_PRO_ACC'
where
p.c_port_code = 'EM2O11'
union all
select
p.c_port_code ,
decode(r.c_rela_content, 'T1', to_date('2024-04-08', 'yyyy-MM-dd') -1, to_date('2024-04-08', 'yyyy-MM-dd'))gzDate,
p.d_build ,
p.d_close ,
'D' c_hday_code
from
T_P_AB_PORT p
left join T_M_T_PORT_RELA r
on
p.c_port_code = r.c_port_code
and r.c_rela_type = 'RELA_BASIC_ZHGZPL'
and r.c_data_idf = 'RELA_PRO_ACC'
where
p.c_port_code = 'EM2O11'
)
bi_v_n_portvaluationdate on bi_v_n_portvaluationdate.c_port_code = t.c_port_code
where
bi_v_n_portvaluationdate.gzdate <= 30+ t.d_aststat
and bi_v_n_portvaluationdate.gzdate > t.d_aststat
and to_date('2024-04-08', 'yyyy-MM-dd') between bi_v_n_portvaluationdate.d_build and bi_v_n_portvaluationdate.d_close
and bi_v_n_portvaluationdate.c_hday_code = 'D'
group by
t.c_port_code
执行计划
1 #NSET2: [203, 530, 165]
2 #PIPE2: [203, 530, 165]
3 #PIPE2: [202, 530, 165]
4 #PIPE2: [200, 530, 165]
5 #PRJT2: [112, 530, 165]; exp_num(2), is_atom(FALSE)
6 #PRJT2: [112, 530, 165]; exp_num(2), is_atom(FALSE)
7 #HAGR2: [112, 530, 165]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(DMTEMPVIEW_890458157.TMPCOL0)
8 #PRJT2: [111, 1060, 165]; exp_num(2), is_atom(FALSE)
9 #SLCT2: [111, 1060, 165]; BI_V_N_PORTVALUATIONDATE.GZDATE <= T.D_ASTSTAT+var7
10 #NEST LOOP INDEX JOIN2: [111, 1060, 165]
11 #PRJT2: [81, 2, 74]; exp_num(2), is_atom(FALSE)
12 #UNION ALL: [81, 2, 74]
13 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
14 #SLCT2: [80, 1, 74]; FALSE
15 #INDEX JOIN LEFT JOIN2: [80, 17704, 74] join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
16 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT as P)
17 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(R)
18 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
19 #PRJT2: [1, 1, 74]; exp_num(2), is_atom(FALSE)
20 #INDEX JOIN LEFT JOIN2: [1, 1, 74] ret_null(0)
21 #SLCT2: [1, 1, 74]; (P.D_BUILD <= var8 AND P.D_CLOSE >= var8)
22 #BLKUP2: [1, 1, 74]; IDX_P_AB_PORT(P)
23 #SSEK2: [1, 1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT as P), scan_range['EM2O11','EM2O11']
24 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(R)
25 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
26 #PARALLEL: [24, 530, 61]; scan_type(FULL), key_num(0, 0, 0), simple(0)
27 #BLKUP2: [24, 530, 61]; IDX_M_T_FUNDNAV2(T)
28 #SSEK2: [24, 530, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV as T), scan_range((BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,null2),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
29 #SPL2: [87, 1, 135]; key_num(1), spool_num(2), is_atom(TRUE), has_var(0), sites(-)
30 #PRJT2: [87, 1, 135]; exp_num(1), is_atom(TRUE)
31 #AAGR2: [87, 1, 135]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
32 #SLCT2: [87, 1060, 135]; BI_V_N_PORTVALUATIONDATE.GZDATE <= T.D_ASTSTAT+var9
33 #NEST LOOP INDEX JOIN2: [87, 1060, 135]
34 #PRJT2: [81, 2, 74]; exp_num(2), is_atom(FALSE)
35 #UNION ALL: [81, 2, 74]
36 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
37 #SLCT2: [80, 1, 74]; FALSE
38 #INDEX JOIN LEFT JOIN2: [80, 17704, 74] join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
39 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT as P)
40 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(R)
41 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
42 #PRJT2: [1, 1, 74]; exp_num(2), is_atom(FALSE)
43 #INDEX JOIN LEFT JOIN2: [1, 1, 74] ret_null(0)
44 #SLCT2: [1, 1, 74]; (P.D_BUILD <= var10 AND P.D_CLOSE >= var10)
45 #BLKUP2: [1, 1, 74]; IDX_P_AB_PORT(P)
46 #SSEK2: [1, 1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT as P), scan_range['EM2O11','EM2O11']
47 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(R)
48 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
49 #PARALLEL: [4, 530, 61]; scan_type(FULL), key_num(0, 0, 0), simple(0)
50 #SSEK2: [4, 530, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV as T), scan_range((BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,null2),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
51 #SPL2: [1, 1, 13]; key_num(1), spool_num(1), is_atom(TRUE), has_var(0), sites(-)
52 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
53 #SLCT2: [1, 1, 13]; DMTEMPVIEW_890458149.RN = var11
54 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
55 #RN: [1, 1, 13]
56 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
57 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
58 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
59 #SLCT2: [1, 1, 1]; exp11 = 'W'
60 #INDEX JOIN LEFT JOIN2: [1, 1, 1] ret_null(0)
61 #SLCT2: [1, 1, 1]; A.D_HDAY < var12
62 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
63 #RN: [1, 1, 1]
64 #HIERARCHICAL QUERY: [1, 1, 1]; KEY_NUM(0);
65 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
66 #SLCT2: [1, 1, 0]; rownum <= var16
67 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
68 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(B)
69 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB as B), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D_HDAY)]
70 #SPL2: [1, 1, 13]; key_num(1), spool_num(0), is_atom(TRUE), has_var(0), sites(-)
71 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
72 #SLCT2: [1, 1, 13]; DMTEMPVIEW_890458143.RN = var17
73 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
74 #RN: [1, 1, 13]
75 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
76 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
77 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
78 #SLCT2: [1, 1, 1]; exp11 = 'W'
79 #INDEX JOIN LEFT JOIN2: [1, 1, 1] ret_null(0)
80 #SLCT2: [1, 1, 1]; A.D_HDAY < var18
81 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
82 #RN: [1, 1, 1]
83 #HIERARCHICAL QUERY: [1, 1, 1]; KEY_NUM(0);
84 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
85 #SLCT2: [1, 1, 0]; rownum <= var22
86 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
87 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(B)
88 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB as B), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D_HDAY)]
修改方案
-
建索引 CREATE INDEX "IDX_M_T_FUNDNAV2" ON "OCP"."T_M_T_FUNDNAV"("C_PORT_CODE" ASC,"D_ASTSTAT" ASC) online parallel 64 ;
-
修改语句
bi_v_n_portvaluationdate.gzdate <= t.d_aststat+30 改为 bi_v_n_portvaluationdate.gzdate-30 <= t.d_aststat才会定位到分区表。
修改后执行计划
1 #NSET2: [203, 530, 165]
2 #PIPE2: [203, 530, 165]
3 #PIPE2: [201, 530, 165]
4 #PIPE2: [200, 530, 165]
5 #PRJT2: [112, 530, 165]; exp_num(2), is_atom(FALSE)
6 #PRJT2: [112, 530, 165]; exp_num(2), is_atom(FALSE)
7 #HAGR2: [112, 530, 165]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(DMTEMPVIEW_890472008.TMPCOL0)
8 #PRJT2: [111, 1060, 165]; exp_num(2), is_atom(FALSE)
9 #NEST LOOP INDEX JOIN2: [111, 1060, 165]
10 #PRJT2: [81, 2, 74]; exp_num(2), is_atom(FALSE)
11 #UNION ALL: [81, 2, 74]
12 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
13 #SLCT2: [80, 1, 74]; FALSE
14 #INDEX JOIN LEFT JOIN2: [80, 17704, 74] join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
15 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT as P)
16 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(R)
17 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
18 #PRJT2: [1, 1, 74]; exp_num(2), is_atom(FALSE)
19 #INDEX JOIN LEFT JOIN2: [1, 1, 74] ret_null(0)
20 #SLCT2: [1, 1, 74]; (P.D_BUILD <= var7 AND P.D_CLOSE >= var7)
21 #BLKUP2: [1, 1, 74]; IDX_P_AB_PORT(P)
22 #SSEK2: [1, 1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT as P), scan_range['EM2O11','EM2O11']
23 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(R)
24 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
25 #PARALLEL: [24, 530, 61]; scan_type(FULL), key_num(0, 0, 0), simple(0)
26 #BLKUP2: [24, 530, 61]; IDX_M_T_FUNDNAV2(T)
27 #SSEK2: [24, 530, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV as T), scan_range[(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE-exp_cast(30)),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
28 #SPL2: [87, 1, 135]; key_num(1), spool_num(2), is_atom(TRUE), has_var(0), sites(-)
29 #PRJT2: [87, 1, 135]; exp_num(1), is_atom(TRUE)
30 #AAGR2: [87, 1, 135]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
31 #NEST LOOP INDEX JOIN2: [87, 1060, 135]
32 #PRJT2: [81, 2, 74]; exp_num(2), is_atom(FALSE)
33 #UNION ALL: [81, 2, 74]
34 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
35 #SLCT2: [80, 1, 74]; FALSE
36 #INDEX JOIN LEFT JOIN2: [80, 17704, 74] join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
37 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT as P)
38 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(R)
39 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
40 #PRJT2: [1, 1, 74]; exp_num(2), is_atom(FALSE)
41 #INDEX JOIN LEFT JOIN2: [1, 1, 74] ret_null(0)
42 #SLCT2: [1, 1, 74]; (P.D_BUILD <= var8 AND P.D_CLOSE >= var8)
43 #BLKUP2: [1, 1, 74]; IDX_P_AB_PORT(P)
44 #SSEK2: [1, 1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT as P), scan_range['EM2O11','EM2O11']
45 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(R)
46 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA as R), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
47 #PARALLEL: [4, 530, 61]; scan_type(FULL), key_num(0, 0, 0), simple(0)
48 #SSEK2: [4, 530, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV as T), scan_range[(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE-exp_cast(30)),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
49 #SPL2: [1, 1, 13]; key_num(1), spool_num(1), is_atom(TRUE), has_var(0), sites(-)
50 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
51 #SLCT2: [1, 1, 13]; DMTEMPVIEW_890472000.RN = var9
52 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
53 #RN: [1, 1, 13]
54 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
55 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
56 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
57 #SLCT2: [1, 1, 1]; exp11 = 'W'
58 #INDEX JOIN LEFT JOIN2: [1, 1, 1] ret_null(0)
59 #SLCT2: [1, 1, 1]; A.D_HDAY < var10
60 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
61 #RN: [1, 1, 1]
62 #HIERARCHICAL QUERY: [1, 1, 1]; KEY_NUM(0);
63 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
64 #SLCT2: [1, 1, 0]; rownum <= var14
65 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
66 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(B)
67 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB as B), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D_HDAY)]
68 #SPL2: [1, 1, 13]; key_num(1), spool_num(0), is_atom(TRUE), has_var(0), sites(-)
69 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
70 #SLCT2: [1, 1, 13]; DMTEMPVIEW_890471994.RN = var15
71 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
72 #RN: [1, 1, 13]
73 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
74 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
75 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
76 #SLCT2: [1, 1, 1]; exp11 = 'W'
77 #INDEX JOIN LEFT JOIN2: [1, 1, 1] ret_null(0)
78 #SLCT2: [1, 1, 1]; A.D_HDAY < var16
79 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
80 #RN: [1, 1, 1]
81 #HIERARCHICAL QUERY: [1, 1, 1]; KEY_NUM(0);
82 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
83 #SLCT2: [1, 1, 0]; rownum <= var20
84 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL)
85 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(B)
86 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB as B), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D_HDAY)]
autotrace 和 et 对比
1 #NSET2: [203, 530->1, 165]
2 #PIPE2: [203, 530->1, 165]
3 #PIPE2: [202, 530->1, 165]
4 #PIPE2: [200, 530->1, 165]
5 #PRJT2: [112, 530->1, 165]; exp_num(2), is_atom(FALSE)
6 #PRJT2: [112, 530->1, 165]; exp_num(2), is_atom(FALSE)
7 #HAGR2: [112, 530->1, 165]; grp_num(1), sfun_num(1), MEM_USED(1649KB), DISK_USED(0KB), distinct_flag[0]; slave_empty(0) keys(DMTEMPVIEW_890992
843.TMPCOL0)
8 #PRJT2: [111, 1060->6296, 165]; exp_num(2), is_atom(FALSE)
9 #SLCT2: [111, 1060->6296, 165]; BI_V_N_PORTVALUATIONDATE.GZDATE <= T.D_ASTSTAT+var7
10 #NEST LOOP INDEX JOIN2: [111, 1060->6319, 165]
11 #PRJT2: [81, 2->1, 74]; exp_num(2), is_atom(FALSE)
12 #UNION ALL: [81, 2->1, 74]
13 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
14 #SLCT2: [80, 1, 74]; FALSE
15 #INDEX JOIN LEFT JOIN2: [80, 17704, 74]: col_num(2); join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
16 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT)
17 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
18 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),(
'RELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
19 #PRJT2: [1, 1->1, 74]; exp_num(2), is_atom(FALSE)
20 #INDEX JOIN LEFT JOIN2: [1, 1->1, 74]: col_num(2) ret_null(0)
21 #SLCT2: [1, 1->1, 74]; (P.D_BUILD <= var8 AND P.D_CLOSE >= var8)
22 #BLKUP2: [1, 1->1, 74]; IDX_P_AB_PORT(T_P_AB_PORT)
23 #SSEK2: [1, 1->1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT), scan_range['EM2O11','EM2O11']
24 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA)
25 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM
2O11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
26 #PARALLEL: [24, 530->6319, 61]; scan_type(FULL)
27 #BLKUP2: [24, 530->6319, 61]; IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV)
28 #SSEK2: [24, 530->6319, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV), scan_range((BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,nul
l2),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
29 #SPL2: [87, 1, 135]; key_num(1), spool_num(2), has_variable(0), sites(-)
30 #PRJT2: [87, 1->1, 135]; exp_num(1), is_atom(TRUE)
31 #AAGR2: [87, 1->1, 135]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
32 #SLCT2: [87, 1060->6296, 135]; BI_V_N_PORTVALUATIONDATE.GZDATE <= T.D_ASTSTAT+var9
33 #NEST LOOP INDEX JOIN2: [87, 1060->6319, 135]
34 #PRJT2: [81, 2->1, 74]; exp_num(2), is_atom(FALSE)
35 #UNION ALL: [81, 2->1, 74]
36 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
37 #SLCT2: [80, 1, 74]; FALSE
38 #INDEX JOIN LEFT JOIN2: [80, 17704, 74]: col_num(2); join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
39 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT)
40 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
41 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('R
ELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
42 #PRJT2: [1, 1->1, 74]; exp_num(2), is_atom(FALSE)
43 #INDEX JOIN LEFT JOIN2: [1, 1->1, 74]: col_num(2) ret_null(0)
44 #SLCT2: [1, 1->1, 74]; (P.D_BUILD <= var10 AND P.D_CLOSE >= var10)
45 #BLKUP2: [1, 1->1, 74]; IDX_P_AB_PORT(T_P_AB_PORT)
46 #SSEK2: [1, 1->1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT), scan_range['EM2O11','EM2O11']
47 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA)
48 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O
11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
49 #PARALLEL: [4, 530->6319, 61]; scan_type(FULL)
50 #SSEK2: [4, 530->6319, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV), scan_range((BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,null2),(
BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
51 #SPL2: [1, 1, 13]; key_num(1), spool_num(1), has_variable(0), sites(-)
52 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
53 #SLCT2: [1, 1, 13]; DMTEMPVIEW_890992833.RN = var11
54 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
55 #RN: [1, 1, 13]
56 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
57 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), is_adaptive(0), MEM_USED(0KB), DISK_USED(0KB)
58 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
59 #SLCT2: [1, 1, 1]; exp11 = 'W'
60 #INDEX JOIN LEFT JOIN2: [1, 1, 1]: col_num(2) ret_null(0)
61 #SLCT2: [1, 1, 1]; A.D_HDAY < var12
62 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
63 #RN: [1, 1, 1]
64 #HIERARCHICAL QUERY: [1, 1, 1]; key_num(0)
65 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
66 #SLCT2: [1, 1, 0]; rownum <= var16
67 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2)
68 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB)
69 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D
_HDAY)]
70 #SPL2: [1, 1, 13]; key_num(1), spool_num(0), has_variable(0), sites(-)
71 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
72 #SLCT2: [1, 1, 13]; DMTEMPVIEW_890992827.RN = var17
73 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
74 #RN: [1, 1, 13]
75 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
76 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), is_adaptive(0), MEM_USED(0KB), DISK_USED(0KB)
77 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
78 #SLCT2: [1, 1, 1]; exp11 = 'W'
79 #INDEX JOIN LEFT JOIN2: [1, 1, 1]: col_num(2) ret_null(0)
80 #SLCT2: [1, 1, 1]; A.D_HDAY < var18
81 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
82 #RN: [1, 1, 1]
83 #HIERARCHICAL QUERY: [1, 1, 1]; key_num(0)
84 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
85 #SLCT2: [1, 1, 0]; rownum <= var22
86 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2)
87 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB)
88 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D_H
DAY)]
1 #NSET2: [203, 530->1, 165]
2 #PIPE2: [203, 530->1, 165]
3 #PIPE2: [201, 530->1, 165]
4 #PIPE2: [200, 530->1, 165]
5 #PRJT2: [112, 530->1, 165]; exp_num(2), is_atom(FALSE)
6 #PRJT2: [112, 530->1, 165]; exp_num(2), is_atom(FALSE)
7 #HAGR2: [112, 530->1, 165]; grp_num(1), sfun_num(1), MEM_USED(1649KB), DISK_USED(0KB), distinct_flag[0]; slave_empty(0) keys(DMTEMPVIEW_891004
265.TMPCOL0)
8 #PRJT2: [111, 1060->6296, 165]; exp_num(2), is_atom(FALSE)
9 #NEST LOOP INDEX JOIN2: [111, 1060->6296, 165]
10 #PRJT2: [81, 2->1, 74]; exp_num(2), is_atom(FALSE)
11 #UNION ALL: [81, 2->1, 74]
12 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
13 #SLCT2: [80, 1, 74]; FALSE
14 #INDEX JOIN LEFT JOIN2: [80, 17704, 74]: col_num(2); join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
15 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT)
16 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
17 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('R
ELA_BASIC_ZHGZPL','RELA_PRO_ACC')]
18 #PRJT2: [1, 1->1, 74]; exp_num(2), is_atom(FALSE)
19 #INDEX JOIN LEFT JOIN2: [1, 1->1, 74]: col_num(2) ret_null(0)
20 #SLCT2: [1, 1->1, 74]; (P.D_BUILD <= var7 AND P.D_CLOSE >= var7)
21 #BLKUP2: [1, 1->1, 74]; IDX_P_AB_PORT(T_P_AB_PORT)
22 #SSEK2: [1, 1->1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT), scan_range['EM2O11','EM2O11']
23 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA)
24 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O
11',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
25 #PARALLEL: [24, 530->6296, 61]; scan_type(FULL)
26 #BLKUP2: [24, 530->6296, 61]; IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV)
27 #SSEK2: [24, 530->6296, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV), scan_range[(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_
N_PORTVALUATIONDATE.GZDATE-exp_cast(30)),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
28 #SPL2: [87, 1, 135]; key_num(1), spool_num(2), has_variable(0), sites(-)
29 #PRJT2: [87, 1->1, 135]; exp_num(1), is_atom(TRUE)
30 #AAGR2: [87, 1->1, 135]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
31 #NEST LOOP INDEX JOIN2: [87, 1060->6296, 135]
32 #PRJT2: [81, 2->1, 74]; exp_num(2), is_atom(FALSE)
33 #UNION ALL: [81, 2->1, 74]
34 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
35 #SLCT2: [80, 1, 74]; FALSE
36 #INDEX JOIN LEFT JOIN2: [80, 17704, 74]: col_num(2); join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
37 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT)
38 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
29 #PRJT2: [87, 1->1, 135]; exp_num(1), is_atom(TRUE)
30 #AAGR2: [87, 1->1, 135]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
31 #NEST LOOP INDEX JOIN2: [87, 1060->6296, 135]
32 #PRJT2: [81, 2->1, 74]; exp_num(2), is_atom(FALSE)
33 #UNION ALL: [81, 2->1, 74]
34 #PRJT2: [80, 1, 74]; exp_num(2), is_atom(FALSE)
35 #SLCT2: [80, 1, 74]; FALSE
36 #INDEX JOIN LEFT JOIN2: [80, 17704, 74]: col_num(2); join condition(R.C_PORT_CODE = 'EM2O11') ret_null(0)
37 #CSCN2: [1, 8852, 74]; INDEX33584323(T_P_AB_PORT)
38 #BLKUP2: [59, 2, 96]; IDX_M_T_PORT_RELA(T_M_T_PORT_RELA)
39 #SSEK2: [59, 2, 96]; scan_type(ASC), IDX_M_T_PORT_RELA(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC'),('REL
A_BASIC_ZHGZPL','RELA_PRO_ACC')]
40 #PRJT2: [1, 1->1, 74]; exp_num(2), is_atom(FALSE)
41 #INDEX JOIN LEFT JOIN2: [1, 1->1, 74]: col_num(2) ret_null(0)
42 #SLCT2: [1, 1->1, 74]; (P.D_BUILD <= var8 AND P.D_CLOSE >= var8)
43 #BLKUP2: [1, 1->1, 74]; IDX_P_AB_PORT(T_P_AB_PORT)
44 #SSEK2: [1, 1->1, 74]; scan_type(ASC), IDX_P_AB_PORT(T_P_AB_PORT), scan_range['EM2O11','EM2O11']
45 #BLKUP2: [1, 1, 144]; IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA)
46 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_M_T_PORT_RELA1(T_M_T_PORT_RELA), scan_range[('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11
',min),('RELA_BASIC_ZHGZPL','RELA_PRO_ACC','EM2O11',max))
47 #PARALLEL: [4, 530->6296, 61]; scan_type(FULL)
48 #SSEK2: [4, 530->6296, 61]; scan_type(ASC), IDX_M_T_FUNDNAV2(T_M_T_FUNDNAV), scan_range[(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_POR
TVALUATIONDATE.GZDATE-exp_cast(30)),(BI_V_N_PORTVALUATIONDATE.C_PORT_CODE,BI_V_N_PORTVALUATIONDATE.GZDATE))
49 #SPL2: [1, 1, 13]; key_num(1), spool_num(1), has_variable(0), sites(-)
50 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
51 #SLCT2: [1, 1, 13]; DMTEMPVIEW_891004252.RN = var9
52 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
53 #RN: [1, 1, 13]
54 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
55 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), is_adaptive(0), MEM_USED(0KB), DISK_USED(0KB)
56 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
57 #SLCT2: [1, 1, 1]; exp11 = 'W'
58 #INDEX JOIN LEFT JOIN2: [1, 1, 1]: col_num(2) ret_null(0)
59 #SLCT2: [1, 1, 1]; A.D_HDAY < var10
60 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
61 #RN: [1, 1, 1]
62 #HIERARCHICAL QUERY: [1, 1, 1]; key_num(0)
63 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
64 #SLCT2: [1, 1, 0]; rownum <= var14
65 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2)
66 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB)
67 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D
_HDAY)]
68 #SPL2: [1, 1, 13]; key_num(1), spool_num(0), has_variable(0), sites(-)
69 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(TRUE)
70 #SLCT2: [1, 1, 13]; DMTEMPVIEW_891004246.RN = var15
71 #PRJT2: [1, 1, 13]; exp_num(2), is_atom(FALSE)
72 #RN: [1, 1, 13]
73 #PRJT2: [1, 1, 13]; exp_num(1), is_atom(FALSE)
74 #SORT3: [1, 1, 13]; key_num(1), is_distinct(FALSE), is_adaptive(0), MEM_USED(0KB), DISK_USED(0KB)
75 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE)
76 #SLCT2: [1, 1, 1]; exp11 = 'W'
77 #INDEX JOIN LEFT JOIN2: [1, 1, 1]: col_num(2) ret_null(0)
78 #SLCT2: [1, 1, 1]; A.D_HDAY < var16
79 #PRJT2: [1, 1, 1]; exp_num(2), is_atom(FALSE)
80 #RN: [1, 1, 1]
81 #HIERARCHICAL QUERY: [1, 1, 1]; key_num(0)
82 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
83 #SLCT2: [1, 1, 0]; rownum <= var20
84 #CSCN2: [1, 1, 0]; SYSINDEXSYSDUAL2(SYSDUAL2)
85 #BLKUP2: [1, 1, 61]; IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB)
86 #SSEK2: [1, 1, 61]; scan_type(ASC), IDX_P_BI_HDAY_SUB(T_P_BI_HDAY_SUB), scan_range[(A.C_HDAY_CODE,A.D_HDAY),(A.C_HDAY_CODE,A.D_H
DAY)]
et(2304508);
SPL2 1 0% 22 70 1 0 0 0 0 0
PRJT2 1 0% 22 13 2 0 0 0 0 0
PRJT2 1 0% 22 6 4 0 0 0 0 0
PRJT2 1 0% 22 11 4 0 0 0 0 0
PIPE2 1 0% 22 2 5 0 0 0 0 0
SLCT2 2 0% 21 14 1 0 0 0 0 0
BLKUP2 3 0% 20 24 2 0 0 0 0 0
PIPE2 4 0% 18 3 5 0 0 0 0 0
PIPE2 4 0% 18 4 5 0 0 0 0 0
IJLO2 6 0% 16 20 5 0 0 0 0 0
UNION_ALL2 6 0% 16 12 5 0 0 0 0 0
DLCK 9 0% 15 0 2 0 0 0 0 0
PRJT2 13 0% 13 19 4 0 0 0 0 0
BLKUP2 13 0% 13 22 4 0 0 0 0 0
SLCT2 29 0.01% 12 21 4 0 0 0 0 0
SSEK2 36 0.01% 11 23 2 0 0 0 0 0
SSEK2 43 0.01% 10 25 1 0 0 0 0 0
NSET2 78 0.02% 9 1 3 0 0 0 0 0
IJI2 6689 1.93% 8 10 12642 0 0 0 0 0
PRJT2 9445 2.72% 7 8 12594 0 0 0 0 0
HAGR2 9658 2.78% 6 7 6299 1649 0 1 0 0
PLL 10094 2.91% 5 26 13012 0 0 0 0 0
SLCT2 12680 3.66% 4 9 12617 0 0 0 0 0
SSEK2 34432 9.93% 3 28 6692 0 0 0 0 0
PRJT2 43375 12.51% 2 5 4 0 0 0 0 0
BLKUP2 220189 63.49% 1 27 13384 0 0 0 0 0
PRJT2 1 0% 22 12 2 0 0 0 0 0
SPL2 1 0% 22 49 1 0 0 0 0 0
SPL2 1 0% 22 28 1 0 0 0 0 0
PIPE2 1 0% 22 3 5 0 0 0 0 0
PRJT2 1 0% 22 10 4 0 0 0 0 0
PIPE2 2 0% 18 4 5 0 0 0 0 0
BLKUP2 2 0% 18 23 2 0 0 0 0 0
SLCT2 2 0% 18 13 1 0 0 0 0 0
SPL2 2 0% 18 68 1 0 0 0 0 0
UNION_ALL2 3 0% 16 11 5 0 0 0 0 0
PRJT2 3 0% 16 6 4 0 0 0 0 0
IJLO2 6 0% 14 19 5 0 0 0 0 0
DLCK 6 0% 14 0 2 0 0 0 0 0
PRJT2 9 0% 13 18 4 0 0 0 0 0
BLKUP2 14 0% 12 21 4 0 0 0 0 0
SLCT2 27 0.01% 11 20 4 0 0 0 0 0
SSEK2 33 0.01% 10 24 1 0 0 0 0 0
SSEK2 38 0.01% 9 22 2 0 0 0 0 0
NSET2 98 0.03% 8 1 3 0 0 0 0 0
IJI2 8396 2.78% 7 9 12596 0 0 0 0 0
PLL 11559 3.82% 6 25 12966 0 0 0 0 0
PRJT2 25936 8.58% 5 8 12594 0 0 0 0 0
BLKUP2 35866 11.86% 4 26 13338 0 0 0 0 0
HAGR2 49627 16.42% 3 7 6299 1649 0 1 0 0
PRJT2 75889 25.1% 2 5 4 0 0 0 0 0
SSEK2 94793 31.36% 1 27 6669 0 0 0 0 0
oracle执行计划
Plan Hash Value :
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 861 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 51 | | |
| 2 | NESTED LOOPS | | 1 | 51 | 429 | 00:00:01 |
| 3 | VIEW | | 2 | 120 | 5 | 00:00:01 |
| 4 | UNION-ALL | | | | | |
| * 5 | VIEW | | 1 | 22 | 4 | 00:00:01 |
| 6 | COUNT | | | | | |
| 7 | VIEW | | 1 | 9 | 4 | 00:00:01 |
| 8 | SORT ORDER BY | | 1 | 24 | 4 | 00:00:01 |
| * 9 | FILTER | | | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 24 | 3 | 00:00:01 |
| * 11 | VIEW | | 1 | 10 | 2 | 00:00:01 |
| 12 | COUNT | | | | | |
| * 13 | CONNECT BY WITHOUT FILTERING | | | | | |
| 14 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | T_P_BI_HDAY_SUB | 1 | 14 | 1 | 00:00:01 |
| * 16 | INDEX UNIQUE SCAN | IDX_P_BI_HDAY_SUB | 1 | | 0 | 00:00:01 |
| * 17 | FILTER | | | | | |
| 18 | NESTED LOOPS OUTER | | 1 | 60 | 5 | 00:00:01 |
| * 19 | TABLE ACCESS BY INDEX ROWID | T_P_AB_PORT | 1 | 24 | 2 | 00:00:01 |
| * 20 | INDEX UNIQUE SCAN | IDX_P_AB_PORT | 1 | | 1 | 00:00:01 |
| * 21 | TABLE ACCESS BY INDEX ROWID BATCHED | T_M_T_PORT_RELA | 1 | 36 | 3 | 00:00:01 |
| * 22 | INDEX RANGE SCAN | IDX_M_T_PORT_RELA | 2 | | 2 | 00:00:01 |
| 23 | NESTED LOOPS OUTER | | 1 | 60 | 5 | 00:00:01 |
| * 24 | TABLE ACCESS BY INDEX ROWID | T_P_AB_PORT | 1 | 24 | 2 | 00:00:01 |
| * 25 | INDEX UNIQUE SCAN | IDX_P_AB_PORT | 1 | | 1 | 00:00:01 |
| * 26 | TABLE ACCESS BY INDEX ROWID BATCHED | T_M_T_PORT_RELA | 1 | 36 | 3 | 00:00:01 |
| * 27 | INDEX RANGE SCAN | IDX_M_T_PORT_RELA | 2 | | 2 | 00:00:01 |
| * 28 | INDEX RANGE SCAN | IDX_M_T_FUNDNAV1 | 1 | 15 | 427 | 00:00:01 |
| 29 | HASH GROUP BY | | 1 | 56 | 861 | 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 56 | 431 | 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 56 | 431 | 00:00:01 |
| 32 | VIEW | | 2 | 120 | 5 | 00:00:01 |
| 33 | UNION-ALL | | | | | |
| * 34 | VIEW | | 1 | 22 | 4 | 00:00:01 |
| 35 | COUNT | | | | | |
| 36 | VIEW | | 1 | 9 | 4 | 00:00:01 |
| 37 | SORT ORDER BY | | 1 | 24 | 4 | 00:00:01 |
| * 38 | FILTER | | | | | |
| 39 | NESTED LOOPS OUTER | | 1 | 24 | 3 | 00:00:01 |
| * 40 | VIEW | | 1 | 10 | 2 | 00:00:01 |
| 41 | COUNT | | | | | |
| * 42 | CONNECT BY WITHOUT FILTERING | | | | | |
| 43 | FAST DUAL | | 1 | | 2 | 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | T_P_BI_HDAY_SUB | 1 | 14 | 1 | 00:00:01 |
| * 45 | INDEX UNIQUE SCAN | IDX_P_BI_HDAY_SUB | 1 | | 0 | 00:00:01 |
| * 46 | FILTER | | | | | |
| 47 | NESTED LOOPS OUTER | | 1 | 60 | 5 | 00:00:01 |
| * 48 | TABLE ACCESS BY INDEX ROWID | T_P_AB_PORT | 1 | 24 | 2 | 00:00:01 |
| * 49 | INDEX UNIQUE SCAN | IDX_P_AB_PORT | 1 | | 1 | 00:00:01 |
| * 50 | TABLE ACCESS BY INDEX ROWID BATCHED | T_M_T_PORT_RELA | 1 | 36 | 3 | 00:00:01 |
| * 51 | INDEX RANGE SCAN | IDX_M_T_PORT_RELA | 2 | | 2 | 00:00:01 |
| 52 | NESTED LOOPS OUTER | | 1 | 60 | 5 | 00:00:01 |
| * 53 | TABLE ACCESS BY INDEX ROWID | T_P_AB_PORT | 1 | 24 | 2 | 00:00:01 |
| * 54 | INDEX UNIQUE SCAN | IDX_P_AB_PORT | 1 | | 1 | 00:00:01 |
| * 55 | TABLE ACCESS BY INDEX ROWID BATCHED | T_M_T_PORT_RELA | 1 | 36 | 3 | 00:00:01 |
| * 56 | INDEX RANGE SCAN | IDX_M_T_PORT_RELA | 2 | | 2 | 00:00:01 |
| * 57 | INDEX RANGE SCAN | IDX_M_T_FUNDNAV1 | 1 | | 427 | 00:00:01 |
| 58 | TABLE ACCESS BY GLOBAL INDEX ROWID | T_M_T_FUNDNAV | 1 | 20 | 429 | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter("RN"=1)
* 9 - filter(NVL("B"."C_DATE_TYPE",'W')='W')
* 11 - filter(INTERNAL_FUNCTION("A"."D_HDAY")<TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 13 - filter(ROWNUM<=370)
* 16 - access("A"."C_HDAY_CODE"="B"."C_HDAY_CODE"(+) AND "B"."D_HDAY"(+)=INTERNAL_FUNCTION("A"."D_HDAY"))
* 16 - filter("B"."D_HDAY"(+)<TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 17 - filter(NULL IS NOT NULL)
* 19 - filter("P"."D_CLOSE">=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."D_BUILD"<=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 20 - access("P"."C_PORT_CODE"='EM2O11')
* 21 - filter("R"."C_PORT_CODE"(+)='EM2O11')
* 22 - access("R"."C_RELA_TYPE"(+)='RELA_BASIC_ZHGZPL' AND "R"."C_DATA_IDF"(+)='RELA_PRO_ACC')
* 24 - filter("P"."D_CLOSE">=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."D_BUILD"<=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 25 - access("P"."C_PORT_CODE"='EM2O11')
* 26 - filter("R"."C_PORT_CODE"(+)='EM2O11')
* 27 - access("R"."C_RELA_TYPE"(+)='RELA_BASIC_ZHGZPL' AND "R"."C_DATA_IDF"(+)='RELA_PRO_ACC')
* 28 - access("BI_V_N_PORTVALUATIONDATE"."C_PORT_CODE"="T"."C_PORT_CODE" AND "BI_V_N_PORTVALUATIONDATE"."GZDATE">"T"."D_ASTSTAT")
* 28 - filter("BI_V_N_PORTVALUATIONDATE"."C_PORT_CODE"="T"."C_PORT_CODE" AND "BI_V_N_PORTVALUATIONDATE"."GZDATE"<=30+INTERNAL_FUNCTION("T"."D_ASTSTAT"))
* 34 - filter("RN"=1)
* 38 - filter(NVL("B"."C_DATE_TYPE",'W')='W')
* 40 - filter(INTERNAL_FUNCTION("A"."D_HDAY")<TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 42 - filter(ROWNUM<=370)
* 45 - access("A"."C_HDAY_CODE"="B"."C_HDAY_CODE"(+) AND "B"."D_HDAY"(+)=INTERNAL_FUNCTION("A"."D_HDAY"))
* 45 - filter("B"."D_HDAY"(+)<TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 46 - filter(NULL IS NOT NULL)
* 48 - filter("P"."D_CLOSE">=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."D_BUILD"<=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 49 - access("P"."C_PORT_CODE"='EM2O11')
* 50 - filter("R"."C_PORT_CODE"(+)='EM2O11')
* 51 - access("R"."C_RELA_TYPE"(+)='RELA_BASIC_ZHGZPL' AND "R"."C_DATA_IDF"(+)='RELA_PRO_ACC')
* 53 - filter("P"."D_CLOSE">=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."D_BUILD"<=TO_DATE(' 2024-04-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 54 - access("P"."C_PORT_CODE"='EM2O11')
* 55 - filter("R"."C_PORT_CODE"(+)='EM2O11')
* 56 - access("R"."C_RELA_TYPE"(+)='RELA_BASIC_ZHGZPL' AND "R"."C_DATA_IDF"(+)='RELA_PRO_ACC')
* 57 - access("BI_V_N_PORTVALUATIONDATE"."C_PORT_CODE"="T"."C_PORT_CODE" AND "BI_V_N_PORTVALUATIONDATE"."GZDATE">"T"."D_ASTSTAT")
* 57 - filter("BI_V_N_PORTVALUATIONDATE"."C_PORT_CODE"="T"."C_PORT_CODE" AND "BI_V_N_PORTVALUATIONDATE"."GZDATE"<=30+INTERNAL_FUNCTION("T"."D_ASTSTAT"))