CODEWITHSUNDEEP
oracle-databaseindexingoracle12chint
jera
jera

Reputation: 322

Oracle does not use invisible index despite of hint

I seem to be messing up the index hint syntax but I've tried every schema/table/index-combination I can think of.

The table as well as the index are in a different schema than the SYS user (which is the user I am testing the index hint with)

This is the statement without the hint

select id from car.event where dat < sysdate and type != 0

These are the ways I tried to implement the index hint for the index dat_type in the car schema

select /*+ index(car.event car.dat_type) */ id from car.event where dat < sysdate and type != 0
select /*+ index(event car.dat_type) */ id from car.event where dat < sysdate and type != 0
select /*+ index(car.event dat_type) */ id from car.event where dat < sysdate and type != 0
select /*+ index(event dat_type) */ id from car.event where dat < sysdate and type != 0
select /*+ index(event (dat, type)) */ id from car.event where dat < sysdate and type != 0

So for these five statements I looked up my five different sql_ids and took a look at the execution plans like so

select * from table(dbms_xplan.display_awr([sql_id]));

But none of them shows the usage of the index. All of them use a DoP of 20. Do I have to explicitely disable parallelism for the use of the index? Or can anybody please correct the syntax of my index hint?

This is the definition of the dat_type index

create index car.dat_type on car.event(dat, type) online;

Edit: The index is set to invisible, so that other statements cannot use the index but I want to use it explicitly with the index hint. In my understanding the invisibility should not be a problem for the index hint. Please correct me if I am wrong.

Upvotes: 2

Views: 6135

Answers (3)

Dave Costa
Dave Costa

Reputation: 48131

I have not really used invisible indexes, but my reading of the documentation indicates that a hint alone will not enable their use. You must use an initialization parameter at the system or session level:

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

(from https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317)

Based on my testing with a visible index, either of these options in the hint work as expected:

/*+ index(event) */
/*+ index(event dat_type) */

Possibly other alternatives would work as well, but these are the simplest and they work fine. The problem is not in your index hint syntax; it is that you need another step to enable the use of an invisible index.

Edited to add: as the OP found, another way to enable the use of invisible indexes is the USE_INVISIBLE_INDEX hint. To accomplish what the OP wants with hints only, both the USE_INVISIBLE_INDEX hint and the INDEX hint must be specified.

Upvotes: 0

jera
jera

Reputation: 322

I stumbled upon this article which indicates that it should in fact not be possible to use invisible indexes with just an index hint. However, invisible indexes can be used with the additional hint USE_INVISIBLE_INDEXES.

So this is how I got it to work:

select /*+ use_invisible_indexes index(car dat_type) */ id from car.event where dat < sysdate and type != 0

Upvotes: 4

sandman
sandman

Reputation: 2118

You forgot to choose the ID column in your examples. You have to specify the schema name and the index without the schema "dot".

You can do:

select /*+ index(car dat_type) */ id from car.event where dat < sysdate and type != 0

or

select /*+ index(car, dat_type) */ id from car.event where dat < sysdate and type != 0

This does NOT always force the index, but getting the syntax right is a good start. Sometimes the Cost Based Optimizer (CBO) is stubborn...

Upvotes: 0

Related Questions