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

骑猪看日落 2022-07-12 11:47 157阅读 0赞

**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> ALTER INDEX emp1\_name\_ind invisible;  
Index altered.

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  
\--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------  
|   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 id):  
\---------------------------------------------------

1 - filter("ENAME"='SCOTT')

Statistics  
\----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
          4  consistent gets  
          0  physical reads  
          0  redo size  
        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 alter session to scott;

Grant succeeded.

11:56:34 SCOTT@ enmo> alter session set optimizer\_use\_invisible\_indexes=true;  
Session altered.

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  
\---------------------------------------------------------------------------------------------  
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
\---------------------------------------------------------------------------------------------  
|   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 id):  
\---------------------------------------------------

2 - access("ENAME"='SCOTT')

Statistics  
\----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
          3  consistent gets  
          0  physical reads  
          0  redo size  
        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> alter index emp1\_name\_ind visible;  
Index altered.

Elapsed: 00:00:00.06

12:00:46 SCOTT@ enmo> alter session set optimizer\_use\_invisible\_indexes=false;  
Session altered.

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  
\---------------------------------------------------------------------------------------------  
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
\---------------------------------------------------------------------------------------------  
|   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 id):  
\---------------------------------------------------  
   2 - access("ENAME"='SCOTT')  
  
Statistics  
\----------------------------------------------------------  
        189  recursive calls  
          0  db block gets  
         26  consistent gets  
          0  physical reads  
          0  redo size  
        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> ALTER INDEX emp1\_name\_ind invisible;  
Index altered.  
  
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  
\--------------------------------------------------------------------------  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
\--------------------------------------------------------------------------  
|   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 id):  
\---------------------------------------------------  
   1 - filter("ENAME"='SCOTT')  
  
Statistics  
\----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
          4  consistent gets  
          0  physical reads  
          0  redo size  
        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