Re: SQL optimizations in Oracle

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.com (Rishi Kumar) wrote in message news:<fd3b9393.0408262011.613cbdfe_at_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
Received on Fri Aug 27 2004 - 12:17:30 CEST