oracle 虚拟索引和不可见索引

1，虚拟索引-(Virtual index)

虚拟索引，顾名思义是不创建实际的段，目的在于，不耗时，耗CPU，耗IO，大量存储空间情况下创建索引，观察判断对SQL的优化效果

SQL> drop table t1;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> explain plan for select * from t1 where object_id<=50;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'BASIC +COST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

-----------------------------------------------
| Id  | Operation         | Name | Cost (%CPU)|
-----------------------------------------------
|   0 | SELECT STATEMENT  |      |   387   (1)|
|   1 |  TABLE ACCESS FULL| T1   |   387   (1)|
-----------------------------------------------

8 rows selected.

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL> create index index_v1 on t1(object_id) nosegment;

Index created.

SQL> explain plan for select * from t1 where object_id<=50;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'BASIC +COST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1628153867

---------------------------------------------------------------------
| Id  | Operation                           | Name     | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     3   (0)|
|   2 |   INDEX RANGE SCAN                  | INDEX_V1 |     2   (0)|
---------------------------------------------------------------------

9 rows selected.

其中_use_nosegment_indexes代表指示数据库可以在执行计划中使用这些索引；create index中的nosegment代表这个索引时虚拟的

2，不可见索引（invisible）

默认情况下，当我们创建索引后，数据库就会自动的识别到该索引，并开始在成本估算中纳入创建的索引，不可见索引可是控制优化器是否对索引进行可见；很有用的一种情况是如果你想在已经创建了索引的情况下，进行一些调整，去除一些不该用的索引

alter index emp_idx1 invisible|visible; -- 使索引可见不可见，当然也可以用在创建索引的时候

默认情况下是不可见索引对优化器不可见，当然可以调整系统或者会话参数进行修改

SQL> show parameter optimizer_use_invisible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

会话修改

SQL>  alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> show parameter optimizer_use_invisible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE