Re: How to pass bind variable value into a view

snip

> All,
>
> RDBMS :  9.2.0.8
>
> I have the following query:
>
> select * from v1 where v1_col1=:BIND_VAR
>
> v1 is a  view:
>
> create view v1
> (v1_col1, v1_col2)
> as
> select t1.col1, t2.col2 from t1,t2
> where t1.col1=t2.col2
>
> t1.col1 - unique index,
> t2.col2 - non unique index.
>
> Noticed that:
>
> select * from v1 where v1.col1=?
>
> takes about 60sec to execute.
>
> however,
>
> select * from ( select t1.col1, t2.col2 from t1,t2
> where t1.col1=t2.col2
> and t1.col1=:BIND_VAR)
>
> takes 1 sec to execute.
>
> mainly here rowsource is reduced when t1.col1 is taken alongwith inner
> query.
>
> question:
>
> a) Is there any way we can pass bind variable to the inner query with
> using view v1 ( i know BIND
> var cannot be passed to DDL)but any other way you would suggest to
> achieve the same result.
>
> Regards

Use a function that returns a ref cursor Received on Thu Oct 08 2009 - 15:07:38 CDT