不可见索引和虚拟索引。 - 数据库编程

不可见索引和虚拟列上的索引。

今天是2014-01-20，今天开始学习其他类型的索引，在此记录一下学习笔记。
不可见索引：

默认情况下，当我们创建索引后，数据库就会自动的识别到该索引，并开始在成本估算中纳入创建的索引，不可见索引可是控制优化器是否对索引进行可见。那么不可见索引的用处就是，有时候当我们在创建一个索引往往对一个或是几个sql的执行存在性能提升，也许会对其他sql执行存在弊端，如果使用不可见索引，可以在优化器使用该索引和不使用该索引之间进行选择，从而便于我们决定索引的创建与否。其他在我们删除一个索引的时候一般步骤是将该索引至于不可见，随后运行将其至于不可用，最后删除索引。

创建不可见索引：

SQL> select index_name,index_TYPE FROM USER_INDEXES WHERE table_name='EMP';

no rows selected

SQL> create index emp_idx1 on emp(empno) invisible;

Index created.

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL>

在user_indexes 视图的visibilit字段可以观察该索引是否为不可见索引。

使当前索引在不可见索引和可见索引之间进行切换：

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL> alter index emp_idx1 visible;

Index altered.

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    VISIBLE

SQL> alter index emp_idx1 invisible;

Index altered.

SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1';

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL>

让优化器如何使用不可见索引？

一般在进行sql优化的时候常用到该功能，当我们发现一个sql中缺少相关索引，但是又不确定该索引创建后对sql到底有多大用处，那么可以使用该方法。注意：当索引被置为不可见的时候，即使使用hits优化器也是不会看到该索引的。

那么其中涉及到一个数据库动态参数：optimizer_use_invisible_indexes，该参数默认为fale，当将该参数改为true时，将对不可见索引进行使用，可以在会话级别和系统级别进行设定（一般我们在会话级别）。

演示过程如下：

eg:SQL> select index_name,index_type,status,visibility from user_indexes where INDEX_NAME='EMP_IDX1'
  2  ;

INDEX_NAME                     INDEX_TYPE                  STATUS   VISIBILIT
------------------------------ --------------------------- -------- ---------
EMP_IDX1                       NORMAL                      VALID    INVISIBLE

SQL> select * from emp where empno=7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SQL> set linesize 200
SQL> set autotrace trace exp
SQL> r
  1* select * from emp where empno=7902

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7902)

SQL> select /*+

不可见索引和虚拟索引。(一)

不可见索引和虚拟列上的索引。