Re: Tuning SQL query with views

Thanks for all the tips!
Following the here posted tuning tips I replaced the view fields and view names with the corresponding table fields and table names. I also added the where clause to my statement. Then I tried to create one index on the 'worst' behaving table with just one field that is really accurate and now my explain plan is totally different: I got rid of my full table scan, the number of retrieved rows in the different steps is now acceptable and most important, the statement retrieves the result almost instantly.

Now the new explain plan is:

SELECT STATEMENT Optimizer=CHOOSE
  SORT (ORDER BY)
    NESTED LOOPS

      NESTED LOOPS
        TABLE ACCESS (BY INDEX ROWID) OF PS_PAYMENT_TBL
          INDEX (RANGE SCAN) OF PSZPAYMENT_TBL (NON-UNIQUE)
        TABLE ACCESS (BY INDEX ROWID) OF PS_BANK_ACCT_DEFN
          INDEX (RANGE SCAN) OF PSZBANK_ACCT_DEFN (NON-UNIQUE)
      TABLE ACCESS (BY INDEX ROWID) OF PS_BANK_ACCT_CPTY
        INDEX (UNIQUE SCAN) OF PS_BANK_ACCT_CPTY (UNIQUE)

whereas the old one was:

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT  GOAL: CHOOSE

  42607      TABLE ACCESS (FULL) OF 'PS_BANK_ACCT_CPTY'
  42607      INDEX (UNIQUE SCAN) OF 'PS_BANK_ACCT_DEFN' (UNIQUE)
132864    TABLE ACCESS (BY INDEX ROWID) OF 'PS_PAYMENT_TBL'
175471      INDEX (RANGE SCAN) OF 'PSAPAYMENT_TBL' (NON-UNIQUE)

In article <3A4AD939.7A117048_at_erols.com>,   localhost_at_127.0.0.1 wrote:
> ordabber_at_my-deja.com wrote:
> >
> > Hi there,
> >
> > I have a bad performance problem. My query seems easy, but the only
> > accessed "table" is in fact a VIEW, and I can't find anywhere tips
 to
