Re: 10g: parallel pipelined table functions with cursor selecting from table(cast(SQL collection)) doesn't work
Date: Thu, 9 Jun 2011 05:00:04 -0700 (PDT)
Message-ID: <d675c455-d090-4102-a2bf-b3a215c41b8e_at_w4g2000yqm.googlegroups.com>
On 9 Jun., 12:12, Randolf Geist <mah..._at_web.de> wrote:
> On Jun 8, 9:00 am, Frank Bergemann <FBergem..._at_web.de> 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:http://oracle-randolf.blogspot.com/
>
> Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
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:
- 'select for update' by main session and collect result in SQL collection.
- 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" :-)
regards,
Frank
Received on Thu Jun 09 2011 - 07:00:04 CDT