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

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 20 Jul 2007 15:02:37 +0200
Message-ID: <f7qbp5$i6u$1@news1.zwoll1.ov.home.nl>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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?
>

It does not do anything, apart from telling you that this particular index is a useful one, and should be kept (retained).

Some indexes could be dropped - some should be retained. It's the opposite of keeping.
- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGoLJtLw8L4IAs830RArbDAJ9sSHmoZ91p8hIeok3SNDABo45RJQCZAYU6 I5kmFu0UXleRDv923W4R6AE=
=s9/x
-----END PGP SIGNATURE----- Received on Fri Jul 20 2007 - 08:02:37 CDT