Oracle之虛擬索引

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"錯誤提示，可以刪除虛擬索引。

傳統的效能優化和調整工作，大都是在系統上線之後，由運維團隊進行的。當系統資料量積累到一定程度之後，原有一些隱藏的問題就不斷出現。所以，在大資料量、應急場景下進行SQL調優，往往是運維團隊經常遇到的問題。

新增索引是我們經常使用的效能優化手段。在遇到問題的時候，試一試新增索引，看看能不能改變執行計劃，是我們分析和解決問題的過程手段。但是對於大資料表情況下，快速的建立索引是比較困難的事情。這個時候，我們可以利用Oracle的virtual index技術。

1、環境介紹和資料準備

Virtual Index出現的很早。筆者從9i時候的文件資料中，就可以看到virtual index的技術材料。我們還是選擇Oracle 11gR2進行試驗。

SQL> select * from v$version;

BANNER

----------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0  Production

我們建立資料表T作為實驗物件，同時建立正常Index和虛擬Index。

SQL> show user;

User is "scott"

SQL> create table t as select * from dba_objects;

Table created

SQL> set timing on;

--建立一個普通索引

SQL> create index idx_t_owner on t(owner);

Index created

Executed in 0.687 seconds

SQL> select count(*) from t;

  COUNT(*)

----------

     72792

Executed in 0.015 seconds

我們建立virtual index，需要使用nosegment關鍵字。

SQL> create index idx_t_obj on t(object_id) nosegment;

Index created

Executed in 0.047 seconds

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

Executed in 1.716 seconds

此處我們需要注意一個細節，同樣是在7萬多基礎資料上面建立索引。nosegment虛擬索引使用的時間很短。

2、資料字典層面看virtual index

我們建立了虛擬索引idx_t_obj，又建立了作為參照的idx_t_owner。下面可以從資料字典的層面，去看看虛擬索引的內容資訊。

Oracle所有索引資訊都記錄在dba_indexes檢視中。

SQL> select index_name, index_type from dba_indexes where wner='SCOTT' and table_name='T';

INDEX_NAME                     INDEX_TYPE

------------------------------ ---------------------------

IDX_T_OWNER                    NORMAL

Executed in 0.031 seconds

SQL> select segment_name from dba_segments where wner='SCOTT' and segment_name in ('IDX_T_OWNER','IDX_T_OBJ');

SEGMENT_NAME

--------------------------------------------------------------------

IDX_T_OWNER

Executed in 0.062 seconds

我們從dba_indexes和dba_segments中，都只能看到普通索引idx_t_owner的資訊。而建立的虛擬索引idx_t_obj沒有蹤跡。nosegment選項可以讓我們猜測是沒有索引段物件的建立過程。但是，作為字典的dba_indexes資訊沒有，就讓人疑惑。

驗證我們的想法，使用dbms_metadata.get_ddl方法，抽取到資料表t的字典定義。其中，我們看到了idx_t_obj的資訊。

  CREATE INDEX "SCOTT"."IDX_T_OBJ" ON "SCOTT"."T" ("OBJECT_ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOSEGMENT ;

  CREATE INDEX "SCOTT"."IDX_T_OWNER" ON "SCOTT"."T" ("OWNER")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

相對於idx_t_owner，虛擬索引的定義全文顯得很簡單，只有nosegment很顯眼。

那麼，作為萬物彙總的dba_objects中呢？

SQL> select owner,object_name, object_id, data_object_id, object_type from dba_objects where object_name in ('IDX_T_OWNER','IDX_T_OBJ');

OWNER OBJECT_NAME      OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

----- --------------- ---------- -------------- -------------------

SCOTT IDX_T_OWNER          78019          78019 INDEX

SCOTT IDX_T_OBJ            78020          78020 INDEX

Executed in 0.047 seconds

在dba_objects中，我們找到idx_t_obj的資訊，它依然被認為是一個索引。更重要的是，我們定位到了object_id和data_object_id，這兩個分別為資料庫物件的邏輯id和物理段id。

dba_indexes字典檢視的基礎資料表是ind$基表。其中定義了所有索引物件的資訊。我們藉助object_id去檢查，發現了無法查詢到的idx_t_obj物件記錄。

SQL> select obj#, ts#, file#, block#, bo# from ind$ where obj# in (78019, 78020);

      OBJ#        TS#      FILE#     BLOCK#        BO#

---------- ---------- ---------- ---------- ----------

     78019          4          4       1586      78017

     78020          4          0          0      78017

Executed in 0.015 seconds

SQL> select owner, object_name from dba_objects where object_id=78017;

OWNER OBJECT_NAME

----- ---------------

SCOTT T

Executed in 0.016 seconds

我們可以從bo#編號，確定的確是資料表scott.t的索引物件。那麼，我們思考一個問題，既然ind$中存在對應記錄，為什麼dba_indexes不能檢索到這個資訊呢？

通過抽取dba_indexes的原始碼資訊，我們可以猜到端倪。

from sys.ts$ ts, sys.seg$ s,

     sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o,

     sys.user$ itu, sys.obj$ ito, sys.deferred_stg$ ds

where u.user# = o.owner#

  and o.obj# = i.obj#

  and i.bo# = io.obj#

  and io.owner# = iu.user#

  and bitand(i.flags, 4096) = 0

  and bitand(o.flags, 128) = 0

  and i.ts# = ts.ts# (+)

  and i.file# = s.file# (+)

  and i.block# = s.block# (+)

  and i.ts# = s.ts# (+)

  and i.obj# = ds.obj# (+)

  and i.indmethod# = ito.obj# (+)

  and ito.owner# = itu.user# (+);

雖然虛擬索引是沒有段的，在seg$中必然沒有對應記錄。但是SQL語句中對於這個條件定義的是外連線。也就是說，即使沒有段結構，索引也能顯示出來。

疑點就落在對一些列flag標記的bitand操作上了。我們檢查一下ind$的基礎flags取值，就可以知道原因了。

SQL> select obj#, bitand(flags, 4096) from ind$ where obj# in (78019, 78020);

      OBJ# BITAND(FLAGS,4096)

---------- ------------------

     78019                  0

     78020               4096

Executed in 0.016 seconds

看來，雖然ind$中包括資訊，但是不顯示出來，也是Oracle的一個本意。

下面我們繼續來看virtual index的實際工作效果。