Oracle Study---Oracle 11g 不可见索引案例

liuyuehua

　　Oracle Study---Oracle 11g 不可见索引案例
　　Oracle 11g较之前的版本，推出了很多新功能，其中一项就是不可见索引（invisible index）。
　　从Oracle 11g开始，可以创建不可见索引（invisible index），优化器会忽略不可见的索引。
　　初始化参数optimizer_use_invisible_indexes决定是否使用invisible index，其默认值为false，即默认不使用invisible index。但如果在session级别或者system级别上将optimizer_use_invisible_indexes初始化参数设置为true，那么就可以使用invisible index。
　　与不可用索引不同，不可见索引在使用DML语句期间仍会得到维护。
　　Oracle引入不可见索引是有用途的，使索引不可见是使索引不可用或者删除索引的一种替代办法。使用不可见的索引，可以完成如下操作：
　　1、在删除索引之前测试对索引的删除，是否会产生影响。
　　2、对应用程序的特定操作或模块是使用临时索引结构，这样就不会影响整个应用程序。
　　当索引不可见时，优化器生成的执行计划不会使用该索引。删除索引时，可以先将索引修改为invisible，如果未发生性能下降的问题，则可以删除该索引。在表上新建索引时，可以先创建一个最初不可见的索引，然后执行测试，看索引的效率怎么样，最后确定是否使该索引可见，是否使用该索引。
　　可以查看dba_indexes、all_indexes、user_indexes视图的visibility字段来确定该索引是visible还是invisible。
　　1、创建索引
　　11:47:59 SCOTT@ enmo> create index emp1_name_ind on emp1(ename) tablespace indx;
　　Index created.
　　Elapsed: 00:00:00.07
　　11:50:13 SCOTT@ enmo> exec dbms_stats.gather_table_stats(user,'emp1',cascade=>true);
　　PL/SQL procedure successfully completed.
　　Elapsed: 00:00:00.08
　　2、设置索引不可见

　　11:53:47 SCOTT@ enmo>>
　　Index>　　Elapsed: 00:00:00.05
　　3、在执行查询时，优化器没有选择索引
　　11:56:17 SCOTT@ enmo> SELECT * FROM EMP1 WHERE ENAME='SCOTT';
　　EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
　　---------- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
　　Elapsed: 00:00:00.00
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2226897347
　　--------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
　　|*  1 |  TABLE ACCESS FULL| EMP1 |     1 |    38 |     3   (0)| 00:00:01 |
　　--------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - filter("ENAME"='SCOTT')
　　Statistics
　　----------------------------------------------------------
　　1  recursive calls
　　0  db block gets
　　4  consistent gets
　　0  physical reads

　　0  redo>　　863  bytes sent via SQL*Net to client
　　415  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　4、设置优化参数

　　11:58:33 SYS@ enmo> grant>　　Grant succeeded.

　　11:56:34 SCOTT@ enmo>>
　　Session>　　Elapsed: 00:00:00.00
　　11:59:45 SCOTT@ enmo> SELECT * FROM EMP1 WHERE ENAME='SCOTT';
　　EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
　　---------- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
　　Elapsed: 00:00:00.00
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 1963203073
　　---------------------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT            |               |     1 |    38 |     2   (0)| 00:00:01 |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1          |     1 |    38 |     2   (0)| 00:00:01 |
　　|*  2 |   INDEX RANGE SCAN          | EMP1_NAME_IND |     1 |       |     1   (0)| 00:00:01 |
　　---------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("ENAME"='SCOTT')
　　Statistics
　　----------------------------------------------------------
　　1  recursive calls
　　0  db block gets
　　3  consistent gets
　　0  physical reads

　　0  redo>　　867  bytes sent via SQL*Net to client
　　415  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　不可见索引被优化器启用
　　5、将索引变为可见，则优化器使用索引访问

　　11:59:56 SCOTT@ enmo>>
　　Index>　　Elapsed: 00:00:00.06

　　12:00:46 SCOTT@ enmo>>
　　Session>　　Elapsed: 00:00:00.00
　　12:00:58 SCOTT@ enmo> select * from emp1 where ename='SCOTT';
　　EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
　　---------- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
　　Elapsed: 00:00:00.01
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 1963203073
　　---------------------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT            |               |     1 |    38 |     2   (0)| 00:00:01 |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| EMP1          |     1 |    38 |     2   (0)| 00:00:01 |
　　|*  2 |   INDEX RANGE SCAN          | EMP1_NAME_IND |     1 |       |     1   (0)| 00:00:01 |
　　---------------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("ENAME"='SCOTT')
　　Statistics
　　----------------------------------------------------------
　　189  recursive calls
　　0  db block gets
　　26  consistent gets
　　0  physical reads

　　0  redo>　　867  bytes sent via SQL*Net to client
　　415  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　6  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　6、使用hints，不可见索引也不能被启用

　　12:01:10 SCOTT@ enmo>>
　　Index>　　12:02:49 SCOTT@ enmo> select table_name,index_name,leaf_blocks,status,VISIBILITY FROM USER_INDEXES
　　12:06:03   2  where table_name='EMP1';
　　TABLE_NAME                     INDEX_NAME                     LEAF_BLOCKS STATUS   VISIBILIT
　　------------------------------ ------------------------------ ----------- -------- ---------
　　EMP1                           EMP1_NAME_IND                            1 VALID    INVISIBLE
　　12:26:11 SCOTT@ enmo>  select /*+ index(emp1 emp1_name_ind) */ * from emp1 where ename='SCOTT'
　　EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
　　---------- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
　　Elapsed: 00:00:00.01
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2226897347
　　--------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
　　|*  1 |  TABLE ACCESS FULL| EMP1 |     1 |    38 |     3   (0)| 00:00:01 |
　　--------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - filter("ENAME"='SCOTT')
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　4  consistent gets
　　0  physical reads

　　0  redo>　　863  bytes sent via SQL*Net to client
　　415  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed