Re: Tuning SQL query with views

ordabber_at_my-deja.com wrote in message <91lkak$24d$1_at_nnrp1.deja.com>...

>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.
>

[ snip a lot of useful but voluminous detail ]

When Oracle parses a statement with a view it almost always tries to integrate the view definition into the query. Think about how you would write the query using just the base tables. Oracle tries to do that.

There are some cases where Oracle can't integrate the view, for example when the view contains a 'set-operator' like GROUP BY, or where you are trying to do perform an outer-join against a view. See my web page: http://home.clara.net/dwotton/dba/ojoin2.htm for more information on that.

In your situation it's a case of not having the right indexes defined, as you already know. What your query is doing is performing a full tablescan of PS_BANK_ACCT_CPTY and then (fairly efficiently) using indexes PS_BANK_ACCT_DEFN and PSAPAYMENT_TBL to join to rows from PS_BANK_ACCT_DEFN and PS_PAYMENT_TBL. Then only rows in the join which satisfy the filter conditions: DOC_SEQ_DATE IS NULL AND DOC_SEQ_STATUS = 'N' AND PAY_CYCLE = 'BACOB1' and PAY_CYCLE_SEQ_NUM = 516 and DOC_TYPE <> ' ' are returned. So you're reading *every* from PS_BANK_ACCT_CPTY and then for each row, reading rows from two other tables.

(Note all the information required from PS_BANK_ACCT_DEFN can be retrieved from the index itself (good) so it doesn't need to also retrieve the data row).

Your only problem is that you don't have any suitable indexes on PS_PAYMENT_TBL to drive the query from that table. Create an index on PS_PAYMENT_TBL(PAY_CYCLE_SEQ_NUM) or PS_PAYMENT_TBL(PAY_CYCLE). That should have enormous benefits.

What do you mean by "when I try to create an index with the correct columns, Oracle doesn't want to take these indexes!". Does it report an error, or just not use them? Remember that the CREATE INDEX statement must specify the underlying tablename, not the view name.

Try creating the index and then show us the new explain plan.

PS. Some tips. Please make it easy to read your message. It took me 5 minutes to format your index definitions into something like:

PS_BANK_ACCT_CPTY              PSABANK_ACCT_CPTY 1 SETID
PS_BANK_ACCT_CPTY              PSABANK_ACCT_CPTY 2 BANK_CD_CUST
PS_BANK_ACCT_CPTY              PSABANK_ACCT_CPTY 3 COUNTERPARTY
PS_BANK_ACCT_CPTY              PSABANK_ACCT_CPTY 4 BANK_ACCT_KEY

PS_BANK_ACCT_CPTY              PSBBANK_ACCT_CPTY 1 SETID
PS_BANK_ACCT_CPTY              PSBBANK_ACCT_CPTY 2 BANK_CD
PS_BANK_ACCT_CPTY              PSBBANK_ACCT_CPTY 3 BANK_ACCT_KEY
PS_BANK_ACCT_CPTY              PSBBANK_ACCT_CPTY 4 COUNTERPARTY
PS_BANK_ACCT_CPTY              PSBBANK_ACCT_CPTY 5 BUSINESS_UNIT_GL

Most people wouldn't have bothered to spend the time and so won't attempt to answer your question. Also, it's good practice to include a full qualifier for each field in your select statement. eg. WHERE .... A.PAY_CYCLE_SEQ_NUM = 516, not PAY_CYCLE_SEQ_NUM. Again, it took me some time to find out where these fields came from. Please help us to help you.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.