How to make index invisible in oracle

To make an index invisible in Oracle, use the command: ALTER INDEX index_name INVISIBLE;. This hides the index from the optimizer while maintaining it during DML operations. To test its impact before dropping, set OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE at the session level. This ensures a risk-free performance evaluation in production environments.

What are Invisible Indexes?

Beginning with Oracle Database 11g Release 1, you can create invisible indexes or make an existing index invisible. An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Unlike unusable indexes, an invisible index is maintained during DML statements. Although you can make a partitioned index invisible, you cannot make an individual index partition invisible while leaving the other partitions visible. Using invisible indexes, you can do the following:

• Test the removal of an index before dropping it.
• Use temporary index structures for certain operations or modules of an application without affecting the overall application.

How to use the Invisible Indexes at the Session level

SQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE
---- ---- -----
optimizer_use_invisible_indexes boolean FALSE
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

How to make invisible index visible

You can just use the alter index command

ALTER INDEX index_name VISIBLE;

How to make visible index invisible

ALTER INDEX index_name INVISIBLE;

How to create the invisible index

CREATE INDEX test_idx ON your_table(column_name) INVISIBLE;

How to Test the removal of an index before dropping it.

Step 1

Make the index invisible using the below alter index statement

ALTER INDEX index_name INVISIBLE;

Step 2

Run your query and generate the execution plan:

EXPLAIN PLAN FOR SELECT * FROM your_table WHERE column_name = value;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Step 3

Set session parameter to force optimizer consideration:

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

Re-run EXPLAIN PLAN

EXPLAIN PLAN FOR SELECT * FROM your_table WHERE column_name = value;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

You can compare the execution plan and decide about the index. If you find out index is required then you can make visible. If you find out index is not required then you can drop it

How to check if the index is invisible

Here is the query to check that

SELECT index_name, table_name, visibility FROM dba_indexes WHERE index_name= 'index-name';

How to check the indexes which are invisible

SELECT index_name, table_name, visibility FROM dba_indexes WHERE visibility = 'INVISIBLE';

Related Articles

How to check Index statistics in Oracle
How to Drop the index in Oracle
How to use Virtual Index in Oracle Database