11g's new parameter OPTIMIZER_USE_INVISIBLE_INDEXES以及不可见索引-CSDN博客

本文链接：https://blog.csdn.net/yushangfu/article/details/6692265

本文探讨了在Oracle数据库中，如何通过调整索引的可见性来优化SQL执行计划，具体包括创建测试表、观察不同索引状态下的执行计划变化、以及通过参数控制索引可见性对性能的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

11G中出现了不可见索引，可以随时设置索引的可见与否，不可见索引仍然维护DML的变化。
1.创建测试表
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table a as select * from all_objects;

Table created.
SQL> desc a;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                 NOT NULL NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                   NOT NULL DATE
LAST_DDL_TIME                             NOT NULL DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NAMESPACE                                 NOT NULL NUMBER
EDITION_NAME                                       VARCHAR2(30)

2.无索引情况下的执行计划
SQL> set autotrace trace exp;
SQL> select * from a where object_id=789;

Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    11 | 1738 |   275   (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| A    |    11 | 1738 |   275   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=789)

Note
-----
- dynamic sampling used for this statement
3.创建不可见索引后的执行计划
SQL> create index a_id on a(object_id) invisible;

Index created.

SQL> set autotrace trace exp;
SQL> select * from a where object_id=789;

Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=789)

Note
-----
- dynamic sampling used for this statement
此时仍然是全表扫描，可见不可见索引没有被采纳
4.把不可见索引改为可见后的执行计划
SQL> alter index a_id visible;

Index altered.

SQL> select * from a where object_id=789;

Execution Plan
----------------------------------------------------------
Plan hash value: 3205579595

--------------------------------------------------------------------------------
----

--------------------------------------------------------------------------------
----

| 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:
01 |

| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 158 | 2 (0)| 00:00:
01 |

|* 2 | INDEX RANGE SCAN | A_ID | 1 | | 1 (0)| 00:00:
01 |

--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=789)

Note
-----
- dynamic sampling used for this statement
此时，执行中就采用了刚才的可见索引。
再次修改为不可见，再看执行计划
SQL> alter index a_id invisible;

Index altered.

SQL> select * from a where object_id=789;

Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=789)

Note
-----
- dynamic sampling used for this statement
此时，又采用的全表扫描
5.以上的这些都由一个参数optimizer_use_invisible_indexes来控制
默认的参数值为
SQL> show parameter optimizer_use_in

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
更改参数为TRUE，再来看执行计划，
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> set autotrace trace exp

SQL> select * from a where object_id=789;

Execution Plan
----------------------------------------------------------
Plan hash value: 3205579595

--------------------------------------------------------------------------------
----

--------------------------------------------------------------------------------
----

| 0 | SELECT STATEMENT | | 1 | 158 | 2 (0)| 00:00:
01 |

| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 158 | 2 (0)| 00:00:
01 |

|* 2 | INDEX RANGE SCAN | A_ID | 1 | | 1 (0)| 00:00:
01 |

--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=789)