Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Drop Index or set Unusable

Re: Drop Index or set Unusable

From: Jaap W. van Dijk <>
Date: Mon, 24 Jan 2005 18:37:24 +0100
Message-ID: <>

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

>In article <>, 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?
>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
>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.
>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

Original text of this message