一個1600萬數(shù)據(jù)表－－短信上行表TBL_SMS_MO結(jié)構(gòu)：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 );問題：從表中查詢某時間段內(nèi)某手機(jī)發(fā)送的短消息，如下SQL語句：SELECT MOBILE,MESSAGE,TRADE_CODE,MO_TIMEFROM TBL_SMS_MOWHERE 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返回結(jié)果大約需要10分鐘，應(yīng)用于網(wǎng)頁查詢，簡直難以忍受。分析：在PL/SQL Developer，點(diǎn)擊“EXPlain Plan”按鈕（或F5鍵），對SQL進(jìn)行分析，發(fā)現(xiàn)缺省使用的索引是IDX_MO_DATE。問題可能出在這里，因?yàn)橄鄬τ诳倲?shù)量1600萬數(shù)據(jù)來說，都mobile的數(shù)據(jù)是很少的，假如使用IDX_MO_MOBILE比較輕易鎖定數(shù)據(jù)。如下優(yōu)化：SELECT /*+;index(TBL_SMS_MO IDX_MO_MOBILE) */ MOBILE,MESSAGE,TRADE_CODE,MO_TIMEFROM TBL_SMS_MOWHERE 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運(yùn)行這個SQL，哇～... ... 2.360s，這就是差別。