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: Sandesh <sandesh_katy_at_yahoo.co.in>
Date: 17 Oct 2006 14:19:49 -0700
Message-ID: <1161119989.193882.202640@f16g2000cwb.googlegroups.com> 





fitzjarrell_at_cox.net wrote:


> 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




sorry guys...am attaching the required info - (Oracle 9i)



There is an index OPE_IE1_ENRICHED_TRIAL_BAL on all the GROUP BY
columns.



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


'OPE_TB_POSTING_KEY' (Cost=1 Card=1 Bytes=103)



   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=366158


Bytes=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=517825


Bytes=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=10


Card=1 Bytes=125)



  27   26           INDEX (RANGE SCAN) OF

'OPE_IE1_ENRICHED_TRIAL_BAL'(NON-UNIQUE) (Cost=9 Card=1)
Received on Tue Oct 17 2006 - 16:19:49 CDT












Original text of this message













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