RE: SQL delete based on aggregate function?

>The SQL optimizer recognizes that the subquery never changes...

That _should_ be the case. The inner select is a non-correlated subquery
and as such should NOT be rerun every time. However, the performance I
get when I do run such a query seems to indicate otherwise, at least on
a V5R2 box.

As a test I tried the following statement on a 270 w/5.2 doing nothing
else:

select sum(latitude) from zip where state in (select state from zip
group by state having count(*) > 1000)

Which should return a sum of the latitudes (a useless value, but forces
sql to read each record in the resultset). Zip has just over 800,000
rows (Canadian zips too)

As far as I can tell that is a NON-Correlated subquery. However, I tried
running the query through visual explain and while I killed the query
before it ended, I did see this in the joblog:

Message ID . . . . . . :   CPI4325
Message . . . . :   Temporary result file built for query.
Cause . . . . . :   A temporary result file was created to contain the
results 
  of the query for reason code 11.  This process took 0 minutes and 1.3 
...       
  11 - The query optimizer creates a run-time temporary file in order t
implement certain correlated group by queries.                         

FWIW, I ran the same query on SQLServer and got the answer in 3 seconds.


-Walden 


------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)