Home » RDBMS Server » Performance Tuning » Index Creation
Index Creation [message #312454] Tue, 08 April 2008 22:59 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,
When i do index dropping and recreate of one index i.e. TOOL_IDX_1 it normally takes 1hr to complete.

TOOL table records are 15 Million. Previously when did index drop and create that time records were 14 Million.

Today i performed same activity i.e.

drop index TOOL_IDX_1;

CREATE INDEX TOOL_IDX_1 ON TOOL
(EMAIL)
NOLOGGING
TABLESPACE TOOL_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
INITIAL          5M
MINEXTENTS       1
MAXEXTENTS       2147483645
PCTINCREASE      0
BUFFER_POOL      DEFAULT
)
NOPARALLEL;



2 hrs already elapsed but it is not created till now.

When i see temporary segment it is increasing very slowly.

Normally when doing index rebuilding it increases very fast.


Please tell me where am i wrong?
Or why index creation is not so fast as normally does?

Thanks,

[Updated on: Tue, 08 April 2008 23:00]

Report message to a moderator

Re: Index Creation [message #312457 is a reply to message #312454] Tue, 08 April 2008 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, why do you drop and create this index. Leave it alone.
Then, activate a trace to know where time is spent.

Regards
Michel
Re: Index Creation [message #312638 is a reply to message #312454] Wed, 09 April 2008 08:57 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Four options come to mind:

1. Leave the index alone (as already suggested, and often a good choice).

2. Coalesce the index.

3. Rebuild the index.

4. Drop and recreate the index.

Others can chime in, but I would think that these 4 options should be considered in this order, as they are generally the fastest to slowest to perform.
Previous Topic: Two similar queries, two different plans
Next Topic: Cursors not closed at the database level
Goto Forum:
  


Current Time: Wed Dec 07 14:24:07 CST 2016

Total time taken to generate the page: 0.14316 seconds