Dropping index [message #285540] |
Wed, 05 December 2007 00:27  |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Hi Experts,
I want to drop one index on the table.
As i am working on the production environment i have some queries regarding it.
1. Does table locks when we drop an index on the table?
2. Does table locks when we are creating index on the table?
Thanks in advance.
|
|
|
|
|
|
|
|
|
|
|
|
Re: Dropping index [message #285994 is a reply to message #285540] |
Thu, 06 December 2007 04:37   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Thanks for your replies.
Next thing i want to know is that dropping an index and creating it is same like rebuilding index?
Does it has an performance impact doing both things?
Thanks in advance.
|
|
|
|
|
Re: Dropping index [message #286000 is a reply to message #285994] |
Thu, 06 December 2007 04:54   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote: | Next thing i want to know is that dropping an index and creating it is same like rebuilding index?
|
No.
This is information that is freely and easily available by reading the appropriate documentation. Please do so here
|
|
|
Re: Dropping index [message #286009 is a reply to message #286000] |
Thu, 06 December 2007 05:18   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Ok thanks.
I will search on the net ragarding it.
Once thing i found while dropping.
I have 2 indexs based on one column.
First is normal index and other one is function based index.
1. IDX1 on TEST_EMP(EMAIL)
2. IDX2 on TEST_EMP(UPPER(EMAIL))
When i drop an index IDX2(Function based) that time , Oracle invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
But when i am dropping an index IDX1 that time not a single object is invalidated.
Please tell me a reason behind it.
Thanks in advance.
|
|
|
|