Re: 10g SQL Access Advisor (retain index recommendation)

On Jul 20, 3:52 pm, DA Morgan <damor..._at_psoug.org> wrote:
> andreik wrote:
> > Hello,
>
> > The SQL Access Advisor, among other recommendations, generates the so
> > called "RETAIN INDEX" recommendations.
>
> > The recommended action itself looks something like this:
>
> > /* RETAIN INDEX "SCOTT"."PK_COLUMN_EMP" */
>
> > Which looks more like an optimizer hint...
>
> > I have looked through the oracle docs and googled it and not even in
> > the "ask tom" or metalink I could find a clear explanation to what
> > does this recommendation do?
>
> > Here:
> >http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/adv...
> > it says that:
> > "RETAIN recommendation indicate that existing access structures must
> > be kept"
>
> > This makes few sence to me.. Can anyone explain what does this...
> > ehm... statement(?) really do?
>
> > Is it so that oracle simply tells me that it was a good idea to create
> > that index and I should never drop it? But according to advisor
> > implementing these recommendations is supposed to reduce the total
> > cost. How can doing nothing reduce me the cost?
>
> From the docs:
>
> Each recommendation consists of one or more actions, which must be
> implemented together to realize the benefit provided by the
> recommendation. The SQL Access Advisor produces the following types of
> actions:
>
> * PARTITION BASE TABLE
> * CREATE|DROP|RETAIN MATERIALIZED VIEW
> * CREATE|ALTER|RETAIN MATERIALIZED VIEW LOG
> * CREATE|DROP|RETAIN INDEX
> * GATHER STATS
>
> HTH
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

yes, I saw that already thank you. I know how to search the docs. Does this information explain anything?

My question was: what do the /* RETAIN INDEX "SCOTT"."PK_COLUMN_EMP" */ statements acutally do? Or they do nothing? If they do nothing, then why are they needed and how can they lower the total cost? Received on Fri Jul 20 2007 - 08:01:14 CDT