andreik wrote:
> 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?
They tell you not to drop it.
--
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
Received on Fri Jul 20 2007 - 08:23:00 CDT
