Re: Converting query result to inlist - View performance

From: ddf <oratune_at_msn.com>
Date: Tue, 31 Mar 2009 11:39:55 -0700 (PDT)
Message-ID: <f8b43cdd-d7c9-4ef7-9082-371c2f6e2552_at_r15g2000vbi.googlegroups.com>

On Mar 31, 1:32 pm, 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

What does the execution plan show for each query you've listed? We can't rely upon your evaluation of this dismal performance for an inlist  query.

Show us the plans, show us the actual queries and possibly someone can assist you.

David Fitzjarrell Received on Tue Mar 31 2009 - 13:39:55 CDT