帮忙优化oracle的一个SQL 谢谢

2009-12-14 07:50:21 240 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

我的表里有两个字段需要联合查询导致索引无效
语句是这样的：
select * from user u where u.xing||u.ming like '%张%'
表里把姓名字段分成两个字段姓和名，不知道怎么建立索引了
结果模糊查询时候 8W条记录用40秒
哪位兄弟指点一下

...全文

避坑指南：QCustomPlot鼠标事件冲突解决方案（左键拖动vs右键框选）本文深入解析QCustomPlot在QT框架下的高级交互实现，重点解决左键拖动与右键框选的冲突问题。通过自定义事件处理和QRubberBand技术，提供了一套完整的无冲突交互方案，帮助开发者优化数据可视化应用的用户体验。阅读详情

dream1210

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

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 谢谢

相关推荐