Converting query result to inlist - View performance

From: Gokul <gokulkumar.gopal_at_gmail.com>
Date: Tue, 31 Mar 2009 11:32:41 -0700 (PDT)
Message-ID: <8eec2d31-a6ed-4f8f-9a69-b1fbb8da2c57_at_u8g2000yqn.googlegroups.com>

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 Received on Tue Mar 31 2009 - 13:32:41 CDT