Understanding INVISIBLE Index

By default an index is VISIBLE.The Oracle optimizer will pick only VISIBLE index.

Oracle 11g allows indexes to be marked as INVISIBLE. INVISIBLE indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level.

Indexes can be created as INVISIBLE by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

In case of INVISIBLE index,the index maintainance will be done in the background by oracle and the index segment will not be dropped,but during query execution,the Oracle optimizer will not pick/use this index.

INVISIBLE index can be used during index cleanup process.

INVISIBLE indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.

SQL> CREATE TABLE sales
  2  (
  3    id INT,
  4    order_id INT,
  5    product_code VARCHAR2(3),
  6    amount INT,
  7    date_of_sale DATE
  8  );

Table created.

SQL> INSERT INTO sales
  2  SELECT ROWNUM,FLOOR(DBMS_RANDOM.VALUE(90,9000)),
  3  DBMS_RANDOM.STRING('U',3),FLOOR(DBMS_RANDOM.VALUE(90,9000)),
  4  TRUNC(SYSDATE) - FLOOR(DBMS_RANDOM.VALUE(10,900))
  5  FROM dual
  6  CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE UNIQUE INDEX idx_sales_id ON sales(id);

Index created.

SQL> ANALYZE INDEX idx_sales_id VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';

SEGMENT_NAME   SEGMENT_TYPE    TABLESPACE_NAME  BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID     INDEX            SYSTEM         256         17        65536     1048576

SQL> SET AUTOTRACE ON EXPLAIN

SQL> SELECT * FROM sales WHERE id = 1000;

        ID   ORDER_ID PRO     AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
      1000       8794 ZQR       2282 22-JAN-18

Execution Plan
----------------------------------------------------------
Plan hash value: 1439439247

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    51 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SALES        |     1 |    51 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_SALES_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000)   

SQL> ALTER INDEX idx_sales_id INVISIBLE;

Index altered.

SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';

SEGMENT_NAME   SEGMENT_TYPE    TABLESPACE_NAME  BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID     INDEX            SYSTEM         256         17        65536     1048576

SQL> SELECT * FROM sales WHERE id = 1000;

        ID   ORDER_ID PRO     AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
      1000       8794 ZQR       2282 22-JAN-18

Execution Plan
----------------------------------------------------------
Plan hash value: 781590677

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   337 | 17187 |   115   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| SALES |   337 | 17187 |   115   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1000)
   
SQL> ALTER INDEX idx_sales_id VISIBLE;

Index altered.

SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';

SEGMENT_NAME   SEGMENT_TYPE    TABLESPACE_NAME  BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID     INDEX            SYSTEM         256         17        65536     1048576

SQL> SELECT * FROM sales WHERE id = 1000;

        ID   ORDER_ID PRO     AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
      1000       8794 ZQR       2282 22-JAN-18

Execution Plan
----------------------------------------------------------
Plan hash value: 1439439247

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    51 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SALES        |     1 |    51 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_SALES_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000) 

SQL> CREATE TABLE invis_indx_tab
  2  (
  3    id  NUMBER
  4  );

Table created.

SQL> BEGIN
  2    FOR i IN 1 .. 10000 LOOP
  3      INSERT INTO invis_indx_tab VALUES (i);
  4    END LOOP;
  5    COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX invis_indx_tab_id ON invis_indx_tab(id) INVISIBLE;

Index created.

SQL> SELECT index_name,visibility
  2  FROM all_indexes
  3  WHERE index_name = 'INVIS_INDX_TAB_ID';

INDEX_NAME              VISIBILITY
-------------------- ----------------
INVIS_INDX_TAB_ID       INVISIBLE

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'invis_indx_tab', CASCADE=> TRUE);

PL/SQL procedure successfully completed.

SQL> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES

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

SQL> SET AUTOTRACE ON EXPLAIN

SQL> SELECT * FROM invis_indx_tab WHERE id = 9999;

        ID
----------
      9999

Execution Plan
----------------------------------------------------------
Plan hash value: 116359494

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INVIS_INDX_TAB |     1 |     4 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=9999)
   
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 * FROM invis_indx_tab WHERE id = 9999;

        ID
----------
      9999

Execution Plan
----------------------------------------------------------
Plan hash value: 2133881760

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| INVIS_INDX_TAB_ID |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=9999)   
   
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

Session altered.

SQL> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES

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

SQL> ALTER INDEX invis_indx_tab_id VISIBLE;

Index altered.

SQL> SELECT index_name,visibility
  2  FROM all_indexes
  3  WHERE index_name = 'INVIS_INDX_TAB_ID';

INDEX_NAME              VISIBILITY
-------------------- ----------------
INVIS_INDX_TAB_ID         VISIBLE

SQL> SELECT * FROM invis_indx_tab WHERE id = 9999;

        ID
----------
      9999

Execution Plan
----------------------------------------------------------
Plan hash value: 2133881760

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| INVIS_INDX_TAB_ID |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=9999)