Re: sorting or temporary tables in execution plan?

Darren <anon5874_at_yahoo.com> writes:

> 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?

As is often the case, it depends on the database engine. There is no 'for all cases do x then y'.

There is an interesting book called "databases for smarties' or something similar, which shows how much variation you can get in sql statements on different database engines. In some cases, even the order of the components of the where clause can have significant differences in either performance or results (particularly with respect to outer joins apparently). these differences can exist even when the different engines supposedly support the same SQL standard.

Tim

-- 
tcross (at) rapttech dot com dot au