Re: Converting query result to inlist - View performance

From: jgar the jorrible <joel-garry_at_home.com>
Date: Tue, 31 Mar 2009 11:48:32 -0700 (PDT)
Message-ID: <2b503dcc-8c53-49b8-a26b-de02916821dc_at_f41g2000pra.googlegroups.com>

On Mar 31, 11:32 am, Gokul <gokulkumar.go..._at_gmail.com> wrote:
> I have a complex view created from which I can query from for data
> refresh purposes.
>
> The following query runs fine without any problems
>
> select * from <complex view> where cola = :value
>
> or
>
> select * from <complex view> where cola in ( :value)
>
> But, If I change the :value to a inlist as shown below, the
> performance worsens severely
>
> select * from <complex view> where cola (select :value from dual)
>
> Well, I am using dual for testing purposes and to simplify the test
> case. In reality, the query is more like
>
> select * from <complex view> where cola (select cola from temp_table)
>
> Now, is there a way in the form of a hint or so to compute the
> subquery first and provide it as a inlist ?
>
> Rgds,
> Gokul

A few links to peruse, if I understand your question:

http://oracle-randolf.blogspot.com/2008/01/subquery-factoring-and-cost-based-query.html
http://jonathanlewis.wordpress.com/join-ordering-pt2/
http://www.freelists.org/post/oracle-l/Materialize-hint,6

jg

--
_at_home.com is bogus.
http://www.cbc.ca/consumer/story/2009/03/17/slot.html

Received on Tue Mar 31 2009 - 13:48:32 CDT