当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g Invisible Index(原创)

发表于: 2013-10-30   作者:czmmiao   来源:转载   浏览次数:
摘要: Invisible IndexesIn 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 m

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》            http://www.oracle-base.com/articles/11g/invisible-indexes-11gr1.php
本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle 11g Invisible Index(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
今年4月份参与系统开发的时候遇到一个情况: 有这么一个存储过程 存储过程: 表结构为: 前提:这个
聚簇索引和非聚簇索引都是为了增加数据检索速度而存在的. 在配置上, 每个表只能有一个聚簇索引,而
1.index需要储存空间和I/O操作。 2.index的目的是加快select的速度的。 3.insert,update,delete数据
oracle index 1.index需要储存空间和I/O操作。 2.index的目的是加快select的速度的。 3.insert,upda
oracle index 1.index需要储存空间和I/O操作。 2.index的目的是加快select的速度的。 3.insert,upda
从表单访问数据时,Oracle提供了两种方法 a.全表扫描(从表中读取每一行);b.通过ROWID一次读取一行
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号