sorting or temporary tables in execution plan?

External Since: Aug 20, 2008 Posts: 4

ok, the book I am reading discusses this type of query:

SELECT p.pname
FROM P, SH, S
WHERE P.pnum = SH.pnum
AND SH.snum = S.snum
AND S.city = 'NY';

and it compares whether to do the full join first then select, or the
other way round.

Anyway the example first joins S and SH over the common column to form
a table TempA, and then sorts the table? The sorting actually takes up
most of the time >80% of total cost.

Is sorting of a temporary table a necessary step in an execution plan?

External Since: Jan 31, 2008 Posts: 63

>> Is sorting of a temporary table a necessary step in an execution plan? <<

No, many products use hashing instead of sequential access and
merges.

I would also have done this query like so in a conventional SQL engine
built on top of a file system with indexes:

1) Build temp table using (S.city = 'NY') -- should be pretty small
subset, but if it is not indexed, you may have to scan the table.
2) (SH.snum = S.snum) with an index on what looks like PK-FK. In some
products that relationship would be in a linked list structure and be
really fast.
3) (P.pnum = SH.pnum) with an index

In short, the answer is that it all depends...