Re: How to pass bind variable value into a view
From: UXDBA <unixdba73_at_googlemail.com>
Date: Mon, 12 Oct 2009 04:48:26 -0700 (PDT)
Message-ID: <b37d7723-426d-420d-8624-b78f0383ebdf_at_j39g2000yqh.googlegroups.com>
On Oct 11, 9:28 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Shakespeare (what..._at_xs4all.nl) wrote:
>
> : Malcolm Dew-Jones schreef:
> : > UXDBA (unixdb..._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.
>
> I'm not sure I follow you. He asked "Is there any way we can pass bind
> variable to the inner query". As a bind variable, no, but as a function
> result yes. The value for the function is set before the query is run,
> just as if you were setting a bind variable before running a query using a
> bind variable.
>
> e.g. in sqlplus
> -- set the bind variable before doing a select
> SQL> exec :bindvar := 123;
> SQL> select * from the_vw where val = :bindvar;
>
> -- set the package value before doing a select
> SQL> exec MY_QUERY_VARS.set_the_value(123);
> SQL> select * from the_parameterized_vw ;
>
> : And what would happen if two users query this view, each with a
> : different value set?
>
> Then the query returns different rows for each user just as would happen
> if bind variables were in use. The package variables are specific to each
> user's session.
>
> : How owuld the optimizer handle this?
>
> The function returns a single value (i.e. it has no input parameters), so
> in my experience the function will be run once and that value used
> (similar to a bind variable). As for how the optimizer handles a single
> value unknown before hand, that is not always optimal. However, the gains
> from restricting an inner query typically more than makes up for any lack
> of optimal optimization of that one lookup in the inner query.
>
> : Or what if the
> : value has NOT been set at all?
>
> What if the bind variable value is not set? The query can be designed to
> do what ever you want in that case. Often I find it most useful to return
> everything
> select * from the_tbl where (MY_QUERY_VARS.the_value is null)
> OR the_column = MY_QUERY_VARS.the_value ;
>
> : Some experimenting could proof if it works, but the 'yuck' factor of
> : solutions like this is too high for me....
>
> Well that's an opinion, you can like them or not. They are not
> fundamentally different that things like the Oracle USER_XXX views, that
> restrict the data displayed by using the USER system function in the view
> definitions.
>
> If they are uncommon in an app then you might want to flag them and
> differently than other views. I normally include comments in any view
> that is non-trivial, so do that also.
>
> create or replace MY_THING1_VW ... "_VW" is common naming convention
> create or replace MY_THING2_VW ... of "run of the mill" views.
>
> create or replace MY_SOMETHING_ELSE_PVW -- "parameterized view"
> as
> select
> -- This view accepts the following packagized parameters
> -- MY_QUERY_VARS.the_value
> --
> col1
> , col2 ... etc ...
Date: Mon, 12 Oct 2009 04:48:26 -0700 (PDT)
Message-ID: <b37d7723-426d-420d-8624-b78f0383ebdf_at_j39g2000yqh.googlegroups.com>
On Oct 11, 9:28 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> Shakespeare (what..._at_xs4all.nl) wrote:
>
> : Malcolm Dew-Jones schreef:
> : > UXDBA (unixdb..._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.
>
> I'm not sure I follow you. He asked "Is there any way we can pass bind
> variable to the inner query". As a bind variable, no, but as a function
> result yes. The value for the function is set before the query is run,
> just as if you were setting a bind variable before running a query using a
> bind variable.
>
> e.g. in sqlplus
> -- set the bind variable before doing a select
> SQL> exec :bindvar := 123;
> SQL> select * from the_vw where val = :bindvar;
>
> -- set the package value before doing a select
> SQL> exec MY_QUERY_VARS.set_the_value(123);
> SQL> select * from the_parameterized_vw ;
>
> : And what would happen if two users query this view, each with a
> : different value set?
>
> Then the query returns different rows for each user just as would happen
> if bind variables were in use. The package variables are specific to each
> user's session.
>
> : How owuld the optimizer handle this?
>
> The function returns a single value (i.e. it has no input parameters), so
> in my experience the function will be run once and that value used
> (similar to a bind variable). As for how the optimizer handles a single
> value unknown before hand, that is not always optimal. However, the gains
> from restricting an inner query typically more than makes up for any lack
> of optimal optimization of that one lookup in the inner query.
>
> : Or what if the
> : value has NOT been set at all?
>
> What if the bind variable value is not set? The query can be designed to
> do what ever you want in that case. Often I find it most useful to return
> everything
> select * from the_tbl where (MY_QUERY_VARS.the_value is null)
> OR the_column = MY_QUERY_VARS.the_value ;
>
> : Some experimenting could proof if it works, but the 'yuck' factor of
> : solutions like this is too high for me....
>
> Well that's an opinion, you can like them or not. They are not
> fundamentally different that things like the Oracle USER_XXX views, that
> restrict the data displayed by using the USER system function in the view
> definitions.
>
> If they are uncommon in an app then you might want to flag them and
> differently than other views. I normally include comments in any view
> that is non-trivial, so do that also.
>
> create or replace MY_THING1_VW ... "_VW" is common naming convention
> create or replace MY_THING2_VW ... of "run of the mill" views.
>
> create or replace MY_SOMETHING_ELSE_PVW -- "parameterized view"
> as
> select
> -- This view accepts the following packagized parameters
> -- MY_QUERY_VARS.the_value
> --
> col1
> , col2 ... etc ...
Thanks a lot Malcom for the detailed analysis. Thanks Shakespeare for the inputs. Received on Mon Oct 12 2009 - 06:48:26 CDT