Re: Tune the query with join and correlated subquery

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 18 Oct 2006 13:33:48 -0700
Message-ID: <1161203627.689543.161800@b28g2000cwb.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. DB is Oracle 9i. There is an index
> OPE_IE1_ENRICHED_TRIAL_BAL on all the GROUP BY
> columns.
>
> 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'
>
> ----------------------------------------------------------
>
> 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)

First suggestion is to clean up the query so that it is clear what is happening, this may be close:
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'; Second, you are performing several full table scans of table A based on the plan that you provided. Consider rewriting the query to use inline views rather than subqueries, where possible. Looking just at the last subquery:
  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) You might be able to write this as an inline view like this:   (SELECT
    TRIAL_BALANCE_ID,
    TRIAL_BAL_EXTRACT_FREQUENCY_CD,
    BUSINESS_DATE,
    MAX(ADJUSTMENT_DATE) MAX_ADJUSTMENT_DATE   FROM
    A
    TRIAL_BALANCE_ID,
    TRIAL_BAL_EXTRACT_FREQUENCY_CD,
    BUSINESS_DATE) MAD You would then need to add to the WHERE clause this:   AND OETB.TRIAL_BALANCE_ID=MAD.TRIAL_BALANCE_ID   AND
OETB.TRIAL_BAL_EXTRACT_FREQUENCY_CD=MAD.TRIAL_BAL_EXTRACT_FREQUENCY_CD   AND OETB.BUSINESS_DATE=MAD.BUSINESS_DATE   AND OETB.ADJUSTMENT_DATE=MAD.MAX_ADJUSTMENT_DATE You can do the same for the other subquery for OETB.TRIAL_BALANCE_PROCESS_ID also.

Third, you can try rewiting this code, which retrieves a single value as a column, so that it is set up as an inline view, as the above subquery was corrected:
  (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 It looks like Oracle may be doing some rewriting of the query for you, based on the plan.

Why must all of this appear in a view?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Oct 18 2006 - 15:33:48 CDT