oracle (二)

今天总结下最近SQL REVIEW时遇见的常见问题，下面是同事发我的SQL，比较有代表性，就一一说下存在的问题。

 

selectcount(*)

  from (

       

        select t.title as title,

               t.consume as price,

               t.address as address,

               t.spic as pic,

               t.huodongoutline as descp,

                cast(t.createdateas date) as publishDate,

               t.topindex as topIndex,

               t.type astype,

               t.work_number asid,

               t.pa_pash_youhui_id as id1,

               t.WORK_NUMBER as WORK_NUMBER,

               t.showenrollbtn as showEnrollBtn,

               t.showpaybtn as showPayBtn

          frompa_pash_youhui_info t

         where t.city in ('上海', '全国')

          AND t.TYPE = '0'

          and trunc(t.dateStart) <= trunc(sysdate)

          and trunc(t.dateEnd) >= trunc(sysdate)

          AND ((t.topIndex = '999'or (t.topIndex in ('1', '2', '3') and

              t.topTime < trunc(sysdate))))

       

        )cms_expand,

       coupon_count c

wherec.huodong_type(+) = 1

   and cms_expand.WORK_NUMBER= c.WORK_NUMBER(+) ;

 

说一下存在的问题

1，  为了做一个count（*）的操作，在子查询中select 出了不必要的字段（除了WORK_NUMBER字段外连接用到了之外其它全是多余的），而且还对一个字段使用了不必要的函数（cast(t.createdate as date) as publishDate）操作;

 

2，  Where 条件中对表字段使用了多余的函数;

 

3，  做count（*）操作一般不会用到外连接。因为外连接是要把一个表相应的结果集做全部的展示，另一个表不匹配的使用null代替，这种情况完全可以对单个表做count（*）操作，如果要用到另外一个表可以同时使用exists 操作，而不是使用连接，如果连接的字段不是主键也有可能产生笛卡尔积，导致count(*)的结果不正确。最终确认这里的连接是不需要的，coupon_count表也是多余的。

 

所以最终改写成了下面的sql语句

 

SELECTCOUNT(*)

FROM  pa_pash_youhui_info t

WHERE  t.city IN ('上海', '全国')

AND   t.type = '0'

AND   t.datestart <= trunc(SYSDATE)

AND   t.dateend >= trunc(SYSDATE)

AND   (t.topindex = '999'OR

      (t.topindex IN ('1', '2', '3') AND t.toptime <trunc(SYSDATE)));

 

下面是前两天生产库报异常的sql，也是类似的问题

 

selectcount(m.resource_id)

  from (selectdistinct v.*

         from (

 

         selectmax(t.resource_id) asresource_id,

                      max(decode(e1.expand_key, 'startDate',e1.expand_value)) as stardateValue,

                      max(decode(e1.expand_key, 'endDate',e1.expand_value)) as enddateValue,

                      max(decode(e1.expand_key, 'qixiang',e1.expand_value)) as qixiangValue,

                      max(decode(e1.expand_key,

                                 'fengxiandengji',

                                 e1.expand_value)) as fengxiandengjiVAlue

                 from (select p.resource_id, p.resource_link

                         from cms_online_resource_props p   

                         where p.resource_type = '5110'

                          and p.resource_path like

                              '/sites/bank.pingan.com/licaichanpin/%.shtml') t

                 join cms_online_resource_expand e1 one1.resource_id =

                                                       t.resource_id

                groupby e1.resource_id

                

                 

                 )v

         leftjoin cms_online_resource_expand m1 on m1.resource_id =

                                                    v.resource_id

        where m1.expand_key = 'yuqishouyiquery') m

where1 = 1 ;

 

可以等价改成下面的sql（初步分析与测试是等价的）：

 

selectcount(*)

   fromcms_online_resource_props p

  wherep.resource_type = '5110'

  andp.resource_path like'/sites/bank.pingan.com/licaichanpin/%.shtml'

  andexists (select0

     from cms_online_resource_expand m

    where m.resource_id = p.resource_id

    and m.expand_key = 'yuqishouyiquery'

   ) ;

 

 

请大家注意下上面提到的问题，谢谢！