Oracle中組合索引怎么用

這篇文章給大家分享的是有關Oracle中組合索引怎么用的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

Oracle中組合索引的使用

關鍵詞：組合索引 前導列  基于規則的優化器（RBO）  基于成本的優化器（CBO）  提示（hint）

在Oracle中可以創建組合索引，即同時包含兩個或兩個以上列的索引。在組合索引的使用方面，Oracle有以下特點：

1、   當使用基于規則的優化器（RBO）時，只有當組合索引的前導列出現在SQL語句的where子句中時，才會使用到該索引；

2、   在使用Oracle9i之前的基于成本的優化器（CBO）時， 只有當組合索引的前導列出現在SQL語句的where子句中時，才可能會使用到該索引，這取決于優化器計算的使用索引的成本和使用全表掃描的成本，Oracle會自動選擇成本低的訪問路徑（請見下面的測試1和測試2）；

3、   從Oracle9i起，Oracle引入了一種新的索引掃描方式——索引跳躍掃描（index skip scan），這種掃描方式只有基于成本的優化器（CBO）才能使用。這樣，當SQL語句的where子句中即使沒有組合索引的前導列，并且索引跳躍掃描的成本低于其他掃描方式的成本時，Oracle就會使用該方式掃描組合索引（請見下面的測試3）；

4、   Oracle優化器有時會做出錯誤的選擇，因為它再“聰明”，也不如我們SQL語句編寫人員更清楚表中數據的分布，在這種情況下，通過使用提示（hint），我們可以幫助Oracle優化器作出更好的選擇（請見下面的測試4）。

[@more@]

關于以上情況，我們分別測試如下：

我們創建測試表T，該表的數據來源于Oracle的數據字典表all_objects，表T的結構如下：

SQL> desc t

名稱                                      是否為空? 類型

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

OWNER                                     NOT NULL VARCHAR2(30)

OBJECT_NAME                               NOT NULL VARCHAR2(30)

SUBOBJECT_NAME                                     VARCHAR2(30)

OBJECT_ID                                 NOT NULL NUMBER

DATA_OBJECT_ID                                     NUMBER

OBJECT_TYPE                                        VARCHAR2(18)

CREATED                                   NOT NULL DATE

LAST_DDL_TIME                             NOT NULL DATE

TIMESTAMP                                          VARCHAR2(19)

STATUS                                             VARCHAR2(7)

TEMPORARY                                          VARCHAR2(1)

GENERATED                                          VARCHAR2(1)

SECONDARY                                          VARCHAR2(1)

表中的數據分布情況如下：

SQL> select object_type,count(*) from t group by object_type;

OBJECT_TYPE          COUNT(*)

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

CONSUMER GROUP             20

EVALUATION CONTEXT         10

FUNCTION                  360

INDEX                      69

LIBRARY                    20

LOB                        20

OPERATOR                   20

PACKAGE                  1210

PROCEDURE                 130

SYNONYM                 16100

TABLE                     180

TYPE                     2750

VIEW                     8600

已選擇13行。

SQL> select count(*) from t;

 COUNT(*)

----------

    29489

我們在表T上創建如下索引并對其進行分析：

SQL> create index indx_t on t(object_type,object_name);

索引已創建。

SQL> ANALYZE TABLE T COMPUTE STATISTICS

 2    FOR TABLE

 3    FOR ALL INDEXES

 4    FOR ALL INDEXED COLUMNS

 5  /

表已分析。

現在讓我們編寫幾條SQL語句來測試一下Oracle優化器對訪問路徑的選擇：

測試1）

SQL> set autotrace traceonly

SQL> SELECT * FROM T WHERE OBJECT_TYPE='LOB';

已選擇20行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)

  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=20 Bytes=1740)

  2    1     INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=2 Card=20)

正如我們所期望的，由于使用了組合索引的前導列并且訪問了表中的少量記錄，Oracle明智地選擇了索引掃描。那么，如果我們訪問表中的大量數據時，Oracle會選擇什么樣的訪問路徑呢？請看下面的測試：

測試2）

SQL> SELECT * FROM T WHERE OBJECT_TYPE='SYNONYM';

已選擇16100行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=16100 Bytes=1400700)

 1    0   TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=16100 Bytes=1400700)

