Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Correlated subquery and join in the query

Re: Correlated subquery and join in the query

From: <fitzjarrell_at_cox.net>
Date: 17 Oct 2006 13:38:52 -0700
Message-ID: <1161117532.651214.254060@k70g2000cwa.googlegroups.com>

Sandesh wrote:
> 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'
Where is your explain plan output? Have you run a trace on this to see what, if any, performance bottlenecks exist? Which release of Oracle? Which operating system?

There is much you need to post before an answer can even be considered.

David Fitzjarrell Received on Tue Oct 17 2006 - 15:38:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US