Re: Dropping Large Indexes

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

On 29/08/06, Anand Rao <panandrao_at_gmail.com> wrote:
>
> 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_at_tdameritrade.com < Jay.Miller_at_tdameritrade.com>
> 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_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
> >
> >
> >
>
>
>
> --
>
> 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

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