Oracle sql 调优：应用虚拟索引在生产环境测试创建索引对数据库性能的影响
日期：2014-05-16 浏览次数：20461 次
Oracle sql 调优：使用虚拟索引在生产环境测试创建索引对数据库性能的影响
虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员
可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试 SQL 语句的执行计划。如果优化器为某个
SQL 语句创建的执行计划代价很高，SQL tuning advisor 可能会建议在某个列上创建索引，但是在生产环境下，我们是没法随意
来创建索引和测试这些更改的。我们需要确保要创建的索引不会对数据库中运行的其他查询的执行计划产生任何影响。虚拟索引的出现就
是为了解决这个问题的：
下面我们来做一个测试来介绍虚拟索引的用法
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> selec
虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员
可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试 SQL 语句的执行计划。如果优化器为某个
SQL 语句创建的执行计划代价很高，SQL tuning advisor 可能会建议在某个列上创建索引，但是在生产环境下，我们是没法随意
来创建索引和测试这些更改的。我们需要确保要创建的索引不会对数据库中运行的其他查询的执行计划产生任何影响。虚拟索引的出现就
是为了解决这个问题的：
下面我们来做一个测试来介绍虚拟索引的用法
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> selec
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
