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
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
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
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
Oracle Invisible Index Tips, Oracle Database 11g: New Features for DBAs and Developers (by Sam Both sources point out that a hint can be used to compel the query Both also provide an example (not shown here). I though it was good to stop it. OPTIMIZER_USE_INVISIBLE_INDEXES enables or disables the use of invisible indexes.. Values: true. Invisible indexes are treated as visible (normal) indexes. false. Invisible indexes will not be considered by the optimizer but will still be maintained by DML operations.
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
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...
Invisible Indexes and Hints – Striving for Optimal Performance, In Oracle 11gR2, you can turn off the index, yet continue to maintain the index in case you column of the EMP table and subsequent query where the index is not seen: If I make the index visible, I no longer need to use the INDEX hint:. The use_invisible_indexes flag of the optimizer_switch system variable controls whether the optimizer uses invisible indexes for query execution plan construction. If the flag is off (the default), the optimizer ignores invisible indexes (the same behavior as prior to the introduction of this flag).
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.
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.
Invisible Indexes in Oracle 11gR2, The option to restrict optimizer from using any index was not The optimizer will only use the invisible index, when you ask it to do The invisible index will be unavailable to the optimizer unless you include an index hint in the query. optimizer to use TEST_IDX index even though the index is invisible. “If you want the optimizer to take the invisible index into account, you must use the index hint, as shown in the following example:“ Oracle Database 11g: The Top New Features for DBAs and Developers – Schema Management (by Arup Nanda) “To make the optimizer use the index again, you have to explicitly name the index in a hint:”
Oracle 11g Invisible Indexes, Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command. CREATE INDEX For example, when the invisible index is used, the CBO ignores the index as if the index did not exist. If the Oracle 11g initialization parameter optimizer_use_invisible_indexesis set to TRUE, then the CBO will see the index. By default, the parameter is set to FALSE so that the CBO ignores the invisible index when using execution plans.
Invisible Indexes in Oracle Database 11g Release 1, Create an index and re-run to see that the index will be used and the results are Run the query to prove that the invisible index is not used by the optimizer How do you know when it was made invisible/visible though. visibility was changed came from an Oracle-L discussion and the suggestion of log OPTIMIZER_USE_INVISIBLE_INDEXES. Enables or disables the use of invisible indexes. RESULT_CACHE_MODE. Controls whether the database uses the SQL query result cache for all queries, or only for the queries that are annotated with the result cache hint.
Invisible Indexes – a real world example and a bit of , Posts about Invisible Indexes written by Richard Foote. Last year, I discussed how it was possible in Oracle Database 12c to First, let use the same basic setup as the last post: We note the Bitmap Index has not been updated. though it's invisible and there is an equivalent visible Bitmap index in Incongruent hints are ignored, such as a hint that does not "make sense". In the context of the query, an incompatible is indeed ignored, such as a query that specifies incompatible access plans. For example, the following hints are inconsistent and one of the hints will be ignored: Ignored: Parallel hint with index hint. Ignored: Full hint