1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Invisible Indexes in Oracle 11g

Discussion in 'SQL PL/SQL' started by SBH, Dec 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    Invisible Indexes are another feature which was introduced in Oracle 11g. It is nothing but a normal index on tables whose usability by the optimizer is set by a parameter. Oracle optimizer would ignore an invisible index on the table if the initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES is FALSE. Reversely, it would use the index for directing access path if the parameter is set as TRUE. By default, the parameter value is FALSE. This value can be set at system or session level.

    An index can upgrade or degrade performance for different queries in the application in different sessions. In such scenario, invisible index can be created and enabled in different sessions by setting parameter OPTIMIZER_USE_INVISIBLE_INDEXES at session level.

    2. Syntax

    An Index can be declared Invisible in two ways. Either it must be created as a new or an existing index can be altered to invisible mode. Below is the syntax for the both.

    Code (Text):
    CREATE INDEX [INDEX NAME] ON [TABLE NAME] ([COLUMN NAMES]) INVISIBLE

    ALTER INDEX [INDEX NAME] [VISIBLE | INVISIBLE]
    3. Example

    The CREATE INDEX statement below creates an invisible index IDX_INV_TEST on the ID column of ORDERS table.

    Code (SQL):
    SQL> CREATE INDEX IDX_INV_TEST ON ORDERS (ID) INVISIBLE;
    INDEX created.
    The ALTER INDEX command below alters a normal index IDX_NOR_TEST to invisible mode.

    Code (SQL):
    SQL> ALTER INDEX IDX_NOR_TEST INVISIBLE
    INDEX altered.
    4. Notes

    1. Dictionary view USER_INDEXES has a new column VISIBILITY to differentiate between Visible and Invisible Indexes. It can be queried to check the mode of the index as below

    Code (SQL):
    SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘IDX_INV_TEXT’;

    INDEX_NAME                     VISIBILIT
    ------------------------       ---------
    IDX_INV_TEST                   INVISIBLE
    2. Initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES can be set at system or session level. Its value can be seen as below.
    SQL>show parameter visible

    Code (SQL):
    NAME                                          TYPE           VALUE
    ----------------------------------      -----------    ------------------------------
    optimizer_use_invisible_indexes              BOOLEAN        FALSE
    It can be set using ALTER command

    At session level

    Code (SQL):
    SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
    SESSION altered.
    At system level

    Code (SQL):
    SQL> ALTER system SET optimizer_use_invisible_indexes=TRUE;
    System altered.
    Illustration

    Code (SQL):
    SQL> SET AUTOTRACE ON
    SQL> SELECT * FROM ORDERS WHERE ID = 139;
    Below trace report shows the full scan of the table ORDERS
    [​IMG]

    Now setting the invisible index usage parameter to TRUE

    Code (SQL):
    SQL>  ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
    SESSION altered.
    Code (SQL):
    SQL> SELECT * FROM ORDERS WHERE ID = 139;
    Below trace report shows the usage of index IDX_INV_TEST and index range scan of the table.

    [​IMG]
     

    Attached Files:

    • Inv.JPG
      Inv.JPG
      File size:
      12.6 KB
      Views:
      5,925
    • INV1.JPG
      INV1.JPG
      File size:
      13.8 KB
      Views:
      713