帮忙优化oracle的一个SQL 谢谢

dream1210 2009-12-14 07:50:21
我的表里 有两个字段需要联合查询 导致索引无效
语句是这样的：
select * from user u where u.xing||u.ming like '%张%'
表里把 姓名字段分成两个字段 姓 和 名 ，不知道怎么建立索引了
结果模糊查询时候 8W条记录 用40秒
哪位兄弟指点一下
...全文
149 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
fcuandy 2009-12-15
  • 打赏
  • 举报
 回复
首先这种语句, 在没有说明有无主健的情况下，不可能简单的通过union 或union all来改造, 会有错误的结果产生:

tb:
f1 f2 f3
a a b
a b c
a a b
a b d

原语句
select f1,f2 from from tb where f2 like '%b%' or f3 like '%b%'
结果
a a
a b
a a
a b
用union ,不用说，排重了。 结果行数少了两行。

用union all, 也会有类似的情况， 具体数据例子就不举了。

如果楼主做了这样的表结构，姓、名 列长度应该不很长， 你需要考查一下你表结构及数据存储空间占用的情况，可行的话，加一列， 把，姓+名 存储起来
以增加数据冗余为代价换取性能。
dream1210 2009-12-15
  • 打赏
  • 举报
 回复
谢谢大家指点 学到不少东西 哈哈。
感觉改造SQL的话 实在是麻烦 还是把姓+名存起来省事一点
分不多 包含
renadg 2009-12-15
  • 打赏
  • 举报
 回复
不懂,学习中!
jiaruimin11 2009-12-15
  • 打赏
  • 举报
 回复
只要你用%% 就会全表扫描,这样索引就失去意义了
appleqwfyhw 2009-12-15
  • 打赏
  • 举报
 回复
你的表数据量大了 不管你用什么索引 只要你用%% 就会慢 还是做下fulltext吧
中智软件科技 2009-12-14
  • 打赏
  • 举报
 回复
加群技术交流:69705156
vc555 2009-12-14
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 crazylaa 的回复:]
select * from user u where  u.xing like '张%' or u.ming like '张%'
[/Quote]
不好意思，看错了。
不过这样不合题意。如叫“姓 李 名 三张”
vc555 2009-12-14
  • 打赏
  • 举报
 回复
我做个实验，用了三种方法，如不用索引，怎样用索引。要具体根据你的数据库环境而定，比如数据量，参数设置等等。下面的内容供参考。

OPER@tl> select * from test;



AAA                  BBB                         CCC

-------------------- -------------------- ----------

张                   一                            1

一                   张                            2

张                   张                            3

多少                 张                            4

少                   发                            5



OPER@tl> create index ind_test1 on test(aaa);



索引已创建。



OPER@tl> create index ind_test2 on test(bbb);



索引已创建。



OPER@tl> set autot traceonly

第一种index_full_scan。6个逻辑读:
OPER@tl> select * from test

  2  where rowid in(select rn from (

  3  (select /*+ index(test ind_test1) */

  4  aaa,rowid rn

  5  from test

  6  where aaa like '%张%'

  7  union

  8  select /*+ index(test ind_test2) */

  9  bbb,rowid rn

 10  from test

 11  where bbb like '%张%')))

 12  /





执行计划

----------------------------------------------------------

Plan hash value: 4114598729



-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     1 |    20 |     6  (50)| 00:00:01 |

|   1 |  NESTED LOOPS               |           |     1 |    20 |     6  (50)| 00:00:01 |

|   2 |   VIEW                      | VW_NSO_1  |     2 |    24 |     4  (50)| 00:00:01 |

|   3 |    HASH UNIQUE              |           |     1 |    24 |            |          |

|   4 |     VIEW                    |           |     2 |    24 |     4  (50)| 00:00:01 |

|   5 |      SORT UNIQUE            |           |     2 |    16 |     4  (75)| 00:00:01 |

|   6 |       UNION-ALL             |           |       |       |            |          |

|*  7 |        INDEX FULL SCAN      | IND_TEST1 |     1 |     8 |     1   (0)| 00:00:01 |

|*  8 |        INDEX FULL SCAN      | IND_TEST2 |     1 |     8 |     1   (0)| 00:00:01 |

|   9 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |     8 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   7 - filter("AAA" LIKE '%张%')

   8 - filter("BBB" LIKE '%张%')





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          6  consistent gets

          0  physical reads

          0  redo size

        602  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          4  rows processed


第二种，index_ffs，8个逻辑读：
OPER@tl> select * from test

  2  where rowid in(select rn from (

  3  (select /*+ index_ffs(test ind_test1) */

  4  aaa,rowid rn

  5  from test

  6  where aaa like '%张%'

  7  union

  8  select /*+ index_ffs(test ind_test1) */

  9  bbb,rowid rn

 10  from test

 11  where bbb like '%张%')))

 12  /





执行计划

----------------------------------------------------------

Plan hash value: 2460625359



-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |     1 |    20 |     7  (43)| 00:00:01 |

|   1 |  NESTED LOOPS               |           |     1 |    20 |     7  (43)| 00:00:01 |

|   2 |   VIEW                      | VW_NSO_1  |     2 |    24 |     5  (40)| 00:00:01 |

|   3 |    HASH UNIQUE              |           |     1 |    24 |            |          |

|   4 |     VIEW                    |           |     2 |    24 |     5  (40)| 00:00:01 |

|   5 |      SORT UNIQUE            |           |     2 |    16 |     5  (60)| 00:00:01 |

|   6 |       UNION-ALL             |           |       |       |            |          |

|*  7 |        INDEX FAST FULL SCAN | IND_TEST1 |     1 |     8 |     2   (0)| 00:00:01 |

|*  8 |        INDEX FULL SCAN      | IND_TEST2 |     1 |     8 |     1   (0)| 00:00:01 |

|   9 |   TABLE ACCESS BY USER ROWID| TEST      |     1 |     8 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   7 - filter("AAA" LIKE '%张%')

   8 - filter("BBB" LIKE '%张%')





统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

        602  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          4  rows processed


第三种，fts，8个。等同于你原来未改造的sql
OPER@tl> select * from test where aaa like '%张%' or  bbb like '%张%';





执行计划

----------------------------------------------------------

Plan hash value: 1357081020



--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     8 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter("AAA" LIKE '%张%' OR "BBB" LIKE '%张%')





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

        602  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed
vc555 2009-12-14
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 crazylaa 的回复:]
select * from user u where  u.xing like '张%' or u.ming like '张%'
[/Quote]
你这和lz的没区别，变了个型而已，能用上索引吗？
crazylaa 2009-12-14
  • 打赏
  • 举报
 回复
select * from user u where u.xing like '张%' or u.ming like '张%'
vc555 2009-12-14
  • 打赏
  • 举报
 回复
还有什么字段？有主键没有？
heao 2009-12-14
  • 打赏
  • 举报
 回复
1、利用3楼的建索引
2、不用'%条件%'进行查询，走不到索引
wojiaochenglong 2009-12-14
  • 打赏
  • 举报
 回复
不太明白你的表为什么这么设计(姓和名分开),不知可否更改
另外最好不要用select *...这样的语句，试着去除一些与业务无关的列......
dream1210 2009-12-14
  • 打赏
  • 举报
 回复
先谢过各们的回复
分别加索引没有效果
执行计划中没有使用索引。复合索引 试着也不行
创建函数索引 还是没好使（函数索引不太会用，不知道用的对不对）
create index pk_xm on TB_EMP CONCAT(ZW_XING,ZW_MING);
select * from TB_EMP where CONCAT(ZW_XING,ZW_MING) like'张%'

4楼说的 效率是能上去 但是结果集有很大的出入。
郁闷中。。 大家再帮忙想想
xue1234567890 2009-12-14
  • 打赏
  • 举报
 回复
模糊查询的时候 最好不要这样查询...like '%张%'

第一个字符写%的话,效率很低...

select * from user u where u.xing like '张%' || u.ming like '张%'

以类似的方式查询的话,会快很多...
碧水幽幽泉 2009-12-14
  • 打赏
  • 举报
 回复
create index index_user_xing on user(xing);
create index index_user_ming on user(ming);
duqiangcise 2009-12-14
  • 打赏
  • 举报
 回复
对向楼上说的那样分别在两个查询字段上创建索引。
Dave 2009-12-14
  • 打赏
  • 举报
 回复

在xing,和 ming 两个字段上建个普通索引，在用执行计划看看SQL，效率怎么样...

create index index_name on table_name(col_name);



------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716
ORACLE 优化sql语句提高oracle执行效率 .doc
在平时的日常开发中，由于SQL语句使用不当，执行效率低下，现整理了文档供大家参考，尽可能帮忙新手，实现资源共享；
sql性能的调整-总结（69页完整版）
本文档主要介绍与 SQL 调整有关的内容，内容涉及多个方面： SQL 语句执行的过程、ORACLE 优化器，表之间的关联，如何得到 SQL 执行计划，如何分析执行计划等内容，从而由浅到深的方式了解 SQL 优化的过程，使大家逐步步入 SQL 调整之门
Oracle数据库SQL语言
本课程全面介绍oracle的SQL语言。 介绍查询语句 DML语句 DDL语句 数据库对象等等
oracle with sql优化,一个SQL性能优化分享---利用with as 优化SQL
马上就要单身节了，正在想今年我去祸害谁家的姑娘，突然QQ好友发来信息，说能否帮忙优化一个SQL，SQL调优做得实在太多了，都已经麻木了，反正优化一个SQL也就几秒钟到几分钟的事情。哥们说下面的SQL要跑5个多小时SELECTB.AREA_ID,A.PARTY_ID,B.AREA_NAME,C.NAME CHANNEL_NAME,B.NAME PARTY_NA...
Oracle - 如何提高SQL的查询效率（where条件优化）
在Oracle数据库中编写查询语句的时候，经常碰到的一个问题就是SQL优化，那么今天就总结一下Where之后，如何进行条件的优化 优化规则： 一、选择合适的基础表 Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有3个以上...
Oracle

17,090

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章