Re: 10g: chaining pipelined functions is slow
Date: Sat, 9 Oct 2010 13:58:19 -0700 (PDT)
Message-ID: <17d2ef80-0468-43db-8956-67250f88e13b_at_e14g2000yqe.googlegroups.com>
On Oct 8, 4:42 pm, Frank Bergemann <FBergem..._at_web.de> wrote:
> Hi,
>
> i use a chain of pipelined functions to separate an entire task into a
> sequence of processing stages - like this:
> (abstract, just pseudo-code, i'll try to post some working pl/sql code
> next - but i hope you get the point)
>
> Could s.o. confirm this?
> Do i miss some additional option/flag/parameter for setting up
> pipelined functions?
> Or is this something not supported by 10g but will be by > 10g?
>
> Or just the wrong approach(?!)
>
> - many thanks!
>
> cheers,
> Frank
I think your slow-down could be simply caused by the fact that each of your pipelined table functions use single-row processing by using the OPEN LOOP FETCH CLOSE pattern, but your collapsed one does this only once. May be you want to try the same when using more efficient bulk processing (BULK INTO ...) when fetching from the cursor.
By the way, the recommended way cascaded pipelined table functions are supposed to be designed is to pass a cursor as parameter into the function - that way you can write something like
select * from table(f(cursor(select * from table(g(cursor(select * from table(h(cursor(some_query)))))))))
where each pipelined table function produces the output that is consumed by the next table function as input - this can be even performed in parallel using parallel execution and corresponding declaration of the table function and its input cursor (parallel_enable partition by ...)
For more information about the concepts, see e.g. here:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/tuning.htm#LNPLS915
More details on parallel processing:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10765/pipe_paral_tbl.htm#i1004978
This is taken from the 11.2 docs but most of it applies to your 10.2 version as well.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sat Oct 09 2010 - 15:58:19 CDT