视图查询优化-不带条件
sql
select /*+ VIEW_FILTER_MERGING(1) NO_USE_CVT_VAR */ v.account_number,
v.bill_id,
v.account_id,
v.bill_line_id,
v.bill_number,
v.business_type,
v.business_type_sub,
v.EVENT_ID,
v.PROJECT_ID,
v.OBJECT_TYPE,
v.OBJECT_ID,
v.EVENT_TYPE,
v.EVENT_DATE,
v.ACCOUNTING_DATE,
v.PERIOD_NAME,
v.CURRENCY_CODE,
v.AMOUNT,
v.LEDGER_ID,
v.ACTUAL_FLAG,
v.BATCH_NAME,
v.BATCH_DESC,
v.JOURNAL_NAME,
v.JOURNAL_DESC,
v.USER_JE_CATEGORY_NAME,
v.USER_JE_SOURCE_NAME,
v.EXCHANGE_RATE_TYPE,
v.EXCHANGE_RATE_DATE,
v.EXCHANGE_RATE,
v.RELATION_ID,
v.STATUS,
v.STATUS_DATE,
v.CREATION_DATE,
V.Business_Stage "business_stage",
v.reverse_status,
v.right_yn,
v.devalue_stage,
v.classify_result,
v.bus_model,
v.sppi_yn,
v.gl_date
from amc_account_event_v V
WHERE 1 = 1
-- and v.business_stage != 'FINANCIAL_TRANSFER'
-- and V.account_id = 2711605
union all
select v.account_number,
a.pack_id,
v.account_id,
v.bill_line_id,
a.pack_number,
v.business_type,
v.business_type_sub,
v.EVENT_ID,
v.PROJECT_ID,
v.OBJECT_TYPE,
v.OBJECT_ID,
v.EVENT_TYPE,
v.EVENT_DATE,
v.ACCOUNTING_DATE,
v.PERIOD_NAME,
v.CURRENCY_CODE,
v.AMOUNT,
v.LEDGER_ID,
v.ACTUAL_FLAG,
v.BATCH_NAME,
v.BATCH_DESC,
v.JOURNAL_NAME,
v.JOURNAL_DESC,
v.USER_JE_CATEGORY_NAME,
v.USER_JE_SOURCE_NAME,
v.EXCHANGE_RATE_TYPE,
v.EXCHANGE_RATE_DATE,
v.EXCHANGE_RATE,
v.RELATION_ID,
v.STATUS,
v.STATUS_DATE,
v.CREATION_DATE,
V.Business_Stage "business_stage",
v.reverse_status,
v.right_yn,
v.devalue_stage,
v.classify_result,
v.bus_model,
v.sppi_yn,
v.gl_date
from amc_account_event_v V
left join amc_bill_financial_head_b a
on a.pack_id = v.pack_id
WHERE 1 = 1
-- and v.business_stage = 'FINANCIAL_TRANSFER'
-- and V.account_id = 2711605
order by EVENT_ID desc;
CREATE OR REPLACE VIEW "JRAF"."AMC_ACCOUNT_EVENT_V_BAK"
AS
select h.bill_id,
/*modify by F.lei 20190220
select av.bill_id,
av.account_id,
av.bill_line_id,
av.account_number,
av.bill_number,
av.business_stage,
av.business_type,
av.business_type_sub,
xse.event_id,
xse.project_id,
xse.object_type,
xse.object_id,
xse.event_type,
xse.event_date,
xse.accounting_date,
xse.period_name,
xse.currency_code,
xse.amount,
xse.ledger_id,
xse.actual_flag,
xse.batch_name,
xse.batch_desc,
xse.journal_name,
xse.journal_desc,
xse.user_je_category_name,
xse.user_je_source_name,
xse.exchange_rate_type,
xse.exchange_rate_date,
xse.exchange_rate,
xse.relation_id,
xse.status,
xse.status_date,
xse.creation_date,
av.reverse_status,
av.sppi_yn,
av.bus_model,
av.right_yn,
av.classify_result,
av.devalue_stage
from amc_bill_account_v av,
xxt_sl_events@toebs xse,
xxt_sl_entity_relatns@toebs xsr
where av.bill_line_id = xsr.entity_id
and xsr.relation_target_id = xse.event_id
and xsr.relation_target = 'EVENT'
and xsr.entity_category = 'BAD_ASSETS';*/
h.pack_id,
a.account_id,
l.bill_line_id,
a.account_number,
h.bill_number,
h.business_stage,
l.business_type,
l.business_type_sub,
se.event_id,
se.project_id,
se.object_type,
se.object_id,
se.event_type,
se.event_date,
se.accounting_date,
se.period_name,
se.currency_code,
se.amount,
se.ledger_id,
se.actual_flag,
se.batch_name,
se.batch_desc,
se.journal_name,
se.journal_desc,
se.user_je_category_name,
se.user_je_source_name,
se.exchange_rate_type,
se.exchange_rate_date,
se.exchange_rate,
se.relation_id,
se.status,
se.status_date,
se.creation_date,
h.reverse_status,
l.sppi_yn,
l.bus_model,
l.right_yn,
l.classify_result,
l.devalue_stage,
h.gl_date
from amc_bill_asset_head_b h,
amc_bill_asset_line_b l,
amc_asset_account_info_b a,
amc_base_project_b p,
xxt_coa_prod_type_v pt,
xxt_sl_entity_relatns@toebs ser,
xxt_sl_events@toebs se
where 1 = 1
and h.bill_id = l.bill_id
and l.account_id = a.account_id
and h.project_id = p.project_id
and a.product_num = pt.prod_type_code
and l.bill_line_id = ser.entity_id
and ser.relation_target = 'EVENT'
and ser.entity_category = 'BAD_ASSETS'
and ser.relation_target_id = se.event_id
and se.event_type = l.business_type
and se.object_id = a.account_id
and h.entry_status not in ('NL', 'SL_IN_ERROR', 'SL_IN_PROCESS');
explain
#执行时间：48s
1 #NSET2: [60, 1499, 2766]
2 #PIPE2: [60, 1499, 2766]
3 #PRJT2: [16, 1499, 2766]; exp_num(40), is_atom(FALSE)
4 #SORT3: [16, 1499, 2766]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
5 #UNION ALL: [14, 1499, 2766]
6 #PRJT2: [5, 749, 2766]; exp_num(40), is_atom(FALSE)
7 #HEAP TABLE SCAN: [5, 749, 2766]; table_no(0)
8 #PRJT2: [7, 749, 2766]; exp_num(40), is_atom(FALSE)
9 #HASH RIGHT JOIN2: [7, 749, 2766]; key_num(1), ret_null(0), KEY(A.PACK_ID=V.PACK_ID)
10 #CSCN2: [1, 43, 78]; INDEX33557182(AMC_BILL_FINANCIAL_HEAD_B as A); btr_scan(1)
11 #HEAP TABLE SCAN: [5, 749, 2766]; table_no(0)
12 #HEAP TABLE: [44, 749, 2766]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
13 #PRJT2: [44, 749, 2766]; exp_num(41), is_atom(FALSE)
14 #HASH2 INNER JOIN: [44, 749, 2766]; RKEY_UNIQUE KEY_NUM(1); KEY(H.PROJECT_ID=P.PROJECT_ID) KEY_NULL_EQU(0)
15 #HASH RIGHT SEMI JOIN2: [40, 749, 2736]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_889763040.colname=H.ENTRY_STATUS) KEY_NULL_EQU(0)
16 #CONST VALUE LIST: [1, 3, 48]; row_num(3), col_num(1)
17 #NEST LOOP INDEX JOIN2: [40, 749, 2736]
18 #HASH2 INNER JOIN: [35, 749, 2441]; KEY_NUM(1); KEY(PT.PROD_TYPE_CODE=A.PRODUCT_NUM) KEY_NULL_EQU(0)
19 #PRJT2: [1, 222, 816]; exp_num(1), is_atom(FALSE)
20 #CSCN2: [1, 222, 816]; INDEX33558407(XXT_COA_PROD_TYPE_B); btr_scan(1)
21 #HASH2 INNER JOIN: [34, 750, 1625]; KEY_NUM(4); KEY(SER.COL3=SE.EVENT_ID AND L.BUSINESS_TYPE=SE.EVENT_TYPE AND A.ACCOUNT_ID=SE.OBJECT_ID AND L.ACCOUNT_ID=SE.OBJECT_ID) KEY_NULL_EQU(0, 0, 0, 0)
22 #NEST LOOP INDEX JOIN2: [23, 1000, 708]
23 #NEST LOOP INDEX JOIN2: [16, 1000, 582]
24 #REMOTE SCAN: [10, 1000, 156]; TMPREMOTETAB889763037@TOEBS as SER, condition((SER.RELATION_TARGET = 'EVENT' AND SER.ENTITY_CATEGORY = 'BAD_ASSETS'))
25 #BLKUP2: [6, 1, 30]; INDEX33559142(L)
26 #SSEK2: [6, 1, 30]; scan_type(ASC), INDEX33559142(AMC_BILL_ASSET_LINE_B as L), scan_range[SER.COL0,SER.COL0]
27 #BLKUP2: [6, 1, 30]; INDEX33557263(A)
28 #SSEK2: [6, 1, 30]; scan_type(ASC), INDEX33557263(AMC_ASSET_ACCOUNT_INFO_B as A), scan_range[L.ACCOUNT_ID,L.ACCOUNT_ID]
29 #REMOTE SCAN: [10, 1000, 917]; XXT_SL_EVENTS@TOEBS as SE
30 #BLKUP2: [4, 1, 30]; INDEX33557203(H)
31 #SSEK2: [4, 1, 30]; scan_type(ASC), INDEX33557203(AMC_BILL_ASSET_HEAD_B as H), scan_range[L.BILL_ID,L.BILL_ID]
32 #SSCN: [1, 9421, 30]; INDEX33557197(AMC_BASE_PROJECT_B as P); btr_scan(1)
视图改写
使有关dblink的表放在一起获取数据。
CREATE OR REPLACE VIEW "JRAF"."AMC_ACCOUNT_EVENT_V"
AS
with tt as (select se.*,to_char(ser.entity_id) entity_id from xxt_sl_entity_relatns@toebs ser,
xxt_sl_events@toebs se
where 1 = 1
and ser.relation_target = 'EVENT'
and ser.entity_category = 'BAD_ASSETS'
and ser.relation_target_id = se.event_id
)
select /*+ ORDER(H,L,A,P,PT,TT) */ h.bill_id,
/*modify by F.lei 20190220
select av.bill_id,
av.account_id,
av.bill_line_id,
av.account_number,
av.bill_number,
av.business_stage,
av.business_type,
av.business_type_sub,
xtt.event_id,
xtt.project_id,
xtt.object_type,
xtt.object_id,
xtt.event_type,
xtt.event_date,
xtt.accounting_date,
xtt.period_name,
xtt.currency_code,
xtt.amount,
xtt.ledger_id,
xtt.actual_flag,
xtt.batch_name,
xtt.batch_desc,
xtt.journal_name,
xtt.journal_desc,
xtt.user_je_category_name,
xtt.user_je_source_name,
xtt.exchange_rate_type,
xtt.exchange_rate_date,
xtt.exchange_rate,
xtt.relation_id,
xtt.status,
xtt.status_date,
xtt.creation_date,
av.reverse_status,
av.sppi_yn,
av.bus_model,
av.right_yn,
av.classify_result,
av.devalue_stage
from amc_bill_account_v av,
xxt_sl_events@toebs xse,
xxt_sl_entity_relatns@toebs xsr
where av.bill_line_id = xsr.entity_id
and xsr.relation_target_id = xtt.event_id
and xsr.relation_target = 'EVENT'
and xsr.entity_category = 'BAD_ASSETS';*/
h.pack_id,
a.account_id,
l.bill_line_id,
a.account_number,
h.bill_number,
h.business_stage,
l.business_type,
l.business_type_sub,
tt.event_id,
tt.project_id,
tt.object_type,
tt.object_id,
tt.event_type,
tt.event_date,
tt.accounting_date,
tt.period_name,
tt.currency_code,
tt.amount,
tt.ledger_id,
tt.actual_flag,
tt.batch_name,
tt.batch_desc,
tt.journal_name,
tt.journal_desc,
tt.user_je_category_name,
tt.user_je_source_name,
tt.exchange_rate_type,
tt.exchange_rate_date,
tt.exchange_rate,
tt.relation_id,
tt.status,
tt.status_date,
tt.creation_date,
h.reverse_status,
l.sppi_yn,
l.bus_model,
l.right_yn,
l.classify_result,
l.devalue_stage,
h.gl_date
from amc_bill_asset_head_b h,
amc_bill_asset_line_b l,
amc_asset_account_info_b a,
amc_base_project_b p,
xxt_coa_prod_type_v pt,
tt
where 1 = 1
and h.bill_id = l.bill_id
and l.account_id = a.account_id
and h.project_id = p.project_id
and a.product_num = pt.prod_type_code
and l.bill_line_id = tt.entity_id
and tt.event_type = l.business_type
and object_id = a.account_id
and h.entry_status not in ('NL', 'SL_IN_ERROR', 'SL_IN_PROCESS');
改写后explain
#12s
1 #NSET2: [2057, 2817, 3337]
2 #PIPE2: [2057, 2817, 3337]
3 #PRJT2: [29, 2817, 3337]; exp_num(40), is_atom(FALSE)
4 #SORT3: [29, 2817, 3337]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
5 #UNION ALL: [26, 2817, 3337]
6 #PRJT2: [11, 1408, 3337]; exp_num(40), is_atom(FALSE)
7 #HEAP TABLE SCAN: [11, 1408, 3337]; table_no(0)
8 #PRJT2: [12, 1408, 3337]; exp_num(40), is_atom(FALSE)
9 #HASH RIGHT JOIN2: [12, 1408, 3337]; key_num(1), ret_null(0), KEY(A.PACK_ID=V.PACK_ID)
10 #CSCN2: [1, 43, 78]; INDEX33557182(AMC_BILL_FINANCIAL_HEAD_B as A); btr_scan(1)
11 #HEAP TABLE SCAN: [11, 1408, 3337]; table_no(0)
12 #HEAP TABLE: [2027, 1408, 3337]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
13 #PRJT2: [2027, 1408, 3337]; exp_num(41), is_atom(FALSE)
14 #HASH2 INNER JOIN: [2027, 1408, 3337]; KEY_NUM(4); KEY(TT.EVENT_TYPE=L.BUSINESS_TYPE AND TT.OBJECT_ID=A.ACCOUNT_ID AND TT.OBJECT_ID=L.ACCOUNT_ID AND exp_cast(TT.ENTITY_ID)=L.BILL_LINE_ID) KEY_NULL_EQU(0, 0, 0, 0)
15 #PRJT2: [10, 1000, 1644]; exp_num(26), is_atom(FALSE)
16 #REMOTE SCAN: [10, 1000, 1644]; TMPREMOTETAB889763124@TOEBS
17 #HASH2 INNER JOIN: [1410, 1107893, 1693]; KEY_NUM(1); KEY(PT.PROD_TYPE_CODE=A.PRODUCT_NUM) KEY_NULL_EQU(0)
18 #PRJT2: [1, 222, 816]; exp_num(1), is_atom(FALSE)
19 #CSCN2: [1, 222, 816]; INDEX33558407(XXT_COA_PROD_TYPE_B); btr_scan(1)
20 #HASH2 INNER JOIN: [1026, 1107900, 877]; LKEY_UNIQUE KEY_NUM(1); KEY(P.PROJECT_ID=H.PROJECT_ID) KEY_NULL_EQU(0)
21 #SSCN: [1, 9421, 30]; INDEX33557197(AMC_BASE_PROJECT_B as P); btr_scan(1)
22 #HASH2 INNER JOIN: [650, 1107900, 847]; LKEY_UNIQUE KEY_NUM(1); KEY(A.ACCOUNT_ID=L.ACCOUNT_ID) KEY_NULL_EQU(0)
23 #CSCN2: [10, 76329, 126]; INDEX33557215(AMC_ASSET_ACCOUNT_INFO_B as A); btr_scan(1)
24 #HASH2 INNER JOIN: [294, 1107900, 721]; KEY_NUM(1); KEY(H.BILL_ID=L.BILL_ID) KEY_NULL_EQU(0)
25 #HASH RIGHT SEMI JOIN2: [24, 130861, 295]; n_keys(1) (ANTI), KEY(DMTEMPVIEW_889763127.colname=H.ENTRY_STATUS) KEY_NULL_EQU(0)
26 #CONST VALUE LIST: [1, 3, 48]; row_num(3), col_num(1)
27 #CSCN2: [24, 141472, 295]; INDEX33557166(AMC_BILL_ASSET_HEAD_B as H); btr_scan(1)
28 #CSCN2: [150, 785045, 426]; INDEX33559141(AMC_BILL_ASSET_LINE_B as L); btr_scan(1)
用时对比
