阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

185次阅读
没有评论

共计 7183 个字符,预计需要花费 18 分钟才能阅读完成。

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。

Oracle 11g 新特性 — Invisible Indexes(不可见的索引) 说明 http://www.linuxidc.com/Linux/2012-12/76008.htm

与不可用索引不同,不可见索引在使用 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

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-02/140996.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7183字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中