Oracle資料庫中的不可見索引 invisible index

Oracle11g開始，我們可以將索引設定為不可見索引，也可以叫隱藏索引。Oracle在對錶進行DML操作時仍然會維護不可見索引，但不會讓最佳化器選擇不可見索引。

不可見索引可以在以下場景使用：

1、在刪除索引之前，可以將器設定為不可見索引，如果後續需要可以將快速恢復。

2、在不影響現有程式和程式碼情況下，建立不可見索引。

以下時不可見索引的實驗：

--連線到CDB
[oracle@]$ sqlplus / as sysdba
SYS@HFXFCDB > show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XFPDB1                         READ WRITE NO
--切換到PDB：XFPDB1
SYS@HFXFCDB > alter session set container=XFPDB1;
Session altered.
--建立使用者hfxrf
SYS@HFXFCDB > create user hfxf identified by hfxf;
User created.
--給使用者授權
SYS@HFXFCDB > grant resource,connect to hfxf;
--建立使用者表空間hfxf_tbs
SYS@HFXFCDB > create tablespace hfxf_tbs datafile '+DATA/xfpdb1/hfxf_tbs01.bdf' size 50m;
Tablespace created.
--建立索引表空間hfxf_tbs_inx
SYS@HFXFCDB > create tablespace hfxf_tbs_inx datafile '+DATA/xfpdb1/hfxf_tbs_inx01.dbf' size 20m;
Tablespace created.
-透過以下命令驗證hfxf使用者的預設表空間
SYS@HFXFCDB > select USERNAME,DEFAULT_TABLESPACE from dba_users where username='HFXF';
USERNAME
--------------------------------------------------------------------------------
DEFAULT_TABLESPACE
------------------------------
HFXF
HFXF_TBS
--使用hfxf使用者連線到xfpdb1中
SYS@HFXFCDB > conn hfxf/hfxf@xfpdb1
Connected.
--建立cust表
HFXF@xfpdb1 > CREATE TABLE cust
  2  (cust_id    NUMBER
  3  ,last_name  VARCHAR2(30)
  4  ,first_name VARCHAR2(30)
  5  );
Table created.
--向cust表中插入資料
HFXF@xfpdb1 > insert into cust values(7,'ACER','SCOTT');
insert into cust values(7,'ACER','SCOTT')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'
HFXF@xfpdb1 > insert into cust values(5,'STARK','JIM');
insert into cust values(5,'STARK','JIM')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'
HFXF@xfpdb1 > insert into cust values(3,'GREY','BOB');
insert into cust values(3,'GREY','BOB')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'
HFXF@xfpdb1 > insert into cust values(11,'KAHN','BRAD');
insert into cust values(11,'KAHN','BRAD')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'
HFXF@xfpdb1 > insert into cust values(21,'DEAN','ANN');
insert into cust values(21,'DEAN','ANN')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'HFXF_TBS'
--hfxf使用者對錶空間HFXF_TBS的操作許可權不足，為使用者授權
HFXF@xfpdb1 > 
SYS@HFXFCDB > grant unlimited tablespace to hfxf;
Grant succeeded.
--在同一列建立多個索引
HFXF@xfpdb1 > create index cust_idx1 on cust(cust_id) tablespace hfxf_tbs_inx;
Index created.
HFXF@xfpdb1 > create index cust_idx2 on cust(cust_id) invisible tablespace hfxf_tbs_inx;
create index cust_idx2 on cust(cust_id) invisible tablespace hfxf_tbs_inx
                               *
ERROR at line 1:
ORA-01408: such column list already indexed
提示該列已經有索引
HFXF@xfpdb1 > create bitmap index cust_idx2 on cust(cust_id) invisible tablespace hfxf_tbs_inx;
Index created.
驗證不可見索引
HFXF@xfpdb1 > select index_name,status,visibility from user_indexes;
INDEX_NAME      STATUS   VISIBILIT
--------------- -------- ---------
CUST_IDX2       VALID    INVISIBLE
CUST_IDX1       VALID    VISIBLE
SYS@HFXFCDB > show parameter visible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
--透過企業自動跟蹤功能檢視執行計劃，驗證不可見索引是否在使用
HFXF@xfpdb1 > set autotrace trace explain
HFXF@xfpdb1 > select cust_id from cust where cust_id=3;
Execution Plan
----------------------------------------------------------
Plan hash value: 3472749082
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CUST_IDX1 |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("CUST_ID"=3)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
HFXF@xfpdb1 > 
HFXF@xfpdb1 > alter session set optimizer_use_invisible_indexes=true;
Session altered.
HFXF@xfpdb1 > select cust_id from cust where cust_id=3;
Execution Plan
----------------------------------------------------------
Plan hash value: 4102297476
--------------------------------------------------------------------------------
----------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT             |           |     1 |    13 |     1   (0)|
00:00:01 |
|   1 |  BITMAP CONVERSION TO ROWIDS |           |     1 |    13 |     1   (0)|
00:00:01 |
|*  2 |   BITMAP INDEX FAST FULL SCAN| CUST_IDX2 |       |       |            |
         |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUST_ID"=3)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
HFXF@xfpdb1 > 
--使用invisible和visible子句進行可見/不可見索引的切換
HFXF@xfpdb1 > alter index cust_idx1 invisible;
Index altered.
HFXF@xfpdb1 > alter index cust_idx2 visible;
Index altered.
--透過以下SQL查詢索引的不可見索引
HFXF@xfpdb1 > select index_name,status,visibility from user_indexes;
INDEX_NAME      STATUS   VISIBILIT
--------------- -------- ---------
CUST_IDX2       VALID    VISIBLE
CUST_IDX1       VALID    INVISIBLE

小結：透過 optimizer_use_invisible_indexes引數可以控制最佳化器是否選擇隱藏索引，預設值時false 即預設情況下最佳化器不選擇

不可見索引。不可見索引是預設情況讓最佳化器看不到該索引，和其他型別索引一樣在下hi下DML操作時，不可見索引會佔用空間和資源。

-the end-**粗體**