invisible index會被優(yōu)化器所忽略，但是dml操作仍然會維護索引。在session或者system級別使用參數(shù)OPTIMIZER_USE_INVISIBLE_INDEX

摘錄自O(shè)racle 11g的官方文檔：

UNUSABLE Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. The space allocated for an index or index partition or subpartition is freed immediately when the object is marked UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it. Refer to CREATE INDEX ... UNUSABLE for more information.

An index that is not maintained by DML operations and is ignored by the optimizer. All indexes are usable (default) or unusable.

VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

unusable index 是被優(yōu)化器所忽略，并且不被dml操作維護，，如果索引被unusable后，需要重建。

invisible index會被優(yōu)化器所忽略，但是dml操作仍然會維護索引。在session或者system級別使用參數(shù)OPTIMIZER_USE_INVISIBLE_INDEXES=true，那么優(yōu)化器會考慮使用invisible index。適用于查看索引是否被正在的使用上。

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle性能優(yōu)化 之 共享池

本文永久更新鏈接地址：

本文原創(chuàng)發(fā)布php中文網(wǎng)，轉(zhuǎn)載請注明出處，感謝您的尊重！

總結(jié)

以上是生活随笔為你收集整理的oracle 索引invisible,Oracle index unusable和invisible的区别的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。