Oracle创建虚拟索引

我们在建索引的时候，需要测试建立索引后，SQL是否走索引，如果表特别大的话，建立索引会耗费大量时间，我们可以建立虚拟索引，查看相关的执行计划。
需要在会话级设置隐含参数_use_nosegment_indexes为true，EXPLAIN查看相关执行计划。
[code]C:\\Documents and Settings\\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 24 16:52:08 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn awen/oracle
已连接。
SQL> create table vt_ind_test as select * from dba_objects;

表已创建。

SQL> select count(*) from vt_ind_test;

COUNT(*)
----------
50602

SQL> desc vt_ind_test;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> alter session set "_use_nosegment_indexes"=TRUE;

会话已更改。

SQL> show parameter nosegment

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_use_nosegment_indexes boolean TRUE

SQL> create index vt_ind_obj_id on vt_ind_test(object_id) nosegment;

索引已创建。

SQL> select index_name,index_type from dba_indexes where table_name=upper('vt_ind_test');

未选定行

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

已解释。

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 4011367164

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| VT_IND_TEST | 8 | 1416 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | VT_IND_OBJ_ID | 147 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

2 - access("OBJECT_ID"=1000)

Note
-----
- dynamic sampling used for this statement

已选择18行。[/code]
—注意该参数是会话级参数
[code]SQL> alter system set "_use_nosegment_indexes"=true;
alter system set "_use_nosegment_indexes"=true
*
第 1 行出现错误:
ORA-02096: 此选项的指定初始化参数不可修改[/code]
而且虚拟索引对其他的session无效
[code]C:\\Documents and Settings\\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 12月 24 17:01:52 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn awen /oracle
已连接。
SQL> explain plan for select * from vt_ind_test where object_id=1000;

已解释。

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2123976704

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1416 | 161 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| VT_IND_TEST | 8 | 1416 | 161 (2)| 00:00:02 |
---------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

1 - filter("OBJECT_ID"=1000)

Note
-----
- dynamic sampling used for this statement

已选择17行。

SQL> [/code]