Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted February 6, 2012

Oracle Invisible Indexes

By David Fitzjarrell

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.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM