Peter Kallweit wrote:
> DA Morgan wrote:
>
>> Peter Kallweit wrote:
>>
>>> DA Morgan wrote:
>>>
>>>> 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.
>>>
>>>
>>>
>>>
>>>
>>> Hi Daniel,
>>>
>>> AFAIK the bulk collect is creating the complete result table in
>>> memory before returning from the function. This consumes a lot of
>>> memory. With typically more then 100K rows in the result table and
>>> multiple users executing this statement, this eats a lot of memory on
>>> the server. And, sadly enough, the machine isn't the biggest.
>>>
>>> I think of something like that: I'm doing the bulk collects in chunks
>>> of 1K rows. These chunks I give direct into the pipeline.
>>> This way, I think, I could combine the advantages of both.
>>>
>>> Regards
>>> Peter
>>
>>
>>
>> You are correct ... but wrapping that functionality inside of a
>> pipelined table function won't change it. What you should look at then
>> is using the LIMIT clause on the bulk collect.
>>
>> http://www.psoug.org
>> click on Morgan's Library
>> click on Bulk Binding
>> search for the keyword LIMIT
>>
>> HTH
>>
>
> Right, using the LIMIT clause gives me the first part of what I want to
> achieve.
> In the second step I would have to go through the table, use 'pipe row'
> on each single value to put it in the pipeline and the make the next round.
> But isn't this handling of each single value loosing performance?
> Something like 'pipe row bulk' would be fine. I think it could be a good
> trade-off.
>
> Regards
> Peter
Instead of piping out rows ... how about piping out an array or ref cursor?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon Feb 28 2005 - 11:04:22 CST
