Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallA regular Oracle table function builds and returns its complete collection before the query can return rows from it. A pipelined table function can emit rows as it produces them, which may reduce the wait for the first result and avoid materializing the entire collection at once. Pipelining is a design option, not a guaranteed speedup or a switch for parallel execution.
What is a table function?
An Oracle 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.
As an Amazon Associate I earn from qualifying purchases.
The key difference is when the function makes those rows available to the query. A regular table function returns a completed collection; a pipelined table function makes rows available iteratively while processing continues.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →How do regular and pipelined functions differ?
| Aspect | Regular table function | Pipelined table function |
|---|---|---|
| Returned form | Returns a collection of rows. | Declares a collection return type, but emits rows iteratively. |
| When the query can receive rows | After the function has constructed and returned the full collection. | As the function produces rows; it need not finish the full result first. |
| Collection materialization | Must construct the complete collection for return. | Can avoid materializing the entire result collection in the object cache. |
| Implementation cue | Return the collection value. | Declare 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 characterizes pipelining as returning a row to the invoker after processing that row while continuing to process subsequent rows. That describes row availability within SQL execution; it does not mean each PIPE ROW triggers a separate client or network fetch.
#1 Best Overall
What does PIPE ROW do?
In native PL/SQL, PIPE ROW emits a row from the function, but it does not return control to the caller. The function can continue processing and emit more rows. Oracle’s runtime may deliver piped rows in batches, so treat pipelining as incremental production rather than a promise of one-at-a-time delivery to the client. The implementation details are covered in Oracle’s Using Pipelined and Parallel Table Functions guide.
When should you use each form?
Choose a regular table function when
- The function naturally creates a modest collection as a whole.
- Returning one completed collection is the simplest fit for the logic.
Consider a pipelined function when
- The function can generate output rows incrementally.
- Earlier availability of rows or avoiding construction of the full result collection may matter to the workload.
Oracle documents potential response-time and memory benefits, but those benefits do not establish that pipelining will make every query faster. Assess the choice with the actual workload, including how rows are consumed and the cost of producing them. Oracle’s cited material provides no numerical benchmark comparing the two forms.
Rank #2
What does a pipelined function require?
A pipelined function needs the PIPELINED declaration and a supported collection return type. Its body emits rows with PIPE ROW and ends with a RETURN statement without a value. Although a collection type is declared, Oracle notes that a pipelined function returns a SQL user-defined type; collection and element types must meet SQL compatibility requirements. Check the applicable language reference for the Oracle Database version you use before adopting a particular type or syntax.
Does pipelining enable parallel execution?
No. PIPELINED describes how rows are produced; it does not, by itself, make a function run in parallel. Oracle’s Database 12.2 Data Cartridge guide describes parallel execution conditions for a table function: it must have a PARALLEL_ENABLE clause and exactly one REF CURSOR input with a PARTITION BY clause. These are version-specific implementation requirements, so verify them against the documentation for the database release in use before configuring production workloads.
What consistency limitation should you know about?
Oracle cautions that read consistency for table data does not apply to mutable PL/SQL collection variables in the same way. If a pipelined or regular function relies on a collection that can change while it is being used, do not assume table read-consistency behavior protects that collection. Consult the consistency discussion in Oracle’s PL/SQL Optimization and Tuning documentation when designing around mutable collection state.
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.
