查看: 7608|回复: 18

【大话IT】求助:oracle中模糊查询如何优化,才能走索引

[复制链接]
论坛徽章:
3
沸羊羊
日期:2015-03-04 14:55:41优秀写手
日期:2015-03-06 06:00:142015年新春福章
日期:2015-03-06 11:59:47
发表于 2015-2-26 16:03 | 显示全部楼层 |阅读模式
问题:table A(col1,col2,其他列),主键(col1,col2)。table中数据量近3千万笔
一程序中的where条件是:where col1 like 'test%' and col2='value'
如何优化where语句,使其可以根据主键索引运行???


谢谢!
认证徽章
论坛徽章:
55
优秀写手
日期:2015-02-12 06:00:13秀才
日期:2016-02-18 10:07:49秀才
日期:2016-02-18 10:08:142016猴年福章
日期:2016-02-23 09:58:34猴赛雷
日期:2016-02-23 10:04:24紫水晶
日期:2016-04-01 12:19:28双子座
日期:2016-04-28 10:19:29秀才
日期:2016-06-23 14:15:06奥运会纪念徽章:曲棍球
日期:2016-08-30 13:12:24奥运会纪念徽章:体操
日期:2016-09-26 11:24:37
发表于 2015-2-26 16:31 | 显示全部楼层
就我知道的来说,你如果是就在这两个字段上建立的普通索引,并且使用了like来做模糊查询,是没有办法通过where来走索引的,如果你要模糊查询,可以试试建立函数索引

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6350
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:252012新春纪念徽章
日期:2014-12-29 12:11:142013年新春福章
日期:2014-12-29 12:11:14马上有车
日期:2014-12-29 12:11:14马上有房
日期:2014-12-29 12:11:14马上有钱
日期:2014-12-29 12:11:14马上有对象
日期:2014-12-29 12:11:14马上加薪
日期:2014-12-29 12:11:14
发表于 2015-2-26 16:33 | 显示全部楼层
记得按钮有一篇关于like 优化的帖子你找找。

利用切割的思想

使用道具 举报

回复
论坛徽章:
6
2014年新春福章
日期:2014-02-18 16:50:09马上有车
日期:2014-02-18 16:50:09优秀写手
日期:2014-05-29 05:59:082015年新春福章
日期:2015-03-04 14:55:132015年新春福章
日期:2015-03-06 11:59:47秀才
日期:2017-09-18 17:02:34
发表于 2015-2-26 16:38 | 显示全部楼层
like 'test%'和 like '%test%'都没有办法走索引

使用道具 举报

回复
论坛徽章:
6
2014年新春福章
日期:2014-02-18 16:50:09马上有车
日期:2014-02-18 16:50:09优秀写手
日期:2014-05-29 05:59:082015年新春福章
日期:2015-03-04 14:55:132015年新春福章
日期:2015-03-06 11:59:47秀才
日期:2017-09-18 17:02:34
发表于 2015-2-26 16:40 | 显示全部楼层
可以不使用like,然后用函数索引替代

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-26 16:44 | 显示全部楼层
本帖最后由 bfc99 于 2015-2-26 16:46 编辑

这样应该是可以走索引的啊。
SQL> create table t1 as select dbms_random.string('x',20) col1,dbms_random.string('x',10) col2,dbms_random.random col3 from dual connect by rownum<=10000;

Table created.

SQL> alter table t1 add constraint pk_t1 primary key (col1,col2);
alter table t1 add constraint pk_t1 primary key (col1,col2)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL> desc t1;
Name                                           Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                                    VARCHAR2(4000)
COL2                                                    VARCHAR2(4000)
COL3                                                    NUMBER

SQL> alter table t1 modify (col1 varchar2(20));

Table altered.

SQL> alter table t1 modify (col2 varchar2(20));

Table altered.

SQL> alter table t1 add constraint pk_t1 primary key (col1,col2);

Table altered.

SQL> set autotrace on
SQL> set linesize 400
SQL> select * from t1 where col1 like 'test%' and col2='value';  

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1249713949

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

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

   2 - access("COL1" LIKE 'test%' AND "COL2"='value')
       filter("COL2"='value' AND "COL1" LIKE 'test%')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         23  recursive calls
          0  db block gets
         99  consistent gets
          7  physical reads
          0  redo size
        464  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

使用道具 举报

回复
论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2015-2-26 16:49 | 显示全部楼层
bfc99 发表于 2015-2-26 16:44
这样应该是可以走索引的啊。
SQL> create table t1 as select dbms_random.string('x',20) col1,dbms_rand ...

估计LZ希望是index unique scan, 不是index range scan, 需求决定设计,呵呵!

使用道具 举报

回复
论坛徽章:
3
沸羊羊
日期:2015-03-04 14:55:41优秀写手
日期:2015-03-06 06:00:142015年新春福章
日期:2015-03-06 11:59:47
 楼主| 发表于 2015-2-26 16:58 | 显示全部楼层
谢谢楼上各位,不好意思。我提问的问题没有解释清楚,应该是优化整个sql,不是仅仅优化Where部分。

通过在select中使用hint指定主键索引,可以实现我的需求,待明天看下具体的效果。

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-26 18:00 | 显示全部楼层
snowmanjie 发表于 2015-2-26 16:58
谢谢楼上各位,不好意思。我提问的问题没有解释清楚,应该是优化整个sql,不是仅仅优化Where部分。

通过 ...

按说不加HINT,也是可以走的。

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-26 18:01 | 显示全部楼层
bell6248 发表于 2015-2-26 16:49
估计LZ希望是index unique scan, 不是index range scan, 需求决定设计,呵呵!

要是这样的话,那过滤条件就必须是等于了吧?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表