Oracle Study---Oracle 11g 不可见索引案例
发布时间：2020-12-12 16:35:29 所属栏目：百科 来源：网络整理
导读：Oracle Study---Oracle 11g 不可见索引案例 Oracle 11g较之前的版本，推出了很多新功能，其中一项就是不可见索引（invisible index）。 从Oracle 11g开始，可以创建不可见索引（invisible index），优化器会忽略不可见的索引。 初始化参数optimizer_use_invi
|
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
11:53:47 SCOTT@ enmo> ALTER INDEX emp1_name_ind invisible; Index altered. Elapsed: 00:00:00.05
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.
Session altered. Elapsed: 00:00:00.00
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
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
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |