Re: Wrapping Query in "select *" changes execution path

  • From: Guang Mei <gmei@xxxxxxxxxxxxxx>
  • To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 07 Jun 2010 10:35:46 -0400

Why using
select * from (
select * from users)
where rownum < 25000

and not
select * from users
where rownum < 25000
?

Are there any other conditions there in the app?


William Robertson wrote:
Out of interest, why isn't this a good thing? It sounds quite smart of the optimizer. I'm guessing the FIRST_ROWS_n approach it comes up with is not working as well as the CBO expects. 


-------- Original Message --------
Subject: Wrapping Query in "select *" changes execution path
From: Ray Feighery <rjfeighery@xxxxxxxxx>
To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
Date: 4/6/10 01:24


All

Oracle 10.2.0.4
Red Hat 4 x86_64

We have an application that runs standard SQL as a reporting tool. The
amount of rows that can be returned is limited by a variable. The
application manages this by changing the straight SQL
from this:

select *
from users;

into this:

select * from (
select * from users)
where rownum < 25000

This is causing a change in some execution paths.

Is there any (simple) way I can stop or influence this behaviour?

Thanks

Ray



--
//www.freelists.org/webpage/oracle-l






--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2010