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

大佬教程收集整理的这篇文章主要介绍了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

大佬总结

以上是大佬教程为你收集整理的Oracle Study---Oracle 11g 不可见索引案例全部内容，希望文章能够帮你解决Oracle Study---Oracle 11g 不可见索引案例所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。