UNUSABLE Clause
When you make an index unusable, it is ignored by the optimizer and is not maintained by DML. When you make one partition of a partitioned
index unusable, the other partitions of the index remain valid.
You must rebuild or drop and re-create an unusable index or index partition before using it.

DISABLE Clause
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.

Invisible Index
An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter
to TRUE at the session or system level.

臨時禁用一個索引時,發現唯一索引置為unusable時,會影響insert操作,但函式索引和非唯一索引不會.
以下測試:
--建立測試表
SQL> create table scott.selectshen01
2 as select * from dba_objects
3 where object_id is not null;
Table created
--建唯一索引
SQL> create unique index scott.idx_selectshen01_01 on scott.selectshen01(object_id);
Index created
--建非唯一索引
SQL> create index scott.idx_selectshen01_02 on scott.selectshen01(data_object_id);
Index created
--建函式索引
SQL> create index scott.idx_selectshen01_03 on scott.selectshen01(lower(owner));
Index created

--設定唯一索引unusable
SQL> alter index scott.idx_selectshen01_01 unusable;
Index altered
--插入資料,報ORA-01502索引不可用.
SQL> insert into scott.selectshen01(object_id)
2 select object_id from scott.selectshen01 where rownum<2;
insert into scott.selectshen01(object_id)
select object_id from scott.selectshen01 where rownum<2
ORA-01502: 索引 'SCOTT.IDX_SELECTSHEN01_01' 或這類索引的分割槽處於不可用狀態
--只能刪除或重建
SQL> drop index scott.idx_selectshen01_01;
Index dropped
--可以插入資料
SQL>
SQL> insert into scott.selectshen01(object_id)
2 select object_id from scott.selectshen01 where rownum<2;
1 row inserted

--設定函式索引unusable
SQL> alter index scott.idx_selectshen01_03 unusable;
Index altered
--可以插入資料
SQL> insert into scott.selectshen01(owner)
2 select owner from scott.selectshen01 where rownum<2;
1 row inserted
--設定非唯一索引unusable
SQL> alter index scott.idx_selectshen01_02 unusable;
Index altered
--可以插入資料
SQL> insert into scott.selectshen01(data_object_id)
2 select data_object_id from scott.selectshen01 where rownum<2;
1 row inserted

順便測試一下index的disable和invisible:
--只有函式索引才有disable/enable選項
SQL> alter index scott.idx_selectshen01_02 disable;
alter index scott.idx_selectshen01_02 disable
ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 選項無效
--設定函式索引disable
SQL> alter index scott.idx_selectshen01_03 disable;
Index altered
--插入資料,報30554索引被禁用
SQL> insert into scott.selectshen01(owner)
2 select owner from scott.selectshen01 where rownum<2;
insert into scott.selectshen01(owner)
select owner from scott.selectshen01 where rownum<2
ORA-30554: 基於函式的索引SCOTT.IDX_SELECTSHEN01_03被禁用
--enable或rebuild函式索引
SQL> alter index scott.idx_selectshen01_03 enable;
Index altered
--可以插入資料
SQL> insert into scott.selectshen01(owner)
2 select owner from scott.selectshen01 where rownum<2;
1 row inserted

--重建索引,測試visible/invisilbe
SQL> alter index scott.idx_selectshen01_02 rebuild;
Index altered
--引數optimizer_use_invisible_indexes為false
SQL> select name,value from v$parameter
2 where name like '%visible%';
NAME                                     VALUE
--------------------------------------- ------------------------------------------
optimizer_use_invisible_indexes          FALSE

--設定索引invisible
SQL> alter index scott.idx_selectshen01_02 invisible;
Index altered

--檢視sql執行計劃,發現不會走這個invisible的索引
SQL> set autotrace traceonly
SQL> select * from scott.selectshen01 where data_object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3630500522

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

| Id | Operation      | Name   | Rows | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT |       |    14 | 2898 |   349   (1)| 00:00:05
|

|* 1 | TABLE ACCESS FULL| SELECTSHEN01 |    14 | 2898 |   349   (1)| 00:00:05
|

--------------------------------------------------------------------------------
...
SQL> set autotrace off

--設定索引visible
SQL> alter index scott.idx_selectshen01_02 visible;
Index altered

--檢視sql執行計劃,發現已經走這個visible的索引
SQL> set autotrace traceonly

SQL> select * from scott.selectshen01 where data_object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2125468280

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

| Id | Operation            | Name          | Rows | Bytes | Cost
(%CPU)| Time      |

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

|   0 | SELECT STATEMENT        |              |   1 |   207 |
1   (0)| 00:00:01 |

|   1 | TABLE ACCESS BY INDEX ROWID| SELECTSHEN01      |   1 |   207 |
1   (0)| 00:00:01 |

|* 2 |   INDEX RANGE SCAN        | IDX_SELECTSHEN01_02 |   1 |      |
1   (0)| 00:00:01 |

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

SQL> set autotrace off

備註:
1.unusable的索引需要rebuild 或 drop,re-create才能回到valid.skip_unusable_indexes為true也是無法在insert時跳過不可用的唯一索引的.
2.disable/enable只能用在函式索引中.從dba_indexes中這個值對應的列名funcidx_status也可以看出.
3.invisible仍然會在表增刪改資料時,更新索引.不可見索引在效能最佳化時測試索引的有效性很有用,不會影響其它會話的執行計劃.可以透過設定系統或會話的
optimizer_use_invisible_indexes為true,讓不可見索引被使用.

oracle小知識點7--索引的unusable,disable,invisible

相關文章