Re: How do I drop and create just one local partition index?

From your description, it sounds as if the best bet is to mark the local indexes on the three critical partitions as unusable, then set the loading sessions to skip unusable, then rebuild those partitions.

However, it may be that marking one partition of a local index as unusable causes a program to avoid updating ALL the partitions of the index - and this is something that might be version dependent, there were certainly bugs in this area in 8.1.5, but I haven't tested more recent versions.

In principle, the idea sounds good, but you probably need to set up a few very careful test cases. (Which have to be re-run every time you upgrade).

You can't (by the way) just drop one partition of a partitioned index; although there are various dirty tricks you can play if you don't have any global indexes.

"P. Fosse" <paul.m.fosse_at_verizon.com> wrote in message news:15b09e7b.0311241148.5190f263_at_posting.google.com...
> I have a large table (for me anyway), 20 million rows that is
> partitioned by date and we drop all indexes, delete and reinsert about
> a million rows a night, but of the 24 monthly partitions, 99% of the
> activity is in the last 3 months (and partitions). Is there a way I
> can just drop the indexes on just those partitions. The indexes are a
> mix of local and global. I'm assuming I can only do this for the
> local ones.
>
> Would making the indexes unusable and rebuilding them also be better
> than drop/recreate?
>
> Thanks in advance.
Received on Mon Nov 24 2003 - 13:54:45 CST