Avoid Index to be used by Queries [message #248934] |
Mon, 02 July 2007 14:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
feroze
Messages: 144 Registered: September 2005 Location: chicago
|
Senior Member |
|
|
Hi,
we impelemented some partitioned indexes (local ) and some global composite indexes are also there for a table with 120 million rows,
Now question some queries from the Application is still using OLD index, 2 questions
a ) Is there a way in the DB to know which queries are using this index
b ) is it possible to disable the index so that select statements and queries don't use the index ,but at same time inserts should be recorded, because if things get's bad then i can enable that index back and in this case i will have those inserted records entry also in index.
Thanks
|
|
|
|
Re: Avoid Index to be used by Queries [message #248945 is a reply to message #248943] |
Mon, 02 July 2007 15:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
feroze
Messages: 144 Registered: September 2005 Location: chicago
|
Senior Member |
|
|
Thanks for follow up,
Answer B can't be done till we know the query,
and to know the query you want us to do Answer (a) which we don't know which query is using those indexes, and we can't trace whole DB or 300 + Users,
I will try for a 1 or 2 hr STATSPACK and see if there is any entry for this index.
Thanks
|
|
|
Re: Avoid Index to be used by Queries [message #248947 is a reply to message #248945] |
Mon, 02 July 2007 15:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
For a), there is no other way to be sure you get ALL queries. Statspack polls the sql area and so may miss some statements.
For b), you can always put the hint on each and every query. But why not drop the index and recreate it if you see it is bad.
In the end, why do you think you have to "disable" the index?
Regards
Michel
|
|
|