Using Index Hints in oracle

Hints :

Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements. Hints should only be used as a last-resort if statistics were gathered and the query is still following a sub-optimal execution plan.

Example of the correct syntax for an index hint:

select /*+ index(TEST_IDX IDX_OS_USR) */ * from TEST_IDX;

If we alias the table (A in below case), you must use the alias in the index hint:

select /*+ index(A IDX_OS_USR) */ * from TEST_IDX A;

Note :

Oracle decides to use weather to use this hint or not, of oracle finds that it has faster execution plan without using hint it ignores it. You might think that an index may be helpfull and provide it as hint but oracle may still ignore it. In below case you can see hint being ignored.

CREATE INVISIBLE INDEX ON A TABLE

INVISIBLE INDEX:

Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level

CREATE AN INVISIBLE INDEX:

CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE;

lets check the newly created index :

SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR';

OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY       
--------      -----------                                    ----------               -----------                  --------
ATOORPU       INV_IDX_OS_USR       ATOORPU            TEST_IDX               INVISIBLE 


USER CAN'T MAKE USE OF INVISIBLE INDEX UNTIL HE MAKES IT VISIBLE IN THAT SESSION LETS SEE IF WE CAN USE INVISIBLE INDEX WITH OUT ENABLING IT IN OPTIMIZER:

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

MAKING AN INDEX VISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

MAKING AN INDEX INVISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

-->> you will not have to provide any hints to use index. I have provided hint just to make sure  oracle uses it.

select  * from TEST_IDX where ID=284;        -->> Same as above



MAKING AN INDEX INVISIBLE OR VISIBLE:


Indexes can be created as invisible by using the INVISIBLE keyword at the end, and their visibility can be managed using the ALTER INDEX command

TO MAKE AN EXISTING INDEX INVISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name INVISIBLE;

TO MAKE AN EXISTING INDEX VISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name VISIBLE;