同样的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, '!@#$%^&*() !?#¥%……?*(', ' '))
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, '!@#$%^&*() !?#¥%……?*(', ' '))
例如,表结构不同,一个库里的表,没有索引、分区等,那直接导致SQL执行计划不同。
这种问题,不用急,先慢慢查一下外围环境,再定位不迟。
trim(translate(a.name, '!@#$%^&*() !?#¥%……?*(', ' '))【问题补充】现在发现和这个也有很大的关系,translate函数影响也很大,
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,'!@#$%^&*() !·#¥%……—*(',' ')))
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, '!@#$%^&*() !?#¥%……?*(', ' '))
顺便请教一下:'!@#$%^&*() !?#¥%……?*(' 这串内容,为什么要替换掉他们,如果应用层允许他们进入数据库,那数据库里面就不应该替换啊?还有为什么是这些符号?是自己随意写的吗?还是行业规定? 谢谢!
'!@#$%^&*() !?#¥%……?*(' 替换这个字符串的作用是排重的,如果有2条记录,他们的name值差不多,只是其中的一条中含有'!@#$%^&*() !?#¥%……?*('这些特殊字符,则可以提示用户是否是相同数据
经后来测试发现,好像是由于 and trim(translate(ftemp.name, '!@#$%^&*() !?#¥%……?*(', ' ')) =
trim(translate(a.name, '!@#$%^&*() !?#¥%……?*(', ' '))导致变慢甚至执行时候停在那里了,请问这个函数是不是有什么问题啊?还是我写的这个sql本身有错啊?
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, '!@#$%^&*() !?#¥%……?*(', ' ')) 函数索引看看