ITPub博客

首页 > 数据库 > Oracle > oracle性能调优-虚拟索引

oracle性能调优-虚拟索引

Oracle 作者:skzhuga 时间:2018-12-12 13:45:03 0 删除 编辑

一、引言

    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

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

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

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

|   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

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

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

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

|   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

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

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

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31544156/viewspace-2284938/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-06-28

  • 博文量
    25
  • 访问量
    36458