Oracle and SQLServer function evaluation in queries

Question

Let's say I have a function call on a select or where clause in Oracle like this:

select a, b, c, dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3)
  from my_table

A similar example can be constructed for MS SQLServer.

What's the expected behavior in each case?

Is the HASH function going to be called once for each row in the table, or DBMS will be smart enough to call the function just once, since it's a function with constant parameters and no side-effects?

Thanks a lot.

Answer 1

The answer for Oracle is it depends. The function will be called for every row selected UNLESS the Function is marked 'Deterministic' in which case it will only be called once.

CREATE OR REPLACE PACKAGE TestCallCount AS
    FUNCTION StringLen(SrcStr VARCHAR) RETURN INTEGER;
    FUNCTION StringLen2(SrcStr VARCHAR) RETURN INTEGER DETERMINISTIC;
    FUNCTION GetCallCount RETURN INTEGER;
    FUNCTION GetCallCount2 RETURN INTEGER;
END TestCallCount;

CREATE OR REPLACE PACKAGE BODY TestCallCount AS
    TotalFunctionCalls INTEGER := 0;
    TotalFunctionCalls2 INTEGER := 0;

    FUNCTION StringLen(SrcStr VARCHAR) RETURN INTEGER AS
    BEGIN
        TotalFunctionCalls := TotalFunctionCalls + 1;
        RETURN Length(SrcStr);
    END;
    FUNCTION GetCallCount RETURN INTEGER AS
    BEGIN
        RETURN TotalFunctionCalls;
    END;

    FUNCTION StringLen2(SrcStr VARCHAR) RETURN INTEGER DETERMINISTIC AS
    BEGIN
        TotalFunctionCalls2 := TotalFunctionCalls2 + 1;
        RETURN Length(SrcStr);
    END;
    FUNCTION GetCallCount2 RETURN INTEGER AS
    BEGIN
        RETURN TotalFunctionCalls2;
    END;

END TestCallCount;




SELECT a,TestCallCount.StringLen('foo') FROM(
    SELECT 0 as a FROM dual
    UNION
    SELECT 1 as a FROM dual
    UNION
    SELECT 2 as a FROM dual
);

SELECT TestCallCount.GetCallCount() AS TotalFunctionCalls FROM dual;

Output:

A                      TESTCALLCOUNT.STRINGLEN('FOO') 
---------------------- ------------------------------ 
0                      3                              
1                      3                              
2                      3                              

3 rows selected


TOTALFUNCTIONCALLS     
---------------------- 
3                      

1 rows selected

So the StringLen() function was called three times in the first case. Now when executing with StringLen2() which is denoted deterministic:

SELECT a,TestCallCount.StringLen2('foo') from(
    select 0 as a from dual
    union
    select 1 as a from dual
    union
    select 2 as a from dual
);

SELECT TestCallCount.GetCallCount2() AS TotalFunctionCalls FROM dual;

Results:

A                      TESTCALLCOUNT.STRINGLEN2('FOO') 
---------------------- ------------------------------- 
0                      3                               
1                      3                               
2                      3                               

3 rows selected

TOTALFUNCTIONCALLS     
---------------------- 
1                      

1 rows selected

So the StringLen2() function was only called once since it was marked deterministic.

For a function not marked deterministic, you can get around this by modifying your query as such:

select a, b, c, hashed
  from my_table
cross join (
  select dbms_crypto.hash(utl_raw.cast_to_raw('HELLO'),3) as hashed from dual
);

Answer 2

For SQL server, it will be evaluated for every single row.

You will be MUCH better off by running the function once and assigning to a variable and using the variable in the query.