Re: Split partition

"zeb" <spam_at_nowhere.com> wrote in message news:411132a9$0$1890$636a15ce_at_news.free.fr...
> Hi,
>
> I have a partitioned table,
> when I split the last partition
> there is no rows in the new partition
> why ?
> the local index is unsuable and I have to drop and create it
> why ?
>
> Thanks in advance
> oracle9i , HP-UX 11.0

I don't know why your new partition is empty, but it's probably to do with date formats, which I'm hopeless at at the best of times. But you ask why the local index is unusable, and that one I can asnwer: it's just the way it is. Partition DDL (such as merge, split, etc) always invalidates *any* index partitions which happen to reference the partition undergoing the DDL. That means the local index for that partition/those partitions; it also means any global indexes you may have on that table, in their entirety (one of the main reasons for not wanting global indexes, of course).

Being 9i, and presumably release 2, you can now do your partition DDL with the new 'update global indexes' clause to keep global indexes in working order rather than have them invalidated. But from the words in the syntax themselves, you can guess that this is only an option for global indexes. Local indexes are still invalidated, and there's no way around that.

Regards
HJR Received on Thu Aug 05 2004 - 15:06:42 CDT