Re: Tuning SQL query with views

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.