| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Tune the query with join and correlated subquery
How can the below query be tuned further - (The query will be a used to
create a view which will be queried on a business date. That's why the business date condition is added in the end and can't be moved anywhere
else.) The table A is partitioned on business date and has 500,000 records for 1-Jan-06. The table B has 100,000 records for the same business date. DB is Oracle 9i. There is an index OPE_IE1_ENRICHED_TRIAL_BAL on all the GROUP BY columns.
Thanks
decode(OETB.trial_bal_extract_frequency_cd , 'M', NULL,OETB.TRIAL_BAL_UNADJ_LTD_AMT) TRIAL_BAL_UNADJ_LTD_AMT, decode(OETB.trial_bal_extract_frequency_cd ,
FROM B OTPK WHERE OTPK.BUSINESS_DATE=OETB.BUSINESS_DATE
AND OTPK.ADJUSTMENT_DATE=OETB.ADJUSTMENT_DATE AND OTPK.TRIAL_BALANCE_ID=OETB.TRIAL_BALANCE_ID) as posting_key_default_value ,
( Select posting_key_override_value
FROM B OTPK WHERE OTPK.BUSINESS_DATE=OETB.BUSINESS_DATE
AND OTPK.ADJUSTMENT_DATE=OETB.ADJUSTMENT_DATE
AND OTPK.TRIAL_BALANCE_ID=OETB.TRIAL_BALANCE_ID) as
posting_key_override_value ,
DECODE(PRODUCT_BALANCE_ID,NULL,DECODE(TRANS_PRODUCT_ID,NULL,DECODE(CASH_BALANCE_ID,NULL,(DECODE(PROVISION_ID,NULL,NULL,PROVISION_ID)),CASH_BALANCE_ID),TRANS_PRODUCT_ID),PRODUCT_BALANCE_ID)
prd_bal_phys_id_c,
DECODE(PRODUCT_BALANCE_ID,NULL,DECODE(TRANS_PRODUCT_ID,NULL,DECODE(CASH_BALANCE_ID,NULL,(DECODE(PROVISION_ID,NULL,NULL,'PROV')),'CBAL'),'PTRN'),'PBAL')
opera_table_phys_id_type_cd,
'N' AS
netting_taint_ind_c,DECODE(UNADJ_SRC_SYS_FEED_ID,NULL,ADJ_SRC_SYS_FEED_ID,UNADJ_SRC_SYS_FEED_ID)
AS src_feed_id_c
FROM A OETB
WHERE OETB.TRIAL_BALANCE_PROCESS_ID=(SELECT
MAX(OETB1.TRIAL_BALANCE_PROCESS_ID)
FROM A OETB1
WHERE OETB.book_global_id =
OETB1.book_global_id
and OETB.deal_type_cd = OETB1.deal_type_cd
and nvl(OETB.trade_deal_id,'X') =
nvl(OETB1.trade_deal_id,'X')
and OETB.currency_cd = OETB1.currency_cd
and OETB.leg_type_cd =
OETB1.leg_type_cd
and OETB.product_remit_cd =
OETB1.product_remit_cd
and OETB.product_id_type_cd =
OETB1.product_id_type_cd
and OETB.product_id = OETB1.product_id
and OETB.product_issue_status_cd =
OETB1.product_issue_status_cd
and OETB.src_type_cd = OETB1.src_type_cd
and OETB.depo_acct_id =
OETB1.depo_acct_id
and OETB.balance_basis_type_cd =
OETB1.balance_basis_type_cd and
OETB.nostro_acct_id
= OETB1.nostro_acct_id
and OETB.trial_bal_amount_type_cd =
OETB1.trial_bal_amount_type_cd and
OETB.gaap_type_cd
= OETB1.gaap_type_cd
and OETB.src_sys_trans_vers_id =
OETB1.src_sys_trans_vers_id and nvl(
OETB.trans_product_id ,'0') = nvl( OETB1.trans_product_id ,'0')
and OETB.trans_underlying_available_cd =
OETB1.trans_underlying_available_cd
and nvl(OETB.src_sys_trans_id,'X') =
nvl(OETB1.src_sys_trans_id,'X')
and OETB.allocation_id_type_cd = OETB1.
allocation_id_type_cd and OETB.allocation_id
= OETB1.allocation_id
and OETB.business_date = OETB1.business_date and
OETB.adjustment_date = OETB1.adjustment_date
GROUP BY
AND OETB.ADJUSTMENT_DATE=(SELECT MAX(ADJUSTMENT_DATE)
FROM A OETB2
WHERE OETB2.TRIAL_BALANCE_ID=OETB.TRIAL_BALANCE_ID AND
OETB.TRIAL_BAL_EXTRACT_FREQUENCY_CD=OETB2.TRIAL_BAL_EXTRACT_FREQUENCY_CD
AND OETB2.BUSINESS_DATE=OETB.BUSINESS_DATE)
AND OETB.BUSINESS_DATE='1-JAN-06'
----------------------------------------------------------
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21938 Card=1 Bytes=4
96)
1 0 PARTITION RANGE (SINGLE)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
3 2 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1 Card=1)
4 0 PARTITION RANGE (SINGLE)
5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'OPE_TB_POSTING_K
EY' (Cost=1 Card=1 Bytes=103)
6 5 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1
Card=1)
7 0 PARTITION RANGE (SINGLE)
8 7 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'OPE_TB_POSTING_K
EY' (Cost=1 Card=1 Bytes=103)
9 8 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1 Card=1)
10 0 PARTITION RANGE (SINGLE)
11 10 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
'OPE_TB_POSTING_KEY' (Cost=1 Card=1 Bytes=38)
12 11 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1 Card=1)
13 0 PARTITION RANGE (SINGLE)
14 13 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
'OPE_TB_POSTING_KEY' (Cost=1 Card=1 Bytes=44)
15 14 INDEX (RANGE SCAN) OF 'B' (UNIQUE) (Cost=1 Card=1)
16 0 FILTER
17 16 HASH JOIN (Cost=21926 Card=1 Bytes=496) 18 17 VIEW OF 'VW_SQ_1' (Cost=10287 Card=366158Bytes=12449372)
19 18 SORT (GROUP BY) (Cost=10287 Card=366158 Bytes=8421634)
20 19 PARTITION RANGE (ALL) 21 20 TABLE ACCESS (FULL) OF 'A' (Cost=5199 Card=517825
Bytes=11909975)
22 17 PARTITION RANGE (ALL) 23 22 TABLE ACCESS (FULL) OF 'A' (Cost=5199 Card=517825Bytes=239235150)
24 16 SORT (GROUP BY) (Cost=12 Card=1 Bytes=125) 25 24 PARTITION RANGE (SINGLE) 26 25 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A' (Cost=10Card=1 Bytes=125)
27 26 INDEX (RANGE SCAN) OF
'OPE_IE1_ENRICHED_TRIAL_BAL'(NON-UNIQUE) (Cost=9 Card=1)
Received on Wed Oct 18 2006 - 08:59:16 CDT
 |
 |