Re: Parameters in view

In article <9hq1r3$k1u$1_at_rex.ip-plus.net>, "Ian says...
>
>Thomas,
>
>I've just run a litt test on the calling of a function:
>
>I added a counter to the get_myvar function to note how many times it had
>been called, then included a second function to return the number of calls
>to the get_myvar function. What happens is intereting. When I select the
>function from Dual, the counter is incremented by 1, as expected. However
>when I add it to a larger query (14,000 records) the function is called
>TWICE, not once per row. The result is that I can still wrap the context
>management in my own package, while keeping the efficiency of the calls.
>
>I haven't found the details in the manual, but my guess is that the second
>call would check for differences before binding the result to the view.
>
>Any ideas?
>

DUAL is magical -- its special.

It all depends on how the optimizer does the plan. Consider:

ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace package my_pkg   2 as
  3 cnt number;
  4 end;
  5 /
Package created.

ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace function foo return number   2 as
  3 begin
  4 my_pkg.cnt := my_pkg.cnt + 1;   5
  6 return 0;
  7 end;
  8 /
Function created.

ops$tkyte_at_ORA817.US.ORACLE.COM> exec my_pkg.cnt := 0 PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817.US.ORACLE.COM> select count(*) from all_objects where object_id = foo;

  COUNT(*)

         0

ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_output.put_line( my_pkg.cnt ); 2

PL/SQL procedure successfully completed.

There is used the index so Oracle evaluated the function AND THEN went to the index. However:

ops$tkyte_at_ORA817.US.ORACLE.COM> exec my_pkg.cnt := 0

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817.US.ORACLE.COM> select count(*) from all_objects where object_id+0 = foo;

  COUNT(*)

         0

ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_output.put_line( my_pkg.cnt ); 27759

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817.US.ORACLE.COM>

There is did it once per row in the query. As I said "it might" -- i would not suggest using this approach in Oracle8i and up as it totally depends on the query plan as to how many times foo will be called.

A trick that "usually" works is:

ops$tkyte_at_ORA817.US.ORACLE.COM> exec my_pkg.cnt := 0

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817.US.ORACLE.COM> select count(*) from all_objects where object_id+0 = (select foo+rownum-1 from dual );

  COUNT(*)

         0

ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_output.put_line( my_pkg.cnt ); 1

PL/SQL procedure successfully completed.

there I used rownum in the subquery which should force the subquery to be resolved off to the side in a temporary table, avoiding the call to FOO every time.

Again, sys_context would be better.

>
>"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>news:9hptqu01ph9_at_drn.newsguy.com...
>> In article <9hp9ge$fg7$1_at_rex.ip-plus.net>, "Ian says...
>> >
>> >Another way is to put all your context variables into a package with
>> >procedures to set the variables and a function to return it. The
 advantage
>> >of this is if there is anything you need to do following the setting of
 the
>> >variable you can hide it from the calling program. For example you can
>> >execute a procedure with will insert a result set into a temporary table
 and
>> >then browse on that.
>> >
>>
>> You can do this, but beware -- Oracle might invoke your function ONCE PER
 ROW of
>> the query -- not once per query.
>>
>> SYS_CONTEXT and an application context would be infinitely preferable to
 this
>> method and much much more performant. SYS_CONTEXT is treated like a bind
>> variable and invoked once per query -- not once per row per query.
>>
>>
>> >Here's the code:
>> >/* Begin create Package */
>> >
>> >CREATE
>> >PACKAGE tpms.context_package
>> >IS
>> > PROCEDURE set_myvar (p_myvar IN NUMBER);
>> >
>> > FUNCTION get_myvar
>> > RETURN NUMBER;
>> >
>> > PRAGMA RESTRICT_REFERENCES (get_myvar, WNDS, WNPS);
>> >END;
>> >/
>> >
>> >CREATE
>> >PACKAGE BODY tpms.context_package
>> >IS
>> > v_myvar NUMBER;
>> >
>> > PROCEDURE set_myvar (p_myvar IN NUMBER)
>> > IS
>> > BEGIN
>> > v_myvar := p_myvar;
>> > END;
>> >
>> > FUNCTION get_myvar
>> > RETURN NUMBER
>> > IS
>> > BEGIN
>> > RETURN v_myvar;
>> > END;
>> >END;
>> >/
>> >
>> >/* End create Package */
>> >
>> >/* Begin set Variable */
>> >
>> >BEGIN
>> > context_package.set_myvar (1);
>> > COMMIT;
>> >END;
>> >
>> >/* End set Variable */
>> >
>> >/* Begin get Variable */
>> >
>> >SELECT context_package.get_myvar
>> >FROM dual
>> >
>> >/* End get Variable */
>> >
>> >
>> >"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>> >news:9hoitm0486_at_drn.newsguy.com...
>> >> In article <fDN%6.149236$qM3.18028774_at_news.soneraplaza.nl>, "Julian
 says...
>> >> >
>> >> >Hi,
>> >> >
>> >> >I am having a big problem with Oracle. We use WebDB in our company to
 make
>> >> >reports. Now the queries in WebDB can't be that large, so I created a
 view
>> >> >for a big query. Now the query takes way too long to execute, because
 a
 view
>> >> >fetches its results first and then webdb filters the date (with
 parameters).
>> >> >Because it takes too long webdb generetes a time-out and customers
 don't
 see
>> >> >results.
>> >> >
>> >> >If I use the source of the view as a query and give as a parameter for
>> >> >example customer_id = 2 than the query is very fast. It would be nice,
 or
>> >> >essential to have parameters within the view itself. But is that
 possible?
>> >> >Or are there any other ways to do this?
>> >> >
>> >> >Please help!!!!
>> >> >
>> >> >Julian Kooiker
>> >> >
>> >> >
>> >>
>> >>
>> >> Sure, depends in your version, but I'll cover a couple of ways....
>> >>
>> >>
>> >> First and easiest for a single parameter is
>> >> dbms_application_info.set_client_info. Consider:
>> >>
>> >> scott_at_TKYTE816> create or replace view emp_v
>> >> 2 as
>> >> 3 select ename, empno
>> >> 4 from emp
>> >> 5 where ename like userenv( 'client_info' )
>> >> 6 /
>> >>
>> >> View created.
>> >>
>> >> scott_at_TKYTE816> select * from emp_v;
>> >> no rows selected
>> >>
>> >> scott_at_TKYTE816> exec dbms_application_info.set_client_info( 'K%' )
>> >> PL/SQL procedure successfully completed.
>> >>
>> >> scott_at_TKYTE816> select * from emp_v;
>> >>
>> >> ENAME EMPNO
>> >> ---------- ----------
>> >> KING 7839
>> >>
>> >>
>> >> userenv('client_info') returns whatever you stuffed into it via
 set_client_info.
>> >> In a before page or report plsql block, you can set the parameter
 (using
 substr
>> >> on userenv, you could set more then one).
>> >>
>> >> A more sophisticated method is an APPLICATION CONTEXT, this is 8i or up
 (no one
>> >> EVER seems to post their version, never):
>> >>
>> >> scott_at_TKYTE816> create or replace context my_context using my_procedure
>> >> 2 /
>> >> Context created.
>> >>
>> >> scott_at_TKYTE816>
>> >> scott_at_TKYTE816> create or replace procedure my_procedure( p_name in
 varchar2,
>> >> p_val in varchar2 )
>> >> 2 as
>> >> 3 begin
>> >> 4 dbms_session.set_context( 'my_context', p_name, p_val );
>> >> 5 end;
>> >> 6 /
>> >>
>> >> Procedure created.
>> >>
>> >> scott_at_TKYTE816> create or replace view emp_v
>> >> 2 as
>> >> 3 select ename, empno
>> >> 4 from emp
>> >> 5 where ename like sys_context( 'my_context', 'ename_like' )
>> >> 6 OR empno = to_number( sys_context( 'my_context', 'empno' ) )
>> >> 7 /
>> >>
>> >> View created.
>> >>
>> >> scott_at_TKYTE816> select * from emp_v;
>> >> no rows selected
>> >>
>> >> scott_at_TKYTE816> exec my_procedure( 'empno', 7654 )
>> >>
>> >> PL/SQL procedure successfully completed.
>> >>
>> >> scott_at_TKYTE816> select * from emp_v;
>> >>
>> >> ENAME EMPNO
>> >> ---------- ----------
>> >> MARTIN 7654
>> >>
>> >> scott_at_TKYTE816> exec my_procedure( 'empno', null )
>> >>
>> >> PL/SQL procedure successfully completed.
>> >>
>> >> scott_at_TKYTE816> exec my_procedure( 'ename_like', 'A%' )
>> >>
>> >> PL/SQL procedure successfully completed.
>> >>
>> >> scott_at_TKYTE816> select * from emp_v;
>> >>
>> >> ENAME EMPNO
>> >> ---------- ----------
>> >> ADAMS 7876
>> >> ALLEN 7499
>> >>
>> >> --
>> >> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>> >> Expert one on one Oracle, programming techniques and solutions for
 Oracle.
>> >> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> >> Opinions are mine and do not necessarily reflect those of Oracle Corp
>> >>
>> >
>> >
>>
>> --
>> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp