Oracle查询指定索引提高查询效率

来源：互联网发布：中文域名ip查询编辑：程序博客网时间：2024/04/24 14:34

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，这就是差别。