Re: 10g: parallel pipelined table functions with cursor selecting from table(cast(SQL collection)) doesn't work
Date: Thu, 9 Jun 2011 04:34:30 -0700 (PDT)
Message-ID: <1376984a-50a4-4254-9c72-482c88a671fe_at_e14g2000yqa.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...
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"
(http://groups.google.com/group/comp.databases.oracle.server/
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 "
(http://groups.google.com/group/comp.databases.oracle.server/
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