Oracle sql 调优：使用虚拟索引在生产环境测试创建索引对数据库性能的影响

dyok

虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员
可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试 SQL 语句的执行计划。如果优化器为某个
SQL 语句创建的执行计划代价很高，SQL tuning advisor suggest 可能会建议在某个列上创建索引，但是在生产环境下，我们是没法随意
来创建索引和测试这些更改的。我们需要确保要创建的索引不会对数据库中运行的其他查询的执行计划产生任何影响。虚拟索引的出现就
是为了解决这个问题的：

下面我们来做一个测试来介绍虚拟索引的用法

1)  创建示例表

SQL> create table test as select * from dba_objects;

2) 对该表执行任意的查询

16:43:55 system@PROD>  select * from test where object_name = 'EMP';

OWNER                          OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED         LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT                          EMP                                                      75315          75315 TABLE               2011-09-18 18:03:42 2013-03-10 17:07:42 2011-09-18:18:03:42 VALID   N N N      1

3) 查看上述查询的执行计划

16:44:31 system@PROD> set autotrace traceonly explain
16:44:42 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   293   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |    12 |  2484 |   293   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='EMP')


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



4)  在 test 表的 object_name 字段上面创建虚拟索引

16:45:44 system@PROD> create index test_index on test(object_name) nosegment;

Index created.

注意，在创建虚拟索引时需要在 CREATE INDEX 语句中指定 nosegment 子句，执行上述语句后，实际上数据库中
并未创建索引段，也就是并未给 test_index 对象分配存储空间，这点我们可以通过下面步骤来验证。

5)  通过 dba_objects 可以查看到刚刚创建的 test_index 对象

16:46:02 system@PROD> set autotrace off

16:50:16 system@PROD> col object_name for a20;
16:50:26 system@PROD> select object_name,object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
TEST_INDEX           INDEX

但是通过 dba_indexes、dba_segments 和 dba_extents 我们查看不到该对象

16:53:06 system@PROD> select index_name,index_type,table_name from dba_indexes where index_name = 'TEST_INDEX';

no rows selected

16:55:50 system@PROD> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name = 'TEST_INDEX';

no rows selected

16:56:46 system@PROD> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_extents where segment_name = 'TEST_INDEX';

no rows selected

通过上述的查询可以看出，数据库中创建了该对象，但未创建相应的 segment ，分配存储空间。

6)  再次查看之前 sql 的执行计划，看看是否使用了刚刚创建的虚拟索引

16:57:11 system@PROD> set autotrace traceonly explain
16:58:47 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   293   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TEST |    12 |  2484 |   293   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='EMP')

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

--我们看到创建虚拟索引后，执行计划并未改变

7)  我们需要修改数据库的隐含参数 _USE_NOSEGMENT_INDEXES 来强制session使用虚拟索引


17:01:24 system@PROD> alter session set "_USE_NOSEGMENT_INDEXES"=true;

Session altered.

8) 再次查看执行计划

17:02:06 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    12 |  2484 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST       |    12 |  2484 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_INDEX |   309 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


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


   2 - access("OBJECT_NAME"='EMP')


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

设置 _USE_NOSEGMENT_INDEXES 隐含参数后，优化器将使用在此表上创建的虚拟索引。在其他session中运行该查询时，不会使用
此虚拟索引，因为我们只是修改了 session 级别的隐含参数。


虚拟索引使用的注意事项：

1、可以对虚拟索引执行 analyze 操作

17:07:13 system@PROD> analyze index TEST_INDEX compute statistics;

Index analyzed.

2、无法对虚拟索引执行 rebuild 操作，否则会报 ORA-8114: "User attempted to alter a fake index" 错误

17:07:52 system@PROD> alter index TEST_INDEX rebuild;
alter index TEST_INDEX rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

3、可以像普通索引那样删除虚拟索引

17:19:20 system@PROD> drop index test_index;

Index dropped.

4、在Oracle 9.2 to 11.1 中利用 DBMS_METADATA.get_ddl 来获取虚拟索引的 DDL 脚本时，不会输出虚拟索引的 nosegment 子句
我个人测试的环境是 11.2.0.3.5 可以输出 nosegment 子句

17:13:46 system@PROD>  select dbms_metadata.get_ddl('INDEX','TEST_INDEX','SYSTEM') DDL from dual;


DDL
--------------------------------------------------------------------------------


  CREATE INDEX "SYSTEM"."TEST_INDEX" ON "SYSTEM"."TEST" ("O
BJECT_NAME")

  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOSEGMENT

zhangsanfeng88

勃起不是万能的，但不能勃起却是万万都不能的!

q2009a06z22

避孕的效果：不成功，便成“人”。

zjp0633

如果恐龙是人，那人是什么？

efn阿克说

爱护环境，人人有病。

crespo09

如果有一双眼睛陪我一同哭泣，就值得我为生命受苦。

zyh3033

鸳鸳相抱何时了，鸯在一边看热闹。