Re: How to pass bind variable value into a view

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 11 Oct 2009 13:28:34 -0700
Message-ID: <4ad23ff2$1_at_news.victoria.tc.ca>

Shakespeare (whatsin_at_xs4all.nl) wrote:
: 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.

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 ...

Received on Sun Oct 11 2009 - 15:28:34 CDT