oracle 高性能查询 注意点

1、同字段多条件查询使用 case ，避免重复使用查询，例如

SELECT COUNT(*) FROM products WHERE price < 13;

    COUNT(*)

   ----------

         2

SELECT COUNT(*) FROM products WHERE price BETWEEN 13 AND 15;

    COUNT(*)

  ----------

       5

SELECT COUNT(*) FROM products WHERE price > 15;

   COUNT(*)

----------

      5

 

应换成case查询：如下

SELECT

COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low,

COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med,

COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high

FROM products;

LOW MED HIGH

---------- ---------- ----------

      2              5             5