当前位置:Gxlcms > mysql > OracleSQL最佳实践

OracleSQL最佳实践

时间:2021-07-01 10:21:17 帮助过:33人阅读

1.用EXISTS代替DISTINCT,消除sort operation 2.如果在GROUP BY中过滤数据,在WHERE从句中指定条件比在HAVING从句中有更好

  1.用EXISTS代替DISTINCT,,消除sort operation

  2.如果在GROUP BY中过滤数据,在WHERE从句中指定条件比在HAVING从句中有更好的性能,因为在GROUP之前已经过滤掉数据,因此更少的行被汇总

  3.UNION会对两个SELECT语句的结果集执行一个SORT,并消除重复行,成本会昂贵,而UNION ALL则不会。因此如果应用能够处理重复,或者确信没有重复记录,那么考虑使用UNION ALL代替UNION

  4.能不用UNION就不要用它

  5.为了避免在一个SQL语句中混合使用LEFT JOIN和RIGHT JOIN产生混淆,应该使用一个一致的视野,例如外连接只使用FULL or LEFT OUTER JOIN,忽略掉RIGHT OUTER JOIN

  例如:

  Sql代码

  SELECT e.lname, j.function, d.name

  FROM job j LEFT OUTER JOIN employee e ON e.job_id = j.job_id

  RIGHT OUTER JOIN department d ON e.dept_id = d.dept_id;

  SELECT e.lname, j.function, d.name

  FROM job j LEFT OUTER JOIN employee e ON e.job_id = j.job_id

  RIGHT OUTER JOIN department d ON e.dept_id = d.dept_id;

  

  应该转换为:

  Sql代码

  SELECT e.lname, j.function, d.name

  FROM department d LEFT OUTER JOIN

  (job j LEFT OUTER JOIN employee e

  ON e.job_id = j.job_id)

  ON e.dept_id = d.dept_id;

  SELECT e.lname, j.function, d.name

  FROM department d LEFT OUTER JOIN

  (job j LEFT OUTER JOIN employee e

  ON e.job_id = j.job_id)

  ON e.dept_id = d.dept_id;

linux

人气教程排行