- 24 Sep, 2023
- 0 Comments
- 2 Mins Read
Invisible Index In Oracle Database
Invisible Index In Oracle Database
Invisible index is introduced in oracle 11g. This type of index will be ignored by database optimizer, as if it doesn’t exists.
Bydefault , Index is visible Only.
SQL> select count(*) from emp; COUNT(*) ---------- 90323 SQL> create index EMP_ID1 on TESTUSER.EMP(OBJECT_ID); Index created.
Check the visibility of the index
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1'; INDEX_NAME VISIBILIT ------------------- --------- EMP_ID1 VISIBLE
Check the explain plan:
SQL> explain plan for select count(*) from TESTUSER.emp where object_id=1;
Explained.
SQL> select * from table(dbms_xplan.display)
2 ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Plan hash value: 853747123
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| EMP_ID1 | 1 | 5 | 1 (0)| 00:00:01 | -->>> INDEX USED
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.
As expected optimizer is using the index for the query.
When index is INVISIBLE:
Now make the INDEX invisible and check the explain plan for the same query.
SQL> alter index EMP_ID1 invisible;
Index altered.
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';
INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 INVISIBLE
SQL> explain plan for select count(*) from TESTUSER.emp where object_id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 149 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 5 | 149 (2)| 00:00:01 | -->> FULL TABLE SCAN
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=1)
14 rows selected.
Create an invisible index directly:
SQL> create index EMP_IDINV on TESTUSER.EMP(OBJECT_ID) invisble; SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_IDINV'; INDEX_NAME VISIBILIT ------------------- --------- EMP_IDINV INVISIBLE
Optimizer_use_invisible_indexes paramter and invisible index There is an parameter.
Optimizer_user_invisible_indexes, which is by default set to FALSE, means, optimizer will ignore all the invisible indexes in the database.SQL> show parameter invi NAME TYPE VALUE ------------------------------------ ----------- -------------------- optimizer_use_invisible_indexes boolean FALSE
To force the optimizer to use all invisible indexes at database level. then set it to TRUE.
SQL> alter system set optimizer_use_invisible_indexes=TRUE scope=both;
System altered.
SQL> show parameter optimizer_use_invisible_indexes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean TRUE
SQL>
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';
INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 INVISIBLE
SQL> explain plan for select count(*) from TESTUSER.emp where object_id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
Plan hash value: 853747123
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| EMP_ID1 | 1 | 5 | 1 (0)| 00:00:01 | --- >>>>>> INDEX USED
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
14 rows selected.