Oracle SQL and PL/SQL : how to minimize execution time of retrieving members of the object (returned by user's function)

I wrote a function to get a number of values in an Oracle view. As functions can't return more then one value, I have used an object (with a signature of 8 numbers). This works, but not fine...

The execution time of a select query (and selecting from view, based on this query) is proportional to retrieved members number, i.e.:

retrie开发者_运维问答ving 1 attribute consumes 1 second (it's equal to retrieve a WHOLE object, but object value is unusable for report), retrieving 2 attributes consumes 2 seconds, and so on...

This looks like Oracle executes PL function to get every member of returned object. I think that function, returning varray(8) of numbers will not solve the problem too: eight implicit calls must be replaced by eight explicit subqueries. Can anybody solve this problem? (Except to rewrite to use a function returning one string, which I will try myself now...)

Here is the type declaration:

create or replace type "ARD"."PAY_FINE_FR_12_" AS object
    (fed1 number
      , reg1 number
      , fed_nach number
      , reg_nach number
      , fed_upl number
      , reg_upl number
      , fed2 number
      , reg2 number);

I will assume you have given meaningful names to your type's attributes. In which case you are returning not eight numbers but four pairs of numbers. This suggests a possible way of improving things. Whether it could actually solve your problem will depend on the precise details of your situation (which you have not provided).

Here is a type representing those number pairs, and a nested table type we can use for array processing.

create or replace type pay_pair as object
    ( pay_cat varchar2(4)
      , fed number
      , reg number )
/

create or replace type pay_pair_nt as table of pay_pair
/

This is a function which populates an array with four pairs of numbers. In the absence of any actual business rule I have plumped for the simplest possible example.

create or replace function get_pay_pairs
    return pay_pair_nt
is
    return_value pay_pair_nt;
begin
    select 
        pay_pair (
            case col1 
                when 1 then 'one'
                when 2 then 'nach'
                when 3 then 'upl'
                when 4 then 'two'
                else null;
            end
            , fed
            , pay )
    bulk collect into return_value
    from v23;
    return return_value;
end;
/

If you need the signature of the original type you can rewrite your function like this:

create or replace function get_pay_fine
    return PAY_FINE_FR_12_
is
    return_value PAY_FINE_FR_12_;
    l_array pay_pair_nt;
begin    
    l_array := get_pay_pairs;
    for i in 1..4 loop
        case l_array(i).pay_cat 
           when 'one' then
               return_value.fed1 := l_array(i).fed;
               return_value.reg1 := l_array(i).reg;
           when 'nach' then
               return_value.fed_nach := l_array(i).fed;
               return_value.reg_nach := l_array(i).reg;
           when 'upl' then
               return_value.fed_upl := l_array(i).fed;
               return_value.reg_upl := l_array(i).reg;            
           else
               return_value.fed2 := l_array(i).fed;
               return_value.reg2 := l_array(i).reg;
           end case;
        end loop;
    return return_value;
end;

I'll repeat, this is a demonstration of available techniques rather than a proposed solution. The crux is how your view supplies the values.