Alter index invisible or visible in Oracle

Alter make index invisible or visible in Oracle

Help in performance, We want to drop the index from the production server but we did not know the effect after drop. Table having large in size if we need to create again the dropped index then we need large time. So, plan to make the index invisible for time period if our environment need the index we will make it visible by alter its property.

Make the index invisible

ALTER INDEX schema_name.index_name INVISIBLE;

Make the index visible

ALTER INDEX schema_name.index_name VISIBLE;

Check the visible or invisible index present on table

COL index_name for a25
select index_name,visibility from dba_indexes where table_name = 'TESTPRIMARY';

INDEX_NAME                VISIBILIT
------------------------- ---------
SYS_C008201               VISIBLE

--Check in complete schema_name
SQL> select index_name,visibility from user_indexes;

INDEX_NAME                VISIBILIT
------------------------- ---------
SYS_C008201               VISIBLE
SYS_C008203               VISIBLE

SQL> alter index test.sys_c008203 invisible;
Index altered.

SQL> select index_name,visibility from user_indexes;

INDEX_NAME                VISIBILIT
------------------------- ---------
SYS_C008201               VISIBLE
SYS_C008203               INVISIBLE

We can also create new index for testing purpose directly in invisible state and check index increase the performance of SQL queries then we make it in visible state.

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

Session use VISIBLE or INVISIBLE index
We can use the invisible and visibile index by changing session parameters. This case is used such as date time we need to use for transactional purpose and in night time we need to use for reporting purpose.
We can increase performance by making index in both two while connecting session we can change paratern of use of visible or invisible index.

-- use invisible index
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

-- use visible index
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.