More Guidelines for Query Tuning

• Unnest queries when possible (single-block opt.)

• Minimize the use of DISTINCT: don’t need it if duplicates are acceptable, or if answer contains a key.

• Minimize the use of GROUP BY and HAVING:

SELECT MIN (E.age)

FROM Employee E

GROUP BY E.dno

HAVING E.dno=102

SELECT MIN (E.age)

FROM Employee E

WHERE E.dno=102

• Consider DBMS use of index when writing arithmetic expressions: E.age=2*D.age will benefit from index on E.age, but might not benefit from index on D.age!

Previous slide

Next slide

Back to first slide

View graphic version