Home » SQL & PL/SQL » SQL & PL/SQL » Non used Index listing .. (winXp, Oracle 10i)
Non used Index listing .. [message #351467] Wed, 01 October 2008 00:48 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

How to list all non used index in a schema & to remove the same? We have a list of index, which may be used or mayn't be used .. please tell me how to identify that?

Thanks,
thiyagu

[Updated on: Wed, 01 October 2008 00:49]

Report message to a moderator

Re: Non used Index listing .. [message #351469 is a reply to message #351467] Wed, 01 October 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ALTER INDEX ... MONITORING USAGE;

Then query V$OBJECT_USAGE.USED.

Regards
Michel
Re: Non used Index listing .. [message #351481 is a reply to message #351469] Wed, 01 October 2008 01:21 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Further to add.It will tell the index is used or not.

But will not tell you whether you need it or not.

Regards,
Rajat

Re: Non used Index listing .. [message #351483 is a reply to message #351467] Wed, 01 October 2008 01:30 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Thanks for your reply. Smile

so i have to alter all my index in order to moniter. But i have 1000+ index in the schema. Is there any other way to identify the index usage without altering the index. My client is asking for report that includes Index name & used or executed no of time. Razz

Thanks,
thiyagu

[Updated on: Wed, 01 October 2008 01:30]

Report message to a moderator

Re: Non used Index listing .. [message #351484 is a reply to message #351483] Wed, 01 October 2008 01:42 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
If you wan't to check their usuage then you have to.
You can easily create a script that automatically enable
monitoring.

Chk This

Regards,
Rajat
Re: Non used Index listing .. [message #351498 is a reply to message #351484] Wed, 01 October 2008 02:46 Go to previous message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Wow .. super ... thanks a lot rajat ...

thiyagu
Previous Topic: Query Optimization
Next Topic: counting function
Goto Forum:
  


Current Time: Tue Feb 11 15:24:55 CST 2025