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;
Smart way of Technology 