Re: Baffled by SQL - again :-))

From: "Watson, Paul" <Paul.Watson@aggregate.com> > >Consider the following SQLs > >select post_code, prod_id, count(*) >from eric >group by 1,2 >having count(*) !=1> >select trim(post_code), trim(prod_id), count(*) >from eric >group by 1,2 >having count(*) !=1 > >The second uses a huge amount sort area (/tmp), whereas the first >doesn't. Why? Probably because applying a function to a column causes the engine to write a result set before aggregation. _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com

Obnoxio The Clown wrote in message <902s3m$1ao$1@news.xmission.com>... > >From: "Watson, Paul" <Paul.Watson@aggregate.com> >> > >Probably because applying a function to a column causes the engine to write >a result set before aggregation. Yeah wot he sez - and most likely you have an index on post_code and/or prod_id in the original table, which it could exploit to do the group-by on the fly, but once you apply the functions, it's too much to expect the engine to realise that the same index is gonna be useful for the job (although it sounds like it could work, how much do these poor sucker engine writers have to do to keep us happy all the time?)