(PL)SQL - Pipelined Table Functions

A	B	C
expected: A	expected: C	expected: B

Not everybody likes pipelined table functions, but I do - when used in moderation of course, like most things :-)

And although I have been using pipelined table functions for years, it turns out it has a "feature" (illustrated in the example report above - see code below) that completely caught me by surprise the other day.
When you fill your table of records, Oracle ignores the field/column names completely. Instead, it just fills them in the same order as in the (cursor) select. So if, like me, you happen to swap B and C without noticing, you can spend half an hour debugging something so small it's almost invisible...

When using pipelined table functions, you may run into a situation where the optimizer doesn't choose the correct execution path, because it has no idea how many rows the pipelined table function will return.
If you know beforehand that the pipelined table function will return a limited number of rows, you can use the Cardinality hint to get the correct execution path.

A good example of how to use this, in this case for Apex (Interactive) Reports, is given here. I used this hint recently to reduce the execution time of an IR from over 5 minutes to under 5 seconds...
Some more detailed info on the hint itself can be found here.

Region

Identification
	Sequence	20
	Title	Field/Column names
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	select t.* from table(ted_p1141.fn_type_test_p) t

Package

Package
	Name	TED_P1141
	Source	package ted_p1141 is type tr_result is record( a varchar2(100) ,b varchar2(100) ,c varchar2(100)); type tt_result is table of tr_result; function fn_type_test_p return tt_result pipelined; end ted_p1141;

Package Body

Package Body
	Name	TED_P1141
	Source	package body ted_p1141 is function fn_type_test_p return tt_result pipelined is cursor c_results is select 'expected: A' as a ,'expected: C' as c ,'expected: B' as b from dual; begin for r_results in c_results loop pipe row(r_results); end loop; return; end fn_type_test_p; end ted_p1141;

Field/Column names

Column Actions

Cardinality hint

Code

Region

Package

Package Body