Skip to Main Content
  • Questions

  • making an index invisible just for some clients

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sokrates.

Asked: April 13, 2011 - 1:01 pm UTC

Last updated: May 08, 2012 - 11:09 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hallo Tom,

we stumbled on a probably optimizer bug where some queries don't use a bitmap index M and prefer instead using a range scan on another (btree) index B (on the same table as M), resulting in a execution time of some minutes instead one second.

we're currently working with support on that.
in the meantime, how to workaround ?

we can

a. hint the queries with index_combine - hint (works).
I don't like this "bad" hint. I want to make index B invisible just for some clients ( for example via logon trigger). So I thought of

b. make index B invisible. set OPTIMIZER_USE_INVISIBLE_INDEXES system-wide to TRUE. and set it per session to false in logon trigger

questions:
I. which approach would you prefer ?
II. Can you think of another approach to make an index invisible just for some clients ?

Thanks

and Tom said...

as a 'quick fix for a single query while working the issue with support' - I would prefer the hint.

I don't like using hints on a permanent basis, I don't like using hints as a 'first approach to tuning', but if I had a single query like this - and I was working the issue with support - I'd be tempted to use the hint as a workaround while getting to the bottom of it.

The invisible index trick isn't something I'd want to do. First, you'd be changing a default parameter, which I wouldn't like. Then you'd be using a hidden bit of magic via the logon trigger to put it back for some people. It would be/could be confusing - especially a couple of weeks from now when you've moved onto something else.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Sokrates, April 14, 2011 - 12:46 am UTC


invisible indexes

Ajeet, May 07, 2012 - 8:56 am UTC

Hi Tom,

We are developing a new application on an existing database schema. on this schema there are 4 other applications runs , they use the tables from this schema.

As part of our new application we need to create 3 indexes on 3 diferent tables of this schema. Now the other application folks have a conceren as some of their load processes ( loaded thru informatica mappings) can get impacted ( they might take a little bit more time) . and then we do not know the impact of new indexes on the select queries.. do you think this is a use case where invisible indexes ( make the 3 indexes which is required only by my new application code) is a valid use case.

also - when we insert data into table, I think oracle still maintaints the index structure so I should disable the indexes before the Load and enable after the Load.

and to ensure there is no impact on the select queries,I can make these indexes invisibe.

or as always you may suggest a better approach please.

My database version is oracle 11g R2 ( 11.2.0.2).

Regards
Ajeet
Tom Kyte
May 07, 2012 - 10:39 am UTC

Invisible will not make them faster to maintain. The Informatica stuff will likely be impacted during the loading (especially since they tend to be slow by slow processes and not bulk processing)

Invisible would only affect the ability of the index to be used by queries.

As for disabling before the load - that really depends. If the time to disable+load+rebuild exceeds the time to load, I wouldn't disable them. If you are adding a little to a lot - it might not be worth it.

any other approach

ajeet, May 07, 2012 - 1:18 pm UTC

Thanks Tom for your review response.

what would you suggest is the best thing to do in above situtaion, or you see it a case of incorrect design and should have been take care at the design level.

Regards
Ajeet
Tom Kyte
May 07, 2012 - 2:15 pm UTC

you either

a) need the indexes
b) do not need the indexes


you will find that if (a) is true you will either

a) disable and load slow by slow because you are using informatica
b) just load slow by slow


You will discover which of the second a and b are correct by benchmarking.


Load using PL/SQL

Ajeet, May 07, 2012 - 10:57 pm UTC

Hi Tom

Thanks for review again.
I can change /replace the informatica Load with a PL/SQL proedure and Load these tables as the logic being used in informatica mapping is not complex ,it can be easily replaced with a PL/SQL proc where I can use bulk load.

If I do that I still want to undestand that how to ensure that by adding new indexes, my existing queries will not be impacted and the Load will take lesser time now..as I understand.

SQL Plan management can help here.
Invisible indexes can help .

what else I can think of.

Regards
Tom Kyte
May 08, 2012 - 11:09 am UTC

how about replacing it with a single sql statement - direct path load it?

If I do that I still want to undestand that how to ensure that by adding new
indexes, my existing queries will not be impacted and the Load will take lesser
time now..as I understand.

but they will - that is just "physics". Every index you add will make every load - no matter how you do the load - take long.

You have to maintain them, that is extra work.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library