为什么加上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, '!@#$%^&*() ！?#￥%……?*（', ' '))
...全文
961 20 打赏 收藏 转发到动态 举报
写回复
用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执行计划不同。
这种问题，不用急，先慢慢查一下外围环境，再定位不迟。
SQL Server数据库基础到精通课程
变量定义，字段约束、等等，Select 、Where、Top、Group By 、Order By，Distinct、Declare等，详细可看图片和课程，本课程带有完整的SQL数据库课程课件，代码有注释解析等等，学习起来简单易懂，易上手直接操作，...
mysql中使用order by 和limit查询变慢解决办法
本文摘自：http://www.phpfensi.com/mysql/20141008/6772.html本文章来给大家介绍一篇关于mysql中使用order by 和limit查询变慢解决办法，因为在mysql分页利用limit来操作，同时排序也需要使用了order by ，所以经常会两者同是使用到，但是如果数据量大的话就会碰到查询很慢很慢，下面我来给大家介绍解决办法。先来看一下测试性能,代码如...
一次mysql联表 join 后 order by desc 慢的排查
mysql order by 速度变慢，order by 后索引失效
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
python groupby 用法详解
进行分析，如电商领域将全国的总销售额根据省份进行划分，分析各省销售额的变化情况，社交领域将用户根据画像（性别、年龄）进行细分，研究用户的使用情况和偏好等。如果按照正常的步骤来计算，需要先求得不同公司的平均薪水，然后按照员工和公司的对应关系填充到对应的位置，不用。转换成列表的形式后，可以看到，列表由三个元组组成，每个元组中，第一个元素是组别（这里是按照。在pandas中，实现分组操作的代码很简单，仅需一行代码，在这里，将上面的数据集按照。而言更加灵活，能够传入任意自定义的函数，实现复杂的数据操作。
Oracle 高级技术

3,491

社区成员

18,714

社区内容

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

加载中

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

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

+ 用AI写文章