Re: Wrapping Query in "select *" changes execution path
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: rjfeighery@xxxxxxxxx
- Date: Fri, 4 Jun 2010 10:49:59 +0900
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:
