Re: Tuning SQL query with views

Thank you for the tips!
Unless my colleagues will try them out - what I fear, because I am supposed to be the (AHUM) tuning guru - , I will post the new explain plans next week.

When I mentioned 'Oracle wouldn't take my "correct" indexes', I wanted to say that did succeed to create new indexes on the accessed tables, but that the explain plan didn't change a single comma!

In article <QjG%5.72521$eT4.5340274_at_nnrp3.clara.net>,   "Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote:
> 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.
>
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 19 2000 - 12:49:15 CST