Oracle虛擬索引的運用

標籤：style   color   使用   os   io   strong   資料   for   

  在做SQL調優的時候，有的時候需要加一個索引，測試下對效能提升有沒有幫組，如果此時這張表非常大，建索引將會非常之麻煩，這種情境虛擬索引就該登場了。下面來做個實驗：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> drop table test purge;
SQL> create table test as select * from user_objects;

SQL> select count(1),
           count(distinct object_name) c_oname,
           count(distinct object_type) s_otype
      from test;
  COUNT(1)    C_ONAME    S_OTYPE
---------- ---------- ----------
     11500      11430         14

SQL> create index ind_test_name on test(object_name) nosegment;

SQL> create index ind_test_otype on test(object_type) nosegment;

SQL> exec dbms_stats.gather_table_stats(user,‘test‘,cascade=>true);

SQL> --必須設定隱含參數”_use_nosegment_indexes”=true（預設為false)後，CBO才能使用虛擬索引

SQL> alter session set "_use_nosegment_indexes"=true;

SQL> set autotrace trace exp

SQL> select * from test where object_name=‘TEST‘;

執行計畫
----------------------------------------------------------
Plan hash value: 3675505035
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    86 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |     1 |    86 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_NAME |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME"=‘TEST‘)

SQL> select * from test where object_type=‘TEST‘;
執行計畫
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   821 | 70606 |    36   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |   821 | 70606 |    36   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE"=‘TEST‘)

SQL> set autotrace off

SQL> select index_name from user_indexes s where s.table_name =‘TEST‘;
未選定行

SQL> col object_name format a15;
SQL> select object_name,object_id,object_type  from user_objects s
     where s.object_name in (‘IND_TEST_NAME‘, ‘IND_TEST_OTYPE‘);
OBJECT_NAME      OBJECT_ID OBJECT_TYPE
--------------- ---------- -------------------
IND_TEST_NAME       670666 INDEX
IND_TEST_OTYPE      670667 INDEX

SQL> select segment_name,s.bytes from user_segments s where s.segment_name
    in (‘IND_TEST_NAME‘, ‘IND_TEST_OTYPE‘);
未選定行

原理：加了虛擬索引後，同時開啟_use_nosegment_indexes，CBO在評估執行計畫的時候會把虛擬索引當做是一個真實的索引，有點欺騙CBO的意思。不能在user_indexes和user_segments中找到，但在user_objects可以找到。說明虛擬說只是加了資料字典，並沒有產生實際的資料，那它也應該不能像真實的索引哪有操作，再做一個實驗：

SQL> alter index ind_test_name rebuild;
alter index ind_test_name rebuild
*
第 1 行出現錯誤:
ORA-08114: 無法改變假索引

最後：虛擬索引只適合做效能調優，加了之後記得要刪除。