Re: How to pass bind variable value into a view

From: UXDBA <unixdba73_at_googlemail.com>
Date: Fri, 9 Oct 2009 00:10:00 -0700 (PDT)
Message-ID: <5daa251c-7bf8-4633-a4c4-d3b7feec69fe_at_g31g2000yqc.googlegroups.com>

On Oct 8, 9:15 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> 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;

Thanks Malcolm. Received on Fri Oct 09 2009 - 02:10:00 CDT