Re: How to pass bind variable value into a view
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 10 Oct 2009 10:54:57 +0200
Message-ID: <4ad04be6$0$83235$e4fe514c_at_news.xs4all.nl>
Malcolm Dew-Jones schreef:
> UXDBA (unixdba73_at_googlemail.com) wrote:
> : 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.
>
> Not as a bind variable.
>
> The SYS_CONTEXT function is often used for this purpose. In conjunction
> with this you must create a context and a package to update the context
> with what ever values the queries will need. (And you have to do the
> setting of the value before calling the query of course). Google etc etc.
>
> You can also create a package and use it to store variables (which must be
> accessed by functions from within the query).
>
> -- untested code
> create or replace package MY_QUERY_VARS as
> procedure set_the_value( p number);
> function the_value return number;
> end ;
> create or replace package body MY_QUERY_VARS as
> l_the_value number;
> procedure set_the_value( p number) is
> begin
> l_the_value := p;
> end;
> function the_value return number is
> begin
> return l_the_value;
> end;
> end ;
>
> create or replace view MY_VIEW as
> select * from my_table where something = MY_QUERY_VARS.the_value;
>
Date: Sat, 10 Oct 2009 10:54:57 +0200
Message-ID: <4ad04be6$0$83235$e4fe514c_at_news.xs4all.nl>
Malcolm Dew-Jones schreef:
> UXDBA (unixdba73_at_googlemail.com) wrote:
> : 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.
>
> Not as a bind variable.
>
> The SYS_CONTEXT function is often used for this purpose. In conjunction
> with this you must create a context and a package to update the context
> with what ever values the queries will need. (And you have to do the
> setting of the value before calling the query of course). Google etc etc.
>
> You can also create a package and use it to store variables (which must be
> accessed by functions from within the query).
>
> -- untested code
> create or replace package MY_QUERY_VARS as
> procedure set_the_value( p number);
> function the_value return number;
> end ;
> create or replace package body MY_QUERY_VARS as
> l_the_value number;
> procedure set_the_value( p number) is
> begin
> l_the_value := p;
> end;
> function the_value return number is
> begin
> return l_the_value;
> end;
> end ;
>
> create or replace view MY_VIEW as
> select * from my_table where something = MY_QUERY_VARS.the_value;
>
I don't think this last option would help. Views are only evaluated at select time. So it does not help to 'limit the rows' of the view in advance. Unless this is meant to do a "select * from view" without a where clause, and force the where clause into the view.
And what would happen if two users query this view, each with a different value set? How owuld the optimizer handle this? Or what if the value has NOT been set at all?
Some experimenting could proof if it works, but the 'yuck' factor of solutions like this is too high for me....
Shakespeare Received on Sat Oct 10 2009 - 03:54:57 CDT