Actually the query returns LOTs of rows. It just took a long time
(and err out) for a Large set of rows. I started trying to do ROWNUM
= 1 and it returned FAST. Then I tried ROWNUM < 1000 and it returned
a thousand rows fast. Then I tried ROWNUM < 10,000, then 1 million
and finally ROWNUM > 0 and ALL cases it returned lots of rows and
FAST.
"Jim Kennedy" <kennedy-down_with_spammers_at_comcast.net> wrote in message news:<V1_Pa.43602$wk6.11054_at_rwcrnsc52.ops.asp.att.net>...
> Because it does not have any rows. Your query probably has an order by and
> that is why it ran out of temp space.(You probably don't have a temp
> tablespace defined or even defined properly and your sort area size it
> probably pretty small - like 64 K) Rownum is the number of the row that is
> returned in the result set before sorting and thus rownum>0 means return no
> rows.
> Jim
>
> "Kin Ng" <kin_ng5_at_yahoo.com> wrote in message
> news:d5b3f600.0307121116.283114b1_at_posting.google.com...
> > I have a complex query that takes a really long time to run and
> > actually never completed because it ran out of space. But if I add
> > this meaningless (oh well, may be meaningful) condition in the
> > existing Where clause, the query returns data in a few seconds.
> >
> > and ROWNUM > 0
> >
> > Why adding this makes a query run so much faster?
Received on Sun Jul 13 2003 - 17:27:03 CDT
