Oracle之虚拟索引

时间：2023-11-18 13:38:44

一、引言

DBA在日常维护管理数据库进行低性能SQL分析时，有时候需要通过创建索引对SQL进行优化，但有些时候我们创建的索引是否能用到？这个只能创建以后才能看出效果，但是在实际工作中，特别是对大表创建索引对系统性能有很大影响，因此我们不得不避开业务高峰时段，但是有没有一种办法创建索引而不影响性能呢？有，那就是虚拟索引。

虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。作用仅仅是为了DBA作SQL优化时使用，DBA根据虚拟索引的优化效果决定是否创建物理索引。

二、虚拟索引类型

虚拟索引支持B-TREE索引和BIT位图索引，在CBO模式下ORACLE优化器会考虑虚拟索引，但是在RBO模式下需要添加hint才行。

三、虚拟索引创建实例

SQL> drop table t purge;

表已删除。

SQL> create table t as select * from dba_objects;

表已创建。

--创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为true

SQL> alter session set "_use_nosegment_indexes"=true;

会话已更改。

--虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字

SQL> create index ix_t_id on t(object_id) nosegment;

索引已创建。

SQL> explain plan for select * from t where object_id=;

已解释。

SQL> set linesize

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value:

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

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|    | SELECT STATEMENT        |          |     |   |         ()| :: |

|    |  TABLE ACCESS BY INDEX ROWID| T       |     |   |         ()| :: |

|*   |   INDEX RANGE SCAN        | IX_T_ID |    |       |         ()| :: |

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

    - access("OBJECT_ID"=)

Note

-----

   - dynamic sampling used for this statement (level=)

已选择18行。

SQL> set autotrace traceonly

SQL> select * from t where object_id=;

未选定行

执行计划

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

Plan hash value: 

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

| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|    | SELECT STATEMENT        |          |     |   |         ()| :: |

|    |  TABLE ACCESS BY INDEX ROWID| T       |     |   |         ()| :: |

|*   |   INDEX RANGE SCAN        | IX_T_ID |    |       |         ()| :: |

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

Predicate Information (identified by operation id):

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

    - access("OBJECT_ID"=)

Note

-----

   - dynamic sampling used for this statement (level=)

统计信息

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

        recursive calls

        db block gets

         consistent gets

      physical reads

        redo size

         bytes sent via SQL*Net to client

      bytes received via SQL*Net from client

        SQL*Net roundtrips to/from client

        sorts (memory)

        sorts (disk)

        rows processed

SQL> set autotrace off

--以下看的是真实执行计划，显然是用不到索引。

SQL> alter session set statistics_level=all;

会话已更改。

SQL> select * from t where object_id=;

未选定行

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID    2qhwh0nzrzx2r, child number

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

select * from t where object_id=

Plan hash value: 

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

| Id  | Operation      | Name | Starts | E-Rows | A-Rows |    A-Time     | Buffers |

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

|    | SELECT STATEMENT  |     |     |       |       |::00.01 |     |

|*   |  TABLE ACCESS FULL| T     |     |     |       |::00.01 |     |

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

    - filter("OBJECT_ID"=)

Note

-----

   - dynamic sampling used for this statement (level=)

已选择22行。

--从数据字段中是无法找到这个索引的。

SQL> select index_name,status from user_indexes where table_name='T';

未选定行

四、虚拟索引的特点
    4.1、虚拟索引无法执行alter index选项

SQL> alter index IX_T_ID rebuild;

alter index IX_T_ID rebuild

*

第 1 行出现错误:

ORA-08114: 无法变更假索引

4.2、使用回收站特性的时候，虚拟索引必须显示drop，才能创建同名的索引。

SQL> create index ind_status on t(status);

索引已创建。

SQL> drop table t;

表已删除。

SQL> flashback table t to before drop;

闪回完成。

SQL> select table_name,index_name,status from user_indexes where table_name='T';

TABLE_NAME               INDEX_NAME              STATUS

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

T                   BIN$7jAFlUG6b1zgQAB/AQAPyw==$0 VALID

SQL> create index ind_object_id on t(object_id);

索引已创建。

SQL> create index inds_status on t(status);

create index inds_status on t(status)

                              *

第 1 行出现错误:

ORA-01408: 此列列表已索引

4.3、不能创建和虚拟索引同名的实际索引；
    4.4、可以创建和虚拟索引包含相同列但不同名的实际索引；

4.5、虚拟索引分析并且有效，但是数据字典里查不到结果。

相关文章