OTN Discussion Forums : Functions in where clauses vs. in ...

Martin T.

Posts: 28
Registered: 07/22/05

Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 4:02 AM

Hello all.
(assuming Oracle 9.2)

First of all, does anyone know why the Oracle SQL Parser will not allow
us to use the <name>-part of "table.column AS <name>" in where or
group-by clauses?? Is this just being lazy on their part, or is there
an actual reason behind this?

Second, and relating to this I am confused as to how ORACLE handles
group-by clauses with functions. (Or rather I find it a bit
inconsistent.)
a)
select my_func(col1), col2
from a_table
where my_func(col1) = :filter;
==> This will evaluate my_func() two(2) times for every row in the
table.

b)
select my_func(col1), sum(col2)
from a_table
group by my_func(col1);
==> This will NOT evaluate the function twice(2) for every result row,
instead it is EXACTLY the same as when I would write:
c = b)
select col1_func, sum(col2)
from (select my_func(col1) col1_func, col2)
group_by col1_func

Consider also the attached example to further confuse the issue.

Any opinions, pointers, corrections on this are most welcome.

cheers,
Martin

** small example **
CREATE OR REPLACE
PACKAGE TEST_PACKAGE
IS
global_counter NUMBER;
END TEST_PACKAGE;
/

CREATE OR REPLACE
FUNCTION TEST_PUT(x IN VARCHAR2)
RETURN VARCHAR2 IS
ret VARCHAR2(100) := x || test_package.global_counter;
BEGIN
dbms_output.put_line(ret);
test_package.global_counter := NVL(test_package.global_counter, 0) +
1;
return ret;
END;
/

-- Test_Put will be evaluated 2 times(!):
select test_put('fn_called') my_string, COUNT(object_name)
from all_objects
where rownum <= 2
group by test_put('fn_called')

-- Test_Put will be evaluated 4 times(!):
select test_put('fn_called') my_string, COUNT(object_name)
from all_objects
where rownum <= 2
group by test_put('fn_call')

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 4:12 AM

in response to: Martin T.

Reply

First of all, does anyone know why the Oracle SQL
Parser will not allow
us to use the <name>-part of "table.column AS <name>"
in where or
group-by clauses?? Is this just being lazy on their
part, or is there
an actual reason behind this?

The aliases assigned to columns are done so upon the conclusion of the result set being built. The result set is built based on your where clause, and at that time the aliases are not assigned to the columns. It's not Oracle being lazy, it's the way RDBMS' work. You're asking for your results to be labelled in a certain way when the data is presented back to you. Oracle (or any RDBMS) doesn't want to work with your names internally as that would impact performance.

Second, and relating to this I am confused as to how
ORACLE handles
group-by clauses with functions. (Or rather I find it
a bit
inconsistent.)
a)
select my_func(col1), col2
from a_table
where my_func(col1) = :filter;
==> This will evaluate my_func() two(2) times for
every row in the
table.

b)
select my_func(col1), sum(col2)
from a_table
group by my_func(col1);
==> This will NOT evaluate the function twice(2) for
every result row,
instead it is EXACTLY the same as when I would
write:
c = b)
select col1_func, sum(col2)
from (select my_func(col1) col1_func, col2)
group_by col1_func

Can you give some data examples of what you mean?

Consider also the attached example to further confuse
the issue.

Any opinions, pointers, corrections on this are most
welcome.

cheers,
Martin

** small example **
CREATE OR REPLACE
PACKAGE TEST_PACKAGE
IS
global_counter NUMBER;
D TEST_PACKAGE;
/

CREATE OR REPLACE
FUNCTION TEST_PUT(x IN VARCHAR2)
RETURN VARCHAR2 IS
ret VARCHAR2(100) := x ||
test_package.global_counter;
EGIN
dbms_output.put_line(ret);
test_package.global_counter :=
NVL(test_package.global_counter, 0) +
1;
return ret;
D;
/

-- Test_Put will be evaluated 2 times(!):
select test_put('fn_called') my_string,
COUNT(object_name)
from all_objects
where rownum <= 2
group by test_put('fn_called')

-- Test_Put will be evaluated 4 times(!):
select test_put('fn_called') my_string,
COUNT(object_name)
from all_objects
where rownum <= 2
group by test_put('fn_call')

Not quite sure what you mean by "evaluated" 4 times.
From what I can see without testing, the first time you call it, you are incrementing the "global" variable within the package twice, and then the second time it is already starting at 2 and increment again by another 2 so this 4.

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 4:14 AM

in response to: BluShadow

Reply

SQL> CREATE OR REPLACE
  2  PACKAGE TEST_PACKAGE
  3  IS
  4  global_counter NUMBER;
  5  END TEST_PACKAGE;
  6  / 
 
Package created.
 
