DBA Hub

📋Steps in this guide1/2

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.

oracle configurationintermediate
by OracleDba
17 views
1

Overview

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.

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SQL> select count(*) from emp;

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

SQL> create index EMP_ID1 on TESTUSER.EMP(OBJECT_ID);

Index created.

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

INDEX_NAME VISIBILIT
------------------- ---------
EMP_ID1 VISIBLE

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.

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.
2

Section 2

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
50
51
52
53
54
55
56
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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!