RE: Dropping Large Indexes

Is the index in a locally managed or dictionary managed tablespace? If it has a lot of extents it can take a long time to drop if it's dictionary managed.  

Thanks,

Jay Miller

Sr. Oracle DBA

x68355  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dennis Williams Sent: Monday, August 28, 2006 10:25 AM
To: panandrao_at_gmail.com
Cc: oracle-l
Subject: Re: Dropping Large Indexes  

Anando,  

Have you checked to what Oracle is waiting on during that 2 hours?  

Dennis Williams  

On 8/27/06, Anand Rao <panandrao_at_gmail.com> wrote:

Hi,

we all know to build large indexes using many fast methods like parallel, nologging, etc.

how can i speed up dropping large local index partitions with sizes exceeding 400GB? it takes a couple of hours on a 64-CPU IBM p595 box. that's too much lost for me.

the mark index 'unsuable' trick doesn't help as my inserts/updates would fail.

since it is not an index supporting a PK, i was thinking of dropping only the index partition that i don't want (leaving the table partition intact). i could later on build the index partition after the data load.

is that the only "fast" method?

thanks in advance,

regards
anand

---
All I need to make a comedy is a park, a policeman and a pretty girl -
Charlie Chaplin 

 


--
http://www.freelists.org/webpage/oracle-l