| Avoid Index to be used by Queries [message #248934] |
Mon, 02 July 2007 14:55  |
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  |
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 |
 |
Michel Cadot
Messages: 68658
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
