Re: 10g: parallel pipelined table functions with cursor selecting from table(cast(SQL collection)) doesn't work

From: Frank Bergemann <>
Date: Thu, 9 Jun 2011 04:34:30 -0700 (PDT)
Message-ID: <>

On 9 Jun., 12:12, Randolf Geist <> wrote:
> On Jun 8, 9:00 am, Frank Bergemann <> wrote:
> > i try to distribute SQL data objects - stored in a TABLE OF <SQL
> > object-Type> - to MULTIPLE (parallel) instances of a table function,
> > by passing a select CURSOR(...) to the table function, which selects
> > from the  SQL TABLE OF storage  via "select * from
> > TABLE(CAST(<storage> as <storage-type>)".
> > Could it be, that this is due to the fact, that my data are not
> > globally available, but only in the main thread data?
> > Can someone confirm, that it's NOT possible to start multiple parallel
> > table functions for selecting on SQL data type TABLE OF <object>
> > storages?
> It is very likely that Oracle requires a "parallelized" source for the
> parallel_enabled table function to be executed in parallel, so a
> "serial" to "parallel" distribution is probably not supported out of
> the box in such a case.
> I have kind of a deja-vu - haven't we discussed a similar issue raised
> by you not too long ago on this list?
> Hope this helps,
> Randolf
> Oracle related stuff blog:
> Co-author of the "OakTable Expert Oracle Practices" book:

Hi Randolf,

no, this here is a new issue (for me).

I had problems with dynamic SQL cooperating with table functions. That i solved with dynamically creating static SQL objects (with code) and delegate to those.
See this: "dynamically created cursor doesn't work for parallel pipelined functions"
( browse_frm/thread/c139fe298bcb2c4f/734468085369e6af? hl=de&lnk=gst&q=dynamically+created+cursor#734468085369e6af)

And we both had another discussion about "10g: chaining pipelined functions is slow "
( browse_frm/thread/4f668e23ba263e21/7a09ea4fa4c986cb? hl=de&lnk=gst&q=bergemann#7a09ea4fa4c986cb). For that you pointed me to AQ.
However i need to check for this one again - if the slow execution, which i recognized, wasn't due to missing /*+ first_rows */ hint respectively avoiding things like 'order by' in the base select and 'cluster' for the table function.

This entry here is for setting up parallel pipelined table function for a SQL select done on a SQL collection data type (via TABLE(CAST(... as ...))).

best regards,
Frank Received on Thu Jun 09 2011 - 06:34:30 CDT

Original text of this message