Oracle效能最佳化之虛擬索引

Oracle效能最佳化之虛擬索引

虛擬索引是定義在資料字典中的虛擬索引，但沒有相關的索引段。虛擬索引的目的是類比索引的存--而不用真實的建立一個完整索引。這允許開發人員建立虛擬索引來查看相關執行計畫而不用等到真實建立完索引才能查看索引對執行計畫的影響，並且不會增加儲存空間的使用。如果我們觀察到最佳化器產生了一個昂貴的執行計畫並且SQL調整指導建議我們對某些的某列建立索引，但在生產資料庫環境中建立索引與測試並不總是可以操作。我們需要確保建立的索引將不會對資料庫中的其它查詢產生負面影響，因此可以使用虛擬索引。

下面舉例進行說明
1.建立一個測試表test
SQL> create table test as select * from dba_objects;

Table created.

2.從表test查詢object_name等於standard的記錄
SQL> select * from test where object_name='STANDARD';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED      LAST_DDL_TIM TIMESTAMP          STATUS  T G S
------------ ------------ ------------------- ------- - - -
SYS
STANDARD
                                      888                PACKAGE
19-APR-10    19-APR-10    2003-04-18:00:00:00 VALID  N N N

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED      LAST_DDL_TIM TIMESTAMP          STATUS  T G S
------------ ------------ ------------------- ------- - - -
SYS
STANDARD
                                      889                PACKAGE BODY
19-APR-10    19-APR-10    2010-04-19:10:22:58 VALID  N N N

3.查詢上面查詢的執行計畫
SQL> set autotrace traceonly explain
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    8 |  1416 |  155  (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |    8 |  1416 |  155  (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJECT_NAME"='STANDARD')

Note
-----
  - dynamic sampling used for this statement

 

4.在表test的object_name列上建立一個虛擬索引
SQL> create index test_index on test(object_name) nosegment;

Index created.

為了建立虛擬索引必須在create index語句中指定nosegment子句，並且不會建立索引段。

5.來驗證虛擬索引不會建立索引段
SQL> set autotrace off
SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX';

no rows selected

SQL> col OBJECT_NAME format a20;
SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
TEST_INDEX          INDEX

從上面的結果可以看到索引對象已經建立，但沒有建立索引段。

6.重新執行sql查看建立的虛擬索引是否被使用
SQL> set autotrace traceonly explain
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    8 |  1416 |  155  (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |    8 |  1416 |  155  (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJECT_NAME"='STANDARD')

Note
-----
  - dynamic sampling used for this statement

從上面的執行計畫可以清楚地看到建立的虛擬索引並沒有被使用

7.為了能使用所建立的虛擬索引，需要將_USE_NOSEGMENT_INDEXES設定為true
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.

8.重新執行sql查看建立的虛擬索引是否被使用
SQL> set long 900
SQL> set linesize 900
SQL> select * from test where object_name='STANDARD';

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |            |    8 |  1416 |    5  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| TEST      |    8 |  1416 |    5  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | TEST_INDEX |  238 |      |    1  (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OBJECT_NAME"='STANDARD')

Note
-----
  - dynamic sampling used for this statement

從上面的執行計畫可以看到當設定隱含參數_USE_NOSEGMENT_INDEXES後，最佳化器將會使用建立的虛擬索引。在使用虛擬索引需要注意，我們可以分析虛擬索引，但不能重建虛擬索引，如果重建虛擬索引會收到ORA-8114: "User attempted to alter a fake index"錯誤提示，可以刪除虛擬索引。