OraFAQ Forum: Performance Tuning » To find the indexes (used and unused on the table

Home » RDBMS Server » Performance Tuning » To find the indexes (used and unused on the table (Oracle 10g on "Windows XP" and "Sun Solaris")

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To find the indexes (used and unused on the table [message #475447]

Wed, 15 September 2010 03:53

xclusiveravi523
Messages: 3
Registered: September 2010
Location: Hyderabad

Junior Member

Hi

I would like to know what is the query used for finding out all the indexes on the table and whether they are used or unused on the table.

Please help me out.

Thanks and REgards
Ravikanth

Report message to a moderator

Re: To find the indexes (used and unused on the table [message #475458 is a reply to message #475447]

Wed, 15 September 2010 04:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

dba_indexes
v$segment_statistics

Regards
Michel

Report message to a moderator

Re: To find the indexes (used and unused on the table [message #475476 is a reply to message #475458]

Wed, 15 September 2010 04:51

xclusiveravi523
Messages: 3
Registered: September 2010
Location: Hyderabad

Junior Member

Hi Michel,

Thanks for the immediate response.
But if you could provide me the query that monitors the indexes (checks whether the index is being used in that particular table or not) that would be of the greatest help.

Thanks
Ravi

Report message to a moderator

Re: To find the indexes (used and unused on the table [message #475496 is a reply to message #475476]

Wed, 15 September 2010 05:36

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe you could:
1/ have a look in Database Reference for the view I mentioned
2/Search for "index monitoring", here, in search firld of Oracle documentation

Regards
Michel

Report message to a moderator

Previous Topic:	Query performance gain using statistics
Next Topic:	Sql Tunning Concept

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 16 15:03:11 CST 2025