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.
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