1.执行多表关联查询时,使用完全限定的列引用
不推荐:SELECT description, price FROM products p, product_types pt WHERE p.id = pt.id;
推荐:SELECT p.description, pt.price FROM products p, product_types pt WHERE p.id = pt.id;
原因:减少数据库搜索description, price位于哪张表中的开销。
2.使用WHERE而不是HAVING
不推荐:SELECT id, avg(price) FROM products GROUP BY id HAVING id IN (1, 2);
推荐:SELECT id, avg(price) FROM products WHERE id IN (1, 2) GROUP BY id;
原因:having在group之后执行,而where在group之前,后者筛选范围大大减小。
3.使用EXISTS而不是IN
不推荐:SELECT id, name FROM products WHERE id IN (SELECT id FROM purchases)
推荐:SELECT id, name FROM products pt WHERE EXISTS (SELECT 1 FROM purchases p WHERE pt.id = p.id)
原因:EXISTS只检查行是否存在,而IN检查实际值。
4.使用EXISTS而不是DISTINCT
不推荐:SELECT DISTINCT pr.id, pr.name FROM products pr, purchase pu WHERE pr.id = pu.id
推荐:SELECT id, name FROM products pr WHERE EXISTS (SELECT 1 FROM purchases pu WHERE pr.id = pu.id)
原因:EXISTS只检查行是否存在,DISTINCT在排除重复行前要对检索的行排序。
选摘自《Oracle Database 11g SQL开发指南》 Jason Price