Statistics

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

         0  recursive calls

         0  db block gets

      1438  consistent gets

        13  physical reads

         0  redo size

    941307  bytes sent via SQL*Net to client

     12306  bytes received via SQL*Net from client

      1075  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

     16100  rows processed

很明顯，即使使用了組合索引的前導列，但是由于訪問了表中的大量數據，Oracle選擇了不使用索引而直接使用全表掃描，因為優化器認為全表掃描的成本更低，但事實是不是真的這樣的？我們通過增加提示（hint）來強制它使用索引來看看：

SQL> SELECT/*+ INDEX (T INDX_T)*/ * FROM T WHERE OBJECT_TYPE='SYNONYM';

已選擇16100行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16180 Card=16100 Bytes=1400700)

  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16180 Card=16100 Bytes=1400700)

  2    1     INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=80 Card=16100)

Statistics

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

         0  recursive calls

         0  db block gets

     17253  consistent gets

        16  physical reads

         0  redo size

    298734  bytes sent via SQL*Net to client

     12306  bytes received via SQL*Net from client

      1075  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

     16100  rows processed

從以上結果可以看出，在訪問大量數據的情況下，使用索引確實會導致更高的執行成本，這從statistics部分的邏輯讀取數（consistent gets）就可以看出，使用索引導致的邏輯讀取數是不使用索引導致的邏輯讀的10倍還多。因此，Oracle明智地選擇了全表掃描而不是索引掃描。

下面，讓我們來看看where子句中沒有索引前導列的情況：

測試3）

SQL> select * from t where object_name= 'DEPT';

已選擇10行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)

  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=29 Card=14 Bytes=1218)

  2    1    INDEX (SKIP SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=14 Card=14)

Statistics

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

         0  recursive calls

         0  db block gets

        24  consistent gets

         0  physical reads

         0  redo size

      1224  bytes sent via SQL*Net to client

       503  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        10  rows processed

OK！由于只查詢了10條數據，即使沒有使用前導列，Oracle正確地選擇了索引跳躍掃描。我們再來看看如果不使用索引跳躍掃描，該語句的成本：

SQL> select/*+ NO_INDEX(T INDX_T)*/ * from t where object_name= 'DEPT';

已選擇10行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)

  1    0   TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=14 Bytes=1218)

Statistics

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

         0  recursive calls

         0  db block gets

       375  consistent gets

        17  physical reads

         0  redo size

      1224  bytes sent via SQL*Net to client

       503  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        10  rows processed

正如我們所料，不使用索引所導致的邏輯讀（375）確實比使用索引的邏輯讀多（24），達到10倍以上。

繼續我們的測試，現在我們來看看Oracle不選擇使用索引的情況：

測試4）

SQL> select * from t where object_name LIKE  'DE%';

已選擇180行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=37 Bytes=3219)

  1    0   TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=37 Bytes=3219)

Statistics

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

         0  recursive calls

         0  db block gets

       386  consistent gets

        16  physical reads

         0  redo size

     12614  bytes sent via SQL*Net to client

       624  bytes received via SQL*Net from client

        13  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

       180  rows processed

這次只選擇了180條數據，跟表T中總的數據量29489條相比，顯然只是很小的一部分，但是Oracle還是選擇了全表掃描，有386個邏輯讀。這種情況下，如果我們強制使用索引，情況會怎樣呢？

SQL> select/*+ INDEX(T INDX_T)*/ * from t where object_name LIKE  'DE%';

已選擇180行。

Execution Plan

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

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=37 Bytes=3219)

  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=182 Card=37 Bytes=3219)

  2    1     INDEX (FULL SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=144 Card=37)

Statistics

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

         0  recursive calls

         0  db block gets

       335  consistent gets

         0  physical reads

         0  redo size

      4479  bytes sent via SQL*Net to client

       624  bytes received via SQL*Net from client

        13  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

       180  rows processed

通過添加提示（hint），我們強制Oracle使用了索引掃描（index full scan），執行了335個邏輯讀，比使用全表掃描的時候少了一些。

由此可見，Oracle優化器有時會做出錯誤的選擇，因為它再“聰明”，也不如我們SQL語句編寫人員更清楚表中數據的分布，在這種情況下，通過使用提示（hint），我們可以幫助Oracle優化器作出更好的選擇。

感謝各位的閱讀！關于“Oracle中組合索引怎么用”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！