Oracle查询指定索引提高查询效率
来源:互联网 发布:微信小程序业务域名 编辑:程序博客网 时间:2024/04/20 03: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''''
ANDMO_TIME BETWEEN TO_DATE(''''2006-04-01'''',''''YYYY-MM-DDHH24:MI:SS'''') AND TO_DATE(''''2006-04-07'''',''''YYYY-MM-DDHH24:MI:SS'''')
ORDER BY MO_TIME DESC
返回结果大约需要10分钟,应用于网页查询,简直难以忍受。
分析:
在PL/SQL Developer,点击“ExplainPlan”按钮(或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''''
ANDMO_TIME BETWEEN TO_DATE(''''2006-04-01'''',''''YYYY-MM-DDHH24:MI:SS'''') AND TO_DATE(''''2006-04-07'''',''''YYYY-MM-DDHH24:MI:SS'''')
ORDER BY MO_TIME DESC
测试:
按F8运行这个SQL,哇~... ... 2.360s,这就是差别。
---------------------------------------------------------------------------------------------------------------
虽然索引并不总会快于全表扫描,但是很多时候我们希望Oracle使用索引来执行某些SQL,这时候我们可以通过index hints来强制SQL使用index.
Index Hints的格式如下:
/*+ INDEX ( table [index [index]...] ) */
我们简单看一下这个提示的用法(范例为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
索引和全表扫描的选择和取舍并非简单,本文不作进一步探讨.
- Oracle查询指定索引提高查询效率
- Oracle查询指定索引提高查询效率
- Oracle查询指定索引提高查询效率
- 提高Oracle查询效率
- oracle提高查询效率
- Oracle提高查询效率
- oracle提高查询效率
- oracle提高查询效率
- oracle提高查询效率
- 提高Oracle查询效率
- 利用索引提高查询效率
- 增加索引提高查询效率
- Oracle中通过位图索引提高查询效率
- Oracle中通过位图索引提高查询效率
- oracle sql强制走索引提高查询效率
- 【转】oracle提高查询效率
- oracle模糊查询效率提高
- oracle 提高查询效率(转)
- 统计某字段值相同的所有记录
- memset用法详解(转)
- mock
- 哦麦嘎,我获奖了
- DHT网络是什么
- Oracle查询指定索引提高查询效率
- 如何使用lex
- ORACLE锁机制研究
- 2009-01-13小记
- 图像分割
- asp.net的web.config节点详解
- Linux嵌入式系统与硬件平台的关系
- 使用avr-libc的标准IO流调试程序
- Eclipse Eclipseme wtk配置JAVA ME开发环境