博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5753337.html
oracle 11g虚拟索引使用示例
虚拟索引(VIrtual Indexes)特性
1.虚拟索引(VIrtual Indexes)在drop前永久存在；
2.虚拟索引的创建不会影响当前和新会话，只对在会话设置了使用虚拟索引才会有效。
3.只有在设置了”_use_nosegment_indexes”为true时，才会使用索引;
4.虚拟索引不适用于基本于规则(RBO)的优化器，只在基于成本(CBO)的优化器才有效。
5.虚拟索引在dba_segments中无记录，在dba_indexes和dba_objects中可以看到。
6.虚拟索引不能alter,否则会报”fake index”
7.虚拟索引可以用anaylyze命令和dbms_stats包进行统计分析，但不显示统计信息，只有在使用的地方才能看到统计信息。
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
SQL> DROP TABLE ora_vidx_tbl PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE ora_vidx_tbl
2 (ID NUMBER,NAME VARCHAR2(30));
Table created.
SQL> -- 创建虚拟索引
SQL> CREATE UNIQUE INDEX ora_vidx_v1 ON ora_vidx_tbl(ID) NOSEGMENT;
Index created.
SQL>
SQL> INSERT INTO ora_vidx_tbl
2 SELECT ROWNUM ID,DBMS_RANDOM.STRING('X',20)
3 FROM DUAL
4 CONNECT BY LEVEL <=1000
5 ORDER BY DBMS_RANDOM.VALUE;
1000 rows created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
--oracle 11g对虚拟索引进行统计分析,还可以用analyze
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(USER,'ora_vidx_v1');
PL/SQL procedure successfully completed.
SQL>
--oracle 11g dba_segments看不到虚拟索引的记录
SQL> SELECT DS.SEGMENT_NAME,DS.OWNER,DS.TABLESPACE_NAME
2 FROM DBA_SEGMENTS DS
3 WHERE DS.SEGMENT_NAME='ora_vidx_v1'
4 AND DS.OWNER=USER;
no rows selected
SQL>
--oracle 11g dba_indexes看不到虚拟索引的记录
SQL> SELECT DI.INDEX_NAME,DI.OWNER,DI.INDEX_TYPE
2 FROM DBA_INDEXES DI
3 WHERE DI.INDEX_NAME = 'ora_vidx_v1'
4 AND DI.OWNER = USER;
no rows selected
SQL>
--oracle 11g dba_objects能看到虚拟索引的记录
SQL> SELECT DO.OBJECT_NAME,
2 DO.OBJECT_TYPE
3 FROM DBA_OBJECTS DO
4 WHERE DO.OBJECT_NAME = 'ora_vidx_v1';
OBJECT_NAME OBJECT_TYPE
------------------------- -------------------
ora_vidx_v1 INDEX
SQL>
SQL>
--oracle 11g 没有明确设置使用虚拟索引，是无法使用虚拟索引的(包括加hint)。
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT * FROM ora_vidx_tbl
2 WHERE ID=1;
ID NAME
---------- ------------------------------
1 CITD9VBGKI2YCUQM53I0
Execution Plan
----------------------------------------------------------
Plan hash value: 2020030371
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ora_vidx_tbl | 1 | 30 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
SQL> SELECT /*+ INDEX(T ora_vidx_V1)*/
2 T.ID
3 FROM ora_vidx_tbl T
4 WHERE ID = 1;
ID
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2020030371
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ora_vidx_tbl | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
--oracle 11g 设置_use_nosegment_indexes为TRUE，使用虚拟索引
SQL> ALTER SESSION SET "_use_nosegment_indexes"=TRUE;
Session altered.
SQL> SELECT *
2 FROM ora_vidx_tbl
3 WHERE ID = 90;
ID NAME
---------- ------------------------------
90 R20NLTO6R1B2H6QWL4XW
Execution Plan
----------------------------------------------------------
Plan hash value: 1883681978
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes |Cost (%CPU)|Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ora_vidx_tbl | 1 | 30 | 2 (0)|00:00:01 |
|* 2 | INDEX UNIQUE SCAN | ora_vidx_v1 | 1 | | 1 (0)|00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=90)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> ALTER SESSION SET "_use_nosegment_indexes"=false;
Session altered.
SQL>
--oracle 11g 不能对虚拟索引进行rebuild等操作
SQL> ALTER INDEX ora_vidx_v1 REBUILD;
ALTER INDEX ora_vidx_v1 REBUILD
*
ERROR at line 1:
ORA-08114: can not alter a fake index
SQL>
--oracle 11g 可以创建和虚拟索引列一致的索引，但必须名称不一样
SQL> CREATE INDEX ora_vidx_tbl_n1 ON ora_vidx_tbl(ID);
Index created.
最后特别说明二点：
1.无论是在oracle 9i、10g、11g，虚拟索引对唯一约束检查不生效，即重复的记录也是可以插入的；
2.虚拟索引在oracle官方文档未明确说明，只作测试使用。
--the end
阅读(1362) | 评论(0) | 转发(0) |