SQL>
SQL> CREATE OR REPLACE
  2  FUNCTION TEST_PUT(x IN VARCHAR2)
  3  RETURN VARCHAR2 IS
  4  ret VARCHAR2(100) := x || test_package.global_counter;
  5  BEGIN
  6  dbms_output.put_line(ret);
  7  test_package.global_counter := NVL(test_package.global_counter, 0) +
  8  1;
  9  return ret;
 10  END;
 11  / 
 
Function created.
 
SQL> select test_put('fn_called') my_string, COUNT(object_name)
  2  from all_objects
  3  where rownum <= 2
  4  group by test_put('fn_called')
  5  / 
 
MY_STRING
---------------------------------------------------------------------------
 
COUNT(OBJECT_NAME)
------------------
fn_called
                 1
 
fn_called1
                 1
 
 
SQL> / 
 
MY_STRING
---------------------------------------------------------------------------
 
COUNT(OBJECT_NAME)
------------------
fn_called2
                 1
 
fn_called3
                 1
 
 
SQL>

Works as I'd expect it.

Martin T.

Posts: 28
Registered: 07/22/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 4:24 AM

in response to: BluShadow

Reply

First of all, does anyone know why the Oracle SQL
Parser will not allow
us to use the <name>-part of "table.column AS

<name>"

in where or
group-by clauses?? Is this just being lazy on

their

part, or is there
an actual reason behind this?

The aliases assigned to columns are done so upon the
conclusion of the result set being built. The result
set is built based on your where clause, and at that
time the aliases are not assigned to the columns.
It's not Oracle being lazy, it's the way RDBMS'
work. You're asking for your results to be labelled
in a certain way when the data is presented back to
you. Oracle (or any RDBMS) doesn't want to work
with your names internally as that would impact
performance.

Thank you for this explanation, but somehow I can't see this as an internal problem. In fact the engine needn't know about it, as long as the parser would allow me to use this. The parser could happily replace my column aliases by the actual expression if that would save me from typing (e.g. ) complex decode-expression in the select AND in the where (or group by) clause.

(...snipped...)
group by test_put('fn_called')

-- Test_Put will be evaluated 4 times(!):
select test_put('fn_called') my_string,
COUNT(object_name)
from all_objects
where rownum <= 2
group by test_put('fn_call')

Not quite sure what you mean by "evaluated" 4 times.
From what I can see without testing, the first time
you call it, you are incrementing the "global"
variable within the package twice, and then the
second time it is already starting at 2 and increment
again by another 2 so this 4.

Note group by test_put('fn_called') versus group by test_put('fn_call') ... if the group by expression is exactly the same as the select expression, the groupy by clause will not be evaluated separately, whereas if it is not the same, it will be (it has to).

Martin T.

Posts: 28
Registered: 07/22/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 4:32 AM

in response to: BluShadow

Reply

[code]

MY_STRING

COUNT(OBJECT_NAME)

fn_called2
1
1
de]

Works as I'd expect it.

Yes, probably this is what one'd expect. My question was rather the why

Try this:
select test_put('fn_called') my_string, object_name
from all_objects
where rownum <= 2
and test_put('fn_called') like 'fn_called%'

... here the function is executed 3 times on my box ... why not 2 or 4 times?

David Tyler

Posts: 1,872
Registered: 06/14/00

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 5:31 AM

in response to: Martin T.

Reply

Hello

Hopefully this will help to explain:


SQL> create or replace package test_package
  2  is
  3     counter_select  number;
  4     counter_where   number;
  5  end;
  6  / 
 
Package created.
 
SQL> create or replace function test_func_select
  2  return varchar2
  3  is
  4  begin
  5
  6     test_package.counter_select := NVL(test_package.counter_select,0) + 1;
  7
  8     dbms_output.put_line('test_func_select Call # '||TO_CHAR(test_package.counter_select));
  9
 10     return 'Call # '||TO_CHAR(test_package.counter_select);
 11
 12  end;
 13  / 
 
Function created.
 
SQL>
SQL> create or replace  function test_func_where
  2  return varchar2
  3  is
  4  begin
  5
  6     test_package.counter_where := NVL(test_package.counter_where,0) + 1;
  7
  8     dbms_output.put_line('test_func_where Call # '||TO_CHAR(test_package.counter_where));
  9
 10     return 'Call # '||TO_CHAR(test_package.counter_where);
 11
 12  end;
 13  / 
 
Function created.
 
SQL> set serveroutput on
SQL>
SQL> SELECT
  2     test_func_select,
  3     count(*)
  4  FROM
  5     dt_test_tab
  6  WHERE
  7     rownum <=2
  8  GROUP BY
  9     test_func_select
 10  / 
 
TEST_FUNC_SELECT                 COUNT(*)
------------------------------ ----------
Call # 2                                1
Call # 1                                1
 
test_func_select Call # 1
test_func_select Call # 2
SQL> exec test_package.counter_select:=NULL;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT
  2     test_func_select
  3  FROM
  4     dt_test_tab
  5  WHERE
  6     rownum <=2
  7  AND
  8     test_func_where LIKE 'Call #%'
  9  / 
 
TEST_FUNC_SELECT
------------------------------
Call # 1
Call # 2
 
test_func_where Call # 1
test_func_select Call # 1
test_func_select Call # 2
SQL>
SQL> create or replace function test_func_select(av_param in varchar2)
  2  return varchar2
  3  is
  4  begin
  5
  6     test_package.counter_select := NVL(test_package.counter_select,0) + 1;
  7
  8     dbms_output.put_line('test_func_select Call # '||TO_CHAR(test_package.counter_select));
  9
 10     return 'Call # '||TO_CHAR(test_package.counter_select);
 11
 12  end;
 13  / 
 
Function created.
 
SQL>
SQL> create or replace  function test_func_where(av_param in varchar2)
  2  return varchar2
  3  is
  4  begin
  5
  6     test_package.counter_where := NVL(test_package.counter_where,0) + 1;
  7
  8     dbms_output.put_line('test_func_where Call # '||TO_CHAR(test_package.counter_where));
  9
 10     return 'Call # '||TO_CHAR(test_package.counter_where);
 11
 12  end;
 13  / 
 
Function created.
 
SQL> exec test_package.counter_select:=NULL;
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> SELECT
  2     test_func_select(object_name) test_func_select,
  3     count(*)
  4  FROM
  5     dt_test_tab
  6  WHERE
  7     rownum <=2
  8  GROUP BY
  9     test_func_select(object_name)
 10  / 
 
TEST_FUNC_SELECT                 COUNT(*)
------------------------------ ----------
Call # 2                                1
Call # 1                                1
 
test_func_select Call # 1
test_func_select Call # 2
SQL> exec test_package.counter_select:=NULL;
 
PL/SQL procedure successfully completed.
 
SQL> exec test_package.counter_where:=NULL;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT
  2     test_func_select(object_name) test_func_select
  3  FROM
  4     dt_test_tab
  5  WHERE
  6     rownum <=2
  7  AND
  8     test_func_where(object_name) LIKE 'Call #%'
  9  / 
 
TEST_FUNC_SELECT
------------------------------
Call # 1
Call # 2
 
test_func_where Call # 1
test_func_select Call # 1
test_func_where Call # 2
test_func_select Call # 2

In the fist example, oracle only has to call the function once to satisfy the predicate as it is not technically being applied to the table. There could be 1million rows but if the function did not return a string in the form Call #, none of the result set would be materialised as the predicate had failed. In the second example, the predicate is being applied to the table as we are passing a column from the result set to the function. This means that the where clause function has to be called once for every row in the result set.

HTH

David

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 5:32 AM

in response to: Martin T.

Reply

What version of 9.2 are you using?

Thank you for this explanation, but somehow I can't see this as an internal ?
problem. In fact the engine needn't know about it, as long as the parser would
allow me to use this. The parser could happily replace my column aliases by
the actual expression if that would save me from typing (e.g. ) complex decode
-expression in the select AND in the where (or group by) clause.

Well there's no pleasing everyone. You find me a RDBMS that does it.

SQL> select test_put('fn_called') my_string, object_name
  2  from all_objects
  3  where rownum <= 2
  4  and test_put('fn_called') like 'fn_called%' ;
 
MY_STRING
--------------------------------------------------------
 
OBJECT_NAME
------------------------------
fn_called9
DUAL
 
fn_called10
SYSTEM_PRIVILEGE_MAP
 
 
SQL>

2 times for me. (I'm on 10g so it could be you're running a buggy version of 9.2)

Message was edited by:
blushadow

Martin T.

Posts: 28
Registered: 07/22/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 6:07 AM

in response to: BluShadow

Reply

What version of 9.2 are you using?

I'm using Release 9.2.0.1.0

Thank you for this explanation, but somehow I can't

see this as an internal ?

problem. In fact the engine needn't know about it,

as long as the parser would

allow me to use this. The parser could happily

replace my column aliases by

the actual expression if that would save me from

typing (e.g. ) complex decode

-expression in the select AND in the where (or

group by) clause.

Well there's no pleasing everyone. You find me a
RDBMS that does it.

PostGreSQL does it as far as I'm aware (at least for group by) ... of course it will not do a bazillion of things Oracle can do.

SQL> select test_put('fn_called') my_string,
object_name
  2  from all_objects
3  where rownum <= 2
  4  and test_put('fn_called') like 'fn_called%' ;
MY_STRING
------------------------------------------------------
--
 
OBJECT_NAME
------------------------------
fn_called9
DUAL
 
fn_called10
SYSTEM_PRIVILEGE_MAP
 
 
SQL>

2 times for me. (I'm on 10g so it could be you're
running a buggy version of 9.2)

I just tried on my 10XE box ... it's also executed 3 times there (Just as David explained)

SQL> select test_put('fn_called') my_string, object_name
  2  from all_objects
  3  where rownum <= 2
  4    and test_put('fn_called') like 'fn_called%'
  5  ;
 
MY_STRING     OBJECT_NAME
--------------------------------------------------
fn_called3         ICOL$
fn_called4         I_USER1
 
fn_called2
fn_called3
fn_called4
SQL>

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 6:20 AM

in response to: Martin T.

Reply

Ah, now I see what you're saying. I didn't have serveroutput switched on so I wasn't seeing the DBMS_OUTPUT messages. I'll have a think about it.

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 6:22 AM

in response to: BluShadow

Reply

Well the first output line is from it evaluating the where clause and the other two outputs are from it retrieving the two rows. Simple.

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 6:23 AM

in response to: BluShadow

Reply

And before you say it, the where clause call for that function only has to be evaluated once because it doesn't reference anything on the selected table.

BluShadow

Posts: 12,879
Registered: 09/21/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 6:24 AM

in response to: BluShadow

Reply

If it does reference something from the table then it has to evaluate for each row...

  1  select test_put('fn_called') my_string, object_name
  2  from all_objects
  3  where rownum <= 2
  4*   and test_put(object_name) like object_name||'%'
SQL> / 
 
MY_STRING
--------------------------------------------------------
 
OBJECT_NAME
------------------------------
fn_called18
DUAL
 
fn_called20
SYSTEM_PRIVILEGE_MAP
 
 
DUAL17
fn_called18
SYSTEM_PRIVILEGE_MAP19
fn_called20
SQL>

So I get 4 outputs in this case.

Martin T.

Posts: 28
Registered: 07/22/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 9:05 AM

in response to: BluShadow

Reply

If it does reference something from the table then it
has to evaluate for each row...

(...)

You know, the really interesting thing here is that the function does not reference anything in this table, even for the select expression, so Oracle could also evaluate it just once there ... yes, I know this is not the way things work

(Except if we use a deterministic function (or whatever this is called exactly).)

The main reason why I started this thread was to discuss that Oracle will evaluate the function for the where clause but it will not evaluate the function for the group by clause ... while I think this is a good thing, it still seems a bit strange.

Does anyone know a book/documentation where such things are discussed in detail? (The O SQL Reference is not really much help in this regard it seems.)

cheers,
Martin

David Tyler

Posts: 1,872
Registered: 06/14/00

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 6, 2006 9:17 AM

in response to: Martin T.

Reply

Hello

Well I'm not sure about documentation but effectively there is no real reason why oracle should evaluate the function for both the select and the group by. All group by does is apply the aggregate function for each distinct value/combination of values specified in the select statement. The fact that one of those values is supplied by a function really doesn't make any difference as that value needs to be extracted once and is then free to be used in the group by.

A deterministic function allows oracle to effectively cache values being returned for given function calls, but it is not true that it will be called only once for each distict set of input parameters. The algorithm behind the deterministic optimisations in 10gr2 does not appear to be completely straight forward to say the least!

HTH

David

Martin T.

Posts: 28
Registered: 07/22/05

Re: Functions in where clauses vs. in group-by ... inconsistent?
Posted: Oct 7, 2006 3:48 PM

in response to: David Tyler

Reply

Hello

Well I'm not sure about documentation but effectively
there is no real reason why oracle should evaluate
the function for both the select and the group by.
All group by does is apply the aggregate function
for each distinct value/combination of values
specified in the select statement. The fact that
one of those values is supplied by a function really
doesn't make any difference as that value needs to
be extracted once and is then free to be used in the
group by.

I didn't mean the aggregate function, I meant a function used in the grouped-by columns.

select func_a(col1), sum(col2) from table group by func_a(col1)

--> func_a will only be invoked for the select expression, not for the group-by expression.

select func_a(col1), sum(col2) from table group by func_b(col1)

--> func_b will (has to) be invoked for the group-by expression.

select func_a(col1), col2 from table where func_a(col1) = :something

--> func_a will be invoked for the where clause as well as for each row.

A deterministic function allows oracle to effectively
cache values being returned for given function calls,
but it is not true that it will be called only once
for each distict set of input parameters. The
algorithm behind the deterministic optimisations in
10gr2 does not appear to be completely straight
forward to say the least!

Straightforward? I think there is about nothing in Oracle that is straighforward

cheers,
Martin