Re: How to pass bind variable value into a view
From: UXDBA <unixdba73_at_googlemail.com>
Date: Sat, 17 Oct 2009 23:27:59 -0700 (PDT)
Message-ID: <9bb4802d-0c96-476a-bc5f-35683b6f03fe_at_j4g2000yqa.googlegroups.com>
On 12 Oct, 12:48, UXDBA <unixdb..._at_googlemail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Sat, 17 Oct 2009 23:27:59 -0700 (PDT)
Message-ID: <9bb4802d-0c96-476a-bc5f-35683b6f03fe_at_j4g2000yqa.googlegroups.com>
On 12 Oct, 12:48, UXDBA <unixdb..._at_googlemail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
More here...
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3561623983484#293473467
30613
Received on Sun Oct 18 2009 - 01:27:59 CDT