---------------------------
3 - access("OBJECT_ID"="OBJECT_ID") 4 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000) 6 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 11017 consistent gets 82 physical reads 0 redo size 1293055 bytes sent via SQL*Net to client 26908 bytes received via SQL*Net from client 2401 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 35992 rows processed
虽然创建了真实索引，但数据库却仍旧在用虚拟索引，此时COST和TIME反而还上去了一点，那么需要先禁用虚拟索引
SQL> alter session set "_use_segment_indexes"=false;
--禁用虚拟索引后继续查看刚才的SQL SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>45500 and object_id<50000);
35992 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 750753197
------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 34375 | 1443K| 2414 (1)| 00:00:29 | |* 1 | HASH JOIN RIGHT SEMI| | 34375 | 1443K| 2414 (1)| 00:00:29 | | 2 | VIEW | VW_NSO_1 | 34375 | 436K| 79 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_REAL_ID | 34375 | 167K| 79 (0)| 00:00:01 | | 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 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11017 consistent gets 0 physical reads 0 redo size 1293055 bytes sent via SQL*Net to client 26908 bytes received via SQL*Net from client 2401 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 35992 rows processed
虽然使用真实索引之后，性能提升并不如使用虚拟索引时那样多，但至少比最初没有索引的情况下，要快了将近28秒，COST也减少了将近一半，当真实索引建立完毕以后，可以对虚拟索引进行删除，以免白白占用一个对象名，删除语法和删除普通索引一致。
虚拟索引有几个要注意的地方：
--虚拟索引并不存在于dba_indexes视图 SQL> select index_name from dba_indexes where index_name='IND_FAKE_ID';
no rows selected
--无法创建与虚拟索引同名的真实索引 SQL> create index ind_fake_id on fakeind_test(object_name); create index ind_fake_id on fakeind_test(object_name) * ERROR at line 1: ORA-00955: name is already used by an existing object
--无法使用alter命令来修改或重建索引 SQL> alter index ind_fake_id rename to ind_fake_name; alter index ind_fake_id rename to ind_fake_name * ERROR at line 1: ORA-08114: can not alter a fake index
SQL> alter index ind_fake_id rebuild; alter index ind_fake_id rebuild * ERROR at line 1: ORA-08114: can not alter a fake index
--查看虚拟索引的方法 SQL> set autot off SQL> SELECT index_owner, index_name 2 FROM dba_ind_columns 3 WHERE index_name NOT LIKE 'BIN$%' 4 MINUS 5 SELECT owner, index_name 6 FROM dba_indexes;
INDEX_OWNER INDEX_NAME ------------------------------ ------------------------------ SYS IND_FAKE_ID
总结：
这个测试其实并没有做的很完善，SQL语句选取得不好，正常情况下，原有语句所涉及的表至少且肯定会有一个主键索引，没有索引的表在OLTP生产库中是不太现实的。本文主要是起到一个抛砖引玉的作用，当我们面对一个数据库优化的场景，需要测试创建某个特定条件的索引是否会给
系统带来性能提升，就可以借助虚拟索引来测试，因为在生产库中通常不允许随意创建索引，而维护索引对数据库而言也是一笔不小的开销，况且如果索引创建不当，可能使数据库性能更糟糕。
|