Invisible Indexes in Oracle Databases

In Oracle Database, an invisible index is a type of index that is not used by the optimizer by default but can still be used if explicitly specified in a query. Invisible indexes can be used to test the impact of an index without affecting the existing queries.

Invisible indexes are useful in the following situations:

Testing the impact of an index without affecting existing queries.
Temporarily disabling an index without dropping it.
Creating an index for a specific query without affecting the performance of other queries.
Creating an index for testing and making it invisible in production.
Creating an index for a new feature and making it invisible until the feature is deployed to production.

Some useful indexes related SQL queries:

To create an invisible index, you can use the following SQL script:

CREATE INDEX index_name ON table_name (column_name) INVISIBLE;

To use an invisible index, you can specify it in the query using the INDEX hint, as in the following example:

SELECT /*+ INDEX(table_name index_name) */ column_name
FROM table_name
WHERE column_name = 'value';

SELECT /*+ USE_INVISIBLE_INDEXES */ column_name
FROM table_name
WHERE column_name = 'value';

To make an invisible index visible, use the following SQL script:

ALTER INDEX index_name VISIBLE;

To drop an invisible index, use the following SQL script:

DROP INDEX index_name;

To view all invisible indexes in a database, use the following SQL script:

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

To view the columns of an invisible index, use the following SQL script:

SELECT column_name, column_position
FROM dba_ind_columns
WHERE index_name = 'index_name' AND table_name = 'table_name';

To check if an index is invisible, use the following SQL script:

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

It is important to note that invisible indexes are not maintained when DML statements are executed on the underlying table and the index may become stale or inconsistent. To maintain an invisible index, it must be made visible and then re-created as invisible.

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Invisible Indexes in Oracle Databases

Related posts: