Re: Wrapping Query in "select *" changes execution path
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: ukja.dion@xxxxxxxxx
- Date: Thu, 3 Jun 2010 23:58:23 -0700
I was wondering this myself, as I want to test execution speeds alone and
leave out the data fetching overhead. I was thinking maybe to wrap the
original query in a Common Table Expression, ie the
"with clause" and use the hint /*+ materialize */
though have sat down to test this yet.
Best Wishes
Kyle Hailey
On Thu, Jun 3, 2010 at 6:49 PM, Dion Cho <ukja.dion@xxxxxxxxx> wrote:
> One possibility would be the fact that the optimizer tries to convert
> ROWNUM predicate to first K rows mode.
>
> _optimizer_rownum_pred_based_fkr TRUE TRUE true
> immediate
> enable the use of first K rows due to rownum predicate
>
> 10053 trace is the best tool to troubleshoot this kind of problem. You
> might get the answer yourself by comparing the 10053 trace files of both
> cases - with rownum vs. without rownum.
>
>
> ================================
> Dion Cho - Oracle Performance Storyteller
>
> http://dioncho.wordpress.com (english)
> http://ukja.tistory.com (korean)
> http://dioncho.blogspot.com (japanese)
> http://ask.ex-em.com (q&a)
> ================================
>
>
> 2010/6/4 Ray Feighery <rjfeighery@xxxxxxxxx>
>
> Also:
>> optimizer_mode string ALL_ROWS
>>
>> If I hint the query to be
>> select /*+ all_rows */ * from ( ... subuery....)
>> it reverts to the initial explain plan.
>>
>>
>>
>> On 4 June 2010 10:24, Ray Feighery <rjfeighery@xxxxxxxxx> wrote:
>> > 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
>>
>>
>>
>
Other related posts:
