DBA Hub

📋Steps in this guide1/6

Invisible index in oracle database 11g DBACLASS

Invisible index is introduced in oracle 11g. As the name suggest, this type of index will be ignored by database optimizer, as if it doesn’t exists. EXAMPLE: —  CREATE AN INDEX ( BY DEFAULT THE INDEX WILL BE A VISIBLE ONE) SQL> desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME […]

oracle configurationintermediate
by OracleDba
13 views
1

EXAMPLE:

—  CREATE AN INDEX ( BY DEFAULT THE INDEX WILL BE A VISIBLE ONE)

Code/Command (click line numbers to comment):

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
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER

SQL> select count(*) from emp;

  COUNT(*)
----------
     90323


SQL>  create index EMP_ID1 on DBACLASS.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
2

Check the explain plan:

As expected optimizer is using the index for the query.

Code/Command (click line numbers to comment):

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
SQL> explain plan for select count(*) from dbaclass.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.
3

When index is INVISIBLE:

Now make the INDEX invisible and check the explain plan for the same query. We can see, it is bypassing the index scan and doing full table scan , Despite the index is present. Because the index is invisible.

Code/Command (click line numbers to comment):

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
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 dbaclass.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.
4

Create an invisible index directly:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SQL> create index EMP_IDINV on DBACLASS.EMP(OBJECT_ID) invisble;

SQL> select index_name,VISIBILITY from dba_indexes where index_NAME='EMP_IDINV';

INDEX_NAME          VISIBILIT
------------------- ---------
EMP_IDINV           INVISIBLE
5

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. To force the optimizer to use all invisible indexes at database level. then set it to TRUE . We can see the optimizer is using the INDEX, despite it is being invisible.

Code/Command (click line numbers to comment):

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
43
44
45
46
47
48
49
SQL> show parameter invi

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

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 dbaclass.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.
6

What is its use?

Most common use is while dropping the INDEX . If we are planning to drop index, then before dropping , keep the index in invisible mode and monitor for sometime. once things are good, we can drop it later.( Because post dropping, creating a index will take lot of time, if table size is huge).

Comments (0)

Please to add comments

No comments yet. Be the first to comment!