Oracle 12C: Invisible index

Doing some reading today as I am trying to make use of invisible indexes but realized that, it didn't get pick up. Did more research and realized, the optimizer_user_invisible_indexes need to be turned on.

Illustration

Default behavior


SQL> show parameter optimizer_use_invisible_indexes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE



SQL> create table A_TBLE (name varchar2(3));
Table created.

SQL> create index A_TBLE_INDEX ON A_TBLE(name);
Index created.

SQL> alter index A_TBLE_INDEX MONITORING USAGE;
Index altered.


SQL> create table B_INVISIBLE_TBLE (name varchar2(3));
Table created.

SQL> create index B_INVISIBLE_INDEX ON B_INVISIBLE_TBLE (NAME) INVISIBLE;
Index created.

SQL> SELECT * FROM A_TBLE;
no rows selected


SQL> COL TABLE_NAME FORMAT A16;
SQL> COL INDEX_NAME FORMAT A20;
SQL> COL MONITORING FORMAT A20;

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME         INDEX_NAME           MONITORING           USE
----------        -------------------- -------------------- ---
A_TBLE             A_TBLE_INDEX         YES                  NO
B_INVISIBLE_TBLE   B_INVISIBLE_INDEX    YES                  NO


SQL> SELECT * FROM A_TBLE WHERE NAME='HELLO';
no rows selected

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME         INDEX_NAME           MONITORING           USE
----------        -------------------- -------------------- ---
A_TBLE             A_TBLE_INDEX         YES                  YES
B_INVISIBLE_TBLE   B_INVISIBLE_INDEX    YES                  NO


SQL> SELECT * FROM B_INVISIBLE_TBLE WHERE NAME='HELLO';
no rows selected


-- the index of B_INVISIBLE_TBLE did not get used.

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME         INDEX_NAME           MONITORING           USE
----------        -------------------- -------------------- ---
A_TBLE             A_TBLE_INDEX         YES                  YES
B_INVISIBLE_TBLE   B_INVISIBLE_INDEX    YES                  NO


Now turning on the optimizer_user_invisible_indexes so, the invisible index will be accessed by the optimizer.

SQL> alter session set optimizer_use_invisible_indexes=TRUE;
Session altered.

SQL> show parameter optimizer_use_invisible_indexes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME       INDEX_NAME           MONITORING           USE
---------------- -------------------- -------------------- ---
A_TBLE           A_TBLE_INDEX         YES                  YES
B_INVISIBLE_TBLE B_INVISIBLE_INDEX    YES                  NO

SQL> SELECT * FROM B_INVISIBLE_TBLE WHERE NAME='HELLO';
no rows selected

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME       INDEX_NAME           MONITORING           USE
---------------- -------------------- -------------------- ---
A_TBLE           A_TBLE_INDEX         YES                  YES
B_INVISIBLE_TBLE B_INVISIBLE_INDEX    YES                  YES

SQL> alter session set optimizer_use_invisible_indexes=FALSE;
Session altered.

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME       INDEX_NAME           MONITORING           USE
---------------- -------------------- -------------------- ---
A_TBLE           A_TBLE_INDEX         YES                  YES
B_INVISIBLE_TBLE B_INVISIBLE_INDEX    YES                  YES


Verdict, when creating invisible index in Oracle 12C, the index will be updated but it will not be used by the optimizer as the
default behavior. In order, to have optimizer to use the invisible index, user need to explicitly enabled.