consider the following queries that deals with report pagination

a ) select MYROWS.* from
(select * from TABLE_NAME) MY_ROWS where rownum between 1 and 100

b) select * from TABLE_NAME where rownum between 1 and 100

My question : Does oracle optimize the first query so that it is not
significantly different (on execution time) when compared to the
second query ?

Thanks

Rishi

First off, you can't possibly be using this for report pagination.
Because your queries, as they are without any valid ORDER BY clause,
will not accomplish your goal. ROWNUM being only a pseudo-column will
not guarantee the same set of rows in between calls. Maybe rethink
that a bit.

Re: your question on query optimization, maybe see the plans for
yourself. "SET AUTOT TRACE EXP" in SQLPLUS to get you started.

HTH.

v_rishi_k (AT) yahoo (DOT) com (Rishi Kumar) wrote in message news:<fd3b9393.0408262011.613cbdfe (AT) posting (DOT) google.com>...

I had a previous post on this thread which was mildly out of whack.
What was I thinking!?

Your queries using ROWNUM will not work for your pagination purposes.
Period.
You cannot get "page 2" by saying "select ... from ... where rownum
between 101 and 200".

Cheers.

v_rishi_k (AT) yahoo (DOT) com (Rishi Kumar) wrote in message news:<fd3b9393.0408262011.613cbdfe (AT) posting (DOT) google.com>...