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

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/advisor.htm#CHDFBEJF
> 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

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org