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

From: Guang Mei <gmei_at_prospectiv.com>
Date: Mon, 07 Jun 2010 10:35:46 -0400
Message-ID: <4C0D03C2.8020709_at_prospectiv.com>

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_at_gmail.com>
> To: Oracle-L List <oracle-l_at_freelists.org>
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

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

Received on Mon Jun 07 2010 - 09:35:46 CDT