joining streamed pipelined function results with "real" tables

i like the approach for pipelined functions. But i wonder about the performance of joining the streamed result of a pipelined function with a "normal" table. There have been stated performance problems for this here: http://asktom.oracle.com/pls/asktom/f?p=100:11:1635437276096187::::P11_QUESTION_ID:19481671347143 However this is from 2005.

Actually i expect oracle to perform good, if my join is like this:

select something(rec.x, rec.y, rec.z) bulk collect into data   from table(cast (dynData(cur) as table_of_something)) rec,

       account acct
  where acct.acct_id = rec.x;

('dynData(...)' is my pipelined function - invoked with dynmically
created ref cursor. 'account' is the "normal" table.)

I think oracle here should continously stream the records generated by dynData(...) to the 'select' invoking code. Oracle should even be able to actually spawn parallel processing units for a work-share here. I mean, there is NO need (IMHO) that oracle need to have ALL dynData(...)-generated records in order to handle the join logic. That's why 'select' invoking code should be perfectly detached from the pipelined function. But i guess it would be completely different, if i would change the sequence and use:

select something(rec.x, rec.y, rec.z) bulk collect into data   account acct
  from table(cast (dynData(cur) as table_of_something)) rec,   where acct.acct_id = rec.x;

(unless oracle anyway detects option to re-oder for performanceoptimization)

Because picking up records from 'acct' one after another needs knowledge about availabilities of corresponding data from 'rec'.

Can anyone comment here?
(or tell a link for information about this issue)
E.g. i could imagine that other tuning measures in oracle generally require full access to table data for join (but i don't know).

• many thanks!

cheers,
Frank Received on Thu Sep 02 2010 - 14:35:08 CDT