Re: Dropping Large Indexes

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

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