【INDEX】索引失效或者不可用 UNUSABLE

xysoul_雲龍發表於2017-06-22
Index索引



在管理資料庫過程中，我們經常遇到索引失效或者大量刪除、插入表資料時暫時讓索引失效，那麼手動讓索引暫時“失效”需要注意什麼呢？


點選(此處)摺疊或開啟

  1. - -使索引失效的兩條語句
  2. alter index index_name disable ;


  5. alter index index_name unusable


此處需要注意，兩條語句執行條件。

disable 方式只能適用於函式索引 ，而當使用unusable方式時，索引需要rebuild或者直接drop。 官方說明如下：

點選( 此處)摺疊或開啟

  1. DISABLE Clause

  3. DISABLE applies only to a function -based index . This clause lets you disable the use of a function -based index . You might want to do so , for example , while working on the body of the function . Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword .



點選( 此處)摺疊或開啟

  1. UNUSABLE Clause

  3. Specify UNUSABLE to mark the index or index partition (s ) or index subpartition (s ) 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 .

  5. Restriction on Marking Indexes Unusable
  6. You cannot specify this clause for an index on a temporary table .



點選( 此處)摺疊或開啟

  1. - -使索引生效語句

  3. alter index index_name enable ;

  5. alter index index_name rebuild





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-2141124/，如需轉載，請註明出處，否則將追究法律責任。

相關文章