Hi,
For those who are interested,
the solution turned out to be an easy one (as a few had earlier suggested). i got caught up in a bit of testing frenzy.
1) Mark the Index Partition as UNUSABLE,
ALTER INDEX I_NORMALISED_EVENT MODIFY PARTITION I_NORMALISED_EVENT_P0001 UNUSABLE;
Instruct Oracle to skip unusable indexes,
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE ;
I thought only Queries will bypass unusable indexes with this parameter, but apparantely, even INSERTS and UPDATES do :)
Voila! it works.
I can now insert data till i drop dead and if needed, i can rebuild the index partition in no time.
Thank you all.
Best Regards anand
Hello,
Thanks Mark, David, Jay and Dennis.
I tried (or considered) all the suggestions.
sorry for not explaining my problem carefully earlier.
the Drop index is not a one time activity but part of a sequence of repetitive tests we are performing. the database is 9206 on AIX 5.3, IBM p595 Server with 64-CPU.
all tablespaces are Local and ASSM. Using RAW devices except for init.ora;)
the session waits on IO and it chews up the CPU. pity we can't parallelise the drop. my IO performance is superb but still i generate truck loads of IO per second.
my indexes are locally partitioned indexes. all the index partitions are in 1 or 2 tablespaces. I have about 1200 of these index partitions.
since they are all local index partitions, i can't drop a single index partition or make 1 single partition unusable. the problem i forsee with making index partition unusable is that my inserts/updates will fail.
i need to insert/update data, then drop indexes, do something then again insert data then drop index.
Hence, i was looking for a way to speed up the drop index.
I guess i just have to live with it for now,
thank you all for your time,
cheers anand
On 28/08/06, Jay.Miller@xxxxxxxxxxxxxxxx < Jay.Miller@xxxxxxxxxxxxxxxx> wrote: > > 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > *On Behalf Of *Dennis Williams > *Sent:* Monday, August 28, 2006 10:25 AM > *To:* panandrao@xxxxxxxxx > *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@xxxxxxxxx> 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 > > >
--
All I need to make a comedy is a park, a policeman and a pretty girl - Charlie Chaplin
-- All I need to make a comedy is a park, a policeman and a pretty girl - Charlie Chaplin