Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

Invisible Index In Oracle Database

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 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.

1
2
3
4
5
6
7
8
9
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
1
2
3
4
5
SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_ID1';
 
INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 VISIBLE
Check the explain plan:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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:
1
2
3
4
5
6
7
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.  
1
2
3
4
5
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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.

Hope it helps!