SQL optimizations in Oracle

[Rishi Kumar]

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

[Romeo Olympia]

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_*******@yahoo.com (Rishi Kumar) wrote in message news:<fd**************************@posting.google. com>...

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

[Romeo Olympia]

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_*******@yahoo.com (Rishi Kumar) wrote in message news:<fd**************************@posting.google. com>...

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