Re: How to pass bind variable value into a view
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 08 Oct 2009 22:27:04 +0200
Message-ID: <4ace4b1b$0$83245$e4fe514c_at_news.xs4all.nl>
UXDBA schreef:
> 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
>
Date: Thu, 08 Oct 2009 22:27:04 +0200
Message-ID: <4ace4b1b$0$83245$e4fe514c_at_news.xs4all.nl>
UXDBA schreef:
> 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
>
You could hint your query to use the index on t2.col2. There is a way to 'push' the hint to the view, can't exactly remember how though. Maybe one of the guru's here can tell you how.
Shakespeare Received on Thu Oct 08 2009 - 15:27:04 CDT