Re: Drop Index or set Unusable

On 23 Jan 2005 16:00:31 -0800, Thomas Kyte <tkyte_at_oracle.com> wrote:

>In article <2cabdd32.0501231528.a2bb339_at_posting.google.com>, nimonic says...
>>
>>I have a generic stored procedure that sets indexes unusable to speed
>>up inserts in a data warehousing environment. I have been told by the
>>DBA that setting indexes unusable is not standard data warehousing
>>practice and the indexes should be dropped and recreated instead. I
>>was told at an Oracle course that setting the index unusable was the
>>better option.
>>
>>The tables are partitioned by range with local partitioned indexes.
>>
>>What is the best method and why?
>>
>>Version 8.1.7.4.0
>
>where did the DBA get the "standard set of practices" from? I'd like to read a
>copy. I've never seen one myself.
>
>did they give a technical reason for doing so? some sort of logic behind the
>thought?
>
>
>I mean -- there could be one they are thinking of (space usage immediately pops
>to the top of the list, but after reading on, you might say "oh well, what is a
>little space") but it would be useful to hear what the concern was exactly.
>
>I prefer to set unusable, load it, rebuild.
>
>Why?
>
>because drop + load + create could accidently become drop + load + create all
>but one and no one read the logs close enough to notice the one failure so it
>went un-noticed and performance tanked for two days whilst everyone scratched
>their heads trying to figure out "why".
>
>
>unusable + load + rebuild could turn into unusable + load + rebuild all but one
>which end users will immediately notify us of since their query fails and we can
>either a) drop it and schedule a create later, b) rebuild it right now --
>without having nailed the system with egregious performance. But we *know*
>immediately about the missing index.
>
>it is "safer" to not drop the index, it won't accidently go "missing"

Another advantage of keeping the indexes: you can write a generic rebuild-script, that will not have to be modified if an obsolete index gets dropped or a new one added.

Jaap. Received on Mon Jan 24 2005 - 11:37:24 CST