Home » SQL & PL/SQL » SQL & PL/SQL » Dropping index
Dropping index [message #285540] Wed, 05 December 2007 00:27 Go to next message
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 #285569 is a reply to message #285540] Wed, 05 December 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Yes
2. Yes

Regards
Michel
Re: Dropping index [message #285632 is a reply to message #285540] Wed, 05 December 2007 03:21 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
No Message Body

[Updated on: Wed, 05 December 2007 03:24]

Report message to a moderator

Re: Dropping index [message #285633 is a reply to message #285569] Wed, 05 December 2007 03:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Michel Cadot wrote on Wed, 05 December 2007 11:20

1. Yes
2. Yes

Regards
Michel


Re: Dropping index [message #285634 is a reply to message #285632] Wed, 05 December 2007 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A solution to what?

Regards
Michel
Re: Dropping index [message #285638 is a reply to message #285540] Wed, 05 December 2007 03:25 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks,

I was not able to see previous solution.

Thanks again.
Re: Dropping index [message #285640 is a reply to message #285638] Wed, 05 December 2007 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How are you unable to see it?

Regards
Michel
Re: Dropping index [message #285641 is a reply to message #285640] Wed, 05 December 2007 03:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe we should appoint someone who does nothing all day but quote your replies.
Since that seems to clarify a lot Very Happy
Re: Dropping index [message #285642 is a reply to message #285540] Wed, 05 December 2007 03:45 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Then 50% replies would be of the same type.

Please read forum guidelines
post oracle version upto 4 decimal places Razz
Re: Dropping index [message #285646 is a reply to message #285642] Wed, 05 December 2007 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So do it for me, that allows me to write more interesting posts. Razz

Regards
Michel
Re: Dropping index [message #285994 is a reply to message #285540] Thu, 06 December 2007 04:37 Go to previous messageGo to next message
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 #285995 is a reply to message #285994] Thu, 06 December 2007 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you see my answer?
If not, it does worth I post a reply.

Regards
Michel
Re: Dropping index [message #285996 is a reply to message #285995] Thu, 06 December 2007 04:43 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Yes i can see you reply Smile
Re: Dropping index [message #286000 is a reply to message #285994] Thu, 06 December 2007 04:54 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous messageGo to next message
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.

Re: Dropping index [message #286073 is a reply to message #286009] Thu, 06 December 2007 07:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't know your Oracle version.
Don't have a test case.

Regards
Michel
Previous Topic: Recycle number
Next Topic: Problem with a query.
Goto Forum:
  


Current Time: Sat Dec 03 14:14:33 CST 2016

Total time taken to generate the page: 0.11505 seconds