为什么加上group by 速度变慢，一致停在那！

JJR1988112 2011-01-04 03:25:50
同样的sql在不同的数据库上执行，执行的速度不一样，后来发现，是由于group by 导致的，添加上group by 语句都执行不出来了，是不是数据库什么配置问题啊？(数据库版本10g)还是sql本身有问题 啊？具体的sql如下:

select ftemp.*
from (select *
from T_A where (age=20) ftemp
where 1 = 1
and exists
(select trim(translate(name, '!@#$%^&*() ！?#￥%……?*（', ' '))
from T_B a
where 1 = 1
and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))
group by trim(translate(name, '!@#$%^&*() ！?#￥%……?*（', ' ')) having count(*) > 1
)
order by trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' '))
...全文
1035 20 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
JJR1988112 2011-01-19
  • 打赏
  • 举报
 回复
感谢各位！我的问题已经解决了，换了种写法，用in
JJR1988112 2011-01-05
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 gjswxhb 的回复:]
) ,这个只是想把 右括号 标红,看来在源代码里面不能加标记了,
[/Quote]

经后来测试发现，好像是由于 and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))导致变慢甚至执行时候停在那里了，请问这个函数是不是有什么问题啊？还是我写的这个sql本身有错啊？
JJR1988112 2011-01-05
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 gjswxhb 的回复:]
做了一下修改,将group by 替换了一下啊,好想lz 提供的代码少了半个括号,

SQL code

select ftemp.*
from ( select *
from T_A
where (age=20) ) ftemp

where 1 = 1
and exists
……
[/Quote]

'!@#$%^&*() ！?#￥%……?*（' 替换这个字符串的作用是排重的，如果有2条记录，他们的name值差不多，只是其中的一条中含有'!@#$%^&*() ！?#￥%……?*（'这些特殊字符，则可以提示用户是否是相同数据
物润声无 2011-01-05
  • 打赏
  • 举报
 回复
) ,这个只是想把 右括号 标红,看来在源代码里面不能加标记了,
物润声无 2011-01-05
  • 打赏
  • 举报
 回复
做了一下修改,将group by 替换了一下啊,好想lz 提供的代码少了半个括号,


select ftemp.*

from (  select *

        from T_A 

        where (age=20) ) ftemp

        

where 1 = 1

and exists

    (   select trim(translate(name, '!@#$%^&*() ！?#￥%……?*（', ' ')) t_name

        from T_B a

        where 1 = 1

        and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) = trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))

        and exists

            ( select ROWNUM  n

              from   T_B

              where  a.t_name = trim(translate(t_B.name, '!@#$%^&*() ！?#￥%……?*（', ' '))

              and    ROWNUM > 1

            )

        /*group by trim(translate(name, '!@#$%^&*() ！?#￥%……?*（', ' ')) having count(*) > 1*/

)

order by trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' '))


顺便请教一下:'!@#$%^&*() ！?#￥%……?*（' 这串内容,为什么要替换掉他们,如果应用层允许他们进入数据库,那数据库里面就不应该替换啊?还有为什么是这些符号?是自己随意写的吗?还是行业规定? 谢谢!
JJR1988112 2011-01-05
  • 打赏
  • 举报
 回复
怎么看上面的2个执行计划啊？
JJR1988112 2011-01-05
  • 打赏
  • 举报
 回复
第一个执行计划：

PLAN_TABLE_OUTPUT

Plan hash value: 2320997337
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 213 | 14 (22)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 213 | 14 (22)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | T_CG_LW | 5 | 1065 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 30 | 4 (25)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| T_CG_LW | 1 | 30 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

2 - filter( EXISTS (SELECT /*+ */ 0 FROM "T_CG_LW" "A" WHERE
TRIM(TRANSLATE("A"."LWMC",'!@#$%^&*() ！·#￥%……—*（','
'))=TRIM(TRANSLATE(:B1,'!@#$%^&*() ！·#￥%……—*（',' ')) GROUP BY
TRIM(TRANSLATE("LWMC",'!@#$%^&*() ！·#￥%……—*（',' ')) HAVING COUNT(*)>1))
3 - filter(TO_NUMBER("CGDL")=1 OR "SSXY"='100088' AND
TO_NUMBER("CGDL")=1)
4 - filter(COUNT(*)>1)
6 - filter(TRIM(TRANSLATE("A"."LWMC",'!@#$%^&*() ！·#￥%……—*（','
'))=TRIM(TRANSLATE(:B1,'!@#$%^&*() ！·#￥%……—*（',' ')))





第二个执行计划:


Plan hash value: 2320997337

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 528 | 45648 (3)| 00:09:08 |
| 1 | SORT ORDER BY | | 1 | 528 | 45648 (3)| 00:09:08 |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | T_CG_LW | 1612 | 831K| 55 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 2 | 132 | 57 (4)| 00:00:01 |
|* 6 | TABLE ACCESS FULL| T_CG_LW | 24 | 1584 | 56 (2)| 00:00:01 |
---------------------------------------------------------------------------------

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

2 - filter( EXISTS (SELECT /*+ */ 0 FROM "T_CG_LW" "A" WHERE
TRIM(TRANSLATE("A"."LWMC",'!@#$%^&*() ！·#￥%……—*（','
'))=TRIM(TRANSLATE(:B1,'!@#$%^&*() ！·#￥%……—*（',' ')) GROUP BY
TRIM(TRANSLATE("LWMC",'!@#$%^&*() ！·#￥%……—*（',' ')) HAVING COUNT(*)>1))
3 - filter(TO_NUMBER("CGDL")=1 OR "SSXY"='100088' AND
TO_NUMBER("CGDL")=1)
4 - filter(COUNT(*)>1)
6 - filter(TRIM(TRANSLATE("A"."LWMC",'!@#$%^&*() ！·#￥%……—*（','
'))=TRIM(TRANSLATE(:B1,'!@#$%^&*() ！·#￥%……—*（',' ')))
JJR1988112 2011-01-05
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 coolkisses 的回复:]
看一下两个SQL的执行计划，具体分析一下
[/Quote]
怎么具体分析执行计划啊？不是很懂，可否讲明白点。谢谢！
心中的彩虹 2011-01-05
  • 打赏
  • 举报
 回复
[Quote=引用楼主 jjr1988112 的回复:]
同样的sql在不同的数据库上执行，执行的速度不一样，后来发现，是由于group by 导致的，添加上group by 语句都执行不出来了，是不是数据库什么配置问题啊？(数据库版本10g)还是sql本身有问题 啊？具体的sql如下:

select ftemp.*
from (select *
from T_A where (age=20) ftemp
where 1 = 1
……
[/Quote]



select ftemp.*
from (select *
from T_A where (age=20) ftemp
where 1 = 1
and exists
(select 1
from T_B a
where 1 = 1
and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))
having count(*) > 1
)
order by trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' '))

---order by 以及里层的group by（多余） 就是时间久的原因

另外你在试试分析下你的表 T-A,T_B
analyze table T_A compute staticstis

在试试建立trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) 函数索引看看
物润声无 2011-01-05
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 jjr1988112 的回复:]
引用 12 楼 gjswxhb 的回复:
做了一下修改,将group by 替换了一下啊,好想lz 提供的代码少了半个括号,

SQL code

select ftemp.*
from ( select *
from T_A
where (age=20) ) ftemp

where 1 = 1
and exists
……


'!@#$%^&……
[/Quote]
是不是说:名字可以包含:'!@#$%^&*() ！?#￥%……?*（'里面的特殊字符,不算这里面的特殊字符,如果其他部分相同,就提示用户:该用户名已经注册!

这条规定挺好, 需求挖得很细啊
物润声无 2011-01-05
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 jjr1988112 的回复:]
引用 13 楼 gjswxhb 的回复:
) ,这个只是想把 右括号 标红,看来在源代码里面不能加标记了,


经后来测试发现，好像是由于 and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))……
[/Quote]
可能如7楼所说,name上有索引~
ruihuahan 2011-01-05
  • 打赏
  • 举报
 回复
在 group by 的字段上建一個索引
liuyi8903 2011-01-04
  • 打赏
  • 举报
 回复
看一下你的执行计划吧。
minitoy 2011-01-04
  • 打赏
  • 举报
 回复
莫非name上有索引?[Quote=引用 6 楼 jjr1988112 的回复:]
and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))

