SQL语句重构

消除重复的模式(union之类的集合操作符的不同部分；子查询中的select,from,where子句中；from子句中以各种其他方式出现的引用) 技术:case,汇总技术; with子句;去掉没必要的distinct转移到子查询;min之类的函数会忽略null;去掉无用的连接；union去掉了重复记录,因此distinct不是必要的;如果有外部连接需要返回记录，则外连接没有意义会更慢，应该改为内连接; in() 隐含着一个distinct
玩转子查询
及早启用过滤条件
简化条件(让访问每个表的次数尽可能的少)
  如果几个子查询引用的是同一个表，那么就检查一下是否能一遍就收集到所有的信息

and c1 in (select col1 from t1 where ...)
and c2 in (select col2 from t1 where ...)

select ...
  from a, (select distinct col1, col2
              from t1
             where ... or ...) b
where a.c1 = b.col1
  and a.c2 = b.col2

  相反，如果外层查询的同一个字段所关联到不同的子查询所访问的是不同的表

and c1 in (select col1 from t1 where ...)
and c1 in (select col2 from t2 where ...)

and c1 in (select col1
             from t1 inner join t2
                        on t2.col1 = t1.col1
            where ...)

  另一个相对常见的模式是那些对两个键进行了排序的子查询

select t.text
  from t
where t.textdate = (select max(x.textdate)
                      from t as x
                     where x.id = t.id)
  and t.versionnumber = (select max(y.versionnumber)
                           from t as y
                          where y.id = t.id
                            and y.textdate = t.textdate)
order by t.id

  SELECT x.text
    FROM (SELECT id,
                 text,
                 RANK()
                     OVER (PARTITION BY id
                           ORDER BY textdate DESC, versionnumber DESC)
                     AS rnk
            FROM t) x
   WHERE x.rnk = 1
ORDER BY x.id

其他优化
  简化汇总(通常可以将在不同子查询中对同一张表分别计算汇总的操作转换成一个一遍的操作。作法：取不同子查询所返回的结果集的合集，对该合集进行查询，然后使用case构造块来仔细地计算各种和，计数或者其他用到的每个子集上的东西)
  使用with(如果子查询在主查询中被引用了若干次的话，使用with就可以使该子查询只运行一次)

with my_subquery as (select distinct blah from ...)
select ...
from ...
where somecol = my_subquery.blah

  合并集合操作符(把子查询作为一个整体考虑，如果不将这两个强大的连接union起来，可以把它们的联合以一种因式分解的方式转移到一个子查询中)

select ...
from a, b, t1
where ...
union
select ...
from a, b, t2
where ...

select ...
from a, b,
  (select ...
     from t1
    where ...
    union
   select ...
     from t2
    where ...)
where ...

select ...
from a,
  (select ...
     from b, t1
    where ...
    union
   select
     from b, t2
   where ...)
where ...

声明：JavaEye文章版权属于作者，受法律保护。没有作者书面许可不得转载。