| Would making indexes "invisible" be the right approach for this? [message #610962] |
Wed, 26 March 2014 06:02  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
OK, the background story:
I have an application where someone (about 15 years ago on Oracle 7.4 I believe) created about 30 (quite similar) indexes on a table. I have checked them with "monitor usage" and all of them seem to be used now and then. But some specific queries run 2-3 times as long now after the change to 11.2.0.3 as they did under 10.2.0.4. I have some idea how I could change some of the indexes to improve then, and even replace 4-5 of the original indexes with a single one.
Now the question:
For indexes we basically have the option to make them "disabled", "unusable" and "invisible". Are the following conclusions I drew from here correct?
Disabled is only a special case for function based indexes. (so that is not what I want).
Marking an index unusable would require me to rebuild it. (so that is not what I want).
Making it invisible on the other hand would only prevent the optimizer from using it, but it still be maintained in the background so that I could switch it on again when I find it is indeed needed for rarely used queries that I so far haven't encountered in tracing the database. (so that would be what I want)
Have I understood that correctly, and/or has anyone ever encountered any pitfalls while proceeding in that direction?
Thanks
Thomas.
|
|
|
|
| Re: Would making indexes "invisible" be the right approach for this? [message #610965 is a reply to message #610962] |
Wed, 26 March 2014 06:17  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I seem to recall that invisible indexes aren't that invisible. The take away was the optimizer considers the indexes on the table in the calculations before it checks for visibility - it can cause weirdness on edge cases of the optimizer permutations. It'll also use them for constraints.
However I doubt you'd fall prey to that because they are already there, visible and being considered.
I can't see issues in your plan, assuming these are stand alones and not enforcing constraints and so forth, just keep in mind that internally invisible != not there. It'll give you an idea from the end user space certainly but it isnt a cast iron guarantee that nothing will break if they are dropped - it is better than nothing if you have nowhere else to test.
[Updated on: Wed, 26 March 2014 06:19] Report message to a moderator |
|
|
|
|
|
| Re: Would making indexes "invisible" be the right approach for this? [message #612294 is a reply to message #610982] |
Tue, 15 April 2014 12:52 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Just a quick feedback about what happened.:
I set different sets of indexes to "invisible" for 2-3 days each and checked Server IO/CPU/Memory utilization during that time. I also did a little questionnaire for key users where they could rate the responsiveness of the application during those periods. From the traces I did I could also verify that the invisible indexes where indeed not used during the time they where invisible.
Doing that in different combinations I came up with six indexes to replace the original thirty that even give a slightly better user experience. So I kept those six and dropped the rest.
|
|
|
|
| Re: Would making indexes "invisible" be the right approach for this? [message #612376 is a reply to message #610962] |
Thu, 17 April 2014 04:30 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ThomasG wrote on Wed, 26 March 2014 16:32
Making it invisible on the other hand would only prevent the optimizer from using it, but it still be maintained in the background so that I could switch it on again when I find it is indeed needed for rarely used queries
I find your way indeed nice.
If you have made these indexes invisible, one good thing is that, you could just set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE at session level for the rarely used queries, so the invisible indexes would be used by the optimizer for those queries and then set the OPTIMIZER_USE_INVISIBLE_INDEXES parameter back to false after the query is executed. This should not have any adverse affects to anything else.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
