10g SQL Access Advisor (retain index recommendation)

From: andreik <spamme.andreik_at_gmail.com>
Date: Fri, 20 Jul 2007 07:30:23 -0000
Message-ID: <1184916623.003098.298770@r34g2000hsd.googlegroups.com>

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? Received on Fri Jul 20 2007 - 02:30:23 CDT