sql
insert
into
register_book_tmp( org_code ,
org_name ,
project_number ,
project_name ,
product_num ,
product_name ,
detail_option ,
market_yn ,
asset_number ,
asset_name ,
account_number ,
account_name ,
split_yn ,
customer_number ,
customer_name ,
holding_rate ,
str_cost ,
add_cost ,
reduce_cost ,
end_cost ,
str_mkt_value_chg ,
add_mkt_value_chg ,
reduce_mkt_value_chg ,
end_mkt_value_chg ,
str_cost_impairment_provision ,
add_impairment_provision ,
reduce_impairment_provision ,
end_cost_impairment_provision ,
months_invest_income_hold ,
years_invest_income_hold )
WITH TMP_PBPSI as
( select pb.project_id,
pb.project_number,
pb.project_name,
pb.belong_org,
psi.project_id as psiproject_id,
psi.split_unit,
psi.split_type
from amc_base_project_b pb,
amc_project_split_info_b psi
where 1=1
)
SELECT pd.deptcode "deptcode",
pd.deptname "deptname",
/*pb.project_number "project_number1",
pb.project_name "project_name1", */
pbpsi.project_number "project_number1",
pbpsi.project_name "project_name1",
aib.product_num "product_num1",
ptv.prod_type_combined "product_name1", (SELECT pc.parana
FROM pcmc_knp_para pc
WHERE pc.paratp = 'GWAMCC_DETAIL_OPTION'
AND pc.paracd = aib.detail_option) "detail_option", (SELECT pc.parana
FROM pcmc_knp_para pc
WHERE pc.paratp = 'GWAMCC_YES_NO'
AND pc.paracd = cb.market_yn) "market_yn",
epb.asset_number "asset_number",
epb.asset_name "asset_name",
aib.account_number "account_number",
aib.account_name "account_name",
--nvl2(psi.project_id, '1', '2') "split_yn",
nvl2(pbpsi.psiproject_id, '1', '2') "split_yn",
epb.customer_number "customer_number",
epb.customer_name "customer_name",
epb.holding_rate "holding_rate",
nvl(sebv1.cost,0) "str_cost",
nvl(aas2.add_cost,0) "add_cost",
nvl(aas2.reduce_cost,0) "reduce_cost",
nvl(sebv2.cost,0) "end_cost",
nvl(sebv1.mkt_value_chg,0) "str_mkt_value_chg",
nvl(aas2.add_mkt_value_chg,0) "add_mkt_value_chg",
nvl(aas2.reduce_mkt_value_chg,0) "reduce_mkt_value_chg",
nvl(sebv2.mkt_value_chg,0) "end_mkt_value_chg",
nvl(sebv1.cost_impairment_provision,0) "str_cost_impairment_provision",
nvl(aas2.add_impairment_provision,0) "add_impairment_provision",
nvl(aas2.reduce_impairment_provision,0) "reduce_impairment_provision",
nvl(sebv2.cost_impairment_provision,0) "end_cost_impairment_provision",
nvl(aas6.months_invest_income_hold,0) "months_invest_income_hold",
nvl(aas4.years_invest_income_hold,0) "years_invest_income_hold"
FROM amc_asset_account_info_b aib,
tmp_pbpsi pbpsi,
--amc_base_project_b pb,
pcmc_dept pd, (SELECT *
FROM amc_bill_asset_line_b b
WHERE 1 = 1
AND b.bill_id IN (SELECT MAX(balb.bill_id)
FROM amc_bill_asset_line_b balb,
amc_bill_asset_head_b b1,
amc_bill_equity_page_b bepb
WHERE 1 = 1
AND balb.bill_id = b1.bill_id
AND balb.asset_orginal_id = bepb.asset_orginal_id
AND (b1.business_stage = 'INVEST_STAGE'
OR (b1.business_type = 'NORMAL_DISPOSAL'
AND bepb.dispose_flag = 'Y'))
AND ((b1.entry_status IN ('PL',
'GL')
and b1.manual_bill='N')
or (b1.entry_status ='SL'
and b1.manual_bill='Y'))
AND to_char(b1.gl_date, 'yyyy-mm-dd') <= '2024-04-07'
GROUP BY balb.account_id)) alb,
amc_bill_equity_page_b epb,
xxt_coa_prod_type_v ptv,
amc_base_customer_b cb,
--amc_project_split_info_b psi,
(SELECT ebv1.account_id,
ebv1.cost,
ebv1.mkt_value_chg,
ebv1.cost_impairment_provision,
ebv1.belong_org
FROM xxt_sl_asset_details_balance_tmp ebv1
WHERE ebv1.event_id IN (SELECT MAX(ebv2.event_id)
FROM xxt_sl_asset_details_balance_tmp ebv2
WHERE to_char(ebv2.accounting_date, 'yyyy-mm-dd') < '2024-04-07'
GROUP BY ebv2.account_id,
ebv2.project_id,
ebv2.belong_org)) sebv1, (SELECT ebv3.account_id,
ebv3.cost,
ebv3.mkt_value_chg,
ebv3.cost_impairment_provision,
ebv3.belong_org
FROM xxt_sl_asset_details_balance_tmp ebv3
WHERE ebv3.event_id IN (SELECT MAX(ebv4.event_id)
FROM xxt_sl_asset_details_balance_tmp ebv4
WHERE to_char(ebv4.accounting_date, 'yyyy-mm-dd') <= '2024-04-07'
GROUP BY ebv4.account_id,
ebv4.project_id,
ebv4.belong_org)) sebv2, (SELECT balb1.account_id,
aas1.segment1,
SUM(decode(xcb1.attribute3,
'COST', aas1.entered_dr,
0)) add_cost,
SUM(decode(xcb1.attribute3,
'COST', aas1.entered_cr,
0)) reduce_cost,
SUM(decode(xcb1.attribute3,
'MKT_VALUE_CHG', aas1.entered_dr,
0)) add_mkt_value_chg,
SUM(decode(xcb1.attribute3,
'MKT_VALUE_CHG', aas1.entered_cr,
0)) reduce_mkt_value_chg,
SUM(decode(xcb1.attribute3,
'COST_IMPAIRMENT_PROVISION', aas1.entered_cr,
0)) add_impairment_provision,
SUM(decode(xcb1.attribute3,
'COST_IMPAIRMENT_PROVISION', aas1.entered_dr,
0)) reduce_impairment_provision,
SUM(decode(xcb1.attribute3,
'RECEIVABLE_DIVIDEND', aas1.entered_cr - aas1.entered_dr,
0)) months_invest_income_hold
FROM amc_entry_sl_v aas1,
amc_bill_asset_line_b balb1,
xxt_sl_coa_balance_v xcb1,
amc_bill_asset_head_b bahb1
WHERE aas1.entity_id = balb1.bill_line_id
AND aas1.segment3 = xcb1.flex_value
AND balb1.bill_id = bahb1.bill_id
AND ((bahb1.entry_status IN ('PL',
'GL')
and bahb1.manual_bill='N')
or (bahb1.entry_status='SL'
and bahb1.manual_bill='Y'))
AND aas1.entity_category = 'BAD_ASSETS'
AND aas1.relation_target = 'EVENT'
AND (('2024-04-07' <= to_char(aas1.accounting_date, 'yyyy-mm-dd')
and '2024-04-07' >= to_char(aas1.accounting_date, 'yyyy-mm-dd')))
GROUP BY balb1.account_id,
aas1.segment1) aas2, (SELECT balb5.account_id,
aas5.segment1,
SUM(aas5.entered_cr - aas5.entered_dr) months_invest_income_hold
FROM amc_entry_sl_v aas5,
amc_bill_asset_line_b balb5,
xxt_sl_coa_balance_v xcb5,
amc_bill_asset_head_b bahb5
WHERE aas5.entity_id = balb5.bill_line_id
AND aas5.segment3 = xcb5.flex_value
AND balb5.bill_id = bahb5.bill_id
AND bahb5.business_stage = 'HANDLE_STAGE'
AND ((bahb5.entry_status IN ('PL',
'GL')
and bahb5.manual_bill='N')
or (bahb5.entry_status='SL'
and bahb5.manual_bill='Y'))
AND xcb5.attribute3 = 'REC_DIVIDEND'
AND aas5.entity_category = 'BAD_ASSETS'
AND aas5.relation_target = 'EVENT'
AND (('2024-04-07' <= to_char(aas5.accounting_date, 'yyyy-mm-dd')
and '2024-04-07' >= to_char(aas5.accounting_date, 'yyyy-mm-dd')))
GROUP BY balb5.account_id,
aas5.segment1) aas6, (SELECT balb3.account_id,
aas3.segment1,
(SUM(aas3.entered_cr) - SUM(aas3.entered_dr)) years_invest_income_hold
FROM amc_entry_sl_v aas3,
amc_bill_asset_line_b balb3,
xxt_sl_coa_balance_v xcb3,
amc_bill_asset_head_b bahb3
WHERE aas3.entity_id = balb3.bill_line_id
AND aas3.segment3 = xcb3.flex_value
AND balb3.bill_id = bahb3.bill_id
AND ((bahb3.entry_status IN ('PL',
'GL')
and bahb3.manual_bill='N')
or (bahb3.entry_status='SL'
and bahb3.manual_bill='Y'))
AND bahb3.business_stage = 'HANDLE_STAGE'
AND xcb3.attribute3 = 'REC_DIVIDEND'
AND aas3.entity_category = 'BAD_ASSETS'
AND aas3.relation_target = 'EVENT'
AND to_char(aas3.accounting_date, 'yyyy-mm-dd') <= '2024-04-07'
GROUP BY balb3.account_id,
aas3.segment1) aas4
WHERE --aib.project_id = pb.project_id
aib.project_id = pbpsi.project_id
--AND nvl(psi.split_unit,pb.belong_org) = pd.deptcode
AND nvl(pbpsi.split_unit,pbpsi.belong_org) = pd.deptcode
AND aib.account_id = alb.account_id
AND alb.asset_orginal_id = epb.asset_orginal_id
AND aib.product_num = ptv.prod_type_code
AND epb.customer_number = cb.customer_number
AND (aib.detail_option IN ('HOLDING_SUBSIDIARY',
'JOINT_CONT_SIGN_INFL')
OR aib.product_num IN ('2010010101',
'2010010201',
'2010010301',
'2010010401',
'2010010501',
'2010010601',
'2010010701',
'2010010801',
'2010020101',
'2010020201'))
AND aib.account_id = sebv1.account_id(+)
AND aib.account_id = sebv2.account_id(+)
AND aib.account_id = aas2.account_id(+)
AND aib.account_id = aas6.account_id(+)
AND aib.account_id = aas4.account_id(+)
/* AND nvl(psi.split_unit,pb.belong_org) = sebv1.belong_org(+)
AND nvl(psi.split_unit,pb.belong_org) = sebv2.belong_org(+)
AND nvl(psi.split_unit,pb.belong_org) = aas2.segment1(+)
AND nvl(psi.split_unit,pb.belong_org) = aas4.segment1(+)
AND nvl(psi.split_unit,pb.belong_org) = aas6.segment1(+)
AND aib.project_id = psi.project_id(+)
AND nvl(psi.split_type,'0') <> '1'*/
AND nvl(pbpsi.split_unit,pbpsi.belong_org) = sebv1.belong_org(+)
AND nvl(pbpsi.split_unit,pbpsi.belong_org) = sebv2.belong_org(+)
AND nvl(pbpsi.split_unit,pbpsi.belong_org) = aas2.segment1(+)
AND nvl(pbpsi.split_unit,pbpsi.belong_org) = aas4.segment1(+)
AND nvl(pbpsi.split_unit,pbpsi.belong_org) = aas6.segment1(+)
AND aib.project_id = pbpsi.project_id(+)
AND nvl(pbpsi.split_type,'0') <> '1' ;
执行计划
1 #INSERT : [0, 0, 0]; table(REGISTER_BOOK_TMP), type(select), hp_opt(0), mpp_opt(0)
2 #PIPE2: [470, 66, 9868]
3 #PIPE2: [470, 66, 9868]
4 #PIPE2: [470, 66, 9868]
5 #PIPE2: [460, 66, 9868]
6 #PIPE2: [450, 66, 9868]
7 #PIPE2: [440, 66, 9868]
8 #PIPE2: [430, 66, 9868]
9 #PIPE2: [420, 66, 9868]
10 #PRJT2: [35, 66, 9868]; exp_num(30), is_atom(FALSE)
11 #UNION FOR OR2: [35, 66, 9868]; key_num(0), outer_join(-)
12 #HASH RIGHT SEMI JOIN2: [17, 33, 9868]; n_keys(1) KEY(DMTEMPVIEW_889785542.colname=DMTEMPVIEW_889785386.TMPCOL3) KEY_NULL_EQU(0)
13 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
14 #HEAP TABLE SCAN: [17, 665, 9868]; table_no(0)
15 #HASH RIGHT SEMI JOIN2: [17, 33, 9868]; n_keys(1) KEY(DMTEMPVIEW_889785543.colname=DMTEMPVIEW_889785386.TMPCOL2) KEY_NULL_EQU(0)
16 #CONST VALUE LIST: [1, 10, 48]; row_num(10), col_num(1)
17 #SLCT2: [17, 33, 9868]; exp11
18 #HEAP TABLE SCAN: [17, 665, 9868]; table_no(0)
19 #HEAP TABLE: [384, 665, 9868]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
20 #HASH2 INNER JOIN: [384, 665, 9868]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=PD.DEPTCODE) KEY_NULL_EQU(0)
21 #HASH2 INNER JOIN: [382, 665, 9772]; KEY_NUM(1); KEY(PTV.PROD_TYPE_CODE=AIB.PRODUCT_NUM) KEY_NULL_EQU(0)
22 #PRJT2: [1, 222, 816]; exp_num(2), is_atom(FALSE)
23 #CSCN2: [1, 222, 816]; INDEX33558407(XXT_COA_PROD_TYPE_B); btr_scan(1)
24 #SLCT2: [379, 665, 8956]; AIB.ACCOUNT_ID = ALB.ACCOUNT_ID
25 #NEST LOOP INNER JOIN2: [379, 665, 8956]; [with var]
26 #HASH2 INNER JOIN: [34, 63, 3550]; KEY_NUM(1); KEY(ALB.ASSET_ORGINAL_ID=EPB.ASSET_ORGINAL_ID) KEY_NULL_EQU(0)
27 #PRJT2: [25, 64, 3202]; exp_num(2), is_atom(FALSE)
28 #NEST LOOP INDEX JOIN2: [25, 64, 3202]
29 #DISTINCT: [24, 8, 439]
30 #PRJT2: [23, 8, 439]; exp_num(1), is_atom(FALSE)
31 #HAGR2: [23, 8, 439]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(BALB.ACCOUNT_ID)
32 #UNION FOR OR2: [22, 283, 439]; key_num(0), outer_join(-)
33 #UNION FOR OR2: [19, 136, 439]; key_num(0), outer_join(-)
34 #UNION FOR OR2: [3, 15, 439]; key_num(0), outer_join(-)
35 #HASH2 INNER JOIN: [1, 8, 439]; KEY_NUM(1); KEY(BALB.ASSET_ORGINAL_ID=BEPB.ASSET_ORGINAL_ID) KEY_NULL_EQU(0)
36 #NEST LOOP INDEX JOIN2: [1, 8, 349]
37 #SLCT2: [1, 1, 247]; B1.MANUAL_BILL = 'Y'
38 #BLKUP2: [1, 2, 247]; AMC_BILL_ASSET_HEAD_U3(B1)
39 #SLCT2: [1, 2, 247]; exp11 <= '2024-04-07'
40 #SSEK2: [1, 2, 247]; scan_type(ASC), AMC_BILL_ASSET_HEAD_U3(AMC_BILL_ASSET_HEAD_B as B1), scan_range[('INVEST_STAGE','SL',min),('INVEST_STAGE','SL',max))
41 #BLKUP2: [1, 8, 30]; AMC_BILL_ASSET_LINE_B_DSH0320_01(BALB)
42 #SSEK2: [1, 8, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_B_DSH0320_01(AMC_BILL_ASSET_LINE_B as BALB), scan_range[B1.BILL_ID,B1.BILL_ID]
43 #CSCN2: [1, 1204, 90]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as BEPB); btr_scan(1)
44 #HASH2 INNER JOIN: [1, 7, 439]; KEY_NUM(1); KEY(BALB.ASSET_ORGINAL_ID=BEPB.ASSET_ORGINAL_ID) KEY_NULL_EQU(0)
45 #NEST LOOP INDEX JOIN2: [1, 8, 349]
46 #SLCT2: [1, 1, 247]; (B1.BUSINESS_TYPE = 'NORMAL_DISPOSAL' AND B1.MANUAL_BILL = 'Y' AND exp11 <= '2024-04-07' AND exp11)
47 #BLKUP2: [1, 54, 247]; AMC_BILL_ASSET_HEAD_B_DSH0320_01(B1)
48 #SSEK2: [1, 54, 247]; scan_type(ASC), AMC_BILL_ASSET_HEAD_B_DSH0320_01(AMC_BILL_ASSET_HEAD_B as B1), scan_range['SL','SL']
49 #BLKUP2: [1, 8, 30]; AMC_BILL_ASSET_LINE_B_DSH0320_01(BALB)
50 #SSEK2: [1, 8, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_B_DSH0320_01(AMC_BILL_ASSET_LINE_B as BALB), scan_range[B1.BILL_ID,B1.BILL_ID]
51 #SLCT2: [1, 147, 90]; BEPB.DISPOSE_FLAG = 'Y'
52 #CSCN2: [1, 1204, 90]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as BEPB); btr_scan(1)
53 #SLCT2: [15, 120, 439]; exp11
54 #HASH2 INNER JOIN: [15, 120, 439]; KEY_NUM(1); KEY(BALB.BILL_ID=B1.BILL_ID) KEY_NULL_EQU(0)
55 #NEST LOOP INDEX JOIN2: [8, 1204, 192]
56 #CSCN2: [1, 1204, 90]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as BEPB); btr_scan(1)
57 #BLKUP2: [7, 1, 30]; AMC_BILL_ASSET_LINE_N2(BALB)
58 #SSEK2: [7, 1, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_N2(AMC_BILL_ASSET_LINE_B as BALB), scan_range[BEPB.ASSET_ORGINAL_ID,BEPB.ASSET_ORGINAL_ID]
59 #SLCT2: [5, 4003, 247]; (B1.MANUAL_BILL = 'N' AND exp11)
60 #NEST LOOP INDEX JOIN2: [5, 4003, 247]
61 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
62 #BLKUP2: [5, 2001, 247]; AMC_BILL_ASSET_HEAD_U3(B1)
63 #SLCT2: [5, 2001, 247]; exp11 <= '2024-04-07'
64 #SSEK2: [5, 2001, 247]; scan_type(ASC), AMC_BILL_ASSET_HEAD_U3(AMC_BILL_ASSET_HEAD_B as B1), scan_range[('INVEST_STAGE',DMTEMPVIEW_889785405.colname,min),('INVEST_STAGE',DMTEMPVIEW_889785405.colname,max))
65 #HASH RIGHT SEMI JOIN2: [2, 147, 439]; n_keys(1) KEY(DMTEMPVIEW_889785544.colname=B1.ENTRY_STATUS) KEY_NULL_EQU(0)
66 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
67 #SLCT2: [2, 147, 439]; (B1.BUSINESS_TYPE = 'NORMAL_DISPOSAL' AND B1.MANUAL_BILL = 'N' AND exp11 <= '2024-04-07' AND exp11 AND exp11 AND exp11)
68 #NEST LOOP INDEX JOIN2: [2, 147, 439]
69 #NEST LOOP INDEX JOIN2: [1, 147, 192]
70 #SLCT2: [1, 147, 90]; BEPB.DISPOSE_FLAG = 'Y'
71 #CSCN2: [1, 1204, 90]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as BEPB); btr_scan(1)
72 #BLKUP2: [1, 1, 30]; AMC_BILL_ASSET_LINE_N2(BALB)
73 #SSEK2: [1, 1, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_N2(AMC_BILL_ASSET_LINE_B as BALB), scan_range[BEPB.ASSET_ORGINAL_ID,BEPB.ASSET_ORGINAL_ID]
74 #BLKUP2: [1, 1, 30]; INDEX33557203(B1)
75 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as B1), scan_range[BALB.BILL_ID,BALB.BILL_ID]
76 #BLKUP2: [1, 8, 30]; AMC_BILL_ASSET_LINE_B_DSH0320_01(B)
77 #SSEK2: [1, 8, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_B_DSH0320_01(AMC_BILL_ASSET_LINE_B as B), scan_range[DMTEMPVIEW_889785383.colname,DMTEMPVIEW_889785383.colname]
78 #NEST LOOP INDEX JOIN2: [8, 1204, 348]
79 #CSCN2: [1, 1204, 252]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as EPB); btr_scan(1)
80 #BLKUP2: [7, 1, 48]; INDEX33558472(CB)
81 #SSEK2: [7, 1, 48]; scan_type(ASC), INDEX33558472(AMC_BASE_CUSTOMER_B as CB), scan_range[EPB.CUSTOMER_NUMBER,EPB.CUSTOMER_NUMBER]
82 #HASH RIGHT JOIN2: [58, 83, 5406]; key_num(1), ret_null(0), KEY(AAS4.SEGMENT1=exp11)
83 #PRJT2: [10, 8, 1502]; exp_num(2), is_atom(FALSE)
84 #HAGR2: [10, 8, 1502]; grp_num(2), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(BALB3.ACCOUNT_ID, AAS3.SEGMENT1)
85 #UNION FOR OR2: [9, 84, 1502]; key_num(0), outer_join(-)
86 #HASH2 INNER JOIN: [3, 9, 1502]; KEY_NUM(1); KEY(AAS3.SEGMENT3=XCB3.FLEX_VALUE) KEY_NULL_EQU(0)
87 #HASH2 INNER JOIN: [2, 3, 1406]; KEY_NUM(1); KEY(BALB3.BILL_LINE_ID=AAS3.ENTITY_ID) KEY_NULL_EQU(0)
88 #NEST LOOP INDEX JOIN2: [1, 1, 288]
89 #SLCT2: [1, 1, 186]; BAHB3.MANUAL_BILL = 'Y'
90 #BLKUP2: [1, 11, 186]; AMC_BILL_ASSET_HEAD_U3(BAHB3)
91 #SSEK2: [1, 11, 186]; scan_type(ASC), AMC_BILL_ASSET_HEAD_U3(AMC_BILL_ASSET_HEAD_B as BAHB3), scan_range[('HANDLE_STAGE','SL',min),('HANDLE_STAGE','SL',max))
92 #SSEK2: [1, 1, 90]; scan_type(ASC), INDEX33559143(AMC_BILL_ASSET_LINE_B as BALB3), scan_range[(BAHB3.BILL_ID,var19,min),(BAHB3.BILL_ID,var19,max))
93 #HEAP TABLE SCAN: [1, 50, 1118]; table_no(4)
94 #HEAP TABLE SCAN: [1, 25, 96]; table_no(5)
95 #HASH2 INNER JOIN: [4, 75, 1502]; KEY_NUM(1); KEY(XCB3.FLEX_VALUE=AAS3.SEGMENT3) KEY_NULL_EQU(0)
96 #HEAP TABLE SCAN: [1, 25, 96]; table_no(5)
97 #HASH2 INNER JOIN: [2, 30, 1406]; KEY_NUM(1); KEY(BALB3.BILL_LINE_ID=AAS3.ENTITY_ID) KEY_NULL_EQU(0)
98 #HASH RIGHT SEMI JOIN2: [1, 10, 288]; n_keys(1) KEY(DMTEMPVIEW_889785545.colname=BAHB3.ENTRY_STATUS) KEY_NULL_EQU(0)
99 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
100 #SLCT2: [1, 10, 288]; (BAHB3.MANUAL_BILL = 'N' AND BAHB3.BUSINESS_STAGE = 'HANDLE_STAGE' AND exp11)
101 #NEST LOOP INDEX JOIN2: [1, 10, 288]
102 #BLKUP2: [1, 10, 102]; AMC_BILL_ASSET_LINE_N1(BALB3)
103 #SSEK2: [1, 10, 102]; scan_type(ASC), AMC_BILL_ASSET_LINE_N1(AMC_BILL_ASSET_LINE_B as BALB3), scan_range[var19,var19]
104 #BLKUP2: [1, 1, 30]; INDEX33557203(BAHB3)
105 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as BAHB3), scan_range[BALB3.BILL_ID,BALB3.BILL_ID]
106 #HEAP TABLE SCAN: [1, 50, 1118]; table_no(4)
107 #HASH RIGHT JOIN2: [47, 83, 3904]; key_num(1), ret_null(0), KEY(AAS6.SEGMENT1=exp11)
108 #PRJT2: [10, 8, 1502]; exp_num(2), is_atom(FALSE)
109 #HAGR2: [10, 8, 1502]; grp_num(2), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(DMTEMPVIEW_889785378.TMPCOL0, DMTEMPVIEW_889785378.TMPCOL1)
110 #PRJT2: [9, 84, 1502]; exp_num(3), is_atom(FALSE)
111 #UNION FOR OR2: [9, 84, 1502]; key_num(0), outer_join(-)
112 #HASH2 INNER JOIN: [3, 9, 1502]; KEY_NUM(1); KEY(AAS5.SEGMENT3=XCB5.FLEX_VALUE) KEY_NULL_EQU(0)
113 #HASH2 INNER JOIN: [2, 3, 1406]; KEY_NUM(1); KEY(BALB5.BILL_LINE_ID=AAS5.ENTITY_ID) KEY_NULL_EQU(0)
114 #NEST LOOP INDEX JOIN2: [1, 1, 288]
115 #SLCT2: [1, 1, 186]; BAHB5.MANUAL_BILL = 'Y'
116 #BLKUP2: [1, 11, 186]; AMC_BILL_ASSET_HEAD_U3(BAHB5)
117 #SSEK2: [1, 11, 186]; scan_type(ASC), AMC_BILL_ASSET_HEAD_U3(AMC_BILL_ASSET_HEAD_B as BAHB5), scan_range[('HANDLE_STAGE','SL',min),('HANDLE_STAGE','SL',max))
118 #SSEK2: [1, 1, 90]; scan_type(ASC), INDEX33559143(AMC_BILL_ASSET_LINE_B as BALB5), scan_range[(BAHB5.BILL_ID,var19,min),(BAHB5.BILL_ID,var19,max))
119 #HEAP TABLE SCAN: [1, 50, 1118]; table_no(2)
120 #HEAP TABLE SCAN: [1, 25, 96]; table_no(3)
121 #HASH2 INNER JOIN: [4, 75, 1502]; KEY_NUM(1); KEY(XCB5.FLEX_VALUE=AAS5.SEGMENT3) KEY_NULL_EQU(0)
122 #HEAP TABLE SCAN: [1, 25, 96]; table_no(3)
123 #HASH2 INNER JOIN: [2, 30, 1406]; KEY_NUM(1); KEY(BALB5.BILL_LINE_ID=AAS5.ENTITY_ID) KEY_NULL_EQU(0)
124 #HASH RIGHT SEMI JOIN2: [1, 10, 288]; n_keys(1) KEY(DMTEMPVIEW_889785546.colname=BAHB5.ENTRY_STATUS) KEY_NULL_EQU(0)
125 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
126 #SLCT2: [1, 10, 288]; (BAHB5.MANUAL_BILL = 'N' AND BAHB5.BUSINESS_STAGE = 'HANDLE_STAGE' AND exp11)
127 #NEST LOOP INDEX JOIN2: [1, 10, 288]
128 #BLKUP2: [1, 10, 102]; AMC_BILL_ASSET_LINE_N1(BALB5)
129 #SSEK2: [1, 10, 102]; scan_type(ASC), AMC_BILL_ASSET_LINE_N1(AMC_BILL_ASSET_LINE_B as BALB5), scan_range[var19,var19]
130 #BLKUP2: [1, 1, 30]; INDEX33557203(BAHB5)
131 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as BAHB5), scan_range[BALB5.BILL_ID,BALB5.BILL_ID]
132 #HEAP TABLE SCAN: [1, 50, 1118]; table_no(2)
133 #HASH RIGHT JOIN2: [36, 83, 2402]; key_num(1), ret_null(0), KEY(AAS2.SEGMENT1=exp11)
134 #PRJT2: [28, 10, 1454]; exp_num(7), is_atom(FALSE)
135 #HAGR2: [28, 10, 1454]; grp_num(2), sfun_num(6), distinct_flag[0,0,0,0,0,0]; slave_empty(0) keys(DMTEMPVIEW_889785375.TMPCOL0, DMTEMPVIEW_889785375.TMPCOL1)
136 #PRJT2: [27, 101, 1454]; exp_num(8), is_atom(FALSE)
137 #UNION FOR OR2: [27, 101, 1454]; key_num(0), outer_join(-)
138 #HASH2 INNER JOIN: [13, 9, 1454]; KEY_NUM(1); KEY(AAS1.SEGMENT3=XCB1.FLEX_VALUE) KEY_NULL_EQU(0)
139 #HASH2 INNER JOIN: [2, 3, 1358]; KEY_NUM(1); KEY(BALB1.BILL_LINE_ID=AAS1.ENTITY_ID) KEY_NULL_EQU(0)
140 #NEST LOOP INDEX JOIN2: [1, 1, 240]
141 #SLCT2: [1, 1, 138]; BAHB1.MANUAL_BILL = 'Y'
142 #BLKUP2: [1, 54, 138]; AMC_BILL_ASSET_HEAD_B_DSH0320_01(BAHB1)
143 #SSEK2: [1, 54, 138]; scan_type(ASC), AMC_BILL_ASSET_HEAD_B_DSH0320_01(AMC_BILL_ASSET_HEAD_B as BAHB1), scan_range['SL','SL']
144 #SSEK2: [1, 1, 90]; scan_type(ASC), INDEX33559143(AMC_BILL_ASSET_LINE_B as BALB1), scan_range[(BAHB1.BILL_ID,var19,min),(BAHB1.BILL_ID,var19,max))
145 #HEAP TABLE SCAN: [1, 50, 1118]; table_no(1)
146 #PRJT2: [10, 1000, 96]; exp_num(2), is_atom(FALSE)
147 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889785366@TOEBS
148 #HASH2 INNER JOIN: [13, 92, 1454]; KEY_NUM(1); KEY(AAS1.SEGMENT3=XCB1.FLEX_VALUE) KEY_NULL_EQU(0)
149 #HASH2 INNER JOIN: [2, 30, 1358]; KEY_NUM(1); KEY(BALB1.BILL_LINE_ID=AAS1.ENTITY_ID) KEY_NULL_EQU(0)
150 #HASH RIGHT SEMI JOIN2: [1, 10, 240]; n_keys(1) KEY(DMTEMPVIEW_889785547.colname=BAHB1.ENTRY_STATUS) KEY_NULL_EQU(0)
151 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
152 #SLCT2: [1, 10, 240]; (BAHB1.MANUAL_BILL = 'N' AND exp11)
153 #NEST LOOP INDEX JOIN2: [1, 10, 240]
154 #BLKUP2: [1, 10, 102]; AMC_BILL_ASSET_LINE_N1(BALB1)
155 #SSEK2: [1, 10, 102]; scan_type(ASC), AMC_BILL_ASSET_LINE_N1(AMC_BILL_ASSET_LINE_B as BALB1), scan_range[var19,var19]
156 #BLKUP2: [1, 1, 30]; INDEX33557203(BAHB1)
157 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as BAHB1), scan_range[BALB1.BILL_ID,BALB1.BILL_ID]
158 #HEAP TABLE SCAN: [1, 50, 1118]; table_no(1)
159 #PRJT2: [10, 1000, 96]; exp_num(2), is_atom(FALSE)
160 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889785366@TOEBS
161 #HASH RIGHT JOIN2: [6, 83, 948]; key_num(1), ret_null(0), KEY(SEBV2.BELONG_ORG=exp11)
162 #PRJT2: [2, 1, 198]; exp_num(4), is_atom(FALSE)
163 #HASH LEFT SEMI JOIN2: [2, 1, 198]; KEY_NUM(1); KEY(EBV3.EVENT_ID=DMTEMPVIEW_889785372.colname) KEY_NULL_EQU(0)
164 #SLCT2: [1, 1, 198]; EBV3.ACCOUNT_ID = var19
165 #CSCN2: [1, 1, 198]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV3); btr_scan(1)
166 #PRJT2: [1, 1, 151]; exp_num(1), is_atom(FALSE)
167 #HAGR2: [1, 1, 151]; grp_num(3), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EBV4.ACCOUNT_ID, EBV4.PROJECT_ID, EBV4.BELONG_ORG)
168 #SLCT2: [1, 1, 151]; exp11 <= '2024-04-07'
169 #CSCN2: [1, 1, 151]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV4); btr_scan(1)
170 #HASH RIGHT JOIN2: [4, 83, 750]; key_num(1), ret_null(0), KEY(SEBV1.BELONG_ORG=exp11)
171 #PRJT2: [2, 1, 198]; exp_num(4), is_atom(FALSE)
172 #HASH LEFT SEMI JOIN2: [2, 1, 198]; KEY_NUM(1); KEY(EBV1.EVENT_ID=DMTEMPVIEW_889785371.colname) KEY_NULL_EQU(0)
173 #SLCT2: [1, 1, 198]; EBV1.ACCOUNT_ID = var19
174 #CSCN2: [1, 1, 198]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV1); btr_scan(1)
175 #PRJT2: [1, 1, 151]; exp_num(1), is_atom(FALSE)
176 #HAGR2: [1, 1, 151]; grp_num(3), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EBV2.ACCOUNT_ID, EBV2.PROJECT_ID, EBV2.BELONG_ORG)
177 #SLCT2: [1, 1, 151]; exp11 < '2024-04-07'
178 #CSCN2: [1, 1, 151]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV2); btr_scan(1)
179 #SLCT2: [1, 83, 552]; AIB.PROJECT_ID = PBPSI.PROJECT_ID
180 #NEST LOOP INNER JOIN2: [1, 83, 552]; [with var]
181 #BLKUP2: [1, 1, 252]; INDEX33557263(AIB)
182 #SSEK2: [1, 1, 252]; scan_type(ASC), INDEX33557263(AMC_ASSET_ACCOUNT_INFO_B as AIB), scan_range[var19,var19]
183 #PRJT2: [1, 83, 300]; exp_num(6), is_atom(FALSE)
184 #NEST LOOP INNER JOIN2: [1, 83, 300];
185 #BLKUP2: [1, 1, 174]; INDEX33557197(PB)
186 #SSEK2: [1, 1, 174]; scan_type(ASC), INDEX33557197(AMC_BASE_PROJECT_B as PB), scan_range[var20,var20]
187 #SLCT2: [1, 83, 126]; exp11 <> '1'
188 #CSCN2: [1, 88, 126]; INDEX33557680(AMC_PROJECT_SPLIT_INFO_B as PSI); btr_scan(1)
189 #CSCN2: [1, 777, 96]; INDEX33558127(PCMC_DEPT as PD); btr_scan(1)
190 #HEAP TABLE: [10, 50, 1118]; table_no(1) full(0), mpp_full(0) autoid(0), sites(-)
191 #PRJT2: [10, 50, 1118]; exp_num(5), is_atom(FALSE)
192 #SLCT2: [10, 50, 1118]; (TMPREMOTETAB889785365.ENTITY_CATEGORY = 'BAD_ASSETS' AND TMPREMOTETAB889785365.RELATION_TARGET = 'EVENT' AND var1 >= '2024-04-07' AND var1 <= '2024-04-07')
193 #REMOTE SCAN: [10, 1000, 1118]; TMPREMOTETAB889785365@TOEBS
194 #HEAP TABLE: [10, 50, 1118]; table_no(2) full(0), mpp_full(0) autoid(0), sites(-)
195 #PRJT2: [10, 50, 1118]; exp_num(5), is_atom(FALSE)
196 #SLCT2: [10, 50, 1118]; (TMPREMOTETAB889785367.ENTITY_CATEGORY = 'BAD_ASSETS' AND TMPREMOTETAB889785367.RELATION_TARGET = 'EVENT' AND var1 >= '2024-04-07' AND var1 <= '2024-04-07')
197 #REMOTE SCAN: [10, 1000, 1118]; TMPREMOTETAB889785367@TOEBS
198 #HEAP TABLE: [10, 25, 96]; table_no(3) full(0), mpp_full(0) autoid(0), sites(-)
199 #PRJT2: [10, 25, 96]; exp_num(1), is_atom(FALSE)
200 #SLCT2: [10, 25, 96]; TMPREMOTETAB889785368.ATTRIBUTE3 = 'REC_DIVIDEND'
201 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889785368@TOEBS
202 #HEAP TABLE: [10, 50, 1118]; table_no(4) full(0), mpp_full(0) autoid(0), sites(-)
203 #PRJT2: [10, 50, 1118]; exp_num(5), is_atom(FALSE)
204 #SLCT2: [10, 50, 1118]; (TMPREMOTETAB889785369.ENTITY_CATEGORY = 'BAD_ASSETS' AND TMPREMOTETAB889785369.RELATION_TARGET = 'EVENT' AND exp11 <= '2024-04-07')
205 #REMOTE SCAN: [10, 1000, 1118]; TMPREMOTETAB889785369@TOEBS
206 #HEAP TABLE: [10, 25, 96]; table_no(5) full(0), mpp_full(0) autoid(0), sites(-)
207 #PRJT2: [10, 25, 96]; exp_num(1), is_atom(FALSE)
208 #SLCT2: [10, 25, 96]; TMPREMOTETAB889785370.ATTRIBUTE3 = 'REC_DIVIDEND'
209 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889785370@TOEBS
210 #SPL2: [1, 1, 144]; key_num(1), spool_num(1), is_atom(TRUE), has_var(1), sites(-)
211 #PRJT2: [1, 1, 144]; exp_num(1), is_atom(TRUE)
212 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_TP_CD_NA(PCMC_KNP_PARA as PC), scan_range[('GWAMCC_YES_NO',var18,min),('GWAMCC_YES_NO',var18,max))
213 #SPL2: [1, 1, 144]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
214 #PRJT2: [1, 1, 144]; exp_num(1), is_atom(TRUE)
215 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_TP_CD_NA(PCMC_KNP_PARA as PC), scan_range[('GWAMCC_DETAIL_OPTION',var17,min),('GWAMCC_DETAIL_OPTION',var17,max))
解决思路
-
尝试执行每个子查询，分析结果集大小，其中子查询单独执行耗时较多的不宜用hash join。
-
对整个查询where条件尝试加减法，发现只要其中一个连接条件都会让 查询效率和原来相比下降很多。
-
分析这些连接条件，它们都有一个共同地方，使用了临时表pbpsi。
-
分析pbpsi这部分表相关的执行计划，猜测瓶颈在这里，尝试使用 NO_USE_CVT_VAR。
-
加
/*+ NO_USE_CVT_VAR*/后，执行计划发生改变，查询用时从1min变为12s。 -
瓶颈究竟出现在哪？显然不在内层循环，循环后还只是一个hash right join。查找其他 use var的地方。
-
此时外层表连接没有nest loop。推测是最外层连接时use var的原因。
优化后执行计划
1 #INSERT : [0, 0, 0]; table(REGISTER_BOOK_TMP), type(select), hp_opt(0), mpp_opt(0)
2 #PIPE2: [17103, 26, 9760]
3 #PIPE2: [17103, 26, 9760]
4 #PRJT2: [17103, 26, 9760]; exp_num(30), is_atom(FALSE)
5 #HASH2 INNER JOIN: [17103, 26, 9760]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=PD.DEPTCODE) KEY_NULL_EQU(0)
6 #HASH2 INNER JOIN: [17102, 26, 9664]; KEY_NUM(1); KEY(AIB.PRODUCT_NUM=PTV.PROD_TYPE_CODE) KEY_NULL_EQU(0)
7 #SLCT2: [17101, 33, 8848]; (AIB.DETAIL_OPTION IN LIST OR AIB.PRODUCT_NUM IN LIST)
8 #HASH2 INNER JOIN: [17101, 33, 8848]; KEY_NUM(1); KEY(ALB.ACCOUNT_ID=AIB.ACCOUNT_ID) KEY_NULL_EQU(0)
9 #NEST LOOP INDEX JOIN2: [19, 8, 3514]
10 #HASH2 INNER JOIN: [19, 8, 3418]; KEY_NUM(1); KEY(ALB.ASSET_ORGINAL_ID=EPB.ASSET_ORGINAL_ID) KEY_NULL_EQU(0)
11 #PRJT2: [18, 8, 3166]; exp_num(2), is_atom(FALSE)
12 #NEST LOOP INDEX JOIN2: [18, 8, 3166]
13 #DISTINCT: [18, 1, 403]
14 #PRJT2: [16, 1, 403]; exp_num(1), is_atom(FALSE)
15 #HAGR2: [16, 1, 403]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(BALB.ACCOUNT_ID)
16 #INDEX JOIN SEMI JOIN2: [15, 1203, 403]; join condition((exp11 <= '2024-04-07' AND B1.MANUAL_BILL IN LIST AND ((B1.ENTRY_STATUS = 'SL' AND B1.MANUAL_BILL = 'Y' AND B1.BUSINESS_STAGE = 'INVEST_STAGE') OR (B1.BUSINESS_TYPE = 'NORMAL_DISPOSAL' AND BEPB.DISPOSE_FLAG = 'Y' AND B1.ENTRY_STATUS = 'SL' AND B1.MANUAL_BILL = 'Y') OR (B1.MANUAL_BILL = 'N' AND B1.BUSINESS_STAGE = 'INVEST_STAGE' AND B1.ENTRY_STATUS IN LIST) OR (B1.BUSINESS_TYPE = 'NORMAL_DISPOSAL' AND BEPB.DISPOSE_FLAG = 'Y' AND B1.MANUAL_BILL = 'N' AND B1.ENTRY_STATUS IN LIST))))
17 #NEST LOOP INDEX JOIN2: [8, 1203, 168]
18 #CSCN2: [1, 1203, 78]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as BEPB); btr_scan(1)
19 #BLKUP2: [7, 1, 30]; AMC_BILL_ASSET_LINE_N2(BALB)
20 #SSEK2: [7, 1, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_N2(AMC_BILL_ASSET_LINE_B as BALB), scan_range[BEPB.ASSET_ORGINAL_ID,BEPB.ASSET_ORGINAL_ID]
21 #BLKUP2: [7, 1, 30]; INDEX33557203(B1)
22 #SSEK2: [7, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as B1), scan_range[BALB.BILL_ID,BALB.BILL_ID]
23 #BLKUP2: [1, 8, 30]; AMC_BILL_ASSET_LINE_B_DSH0320_01(B)
24 #SSEK2: [1, 8, 30]; scan_type(ASC), AMC_BILL_ASSET_LINE_B_DSH0320_01(AMC_BILL_ASSET_LINE_B as B), scan_range[DMTEMPVIEW_889360133.colname,DMTEMPVIEW_889360133.colname]
25 #CSCN2: [1, 1203, 252]; INDEX33557178(AMC_BILL_EQUITY_PAGE_B as EPB); btr_scan(1)
26 #BLKUP2: [1, 1, 48]; INDEX33558472(CB)
27 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558472(AMC_BASE_CUSTOMER_B as CB), scan_range[EPB.CUSTOMER_NUMBER,EPB.CUSTOMER_NUMBER]
28 #HASH RIGHT JOIN2: [12448, 6380937, 5334]; key_num(2), ret_null(0), KEY(AAS4.ACCOUNT_ID=AIB.ACCOUNT_ID AND AAS4.SEGMENT1=exp11)
29 #PRJT2: [3300, 1, 1478]; exp_num(3), is_atom(FALSE)
30 #HAGR2: [3300, 1, 1478]; grp_num(2), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(BALB3.ACCOUNT_ID, AAS3.SEGMENT1)
31 #HASH RIGHT SEMI JOIN2: [3296, 75, 1478]; n_keys(1) KEY(DMTEMPVIEW_889360143.colname=BAHB3.MANUAL_BILL) KEY_NULL_EQU(0)
32 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
33 #SLCT2: [3296, 75, 1478]; (BAHB3.BUSINESS_STAGE = 'HANDLE_STAGE' AND ((BAHB3.ENTRY_STATUS = 'SL' AND BAHB3.MANUAL_BILL = 'Y') OR (BAHB3.MANUAL_BILL = 'N' AND BAHB3.ENTRY_STATUS IN LIST)))
34 #NEST LOOP INDEX JOIN2: [3296, 75, 1478]
35 #NEST LOOP INDEX JOIN2: [3291, 75, 1304]
36 #SLCT2: [3286, 75, 1214]; AAS3.SEGMENT3 = XCB3.FLEX_VALUE
37 #NEST LOOP INNER JOIN2: [3286, 75, 1214];
38 #PRJT2: [10, 1000, 96]; exp_num(1), is_atom(FALSE)
39 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889360124@TOEBS, condition(TMPREMOTETAB889360124.ATTRIBUTE3 = 'REC_DIVIDEND')
40 #PRJT2: [10, 1000, 1118]; exp_num(5), is_atom(FALSE)
41 #REMOTE SCAN: [10, 1000, 1118]; TMPREMOTETAB889360123@TOEBS, condition((TMPREMOTETAB889360123.ENTITY_CATEGORY = 'BAD_ASSETS' AND TMPREMOTETAB889360123.RELATION_TARGET = 'EVENT' AND exp11 <= '2024-04-07'))
42 #BLKUP2: [4, 1, 30]; INDEX33559142(BALB3)
43 #SSEK2: [4, 1, 30]; scan_type(ASC), INDEX33559142(AMC_BILL_ASSET_LINE_B as BALB3), scan_range[AAS3.ENTITY_ID,AAS3.ENTITY_ID]
44 #BLKUP2: [4, 1, 30]; INDEX33557203(BAHB3)
45 #SSEK2: [4, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as BAHB3), scan_range[BALB3.BILL_ID,BALB3.BILL_ID]
46 #HASH RIGHT JOIN2: [5685, 6380937, 3856]; key_num(2), ret_null(0), KEY(AAS6.ACCOUNT_ID=AIB.ACCOUNT_ID AND AAS6.SEGMENT1=exp11)
47 #PRJT2: [28, 1, 1478]; exp_num(3), is_atom(FALSE)
48 #HAGR2: [28, 1, 1478]; grp_num(2), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(DMTEMPVIEW_889360130.TMPCOL0, DMTEMPVIEW_889360130.TMPCOL1)
49 #PRJT2: [27, 75, 1478]; exp_num(3), is_atom(FALSE)
50 #HASH2 INNER JOIN: [27, 75, 1478]; KEY_NUM(1); KEY(XCB5.FLEX_VALUE=AAS5.SEGMENT3) KEY_NULL_EQU(0)
51 #PRJT2: [10, 1000, 96]; exp_num(1), is_atom(FALSE)
52 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889360122@TOEBS, condition(TMPREMOTETAB889360122.ATTRIBUTE3 = 'REC_DIVIDEND')
53 #HASH RIGHT SEMI JOIN2: [16, 50, 1382]; n_keys(1) KEY(DMTEMPVIEW_889360144.colname=BAHB5.MANUAL_BILL) KEY_NULL_EQU(0)
54 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
55 #SLCT2: [16, 50, 1382]; (BAHB5.BUSINESS_STAGE = 'HANDLE_STAGE' AND ((BAHB5.ENTRY_STATUS = 'SL' AND BAHB5.MANUAL_BILL = 'Y') OR (BAHB5.MANUAL_BILL = 'N' AND BAHB5.ENTRY_STATUS IN LIST)))
56 #NEST LOOP INDEX JOIN2: [16, 50, 1382]
57 #NEST LOOP INDEX JOIN2: [13, 50, 1208]
58 #PRJT2: [10, 1000, 1118]; exp_num(5), is_atom(FALSE)
59 #REMOTE SCAN: [10, 1000, 1118]; TMPREMOTETAB889360121@TOEBS, condition((TMPREMOTETAB889360121.ENTITY_CATEGORY = 'BAD_ASSETS' AND TMPREMOTETAB889360121.RELATION_TARGET = 'EVENT' AND exp11 >= '2024-04-07' AND exp11 <= '2024-04-07'))
60 #BLKUP2: [3, 1, 30]; INDEX33559142(BALB5)
61 #SSEK2: [3, 1, 30]; scan_type(ASC), INDEX33559142(AMC_BILL_ASSET_LINE_B as BALB5), scan_range[AAS5.ENTITY_ID,AAS5.ENTITY_ID]
62 #BLKUP2: [3, 1, 30]; INDEX33557203(BAHB5)
63 #SSEK2: [3, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as BAHB5), scan_range[BALB5.BILL_ID,BALB5.BILL_ID]
64 #HASH RIGHT JOIN2: [3366, 6380937, 2378]; key_num(2), ret_null(0), KEY(AAS2.ACCOUNT_ID=AIB.ACCOUNT_ID AND AAS2.SEGMENT1=exp11)
65 #PRJT2: [24, 1, 1430]; exp_num(8), is_atom(FALSE)
66 #HAGR2: [24, 1, 1430]; grp_num(2), sfun_num(6), distinct_flag[0,0,0,0,0,0]; slave_empty(0) keys(DMTEMPVIEW_889360128.TMPCOL0, DMTEMPVIEW_889360128.TMPCOL1)
67 #PRJT2: [22, 150, 1430]; exp_num(8), is_atom(FALSE)
68 #HASH RIGHT SEMI JOIN2: [22, 150, 1430]; n_keys(1) KEY(DMTEMPVIEW_889360145.colname=BAHB1.MANUAL_BILL) KEY_NULL_EQU(0)
69 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1)
70 #SLCT2: [22, 150, 1430]; ((BAHB1.ENTRY_STATUS = 'SL' AND BAHB1.MANUAL_BILL = 'Y') OR (BAHB1.MANUAL_BILL = 'N' AND BAHB1.ENTRY_STATUS IN LIST))
71 #NEST LOOP INDEX JOIN2: [22, 150, 1430]
72 #NEST LOOP INDEX JOIN2: [21, 150, 1304]
73 #HASH2 INNER JOIN: [20, 150, 1214]; KEY_NUM(1); KEY(AAS1.SEGMENT3=XCB1.FLEX_VALUE) KEY_NULL_EQU(0)
74 #PRJT2: [10, 1000, 1118]; exp_num(5), is_atom(FALSE)
75 #REMOTE SCAN: [10, 1000, 1118]; TMPREMOTETAB889360119@TOEBS, condition((TMPREMOTETAB889360119.ENTITY_CATEGORY = 'BAD_ASSETS' AND TMPREMOTETAB889360119.RELATION_TARGET = 'EVENT' AND exp11 >= '2024-04-07' AND exp11 <= '2024-04-07'))
76 #PRJT2: [10, 1000, 96]; exp_num(2), is_atom(FALSE)
77 #REMOTE SCAN: [10, 1000, 96]; TMPREMOTETAB889360120@TOEBS
78 #BLKUP2: [1, 1, 30]; INDEX33559142(BALB1)
79 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33559142(AMC_BILL_ASSET_LINE_B as BALB1), scan_range[AAS1.ENTITY_ID,AAS1.ENTITY_ID]
80 #BLKUP2: [1, 1, 30]; INDEX33557203(BAHB1)
81 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as BAHB1), scan_range[BALB1.BILL_ID,BALB1.BILL_ID]
82 #HASH RIGHT JOIN2: [2184, 6380937, 948]; key_num(2), ret_null(0), KEY(SEBV2.ACCOUNT_ID=AIB.ACCOUNT_ID AND SEBV2.BELONG_ORG=exp11)
83 #PRJT2: [1, 1, 198]; exp_num(5), is_atom(FALSE)
84 #HASH LEFT SEMI JOIN2: [1, 1, 198]; KEY_NUM(1); KEY(EBV3.EVENT_ID=DMTEMPVIEW_889360126.colname) KEY_NULL_EQU(0)
85 #CSCN2: [1, 1, 198]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV3); btr_scan(1)
86 #PRJT2: [1, 1, 151]; exp_num(1), is_atom(FALSE)
87 #HAGR2: [1, 1, 151]; grp_num(3), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EBV4.ACCOUNT_ID, EBV4.PROJECT_ID, EBV4.BELONG_ORG)
88 #SLCT2: [1, 1, 151]; exp11 <= '2024-04-07'
89 #CSCN2: [1, 1, 151]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV4); btr_scan(1)
90 #HASH RIGHT JOIN2: [1181, 6380937, 750]; key_num(2), ret_null(0), KEY(SEBV1.ACCOUNT_ID=AIB.ACCOUNT_ID AND SEBV1.BELONG_ORG=exp11)
91 #PRJT2: [1, 1, 198]; exp_num(5), is_atom(FALSE)
92 #HASH LEFT SEMI JOIN2: [1, 1, 198]; KEY_NUM(1); KEY(EBV1.EVENT_ID=DMTEMPVIEW_889360125.colname) KEY_NULL_EQU(0)
93 #CSCN2: [1, 1, 198]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV1); btr_scan(1)
94 #PRJT2: [1, 1, 151]; exp_num(1), is_atom(FALSE)
95 #HAGR2: [1, 1, 151]; grp_num(3), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EBV2.ACCOUNT_ID, EBV2.PROJECT_ID, EBV2.BELONG_ORG)
96 #SLCT2: [1, 1, 151]; exp11 < '2024-04-07'
97 #CSCN2: [1, 1, 151]; INDEX33557737(XXT_SL_ASSET_DETAILS_BALANCE_TMP as EBV2); btr_scan(1)
98 #HASH2 INNER JOIN: [335, 6380937, 552]; KEY_NUM(1); KEY(AIB.PROJECT_ID=PBPSI.PROJECT_ID) KEY_NULL_EQU(0)
99 #CSCN2: [11, 76327, 252]; INDEX33557215(AMC_ASSET_ACCOUNT_INFO_B as AIB); btr_scan(1)
100 #PRJT2: [228, 787595, 300]; exp_num(6), is_atom(FALSE)
101 #NEST LOOP INNER JOIN2: [228, 787595, 300];
102 #SLCT2: [1, 83, 126]; exp11 <> '1'
103 #CSCN2: [1, 88, 126]; INDEX33557680(AMC_PROJECT_SPLIT_INFO_B as PSI); btr_scan(1)
104 #CSCN2: [1, 9421, 174]; INDEX33557154(AMC_BASE_PROJECT_B as PB); btr_scan(1)
105 #PRJT2: [1, 222, 816]; exp_num(2), is_atom(FALSE)
106 #CSCN2: [1, 222, 816]; INDEX33558407(XXT_COA_PROD_TYPE_B); btr_scan(1)
107 #CSCN2: [1, 777, 96]; INDEX33558127(PCMC_DEPT as PD); btr_scan(1)
108 #SPL2: [1, 1, 144]; key_num(1), spool_num(1), is_atom(TRUE), has_var(1), sites(-)
109 #PRJT2: [1, 1, 144]; exp_num(1), is_atom(TRUE)
110 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_TP_CD_NA(PCMC_KNP_PARA as PC), scan_range[('GWAMCC_YES_NO',var12,min),('GWAMCC_YES_NO',var12,max))
111 #SPL2: [1, 1, 144]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
112 #PRJT2: [1, 1, 144]; exp_num(1), is_atom(TRUE)
113 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_TP_CD_NA(PCMC_KNP_PARA as PC), scan_range[('GWAMCC_DETAIL_OPTION',var11,min),('GWAMCC_DETAIL_OPTION',var11,max))
调整内容
- 子查询加
/*+ enable_hash_join(0)*/ - 主查询加
/*+ NO_USE_CVT_VAR optimizer_or_nbexp(3)*/