Re: Wrapping Query in "select *" changes execution path
- From: Andy Klock <andy@xxxxxxxxxxxxxxx>
- To: gmei@xxxxxxxxxxxxxx
- Date: Mon, 7 Jun 2010 11:27:12 -0400
Typically, you need an inline view because you need to do some sorting
before filtering by rownum. I'm pretty sure that the OP just took it out to
simplify the query. But, without a sort it is most likely a bug since:
select * from users where rownum < 100
could quite possibly return a completely different subset than
select * from users where rownum < 150.
Andy
On Mon, Jun 7, 2010 at 10:35 AM, Guang Mei <gmei@xxxxxxxxxxxxxx> wrote:
> 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:
