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 05:00:04 -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:

Pls. see my 'sqlplus' example for entry #4 of this thread. The problem actually results from some another issues of table functions:

My application has a #2 steps/stages data selection. A 1st select for minimal context base data - mainly to evaluate for DUE driving data records.
And a 2nd select for all the "real" data to process a context (joining much more other tables here, which i don't want to do for non-due records).
So it's doing stage #1 select first, then stage #2 select - based on stage #1 results - next.

The first implementation of the application did the stage #1 select in the main session of the pl/sql code.
And for the stage #2 select there was done a dispatch to multiple parallel table functions (in multiple worker sessions) for the "real work".
That worked.

However there was a flaw:
Between records from stage #1 selection and records from stage #2 selection there is a 1:n relation (via key / foreign key relation). Means, for #1 resulting record from stage #1 selection, there are #x records from stage #2 selection.
That forced me to use "cluster curStage2 by (theKey)". Because the worker sessions need to evaluate the all-over status for a context of #1 record from stage #1 and #x records from stage #2 (so it needs to have #x records of stage #2 together). This then resulted in delay for starting up the worker sessions (i didn't find a way to get rid of this).

So i wanted to shift the invocation of the worker sessions to the stage #1 selection.
Then i don't need the "cluster curStage2 by (theKey)" anymore! But: i also need to do an update of the primary driving data! So the stage #1 select is a 'select ... for update ...'. But you can't use such in CURSOR for table functions (which i can understand, why it's not possible).
So i have to do my stage #1 selection in two steps:

  1. 'select for update' by main session and collect result in SQL collection.
  2. pass collected data to parallel table functions

And for 2. i recognized, that it doesn't start up multiple parallel table function instances.

As a work-around - if it's just not possible to start multiple parallel pipelined table functions for dispatching from 'select * from TABLE(CAST(... as ...))' - i need to select again on the base tables - driven by the SQL collection data.
But before i do so, i wanted to verify, if it's really not possible. Maybe i just miss a special oracle hint or whatever you can get "out of another box" :-)

Frank Received on Thu Jun 09 2011 - 07:00:04 CDT

Original text of this message