How to conditionally join a table function in Oracle SQL, which has a primary table row column as an argument, without excessive function calls?

Question

I have the following issue: Every row in my base table have a flag column ('flg' in the listing below) and an function argument column ('arg'). If in a row #N the flag is 'Y', then function has to be called; after, it shall return, let's say, a column (actually a bunch of them, but i'll simplify as much as i can). And finally, the row #N should transform into a sub-table - full join of a row #N and the column, returned by the function. If the flag is 'N' then a result for the row #N shall be as the row itself plus NULL in that "function return column".

So here's an example:

create or replace package SIEBEL.TEST_PACKAGE as
    type ret_type is table of number;
    function testFunc(inp number)
    return ret_type
    pipelined;
end TEST_PACKAGE;
/
create or replace package body SIEBEL.TEST_PACKAGE is
    function testFunc(inp number)
    return ret_type
    pipelined
    is
    i number;
    begin
        dbms_output.put_line('Function call, arg = ' || to_char(inp));
        if (inp is null OR inp = 0) then
            pipe row (null);
        else
            for i in 1..inp loop
                 pipe row (i);
            end loop;
        end if;
    end testFunc;
end TEST_PACKAGE;
/
with base_table as
(
    select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
    union
    select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
)
select * from base_table t0, table(siebel.test_package.testFunc(t0.arg)) t1;

It will return what i actually want:

COL0              | FLG | ARG | COLUMN_VALUE
--------------------------------------------
Shall invoke      |  Y  |  2  |      1
Shall invoke      |  Y  |  2  |      2
Shall not invoke  |  N  |  0  |

The thing is, even for the 'N' flag the function is still called - the database output will show

Function call, arg = 2
Function call, arg = 0

If a real world i have hundreds of records with 'Y' flag, ~100K with 'N'. Alse my actual function is much more complicated and have a lot of stuff in its namespace, so every function call is crucial in terms of perfomance.

What i do want is the database output for the example:

Function call, arg = 2

I could achieve it with

with base_table as
    (
        select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
        union
        select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
    )
select t.*, t1.column_value
    from base_table t, table(SIEBEL.TEST_PACKAGE.testFunc(t.arg)) t1
    where t.flg = 'Y'
union all
select t.*, null as column_value
    from base_table t 
    where t.flg = 'N';

but then all indexes became useless - every 'order by' instruction will take alot to complete.

Please, help me to achieve the desired behaviour of function calls and still to save the primal rows order.

Feel free to ask me if anything is not clear.

Best Regards, Alexey

Answer 1

For flag = "N" the function is not called if you conditionally join

set serveroutput on
with base_table as
(
    select 'Shall invoke' col0, 'Y' flg, '2' arg from dual
    union
    select 'Shall not invoke' col0, 'N' flg, '0' arg from dual
)
select * from base_table t0
left join table( test_package.testFunc(t0.arg) ) t1 on (t0.flg = 'Y');

Script Output

Package created.
Package body created.

COL0             FLG ARG COLUMN_VALUE
---------------- --- --- ------------
Shall invoke     Y   2              1
Shall invoke     Y   2              2
Shall not invoke N   0               

3 rows selected.

Server Output:

Function call, arg = 2