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?

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 (DOT) net> wrote

Kin Ng wrote:

To answer your question requires running EXPLAIN PLAN on both versions ... with and without the added clause.

BTW: Something you should be doing with every SQL statement you write.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

kin_ng5 (AT) yahoo (DOT) com (Kin Ng) wrote in message

As Daniel said, _always_ check your SQL via explain plan to see how
Oracle will be executing it.

As for adding the ROWNUM clause. I think that you are seeing the
difference between an ALL_ROWS and FIRST_ROW execution. BTW, the query
may likely _seem_ faster when it is not. With FIRST_ROW it returns the
1st set of results pretty quickly.. and while you are
looking/processing those, Oracle gets the next set, and next... etc.

However, doing an explain plan will show what's happening better than
my guestimates.

--
Billy

Until my DBA fixed the Explain Plan problem (getting Plan table not
setup error), I can tell you that when I changed the result to a
single row Count(*), I still get much faster result (12 seconds vs 43
seconds). If I break the query into 2 parts by storing the result
from the 1st part in a table and join the table with the 2nd part of
the query and the result is fast without the RowNum > 0 clause. We
are at 9.2 already so this isn't something old.

vslabs (AT) onwe (DOT) co.za (Billy Verreynne) wrote in message news:<1a75df45.0307132322.6f372239 (AT) posting (DOT) google.com>...

Kin Ng wrote:

Show us the explain plans.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

kin_ng5 (AT) yahoo (DOT) com (Kin Ng) wrote in message news:<d5b3f600.0307121116.283114b1 (AT) posting (DOT) google.com>...
One example may be subquery unnesting and view merging. If inner view
has a predicate with rownum then it wouldn't be merged. Same for
subqueries. Therefore, you'll get a different plan and different
execution time.

In theory, however, a plan with query transformations applied should
be more efficient than without. Not in practice, of course.

kin_ng5 (AT) yahoo (DOT) com (Kin Ng) wrote i
..
Of course!!!

The select count(*) does _not_ have to read entire physical rows
(especially when it can use something like a bitmap index instead). It
does_not_ have to built a (big) cursor in memory containing all the
rows selected (the resulting cursor contains a single row and column).
It does _not_ have to return the physical rows across the network to
your application.

It takes me just under 3 seconds to run a 'SELECT count(*) FROM table'
where the result is 77.8 million rows.

Changing that into a 'SELECT * FROM table' will mean that I want to
pull 14+ GB worth of data across to the client. That will _not_ happen
in 3 hours, never mind the 3 second response I got from the count(*).

BTW, you do not need the DBA to set up a plan table for you. You can
create that table inside your schema and the EXPLAIN PLAN statement
will use you schema (assuming it is rum from inside your schema).

The script to create it sits in ORACLE_HOME/rdbms/admin and is called
UTLPLAN.SQL if I'm not mistaken.

--
Billy

Oh, I can't even create a table. We are security paranoid.