Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning SQL query with views

Re: Tuning SQL query with views

From: Tom Leary <tleary1_at_uswest.net>
Date: Mon, 1 Jan 2001 09:08:36 -0800
Message-ID: <k1146.1513$mL.96155@news.uswest.net>

When you create a new index, the CBO (Optimizer) doesn't know about the index until you analyze the underlying table. Analyze it and the CBO will likely use the index...

DBA Tom

<ordabber_at_my-deja.com> wrote in message news:91oaj6$a47$1_at_nnrp1.deja.com...
> 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 Mon Jan 01 2001 - 11:08:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US