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-**粗体**
