Wednesday, June 6, 2012

Oracle 11g: Invisible Indexes



Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level.


Indexes can be created as invisible by using the INVISIBLE keyword. Below are the possible syntaxes related to invisible indexes

  • CREATE INDEX indx_name ON table_name(column_name) INVISIBLE;
  • ALTER INDEX indx_name VISIBLE;
  • ALTER INDEX indx_name INVISIBLE;
  • SELECT INDEX_NAME, VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='INDX_NAME';

Below is the example of creating the invisible index


SQL> create table test
  2  (a number);


Table created.


SQL> begin
  2  for i in 1..10000 loop
  3  insert into test values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> create index test_index on test(a) invisible;

Index created.

SQL> exec dbms_stats.gather_table_stats('TEST_USER','test',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select * from test where a =9123;

         A
----------
      9123

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"=9123)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Note full table scan as highlighted in red above

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

Session altered.

SQL> select * from test where a =9123;

         A
----------
      9123


Execution Plan
----------------------------------------------------------
Plan hash value: 3185533302
-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_INDEX |     1 |     4 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   1 - access("A"=9123)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Note Index table scan as highlighted in red above after changing the optimizer_use_invisible_indexes to TRUE

No comments:

Post a Comment