Guidelines for Query Tuning (Contd.)

• Avoid using intermediate relations:

SELECT * INTO Temp

FROM Emp E, Dept D

WHERE E.dno=D.dno

AND D.mgrname=‘Joe’

SELECT T.dno, AVG(T.sal)

FROM Temp T

GROUP BY T.dno

vs.

SELECT E.dno, AVG(E.sal)

FROM Emp E, Dept D

WHERE E.dno=D.dno

AND D.mgrname=‘Joe’

GROUP BY E.dno

and

• Does not materialize the intermediate reln Temp.

• If there is a dense B+ tree index on <dno, sal>, an index-only plan can be used to avoid retrieving Emp tuples in the second query!

Previous slide

Next slide

Back to first slide

View graphic version