【问题补充】现在发现和这个也有很大的关系，translate函数影响也很大，
[/Quote]
JJR1988112 2011-01-04
  • 打赏
  • 举报
 回复
and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))

【问题补充】现在发现和这个也有很大的关系，translate函数影响也很大，
coolkisses 2011-01-04
  • 打赏
  • 举报
 回复
看一下两个SQL的执行计划，具体分析一下
JJR1988112 2011-01-04
  • 打赏
  • 举报
 回复
[Quote=引用楼主 jjr1988112 的回复:]
同样的sql在不同的数据库上执行，执行的速度不一样，后来发现，是由于group by 导致的，添加上group by 语句都执行不出来了，是不是数据库什么配置问题啊？(数据库版本10g)还是sql本身有问题 啊？具体的sql如下:

select ftemp.*
from (select *
from T_A where (age=20) ftemp
where 1 = 1
……
[/Quote]

数据库是完全一样的，表结构等都是一样的
highsea_004 2011-01-04
  • 打赏
  • 举报
 回复
跑一下执行计划看两个库德执行计划是不是一样的?如果不一样就要看一下表结构的差异,如索引等,或者对表做一下统计分析,重建索引等.如果执行计划一致,那只能优化sql了,效率不一样只是数据量或硬件的差异问题
minitoy 2011-01-04
  • 打赏
  • 举报
 回复
为什么不去掉group by,根本没意义.本来exists是找到一条满足的数据就返回了,因为你加了group by,会导致将所有的数据查找出来,效率的差距可想而知
coolkisses 2011-01-04
  • 打赏
  • 举报
 回复
同样的SQL，在不同的库上，执行速度不一样很正常。
例如，表结构不同，一个库里的表，没有索引、分区等，那直接导致SQL执行计划不同。
这种问题，不用急，先慢慢查一下外围环境，再定位不迟。
MySQL的Order by与Group by优化详解！
MySQL的Order by和Group by优化详解，通俗易懂！🎉
mysql中加desc加limit速度慢_mysql中使用order by 和limit查询变慢解决办法
先来看一下测试性能代码如下1.显示行 0 - 9 (10 总计, 查询花费 32.4894 秒)2.SQL 查询: SELECT *3.FROM tables4.WHERE m_id5.IN ( 50, 49 )6.ORDER BY id DESC7.LIMIT 10显示行 0 - 9 (10 总计, 查询花费 32.4894 秒)SQL 查询: SELECT *FROM tablesWHERE ...
count(distinct) 与 group by 再 count 的区别
在传统关系型数据库中，group by与count(distinct)都是很常见的操作。count(distinct colA)就是将colA中所有出现过的不同值取出来，相信只要接触过数据库的同学都能明白什么意思。 count(distinct colA)的操作也可以用group by的方式完成，具体代码如下： select count(distinct colA) from table1; select count(1) from (select colA from table1 group by col
关于mysql group by 执行原理和超大数据量查询优化介绍
关于MySQL中group by的执行流程和大数据量下的查询优化方案介绍
oracle 查询慢的原因总结
查询速度慢的原因很多，常见如下几种： 　　 　　1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 　　 　　2、I/O吞吐量小，形成了瓶颈效应。 　　 　　3、没有创建计算列导致查询不优化。 　　 　　4、内存不足 　　 　　5、网络速度慢 　　 　　6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 　　 　　7、锁或者死锁(这也
Oracle 高级技术

3,494

社区成员

18,710

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章