oracle性能调优-虚拟索引

一、引言

站在用户的角度思考问题，与客户深入沟通，找到九台网站设计与九台网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站建设、网站制作、企业官网、英文网站、手机端网站、网站推广、域名注册、网络空间、企业邮箱。业务覆盖九台地区。

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

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

二、虚拟索引类型

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

三、虚拟索引创建实例

SQL> create table scott.t as select * from dba_objects;

Table created.

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

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

Index created.

SQL> set autot traceonly

SQL> select * from scott.t where object_id=1;

no rows selected

Execution Plan

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

Plan hash value: 206018885

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

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

| 0 | SELECT STATEMENT | | 14 | 2898 | 5 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 2898 | 5 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

4 recursive calls

0 db block gets

1308 consistent gets

1239 physical reads

0 redo size

1343 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

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

SQL> set autot off

SQL> alter session set statistics_level=all;

Session altered.

SQL> select * from scott.t where object_id=1;

no rows selected

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

PLAN_TABLE_OUTPUT

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

SQL_ID 2qhwh0nzrzx2r, child number 1

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

select * from t where object_id=1

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.52 | 1242 | 1239 |

|* 1 | TABLE ACCESS FULL| T | 1 | 14 | 0 |00:00:00.52 | 1242 | 1239 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1)

Note

-----

- dynamic sampling used for this statement (level=2)

22 rows selected.

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

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

no rows selected

四、虚拟索引的特点

4.1、虚拟索引无法执行alter index选项

SQL> alter index scott.IX_T_ID rebuild;

alter index scott.IX_T_ID rebuild

ERROR at line 1:

ORA-08114: can not alter a fake index

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

SQL> drop table scott.t;

Table dropped.

SQL> flashback table scott.t to before drop;

Flashback complete.

SQL> create index scott.idx_t_id on scott.t(object_id) nosegment;

create index scott.idx_t_id on scott.t(object_id) nosegment

ERROR at line 1:

ORA-00955: name is already used by an existing object

oracle性能调优-虚拟索引

4.3、不能创建和虚拟索引同名的实际索引；

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

4.5、虚拟索引在数据字典里看不到

本文题目：oracle性能调优-虚拟索引
网页链接：http://scyanting.com/article/ihsjso.html

oracle性能调优-虚拟索引

其他资讯