Hi,

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/...4 81671347143
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 performance-
optimization)
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

Adrian Billington has written some great stuff wrt pipelined table
functions and performance.

A couple of things that you essentially touch on:
- accurate estimates to the optimizer for the pipelined function.
There are a number of different options, depending on version.
- parallelising the pipelined function

See here:
http://www.oracle-developer.net/display.php?id=427
http://www.oracle-developer.net/display.php?id=429

Hi!

Frank Bergemann wrote:

As of 9i you do not need the CAST ... AS any more.
Select something from TABLE(my_table_function(arg1,arg2)) will be fine.

Regards

Gunter in Orlando, Fla