【SQL】不可见索引INVISIBLE index

目录

定义

作用

测试

---

定义

不可见索引可以被oracle优化器所忽略，受system或者session级别的OPTIMIZER_USE_INVISIBLE_INDEXES 参数影响，若该参数为false则默认不识别不可见索引，true则反之

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.

To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views.

 

作用

可以在删除索引之前测试是否会产生影响

对于当前会话使用临时的索引结构，这样可不影响整体

可以在dba_indexes、all_indexes、user_indexes视图的visibility字段来查看否为不可见（invisible）

 

测试

创建测试表

SQL> create table tb_invisible01 as select * from emp;   Table created.

创建一个invisible索引在empno字段

SQL> create index ind_tbinv01 on tb_invisible01(empno) invisible;   Index created.

查看索引定义

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,VISIBILITY from user_indexes where index_name='IND_TBINV01';   INDEX_NAME  INDEX_TYPE TABLE_OWNER TABLE_NAME     VISIBILITY ----------- ---------- ----------- -------------- ----------- IND_TBINV01 NORMAL     SCOTT       TB_INVISIBLE01 INVISIBLE

查看OPTIMIZER_USE_INVISIBLE_INDEXES参数状态

NAME                            TYPE       VALUE ------------------------------- ---------- ---------- optimizer_use_invisible_indexes boolean    FALSE

查看执行计划

SQL> explain plan for select * from TB_INVISIBLE01 where empno=7788;   Explained.   SQL> select * from table(dbms_xplan.display());   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1142811946   ------------------------------------------------------------------------------------ | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |                |     1 |    38 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_INVISIBLE01 |     1 |    38 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("EMPNO"=7788)   13 rows selected.

SQL> explain plan for select /*+index(TB_INVISIBLE01 IND_TBINV01)*/* from TB_INVISIBLE01 where empno=7788;   Explained.   SQL> select * from table(dbms_xplan.display());   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1142811946   ------------------------------------------------------------------------------------ | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |                |     1 |    38 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB_INVISIBLE01 |     1 |    38 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("EMPNO"=7788)   13 rows selected.

可以看出无论如何都没有走索引，即便是加了hint，结果都是table access full

将索引修改成visible

SQL> alter index IND_TBINV01 visible;   Index altered.

继续测试

SQL>  explain plan for select * from TB_INVISIBLE01 where empno=7788;   Explained.   SQL> select * from table(dbms_xplan.display());   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1034350258   ------------------------------------------------------------------------------------------------------ | Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                    |                |     1 |    38 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TB_INVISIBLE01 |     1 |    38 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IND_TBINV01    |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("EMPNO"=7788)   14 rows selected.

由此可见该查询已经用了索引

 

将索引改回不可见

SQL> alter index IND_TBINV01 invisible;   Index altered.

将会话OPTIMIZER_USE_INVISIBLE_INDEXES参数改为true

SQL>  explain plan for select * from TB_INVISIBLE01 where empno=7788;   Explained.   SQL> select * from table(dbms_xplan.display());   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1034350258   ------------------------------------------------------------------------------------------------------ | Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                    |                |     1 |    38 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TB_INVISIBLE01 |     1 |    38 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IND_TBINV01    |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("EMPNO"=7788)

可见虽然索引为不可见，但依然可以走该索引

 

关于索引的介绍参见https://blog.csdn.net/u012778985/article/details/112314991