Oracle虛擬索引的運用，oracle虛擬索引

Oracle虛擬索引的運用，oracle虛擬索引

  在做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: 無法改變假索引

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

oracle 索引建立後怎使用

建立索引不是為了在sql語句中用的，而是可以大大提高系統的效能。
第一，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二，可以大大加快 資料的檢索速度，這也是建立索引的最主要的原因。
第三，可以加速表和表之間的串連，特別是在實現資料的參考完整性方面特別有意義。
第四，在使用分組和排序 子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，通過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。
 
oracle 索引使用 不要概念! 要可以執行的語句

如果你的sql語句適合用這條索引的話，oracle的CBO在執行sql語句的時候就會使用index的方式，你沒必要自己限制它使用，認為的強制改變oracle的執行計畫對oracle的效率很危險，當然你要是一定要在你的sql使用，那麼就在sql添加Hint,具體sql語句為：
select /*+ index（emp non_deptno）*/ * from emp
強烈建議提問者不要這樣寫，原因上面已經說了。