Oracle 11g: Invisible Indexes

Oracle 11g 允许将索引标记为invisible. oracle像维护其他索引一样维护 invisible index ，但是默认invisible index不能被优化器使用，除非显式设置 OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE(可以alter system/session).可以在创建索引的时候指定 INVISIBLE关键字或 ALTER INDEX命令来invisible一个索引。

CREATE INDEX idx_name on table_name(column_name) INVISIBLE;
ALTER INDEX idx_name INVISIBLE;
ALTER INDEX idx_name VISIBLE;

demo:

SQL> create table ii_tab( id number);

Table created.

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

PL/SQL procedure successfully completed.

SQL> create index ii_tab_id on ii_tab(id) invisible;

Index created.

SQL> exec dbms_stats.gather_table_stats(USER,'ii_tab',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select * from ii_tab where id=9999;

        ID
----------
      9999


Execution Plan
----------------------------------------------------------
Plan hash value: 2057286804

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| II_TAB |     1 |     4 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("ID"=9999)


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

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select * from ii_tab where id=9999;

        ID
----------
      9999


Execution Plan
----------------------------------------------------------
Plan hash value: 81730945

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| II_TAB_ID |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("ID"=9999)


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        521  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> alter session set optimizer_use_invisible_indexes=false;

Session altered.

可以看到即使设置索引为invisible，当optimizer_use_invisible_indexes为true的时候 优化器仍然会走索引而非全表。

对比一下 ALTER INDEX ... UNUSABLE, 优化器不会使用UNUSABLE索引，需要 REBUILD