当我们在对生产库做优化的时候，主要就是对SQL语句的优化，包括语句的等价改写等，但其中很大一部分情况，又与索引有关。如果能合理利用合适的索引，可以使原本走全表扫描产生的逻辑读大大降低，提高
数据库的性能。由于
Oracle数据库中的索引本身就要占用磁盘空间，维护索引需要一定的开销，如何才能知道创建某个索引，会给数据带来性能的提升，而又不至于判断失误，创建了一个不恰当的索引，最后又不得不删除呢？这种情况下，我们可以利用Oralce提供的虚拟索引，即nosegment索引，它并不占用磁盘资源，只是在数据字典中增加一个定义。它为DBA在创建索引对提升数据库性能的方面提供了一定的参考。下面来看具体测试和分析：
SQL> startup ORACLE instance started.
Total System Global Area 835104768 bytes Fixed Size 2232960 bytes Variable Size 675286400 bytes Database Buffers 155189248 bytes Redo Buffers 2396160 bytes Database mounted. Database opened.
--本测试在11.2.0.3.0环境，与10g略有不同 SQL> select * from v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
--创建测试表fakeind并插入数据 SQL> drop table fakeind_test; drop table fakeind_test * ERROR at line 1: ORA-00942: table or view does not exist
SQL> create table fakeind_test as select * from dba_objects;
Table created.
SQL> insert into fakeind_test select * from fakeind_test;
75540 rows created.
SQL> /
151080 rows created.
SQL> /
302160 rows created.
SQL> select count(*) from fakeind_test;
COUNT(*) ---------- 604320
--开始测试，执行查询 SQL> set line 130 pages 130
SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>44500 and object_id<45000);
3992 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 1190425891
------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3816 | 160K| 4667 (1)| 00:00:57 | |* 1 | HASH JOIN RIGHT SEMI| | 3816 | 160K| 4667 (1)| 00:00:57 | | 2 | VIEW | VW_NSO_1 | 3819 | 49647 | 2333 (1)| 00:00:28 | |* 3 | TABLE ACCESS FULL | FAKEIND_TEST | 3819 | 19095 | 2333 (1)| 00:00:28 | | 4 | TABLE ACCESS FULL | FAKEIND_TEST | 604K| 17M| 2331 (1)| 00:00:28 | -------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID") 3 - filter("OBJECT_ID">44500 AND "OBJECT_ID"<45000)
Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 17436 consistent gets 0 physical reads 0 redo size 144488 bytes sent via SQL*Net to client 3445 bytes received via SQL*Net from client 268 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 3992 rows processed
可以看到，用CTAS创建的测试表fakeind上目前并没有索引，因此在生成的执行计划中，该条SQL语句只能走全表扫描
--创建虚拟索引(在普通创建索引命令后加一个nosegmnet即可) SQL> create index ind_fake_id on fakeind_test(object_id) nosegment;
Index created.
--设置隐含参数使虚拟索引生效 SQL> alter session set "_use_nosegment_indexes"=true; --注意必须要写双引号，单引号不行
Session altered.
SQL> set autot off
--查看表是否被分析过 SQL> select table_name,last_analyzed from dba_tables where table_name='FAKEIND_TEST';
TABLE_NAME LAST_ANALYZED ------------------------------ ------------------ FAKEIND_TEST
--收集测试表的统计信息 SQL> exec dbm |