s_stats.gather_table_stats(ownname=>'SYS',tabname=>'FAKEIND_TEST',degree=>4,estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
--再次确认表的分析情况 SQL> select table_name,last_analyzed from dba_tables where table_name='FAKEIND_TEST';
TABLE_NAME LAST_ANALYZED ------------------------------ ------------------ FAKEIND_TEST 17-SEP-14
--再次查询测试表 SQL> set autot trace 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: 2531911586
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3904 | 308K| 12 (17)| 00:00:01 | | 1 | VIEW | VM_NWVW_2 | 3904 | 308K| 12 (17)| 00:00:01 | | 2 | HASH UNIQUE | | 3904 | 179K| 12 (17)| 00:00:01 | |* 3 | HASH JOIN | | 3904 | 179K| 11 (10)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_FAKE_ID | 3819 | 19095 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| FAKEIND_TEST | 3819 | 156K| 8 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IND_FAKE_ID | 3819 | | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"="OBJECT_ID") 4 - access("OBJECT_ID">44500 AND "OBJECT_ID"<45000) 6 - access("OBJECT_ID">44500 AND "OBJECT_ID"<45000)
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17418 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 1 sorts (memory) 0 sorts (disk) 3992 rows processed
SQL> set autot off
此时利用虚拟索引获得的执行计划中，COST从之前的4000多降低到12，执行时间也从57s到1s，由此可以判断，当加上真实索引后，性能会大大提高。
--创建真实索引
SQL> create index ind_real_id on fakeind_test(object_id);
Index created.
SQL> set autot trace 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: 2531911586
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 41816 | 3307K| | 548 (1)| 00:00:07 | | 1 | VIEW | VM_NWVW_2 | 41816 | 3307K| | 548 (1)| 00:00:07 | | 2 | HASH UNIQUE | | 41816 | 1919K| 2472K| 548 (1)| 00:00:07 | |* 3 | HASH JOIN | | 41816 | 1919K| | 53 (2)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_FAKE_ID | 34375 | 167K| | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| FAKEIND_TEST | 34375 | 1409K| | 49 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IND_FAKE_ID | 34375 | | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ------------------------ |