oracle 索引invisible,Oracle 11g Invisible Index(原创)

Invisible Indexes

In Oracle Database 11g, you can create invisible indexes. An invisible index is similar to regular indexes in most respects. However, you can hide the index from the optimizer, thus making it invisible to the optimizer. You can also change the status of an index from visible to invisible any time you want, and you can set an initialization parameter at the database level to make all invisible indexes visible to the optimizer, thus treating the invisible indexes as any other regular indexes. You can use the invisible indexes feature to use a temporary index for specific operations without forcing all operations to use that index. You can also use invisible indexes to test the effects of removing an index before you get rid of an index for good. You can create an invisible index by specifying the invisible clause, as shown here:

SQL> create index invib_idx1 on test_tab(name) invisible;

You can also modify a regular index into an invisible index by using the alter index statement, as shown here:

SQL> alter index indx1 invisible;

Note:The database maintains an invisible index during DML statements.

The previous statement will make the index INDX1 invisible to the optimizer. Therefore, the optimizer disregards the index when creating an execution plan that involves the table column indexed by INDX1.Whether you create a new index as invisible or you modify a regular index to an invisible index with the alter index statement, you can make an index visible again by issuing the following statement:

SQL> alter index invisib_idx1 visible;

Once you issue the previous statement, the index becomes a regular index visible to the optimizer.

The new initialization parameter optimizer_use_invisible_indexes helps you enable or disable invisible indexes. You can make the cost optimizer take into account all invisible indexes in the database by setting the optimizer_use_ invisible_indexes parameter to true. You can do this at the session or at the system level, as shown here:

SQL> alter system set optimizer_use_invisible_indexes = true

scope=spfile;

When you set the optimizer_use_invisible_indexes parameter to true, the database treats all invisible indexes as visible (normal) indexes. The default value of the optimizer_use_invisible_indexes initialization parameter is false, which means that the optimizer doesn’t consider any invisible indexes, although the database will maintain the invisible indexes through all DML operations, just as if they were normal indexes. You can check whether an index is visible or not by issuing a query such as the one shown in this example:

SQL> select index_name, visibility from dba_indexes

where index_name like '%NAME_IDX%';

INDEX_NAME         VISIBILITY

------------       -----------

NAME_IDX2          INVISIBLE

NAME_IDX1          INVISIBLE

The VISIBILITY column in the DBA_INDEXES view shows whether an index is visible. In the preceding example, both indexes retrieved by the query are invisible to the optimizer.

参考至: 《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》            外链网址已屏蔽

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:czmcj@