By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,276 Members | 1,350 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,276 IT Pros & Developers. It's quick & easy.

SQL optimizations in Oracle

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Jul 19 '05 #2

P: n/a
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.