> > tune these kind of statements.
> >
> > I'm running ORACLE 8.0.5 on Windows NT.
> > The database is RULE-BASED !!
>
> ...
>
> >
> > select AP.BANK_SETID, AP.BANK_CD, AP.BANK_ACCT_KEY, AP.PYMNT_ID,
> > BUSINESS_UNIT_GL, ACCOUNTING_DT, AP.DOC_TYPE, AP.DOC_SEQ_NBR
> > FROM
> > PS_PAYMENT_TBL_VW5 AP
> > WHERE DOC_SEQ_DATE IS NULL AND DOC_SEQ_STATUS = 'N'
> > AND PAY_CYCLE = 'BACOB1' and PAY_CYCLE_SEQ_NUM = 516
> > and DOC_TYPE <> ' '
> > ORDER BY AP.BUSINESS_UNIT_GL, AP.DOC_TYPE, AP.ACCOUNTING_DT
>
> ...
>
> > -------------------------------------------------
> > PS_PAYMENT_TBL_VW5
> > -------------------------------------------------
> >
> > select
> > a.BANK_SETID
> > ,a.BANK_CD
> > ,a.BANK_ACCT_KEY
> > ,a.PYMNT_ID
> > ,a.PYMNT_ID_REF
> > ,a.BANK_ACCOUNT_NUM
> > ,a.BRANCH_ID
> > ,a.CHECK_DIGIT
> > ,a.REMIT_SETID
> > ,a.REMIT_VENDOR
> > ,a.vndr_loc
> > ,a.EMPLID
> > ,a.NAME1
> > ,a.NAME2,
> > %subrec(ADDRess_SBR,a)
> > ,a.PYMNT_DT
> > ,a.CREATION_DT
> > ,a.OPRID
> > ,a.PYMNT_AMT
> > ,a.CURRENCY_PYMNT
> > ,a.PYMNT_METHOD
> > ,a.PYMNT_HANDLING_CD
> > ,a.PYMNT_STATUS
> > ,a.PYMNT_RECONCILE_DT
> > ,a.CANCEL_ACTION
> > ,a.CANCEL_DT
> > ,a.ACCOUNTING_DT
> > ,a.POST_STATUS_AP
> > ,a.PROCESS_INSTANCE
> > ,a.PAY_CYCLE
> > ,a.PAY_CYCLE_SEQ_NUM
> > ,a.RECON_TYPE
> > ,a.RECON_CYCLE_NBR
> > ,a.RECONCILE_OPRID
> > ,a.BNK_ID_NBR
> > ,a.RECON_STATUS
> > ,a.DFI_ID_NUM
> > ,a.DFI_ID_QUAL
> > ,a.EFT_PYMNT_FMT_CD
> > ,a.STTLMNT_DT_EST
> > ,a.STTLMNT_DT_ACTUAL
> > ,a.EFT_TRANS_HANDLING
> > ,a.REMIT_BANK_ACCOUNT
> > ,a.REMIT_BRANCH_ID
> > ,a.REMIT_CHECK_DIGIT
> > ,a.REP_RTNG_NUM
> > ,a.FORM_NBR_CONFIRMED
> > ,a.IN_PROCESS_FLG
> > ,a.POSITIVE_PAY_DT
> > ,a.POSITIVE_PAY_STAT
> > ,a.MICR_LINE
> > ,a.CASH_CLEARED_FLG
> > ,a.CASH_CLEARED_DT
> > ,a.REPLACED_DT
> > ,a.PYMNT_TYPE
> > ,a.doc_type
> > ,a.doc_seq_nbr
> > ,a.doc_seq_date
> > ,a.doc_seq_status
> > ,a.preferred_language
> > ,b.business_unit_gl
> > from ps_payment_tbl a,
> > ps_bank_acct_tbl b
> > where a.bank_setid = b.setid and
> > a.bank_cd = b.bank_cd and
> > a.bank_acct_key = b.bank_acct_key
>
> ...
>
> > -------------------------------------------------
> > ps_bank_acct_tbl
> > -------------------------------------------------
> >
> > SELECT
> > A.SETID,
> > B.COUNTERPARTY,
> > A.BANK_ACCT_KEY,
> > A.BANK_ACCOUNT_NUM,
> > A.DESCR,
> > A.DESCRSHORT,
> > A.PYMNT_HANDLING_CD,
> > A.PYMNT_OVRD_AMT,
> > A.RECON_TYPE_FLG,
> > A.RECON_HEADER,
> > A.CURRENCY_CD,
> > A.CUR_RT_TYPE,
> > A.RATE_INDEX,
> > A.SETTLEMENT_ID,
> > B.BUSINESS_UNIT_GL,
> > A.LAST_PYMNT_ID_USED,
> > A.PYMNT_ID_REF_LEN,
> > A.FRACT_RTG_NUM,
> > A.DFI_ID_NUM,
> > A.DFI_ID_QUAL,
> > A.NPL_BNK_CHRT_VALUE,
> > A.IU_PYBL_NPL_ACCT,
> > A.IU_RCVBL_NPL_ACCT,
> > A.CSH_PL_CLRG_ACCT,
> > A.CSH_PL_ACCT,
> > A.PL_BNK_ACCT_FLG,
> > A.LOCATION,
> > A.AR,
> > A.AP,
> > A.TR,
> > A.BI,
> > A.PP_SW,
> > A.ENABLE_WF_SW,
> > A.DEPOSIT_TYPE,
> > A.FORECAST,
> > A.TARGET_BALANCE,
> > A.PRE_RECONCILE,
> > A.CASH_CNTL_USE_FLG,
> > A.DRAFT_CNTL_USE_FLG,
> > A.BRANCH_ID,
> > A.CHECK_DIGIT,
> > A.INT_BASIS,
> > A.MARGIN_PTS,
> > A.CREATION_DT,
> > A.OPRID_ENTERED_BY,
> > A.OPRID_LAST_UPDT,
> > A.DEPOSIT_BU,
> > A.CHRG_BANK_CD,
> > A.CHRG_BANK_ACCT_KEY,
> > A.RCN_ACCTG_SW,
> > A.RCN_TLR_DT_FROM,
> > A.RCN_TLR_DT_TO,
> > A.DEFAULT_ACCT_SW,
> > A.BANK_ACCT_QUAL,
> > A.PREFERRED_LANGUAGE,
> > A.PAYER_ID_NUM,
> > A.BANK_ACCT_TYPE,
> > A.CREDIT_RATE,
> > A.DEBIT_RATE
> > FROM PS_BANK_ACCT_DEFN A,
> > PS_BANK_ACCT_CPTY B
> > WHERE A.SETID = B.SETID
> > AND A.BANK_CD = B.BANK_CD
> > AND A.BANK_CD_CPTY = B.BANK_CD_CPTY
> > AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
>
> You are certainly thorough. Your message was difficult to digest due
 sheer
> volume (some of it unnecessary in the first post) and formatting.
>
> Here's a few questions:
>
> 1. There's a b.bank_cd referring to the ps_bank_acct_tbl view
 (unfortunate
> choice of name there). Can't see a bank_cd column in definition of
> ps_bank_acct_tbl.
>
> 2. Ultimately, the SQL is a simple join of a few tables using a minor
 sub-set of
> columns from base tables. Writing it as such simplifies debugging
 and maintenance.
> At the very least.
>
> Your query actually boils down to:
>
> SELECT
> A.BANK_SETID
> ,A.BANK_CD
> ,A.BANK_ACCT_KEY
> ,A.PYMNT_ID
> ,B.business_unit_gl
> ,A.ACCOUNTING_DT
> ,A.doc_type
> ,A.doc_seq_nbr
> FROM
> ps_payment_tbl A
> PS_BANK_ACCT_CPTY B,
> WHERE
> AND A.bank_setid = B.setid
> AND A.bank_cd = B.bank_cd
> AND A.DOC_SEQ_DATE IS NULL
> AND A.DOC_SEQ_STATUS = 'N'
> AND A.PAY_CYCLE = 'BACOB1'
> AND A.PAY_CYCLE_SEQ_NUM = 516
> AND A.DOC_TYPE <> ' '
> ORDER BY
> B.BUSINESS_UNIT_GL,
> C.DOC_TYPE,
> C.ACCOUNTING_DT
>
> PS_BANK_ACCT_DEFN is not even required. See how this query
 performs.
>
> --
> MikeC
>
> Please reply to the group.
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 29 2000 - 03:42:43 CST