Oracle查詢指定索引提高查詢效率__Oracle

 

Oracle查詢指定索引提高查詢效率 一個1600萬資料表－－簡訊上行表TBL_SMS_MO
結構：
CREATE TABLE TBL_SMS_MO
(
 SMS_ID NUMBER,
 MO_ID VARCHAR2(50),
 MOBILE VARCHAR2(11),
 SPNUMBER VARCHAR2(20),
 MESSAGE VARCHAR2(150),
 TRADE_CODE VARCHAR2(20),
 LINK_ID VARCHAR2(50),
 GATEWAY_ID NUMBER,
 GATEWAY_PORT NUMBER,
 MO_TIME DATE DEFAULT SYSDATE
);
CREATE INDEX IDX_MO_DATE ON TBL_SMS_MO (MO_TIME)
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );
CREATE INDEX IDX_MO_MOBILE ON TBL_SMS_MO (MOBILE)
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  );

問題：從表中查詢某時間段內某手機發送的短訊息，如下SQL語句：

SELECT MOBILE,MESSAGE,TRADE_CODE,MO_TIME
FROM TBL_SMS_MO
WHERE MOBILE=''''130XXXXXXXX''''
AND MO_TIME BETWEEN TO_DATE(''''2006-04-01'''',''''YYYY-MM-DD HH24:MI:SS'''') AND TO_DATE(''''2006-04-07'''',''''YYYY-MM-DD HH24:MI:SS'''')

ORDER BY MO_TIME DESC

返回結果大約需要10分鐘，應用於網頁查詢，簡直難以忍受。

 

分析：

在PL/SQL Developer，點擊“Explain Plan”按鈕（或F5鍵），對SQL進行分析，發現預設使用的索引是IDX_MO_DATE。問題可能出在這裡，因為相對於總數量1600萬資料來說， 都mobile的資料是很少的，如果使用IDX_MO_MOBILE比較容易鎖定資料。

 

如下最佳化：

SELECT /*+ index(TBL_SMS_MO IDX_MO_MOBILE) */ MOBILE,MESSAGE,TRADE_CODE,MO_TIME
FROM TBL_SMS_MO
WHERE MOBILE=''''130XXXXXXXX''''
AND MO_TIME BETWEEN TO_DATE(''''2006-04-01'''',''''YYYY-MM-DD HH24:MI:SS'''') AND TO_DATE(''''2006-04-07'''',''''YYYY-MM-DD HH24:MI:SS'''')

ORDER BY MO_TIME DESC

測試：

按F8運行這個SQL，哇～... ... 2.360s，這就是差別。

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

雖然索引並不總會快於全表掃描,但是很多時候我們希望Oracle使用索引來執行某些SQL,這時候我們可以通過index hints來強制SQL使用index.

Index Hints的格式如下:

我們簡單看一下這個提示的用法(範例為Oracle10g資料庫):

SQL> create table t as select username,password from dba_users;

Table created.

SQL> create index i_t on t(username);

Index created.

SQL> set autotrace trace explain

SQL> select /*+ index(t i_t) */ * from t where username='EYGLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    34 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    34 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("USERNAME"='EYGLE')

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

這裡的查詢使用了索引.

需要注意的是使用CTAS方式建立資料表,建立表會繼承原表的約束屬性:

SQL> desc t  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  USERNAME                                  NOT NULL VARCHAR2(30)  PASSWORD                                           VARCHAR2(30)

 

如果不使用Hints,此處Oracle不會使用索引:

SQL> select * from t where username='EYGLE'; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |    34 |     2   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T    |     1 |    34 |     2   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("USERNAME"='EYGLE') Note -----    - dynamic sampling used for this statement

索引和全表掃描的選擇和取捨並非簡單,本文不作進一步探討.