Re: Dropping Large Indexes
- From: "Anand Rao" <panandrao@xxxxxxxxx>
- To: Jay.Miller@xxxxxxxxxxxxxxxx, oracledba.williams@xxxxxxxxx
- Date: Tue, 29 Aug 2006 09:25:48 +0530
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
Other related posts:
