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

同样的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, '!@#$%^&*() ！?#￥%……?*（', ' '))

解决方案 »

  1.   

    同样的SQL，在不同的库上，执行速度不一样很正常。
    例如，表结构不同，一个库里的表，没有索引、分区等，那直接导致SQL执行计划不同。
    这种问题，不用急，先慢慢查一下外围环境，再定位不迟。
      

  2.   

    为什么不去掉group by,根本没意义.本来exists是找到一条满足的数据就返回了,因为你加了group by,会导致将所有的数据查找出来,效率的差距可想而知
      

  3.   

    跑一下执行计划看两个库德执行计划是不是一样的?如果不一样就要看一下表结构的差异,如索引等,或者对表做一下统计分析,重建索引等.如果执行计划一致,那只能优化sql了,效率不一样只是数据量或硬件的差异问题
      

  4.   

    看一下两个SQL的执行计划，具体分析一下
      

  5.   

    and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
      trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))    【问题补充】现在发现和这个也有很大的关系，translate函数影响也很大，
      

  6.   

    第一个执行计划：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,'!@#$%^&*() ！·#￥%……—*（',' ')))
      

  7.   

    做了一下修改,将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, '!@#$%^&*() ！?#￥%……?*（', ' '))
    顺便请教一下:'!@#$%^&*() ！?#￥%……?*（' 这串内容,为什么要替换掉他们,如果应用层允许他们进入数据库,那数据库里面就不应该替换啊?还有为什么是这些符号?是自己随意写的吗?还是行业规定? 谢谢!
      

  8.   


    '!@#$%^&*() ！?#￥%……?*（'  替换这个字符串的作用是排重的，如果有2条记录，他们的name值差不多，只是其中的一条中含有'!@#$%^&*() ！?#￥%……?*（'这些特殊字符，则可以提示用户是否是相同数据
      

  9.   


    经后来测试发现，好像是由于 and trim(translate(ftemp.name, '!@#$%^&*() ！?#￥%……?*（', ' ')) =
      trim(translate(a.name, '!@#$%^&*() ！?#￥%……?*（', ' '))    导致变慢甚至执行时候停在那里了，请问这个函数是不是有什么问题啊？还是我写的这个sql本身有错啊？
      

  10.   

    在 group by 的字段上建一個索引
      

  11.   

    是不是说:名字可以包含:'!@#$%^&*() ！?#￥%……?*（'里面的特殊字符,不算这里面的特殊字符,如果其他部分相同,就提示用户:该用户名已经注册!这条规定挺好, 需求挖得很细啊
      

  12.   


    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, '!@#$%^&*() ！?#￥%……?*（', ' ')) 函数索引看看