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

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

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

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.

Shakespeare

UXDBA (unixdba73 at (no spam) 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;

Shakespeare (what... at (no spam) xs4all.nl) wrote:

: Malcolm Dew-Jones schreef:
: > UXDBA (unixdb... at (no spam) 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 ...

On Oct 11, 9:28 pm, yf... at (no spam) vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:





Shakespeare (what... at (no spam) xs4all.nl) wrote:

: Malcolm Dew-Jones schreef:
: > UXDBA (unixdb... at (no spam) 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 -