iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The key difference is when rows become available: a regular table function builds and returns its full collection before SQL can return rows from it; a pipelined table function emits rows incrementally as it produces them. Pipelining can reduce the wait for the first row and avoid materializing the entire result collection, but it does not guarantee faster execution.
What is a table function in Oracle?
A table function is a user-defined PL/SQL function that returns a collection of rows, such as a nested table or varray, that SQL can query as a table. Oracle describes this in its PL/SQL Optimization and Tuning documentation.
Both regular and pipelined table functions return rows through a collection type. The distinction is whether the function constructs that collection in full or produces rows incrementally.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteHow do regular and pipelined table functions differ?
| Comparison | Regular table function | Pipelined table function |
|---|---|---|
| How rows are produced | Constructs a complete collection, then returns it. | Emits rows as they are produced. |
| When SQL can receive rows | After the result collection has been constructed and returned. | As the function produces rows for consumption. |
| Collection materialization | The full result collection must be constructed. | Can avoid materializing the entire result collection in the object cache. |
| Main implementation cue | Return the collection value. | Declare the function PIPELINED, emit rows with PIPE ROW, and end with a value-less RETURN. |
| Parallel execution | Not implied by being a table function. | Not implied by the PIPELINED keyword. |
Oracle says a pipelined table function returns a row to its invoker after processing it and continues processing rows. In native PL/SQL, PIPE ROW emits a row but does not return control to the caller. The runtime may deliver piped rows in batches, so a PIPE ROW call does not mean that each row is immediately sent to a client or causes a separate fetch.
#1 Best Overall
When should you choose each form?
Use a regular table function when
- The function naturally builds a modest result collection.
- Returning one completed collection is the simplest fit for the implementation.
Consider a pipelined table function when
- The function can produce rows incrementally.
- Earlier availability of rows or avoiding construction of the full collection matters to the workload.
These are design considerations, not a universal tuning rule. Pipelining may improve response time and reduce memory used to materialize the collection, but the result depends on the workload. Oracle’s cited documentation provides qualitative guidance, not a numerical benchmark comparing the two forms; assess the behavior with the actual query and data.
What does a pipelined function require?
A pipelined function must be declared with PIPELINED and return a supported collection type. Its body uses PIPE ROW to emit rows and ends with RETURN without a value. Oracle’s Using Pipelined and Parallel Table Functions guide documents these PL/SQL mechanics.
Rank #2
The declared return type and element type must meet SQL compatibility requirements. Oracle also notes that a pipelined function returns a SQL user-defined type even where its declared return type appears to be a PL/SQL type, so do not assume every PL/SQL collection type is suitable for use from SQL.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteDoes PIPELINED make a function run in parallel?
No. Pipelining controls how rows are emitted; it does not by itself enable parallel execution. Oracle’s 12.2 Data Cartridge guide describes parallel table-function requirements that include a PARALLEL_ENABLE clause and exactly one REF CURSOR input with a PARTITION BY clause. Treat that as version-specific guidance and check the documentation for the Oracle Database version you use before applying it in production.
What consistency caveat applies to collections?
Oracle cautions that read consistency for table data does not apply to mutable PL/SQL collection variables in the same way. If a function operates on a collection that can change during processing, account for that distinction rather than assuming the collection has the same consistency behavior as a queried database table.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
