Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Invisible Indexes and Locks

17 October 2008 6 Comments Written by Christian Antognini

Invisible indexes are useful to temporarily hide an index from the query optimizer. In this post, instead of explaining what invisible indexes are, I would like to show whether the database engine uses invisible indexes to avoid false contention caused by unindexed foreign key.

  • Let’s start by creating two test tables with a relation (foreign key) between them:
SQL> CREATE TABLE t1 AS SELECT rownum AS n FROM dual CONNECT BY level <= 10;

Table created.

SQL> CREATE TABLE t2 AS SELECT rownum AS n FROM dual CONNECT BY level <= 10;

Table created.

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_uk UNIQUE (n);

Table altered.

SQL> ALTER TABLE t2 ADD CONSTRAINT t1_t2_fk FOREIGN KEY (n) REFERENCES t1 (n);

Table altered.
  • Note that the foreign key is unindexed. Therefore, as shown by the following example, false contention due to locks is possible:
Session 1 Session 2
SQL> SELECT sys_context('userenv','sid') AS sid
  2  FROM dual;

SID
----
86

SQL> INSERT INTO t2 VALUES (1);

1 row created.
SQL> SELECT sys_context('userenv','sid') AS sid
  2  FROM dual;

SID
----
85

SQL> DELETE FROM t2 WHERE n = 2;

1 row deleted.

SQL> DELETE FROM t1 WHERE n = 2;
SQL> SELECT blocking_session
  2  FROM v$session
  3  WHERE sid = 85;

BLOCKING_SESSION
----------------
              86

SQL> ROLLBACK;

Rollback complete.
1 row deleted.

SQL> ROLLBACK;

Rollback complete.
  • Now, let’s execute the same SQL statements when the the foreign key is indexed by an invisible index:
Session 1 Session 2
SQL> CREATE INDEX i ON t2 (n) INVISIBLE;

Index created.

SQL> INSERT INTO t2 VALUES (1);

1 row created.
SQL> DELETE FROM t2 WHERE n = 2;

1 row deleted.

SQL> DELETE FROM t1 WHERE n = 2;

1 row deleted.
  • As you can see there is no false contention due to locks.

Conclusion: invisible indexes can only be used to assess how the query optimizer would behave when a specific index is (not) available. But, be careful, with them you cannot assess how an application would behave when a specific index is (not) available. In fact, except for access paths, invisible indexes are regularly updated and used for setting locks when DML statements are executed.

Update 2014-03-01: Since I was able to reproduce this behavior with 11.2.0.4 and 12.1.0.1, I added this post to the categories 11gR2 and 12cR1.

11gR1, 11gR2, 12cR1, Indexes
TOP on Google Book Search
Introduce TVD$XTAT

6 Comments

3 Pings/Trackbacks

  1. Asif Momen Asif Momen
    17 October 2008    

    Well demonstrated.

    Reply
  2. Visible Invisible Indexes (The Invisible Band) « Richard Foote’s Oracle Blog Visible Invisible Indexes (The Invisible Band) « Richard Foote’s Oracle Blog
    20 November 2008    

    […] a comment on the above post by Michael Sorensen mentions a nice post by Christian Antognini in which he demonstrates how so-called Invisible Indexes on Foreign Keys can still be used by […]

  3. Surachart Opun Surachart Opun
    3 December 2009    

    It’s a good example & idea to use invisible index with FOREIGN KEY.

    Reply
  4. Book Review: Pro Oracle SQL « Charles Hooper's Oracle Notes Book Review: Pro Oracle SQL « Charles Hooper's Oracle Notes
    17 January 2011    

    […] invisible indexes may be used by the optimizer to more accurately determine cardinality estimates (reference […]

  5. Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes
    10 September 2011    

    […] not a complete/safe test to determine whether or not an index may be safely dropped. (pages 70-71 reference […]

  6. Matthias Rogel Matthias Rogel
    13 May 2013    

    Christian,

    invisible indexes can even be used to back up a primary key
    http://richardfoote.wordpress.com/2008/11/20/visible-invisible-indexes-the-invisible-band/#comment-87679

    Regards
    Matthias

    Reply
  1. Visible Invisible Indexes (The Invisible Band) « Richard Foote’s Oracle Blog on 20 November 2008 at 13:57
  2. Book Review: Pro Oracle SQL « Charles Hooper's Oracle Notes on 17 January 2011 at 07:06
  3. Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes on 10 September 2011 at 22:13

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.