Indexes can be very useful tools to improve query performance, but it can be difficult to test indexes in an environment that exactly mirrors production. Although not the preferred method, Oracle offers in 11.2 the option of invisible indexes. What are invisible indexes? An invisible index can’t be ‘seen’ by any session by default, so it can be created and remain unavailable until testing is scheduled. This can be confusing since Oracle has never offered such a feature. Looking at an example might clear up any confusion.
Creating an invisible index is fairly straightforward:
SQL> create index emp_empno_idx
2 on emp(empno)
3 invisible;
Index created.
SQL>
We now have an invisible index on the EMPNO column of the ubiquitous EMP table. Will it be used? No, since the required parameter, optimizer_use_invisible_indexes, has not been altered from its default of FALSE. We prove this by executing the following query and examining the plan:
SQL>
SQL> select
2 *
3 from
4 emp
5 where
6 empno < 7400;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"<7400)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Notice the full table scan for a single row result set; even though we know the index exists the INVISIBLE keyword prevents Oracle from using it. Let’s fix that with the next attempt:
SQL> alter session set
optimizer_use_invisible_indexes=true;
Session altered.
SQL>
SQL> select
2 *
3 from
4 emp
5 where
6 empno < 7400;
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 576302360
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
| 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1
| 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMPNO_IDX | 1
| | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"<7400)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The current session can now see and use this invisible index even though no one else can. The parameter optimizer_use_invisible_indexes was set to TRUE at the session level, which now allows Oracle to know this index exists. If you’ve created more than one invisible index then all invisible indexes are visible from the session where this parameter is set to TRUE. This could make testing difficult if several invisible indexes are created using a common column especially if it’s the leading column. In the following example the intent was to test EMO_EMPNO_IDX but two similar invisible indexes were created:
SQL> create index emp_empno_idx
2 on emp(empno,sal)
3 invisible;
Index created.
SQL>
SQL> create index emp_empdept_idx
2 on emp(empno,deptno)
3 invisible;
Index created.
SQL>
SQL> set autotrace on
SQL>
SQL> select
2 *
3 from
4 emp
5 where
6 empno < 7400;
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3
(0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"<7400)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> alter session set
optimizer_use_invisible_indexes=true;
Session altered.
SQL>
SQL> select
2 *
3 from
4 emp
5 where
6 empno < 7400;
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 1632405565
-----------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMPDEPT_IDX |
1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"<7400)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Notice that the intended index was not selected; the second of the two invisible indexes was favored for the query. It’s best if only one invisible index per table is created to avoid such occurrences. Of course it’s not hard to drop an invisible index so if the first attempt doesn’t function as intended the index can be dropped and a revised invisible index can replace it.
When should you use invisible indexes? Normally in a development or load test environment when performance issues need to be addressed. An invisible index can be created and then tested using the commands shown previously; the beauty of such a system is that only one session needs to see the invisible index so it can be tested. This prevents a new index from affecting existing processes so that other sessions on a heavily used test server won’t be disrupted by its presence. Test suite results can be invalidated by creating visible indexes without first verifying prior testing is complete (I’ve seen situations where indexes were created on load test systems because the person creating the indexes thought the system was ‘free’ and did not check that information first).
So you have an invisible index created and it provides the desired results; how does it become visible to all? The following command establishes its visibility:
SQL> alter index emp_empno_idx visible;
Index altered.
SQL>
A quick query against EMP from another session proves the index can be seen by all:
SQL> select
2 *
3 from
4 emp
5 where
6 empno < 7400;
EMPNO ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 576302360
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
| 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1
| 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMPNO_IDX | 1
| | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"<7400)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
210 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
861 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Invisible indexes are a very useful addition to an already world-class database, allowing performance tuning in a relatively isolated environment. Of course such tools must be used with discretion, forethought and care as the example with two similar invisible indexes illustrates. Keeping that in mind I find that invisible indexes can greatly improve the tuning process since the indexes can be tested and refined with a minimal impact on other sessions. Implementation then becomes less of a ‘hit-or-miss’ proposition in the finalized version of a product release or in a firefighting session geared toward immediate performance improvement since various forms of an index can be created and the most beneficial version can be selected. Remember, too, that there are no ‘silver bullets’ when it comes to Oracle performance.
