Re: Bulk Collect and Pipelined Table Function

Peter Kallweit wrote:

> Hi all,
>
> I have a table function which selects from a view with a where clause
> defined at runtime. The fetch works with bulk collect.
> Today this function is not pipelined. Therefore, the select where this
> table function is called, is sometimes quite slow.
> That's the function:
>
> FUNCTION F_GET_FILTER_RESULT
> (sFILTER IN VARCHAR2
> )RETURN NUMBERTABLE_TYPE IS
> TYPE RULECURSORTYPE IS REF CURSOR;
> RULE_CURSOR RULECURSORTYPE;
> RESOLVED_RULE NUMBERTABLE_TYPE;
> BEGIN
> OPEN RULE_CURSOR FOR 'SELECT V_NR FROM SHOW_VERSION WHERE ' ||sFILTER;
> FETCH RULE_CURSOR BULK COLLECT INTO RESOLVED_RULE;
> CLOSE RULE_CURSOR;
> EXCEPTION
> WHEN OTHERS THEN
> RESOLVED_RULE := NUMBERTABLE_TYPE();
> END;
> RETURN RESOLVED_RULE;
> END F_GET_FILTER_RESULT;
>
>
> Now I'd like to change this function into a pipelined table function.
> AFAIK, to do the pipelining I have to work with 'pipe row' for each
> single row.
> But I don't have / want the single row, because I fetch with bulk
> collect. How can I resolve this contradiction?
>
> Btw., I'm working with 9.2.0.5 on W2K.
>
>
> Regards
> Peter

First I would suggest that you return to the starting point ... what is it that makes you think a pipelined table function is going to perform faster than a bulk collection in and of itself? I've seen nothing that would support that presumption.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)