Friday, May 02, 2014

Invisible Indexes (Part 1)

Oracle introduced INVISIBLE indexes in version 11. The example below creates an INVISIBLE index, makes it VISIBLE then makes it INVISIBLE again. The VISIBILITY column in USER_INDEXES is used to check that each change has worked:

SQL> col visibility format a10
SQL> create table my_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> create index my_index
  2  on my_table(owner)
  3  invisible
  4  /

Index created.

SQL> select visibility
  2  from user_indexes
  3  where index_name = 'MY_INDEX'
  4  /

VISIBILITY
----------
INVISIBLE

SQL> alter index my_index visible
  2  /

Index altered.

SQL> select visibility
  2  from user_indexes
  3  where index_name = 'MY_INDEX'
  4  /

VISIBILITY
----------
VISIBLE

SQL> alter index my_index invisible
  2  /

Index altered.

SQL> select visibility
  2  from user_indexes
  3  where index_name = 'MY_INDEX'
  4  /

VISIBILITY
----------
INVISIBLE

SQL>

********************

When an index is INVISIBLE, it is still updated by DML but it is ignored by the optimizer. You can make an index INVISIBLE and see if this has a detrimental effect on an application’s performance. If it does not then you can consider dropping it.

In the example below, an index is created and used by a SELECT statement.

The index is made INVISIBLE and the SELECT statement is repeated. This time it does not use the index:

SQL> col visibility format a10
SQL> create table my_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> create index my_index
  2  on my_table(owner)
  3  /

Index created.

SQL> set autotrace on
SQL> select count(*) from my_table
  2  where owner = 'SYSTEM'
  3  /

  COUNT(*)
----------
       154


Execution Plan
----------------------------------------------------------
Plan hash value: 4077732364

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    17 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| MY_INDEX |   154 |  2618 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYSTEM')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         32  recursive calls
          0  db block gets
         68  consistent gets
          1  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter index my_index invisible
  2  /

Index altered.

SQL> select count(*) from my_table
  2  where owner = 'SYSTEM'
  3  /

  COUNT(*)
----------
       154


Execution Plan
----------------------------------------------------------
Plan hash value: 228900979

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    17 |     6  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_TABLE |   152 |  2584 |     6  (17)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYSTEM')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        253  recursive calls
          0  db block gets
        164  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

No comments: