Home » SQL & PL/SQL » SQL & PL/SQL » using, in the select-clause, a result calculated in the where-clause
using, in the select-clause, a result calculated in the where-clause [message #306528] Fri, 14 March 2008 10:08 Go to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Is it possible to use, in the select-clause, a result calculated in the where-clause, without recalculating it?

In the following snippet of code, the expression AddOne(DO.RIGHT_OPERAND) is calculated twice in the same SQL statement (see the body of the DivideAndPrint procedure): in the where-clause and in the select-clause. Ideally, it would be possible to calculate it only once, in the where-clause, then store the result in a variable and use this variable in the select-clause.

The code relies on a table, DIVISION_OPERANDS, whose definition is given below.

create or replace package PKG_TEST is

  function AddOne(aNumber Number) return Number;
  procedure DivideAndPrint;

end PKG_TEST;
/
create or replace package body PKG_TEST is

  function AddOne(aNumber number)
  return number
  is
  begin
    return aNumber + 1;
  end AddOne;

  procedure DivideAndPrint
  is
    type TableOfNumbers is Table of Number;
    divisionResults TableOfNumbers;
  begin
    select DO.LEFT_OPERAND / AddOne(DO.RIGHT_OPERAND)
    bulk collect into divisionResults
    from DIVISION_OPERANDS DO
    where AddOne(DO.RIGHT_OPERAND) <> 0;

    for i in divisionResults.first .. divisionResults.last loop
      dbms_output.put_line(divisionResults(i));
    end loop;
  end DivideAndPrint;

end PKG_TEST;
/



Following is the definition of the table DIVISION_OPERANDS, required by the package above, with sample data.
create table DIVISION_OPERANDS
(
  LEFT_OPERAND number not null,
  RIGHT_OPERAND number not null
);

insert into DIVISION_OPERANDS(LEFT_OPERAND, RIGHT_OPERAND) values(1, -2);
insert into DIVISION_OPERANDS(LEFT_OPERAND, RIGHT_OPERAND) values(4, -3);
insert into DIVISION_OPERANDS(LEFT_OPERAND, RIGHT_OPERAND) values(7, -1);
insert into DIVISION_OPERANDS(LEFT_OPERAND, RIGHT_OPERAND) values(9, -4);

commit;

[Updated on: Fri, 14 March 2008 10:17]

Report message to a moderator

Re: using, in the select-clause, a result calculated in the where-clause [message #306531 is a reply to message #306528] Fri, 14 March 2008 10:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2129904

HTH

Regards

Raj
Re: using, in the select-clause, a result calculated in the where-clause [message #306554 is a reply to message #306528] Fri, 14 March 2008 12:15 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Thanks for the reply, Raj.

It was interesting to read the reference you referred to. I hadn't been aware of subquery factoring. I'm glad to have been introduced to it.

However, this is not quite what i was looking for: A subquery factoring doesn't take parameters. In contrast, in my sample code, the expression i'd like to factor, namely AddOne(DO.RIGHT_OPERAND), may evaluate to different values, depending on the argument passed to it, and this argument's value may change several times in the course of processing the select statement.
Re: using, in the select-clause, a result calculated in the where-clause [message #306564 is a reply to message #306554] Fri, 14 March 2008 12:48 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
  1  create or replace function test_func(a number) return number
  2  is
  3  begin
  4  if mod(a,2) = 0
  5  then
  6     return 1;
  7  else
  8     return 0;
  9  end if;
 10* end;
SQL> /

Function created.

SQL> select * from test;

     EMPNO ENAME                  SAL
---------- --------------- ----------
         1 A                        1
         2 B                        2
         3 C                        3
         4 D                        4
         5 E                        5
         6 F                        6
         7 G                        7
         8 H                        8
         9 I                        9

SQL> select empno, ename, sal, test_func(empno) func_output from test
  2  where test_func(empno) != 0;

     EMPNO ENAME                  SAL FUNC_OUTPUT
---------- --------------- ---------- -----------
         2 B                        2           1
         4 D                        4           1
         6 F                        6           1
         8 H                        8           1

SQL> with
  2  t
  3  as
  4  (select empno, ename, sal, test_func(empno) func_output from test)
  5  select * from t
  6  where func_output > 0;

     EMPNO ENAME                  SAL FUNC_OUTPUT
---------- --------------- ---------- -----------
         2 B                        2           1
         4 D                        4           1
         6 F                        6           1
         8 H                        8           1


Regards

Raj
Re: using, in the select-clause, a result calculated in the where-clause [message #306590 is a reply to message #306528] Fri, 14 March 2008 16:38 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Thanks again, Raj. Your ideas are very creative.

There's a delicate nuance in the problem i've posed, though, which your solution fails to address. Namely, the select-clause must not be evaluated for certain records, or else an exception would be thrown (due to an attempt to divide by zero). But according to your solution, a full table scan is undertaken, before the desired records are filtered.
Re: using, in the select-clause, a result calculated in the where-clause [message #306615 is a reply to message #306590] Sat, 15 March 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference with a query that does not use "with" clause regarding the fucntion evaluation for each row and FTS (unless you have a FBI, function based-index).
And to come back to your first question and query, are you sure the function is called twice for each row?

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306626 is a reply to message #306528] Sat, 15 March 2008 03:25 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Thanks for your reply, Michel.

Following your hint, i've set up a test, checking how many times the function is called for each row (see code below). To my surprise - to my horror, rather - it's turned out that it sometimes runs twice and sometimes once, per row. More accurately, it seems to be running twice for the first row only; later rows generate only a single function invokation. Is this a bug in Oracle, or a feature? And if it's a feature - what are the rules that govern it, and what's the rational behind it?

The following code relies on a table, LEFT_OPERANDS, whose definition is given below. The test i performed, and the output it generated, are given in the end of this message.

*** P.S., I request that this topic be relocated to the expert bulletin. ***

create or replace package PKG_TEST is

  counter number;

  function AddOne return Number;
  procedure DivideAndPrint;

end PKG_TEST;
/
create or replace package body PKG_TEST is

  function AddOne
  return number
  is
    temp number;
  begin
    temp := counter;
    counter := counter + 1;
    return temp;
  end AddOne;

  procedure DivideAndPrint
  is 
    type TableOfNumbers is Table of number;
    divisionResults TableOfNumbers;
  begin
    select LO.OPERAND / AddOne
    bulk collect into divisionResults
    from LEFT_OPERANDS LO
    where AddOne <> -1;

    for i in divisionResults.first .. divisionResults.last loop
      dbms_output.put_line(divisionResults(i));
    end loop;
  end DivideAndPrint;
begin
  counter := 0;
end PKG_TEST;
/


Following is the definition of the table LEFT_OPERANDS, required by the package above, with sample data.
create table LEFT_OPERANDS
(
  OPERAND number not null
);

insert into LEFT_OPERANDS(OPERAND) values(1);
insert into LEFT_OPERANDS(OPERAND) values(2);
insert into LEFT_OPERANDS(OPERAND) values(3);
commit;


Following is the code for the test i performed.
begin
  dbms_output.put_line(PKG_TEST.counter);
  PKG_TEST.DivideAndPrint;
  dbms_output.put_line(PKG_TEST.counter);
end;


And here's the output of the test (immediately after a compilation of the package).
0
1
1
1
4

[Updated on: Sat, 15 March 2008 03:29]

Report message to a moderator

Re: using, in the select-clause, a result calculated in the where-clause [message #306627 is a reply to message #306626] Sat, 15 March 2008 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a feature, there is a local function cache.
You can't know the number of times the function is called, it depends on internal code and on each version and more on patch level.
More if you tell function is deterministic I think you will get less calls, and even less with "with" clause (but maybe not as your function is now not pure: it modifies a package variable).

I will move back this topic to the SQL expert forum.

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306631 is a reply to message #306528] Sat, 15 March 2008 04:12 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Thanks, Michel, for promptly replying to my message, and for according my request to move this thread to the experts forum.

In my opinion, Oracle should provide the developers with a way to indicate explicitly whether a function should be treated as deterministic. If the function is so marked, the Oracle engine should be free to perform caching and indexing optimizations on the function calls. Otherwise, it should execute the function each time it is invoked.

Moreover, Oracle should provide a way of passing the results of intermediate calculations between the clauses of an SQL statement; particularly, from the where-clause of a select-statement to its select-clause.

[Updated on: Sat, 15 March 2008 04:12]

Report message to a moderator

Re: using, in the select-clause, a result calculated in the where-clause [message #306634 is a reply to message #306631] Sat, 15 March 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle should provide the developers with a way to indicate explicitly whether a function should be treated as deterministic

There is, this is the DETERMINISTIC keyword on CREATE FUNCTION.

Quote:
Moreover, Oracle should provide a way of passing the results of intermediate calculations between the clauses of an SQL statement; particularly, from the where-clause of a select-statement to its select-clause.

This is what it mostly does if the function is deterministic (or it estimates it is when it analyzes the code). But sometimes it has to pass in some code that force it to reevaluate the function.

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306636 is a reply to message #306528] Sat, 15 March 2008 04:38 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Thanks for pointing this out. But the point is that when the function is not explicitly marked as deterministic, the engine should assume it is non-deterministic, and execute it afresh each time it is invoked. As my test demonstrates, and as you stated in your penultimate message on this thread, this is not the case, in general.
Re: using, in the select-clause, a result calculated in the where-clause [message #306637 is a reply to message #306636] Sat, 15 March 2008 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When not specified the deterministic/undeterministic nature is determined by the oracle code analyzer, so it is highly dependent on the version and patch level.
This is the same thing for the number of times the function is actually called during a sql statement. It is for us underministic. Smile
In fact, Jonathan Lewis made numerous tests in different versions and patchsets to know the size of function cache and how it is handled, the conclusion is that it can be known for a version and patchset but the results for one can not be reported to another one and all tests have to be redone to determine the new behaviour at each update/upgrade.

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306640 is a reply to message #306528] Sat, 15 March 2008 05:10 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
put the function int he select clause and factor the query using an inline view or with clause.

That is the only way to guarantee how many times the funciton will get called.

If you put it into the where clause, and rely heavily on how many times it gets called, you will be disappointed.

You can change how many times it gets called in a where clause by simply hinting a query, or by not analyzing a table at the right time. Different explain plan, different number of calls.


Perform your test with a subquery factor and it will probably show you the difference.

you can avoid divide by zero problems by writing the function better to cater for that scenario.


Re: using, in the select-clause, a result calculated in the where-clause [message #306656 is a reply to message #306640] Sat, 15 March 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That is the only way to guarantee how many times the funciton will get called.

This is wrong.
You cannont guarantee anything unless you are the one that wrote SQL engine and optimizer.

Quote:
You can change how many times it gets called in a where clause by...

This is true but you cannot know how you change it, so this is useless.

Quote:
Different explain plan, different number of calls.

Yes and no. Maybe. Noone knows.
You can have same explain plan and different number of calls and different explain plan and same number of calls.


Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306667 is a reply to message #306636] Sat, 15 March 2008 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I reproduced your test case in 10.2.0.3, just changng the function DivideAndPrint to directly return the counter easier to read. In this case and version, the function is only call once per row:
SQL> create or replace package body PKG_TEST is
  2  
  3    function AddOne
  4    return number
  5    is
  6      temp number;
  7    begin
  8      temp := counter;
  9      counter := counter + 1;
 10      return temp;
 11    end AddOne;
 12  
 13    procedure DivideAndPrint
 14    is 
 15      type TableOfNumbers is Table of number;
 16      divisionResults TableOfNumbers;
 17    begin
 18      select AddOne
 19      bulk collect into divisionResults
 20      from LEFT_OPERANDS LO
 21      where AddOne <> -1;
 22  
 23      for i in divisionResults.first .. divisionResults.last loop
 24        dbms_output.put_line(divisionResults(i));
 25      end loop;
 26    end DivideAndPrint;
 27  begin
 28    counter := 0;
 29  end PKG_TEST;
 30  /

Package body created.

SQL> begin
  2    dbms_output.put_line(PKG_TEST.counter);
  3    PKG_TEST.DivideAndPrint;
  4    dbms_output.put_line(PKG_TEST.counter);
  5  end;
  6  /
0
1
2
3
4

PL/SQL procedure successfully completed.

Now if you set the function as DETERMINISTIC, the function is only called once per each call/fetch:
SQL> create or replace package PKG_TEST is
  2  
  3    counter number;
  4  
  5    function AddOne return Number DETERMINISTIC;
  6    procedure DivideAndPrint;
  7  
  8  end PKG_TEST;
  9  /

Package created.

SQL> create or replace package body PKG_TEST is
  2  
  3    function AddOne 
  4    return number
  5    DETERMINISTIC
  6    is
  7      temp number;
  8    begin
  9      temp := counter;
 10      counter := counter + 1;
 11      return temp;
 12    end AddOne;
 13  
 14    procedure DivideAndPrint
 15    is 
 16      type TableOfNumbers is Table of number;
 17      divisionResults TableOfNumbers;
 18    begin
 19      select AddOne
 20      bulk collect into divisionResults
 21      from LEFT_OPERANDS LO
 22      where AddOne <> -1;
 23  
 24      for i in divisionResults.first .. divisionResults.last loop
 25        dbms_output.put_line(divisionResults(i));
 26      end loop;
 27    end DivideAndPrint;
 28  begin
 29    counter := 0;
 30  end PKG_TEST;
 31  /

Package body created.

SQL> begin
  2    dbms_output.put_line(PKG_TEST.counter);
  3    PKG_TEST.DivideAndPrint;
  4    dbms_output.put_line(PKG_TEST.counter);
  5  end;
  6  /
0
1
1
1
2

PL/SQL procedure successfully completed.

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306670 is a reply to message #306667] Sat, 15 March 2008 11:55 Go to previous messageGo to next message
itayb
Messages: 10
Registered: March 2008
Junior Member
Interesting and instructive experiment! But, actually, the output from your tests reveals that the function is called 4 times (rather than 3) in the first case, and 2 times (rather than 1) in the second one. The counter's value at the end indicates the number of times the function has been called. (As a matter of fact, the last assertion is true only when you run the test on a freshly compiled package. Otherwise, it's the difference between the counter's value at the end and its value at the beginning that indicates the number of times the function has been called.)

[Updated on: Sat, 15 March 2008 12:08]

Report message to a moderator

Re: using, in the select-clause, a result calculated in the where-clause [message #306673 is a reply to message #306670] Sat, 15 March 2008 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The one more in the counter is due to the internal way to get the "no more data to fetch" condition.
If you trace the session, you will see there are 2 fetches and not one. This is this second fetch that implies the extra function call.

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #306691 is a reply to message #306528] Sat, 15 March 2008 17:51 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Michael,

Im trying to understand how what this add counter function is doing, is different to how I use functions with package variables to seed tables which need a sequential primary key. I never have a problem with repeated values. The only difference I can see is that you have counter sitting in the package body begin section, instead of a package spec variable.

If you used a package variable in the package spec instead of setting the counter in the package body, I think you would get much more predictable results.

Im not at a terminal, so cant check it out.

[Updated on: Sat, 15 March 2008 17:51]

Report message to a moderator

Re: using, in the select-clause, a result calculated in the where-clause [message #306708 is a reply to message #306691] Sun, 16 March 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The purpose of the counter here is to know how many times the function is called during a query, it has no functional interest.
The real purpose of the function is not counting, the counter has been added to know how many times the function (that can do many things) is called.

Regards
Michel

[Updated on: Sun, 16 March 2008 02:09]

Report message to a moderator

Re: using, in the select-clause, a result calculated in the where-clause [message #306744 is a reply to message #306528] Sun, 16 March 2008 09:00 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
But using DETERMINISTIC tells oracle that for all inputs, the output will be the same, but for this demonstration, it will never be, so you are fooling oracle.

Re: using, in the select-clause, a result calculated in the where-clause [message #306756 is a reply to message #306744] Sun, 16 March 2008 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, DETERMINISTIC means that for the SAME inputs you get the same output, not for ALL inputs.
And right we are fooling Oracle but we don't care for our purpose, there, this one, this very one, for our test. Of course, this MUST NOT be done in real case or you will get unexpected results.
And this is expert forum, so we hope people reading this we'll understand it.

Regards
Michel
Re: using, in the select-clause, a result calculated in the where-clause [message #307106 is a reply to message #306631] Mon, 17 March 2008 18:17 Go to previous messageGo to next message
gamyers
Messages: 3
Registered: October 2007
Location: Sydney, Aus
Junior Member
"But the point is that when the function is not explicitly marked as deterministic, the engine should assume it is non-deterministic"

Actually, Oracle has to assume any function used in SQL is deterministic. If a function is not deterministic, then Oracle cannot guarantee (or assume) that, for a given query and set of data, the results will be the same. How could it decide on a plan if every plan it came up with could give different results ? Which would be the 'right' result ?

I recall back in 8.0 or 8i, if you wanted to use functions in SQL, you had to 'promise' that it neither read nor wrote either database or package state.

The changes between releases have been more about how the function cache operates (eg how many values are cached).
Re: using, in the select-clause, a result calculated in the where-clause [message #307944 is a reply to message #306590] Thu, 20 March 2008 08:18 Go to previous message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
itayb wrote on Fri, 14 March 2008 16:38
Thanks again, Raj. Your ideas are very creative.

There's a delicate nuance in the problem i've posed, though, which your solution fails to address. Namely, the select-clause must not be evaluated for certain records, or else an exception would be thrown (due to an attempt to divide by zero). But according to your solution, a full table scan is undertaken, before the desired records are filtered.


Just a remark : one can always divide by Null in order to avoid an zero divide exception and maybe this permits the function evaluation removal in the where clause

Previous Topic: Insert CR into line
Next Topic: PL/SQL collection with bulk collect
Goto Forum:
  


Current Time: Thu Dec 08 02:14:35 CST 2016

Total time taken to generate the page: 0.09075 seconds