Strange behavior in ORDER BY
The following SQL statement is returning strange results. If I run the
SELECT without the ORDER BY, I get a result set of about
70 rows (which is what I would expect). When I run the same query WITH
the ORDER BY, the query returns about 300+ rows. Hum?!?
Thinking that maybe I had a bad index or statistics were bad, I rebuilt
the indexes and reran the statistics. Still, the same behavior.
I ran a SET EXPLAIN and the results were identical except with the ORDER
BY clause, temporary files were created by the
optimizer.
<<<<< SQL Statement >>>>>>
select orgid,zip,cityname,domstatecd
from org a
where a.cityname like 'Colo%'
and not exists (select zipcode
from temp_zipload b
where upper(a.cityname)=b.city and a.domstatecd = b.statecd)
order by zip;
Any thoughts?
Steve Romankiw
Env:
Solaris 2.6
IDS v7.30.UC9