Re: How to pass bind variable value into a view

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 08 Oct 2009 22:28:30 +0200
Message-ID: <4ace4b71$0$83245$e4fe514c_at_news.xs4all.nl>

Shakespeare schreef:

> 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

I meant to say index on t1.col1 of course.

Shakespeare Received on Thu Oct 08 2009 - 15:28:30 CDT