Re: Drop Index or set Unusable

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"

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation