Re: sorting or temporary tables in execution plan?

On Aug 21, 12:47 pm, Darren <anon5..._at_yahoo.com> wrote:
> Is sorting of a temporary table a necessary step in an execution plan?

Found this on DB2 site regarding sorting decisions:

"When it evaluates a nested loop join, the optimizer also decides whether to sort the outer table before performing the join. If it orders the outer table, based on the join columns, the number of read operations to access pages from disk for the inner table might be reduced, because they are more likely to be be in the buffer pool already. If the join uses a highly clustered index to access the inner table and if the outer table has been sorted, the number of index pages accessed might be minimized.
In addition, if the optimizer expects that the join will make a later sort more expensive, it might also choose to perform the sort before the join. A later sort might be required to support a GROUP BY, DISTINCT, ORDER BY or merge join."

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/c0005311.htm Received on Fri Aug 22 2008 - 00:46:25 CEST