Correlated subquery and join in the query

From: Sandesh <sandesh_katy_at_yahoo.co.in>
Date: 17 Oct 2006 13:28:49 -0700
Message-ID: <1161116929.394109.218680@k70g2000cwa.googlegroups.com>

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.

Thanks

---

SELECT
OETB.ADJ_SRC_SYS_FEED_ID,OETB.ADJUSTMENT_DATE,OETB.AGENT_IND,OETB.ALLOCATION_ID,OETB.ALLOCATION_ID_TYPE_CD,

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 ,

'M',OETB.trial_bal_adj_ltd_amt, NULL) trial_bal_adj_ltd_amt,
(SELECT OTPK.POSTING_KEY_VALUE FROM OPE_TB_POSTING_KEY OTPK WHERE OTPK.BUSINESS_DATE=OETB.BUSINESS_DATE
 AND OTPK.ADJUSTMENT_DATE=OETB.ADJUSTMENT_DATE  AND OTPK.TRIAL_BALANCE_ID=OETB.TRIAL_BALANCE_ID) POSTING_KEY_VALUE,  ( Select posting_key_default_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_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

OETB1.BUSINESS_DATE,OETB1.BOOK_GLOBAL_ID,OETB1.DEAL_TYPE_CD,NVL(OETB1.TRADE_DEAL_ID,'X'),OETB1.CURRENCY_CD,OETB1.LEG_TYPE_CD,OETB1.PRODUCT_REMIT_CD,OETB1.PRODUCT_ID_TYPE_CD,OETB1.PRODUCT_ID,OETB1.PRODUCT_ISSUE_STATUS_CD, OETB1.STRUCTURE_TYPE_CD,OETB1.DEPO_ACCT_ID,OETB1.BALANCE_BASIS_TYPE_CD,OETB1.NOSTRO_ACCT_ID,OETB1.TRIAL_BAL_AMOUNT_TYPE_CD, OETB1.GAAP_TYPE_CD,OETB1.SRC_SYS_TRANS_VERS_ID,NVL(OETB1.TRANS_PRODUCT_ID,'0'),OETB1.TRANS_UNDERLYING_AVAILABLE_CD, NVL(OETB1.SRC_SYS_TRANS_ID,'0'),OETB1.ALLOCATION_ID_TYPE_CD,OETB1.ALLOCATION_ID)

     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'

Received on Tue Oct 17 2006 - 15:28:49 CDT