Invisible Indexes and Locks

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:

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:

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.