Improves query efficiency by specifying an index for Oracle queries

One 16 million data table-text message upstream table TBL_SMS_MO
Structure:
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 1 M
NEXT 1 M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
);
Create index IDX_MO_MOBILE ON TBL_SMS_MO (MOBILE)
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 64 K
NEXT 1 M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
); Problem: query the short messages sent by a MOBILE phone in a certain period of time from the table. The following SQL statement: SELECT MOBILE, MESSAGE, TRADE_CODE, MO_TIME
FROM TBL_SMS_MO
Where mobile = 130 XXXXXXXX
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 returns results about 10 minutes, it is intolerable to apply it to web queries. Analysis: In PL/SQL Developer, click the "EXPlain Plan" button (or press F5) to analyze the SQL statement. It is found that the default index is IDX_MO_DATE. The problem may be caused here, because compared to the total number of 16 million data items, data on mobile is very small. If IDX_MO_MOBILE is used, data is easily locked. Optimization: SELECT/* + index (TBL_SMS_MO IDX_MO_MOBILE) */MOBILE, MESSAGE, TRADE_CODE, MO_TIME
FROM TBL_SMS_MO
Where mobile = 130 XXXXXXXX
AND MO_TIME BETWEEN TO_DATE (, YYYY-MM-DD HH24: MI: SS) AND TO_DATE (, YYYY-MM-DD HH24: MI: SS) order by MO_TIME DESC test: Run this SQL BY F8, wow ~... ... 2.360 s, which is the difference.