| Index Creation [message #312454] |
Tue, 08 April 2008 22:59  |
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 #312638 is a reply to message #312454] |
Wed, 09 April 2008 08:57  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
