Data is Data and Control Should be Data, Too—Compiling Iterative Table-valued PL/SQL UDFs into Recursive SQL Code

PL/SQL functions suffer from poor runtime performance due to the frequent context switches that occur between the PL/SQL interpreter and the SQL executor. This switching causes friction that can slow down UDF execution significantly. Table-valued UDFs incur the additional challenge of the efficient treatment of the sizable results they generate. In this paper, we generalize our PL/SQL UDF compilation strategy to also handle such table-valued UDFs. The generated SQL code carefully separates control flow from data flow at runtime. Compiled UDFs efficiently stream their table-valued results (as opposed to UDF variants that need to hold and copy intermediate states in array variables) and thus impose significantly less memory pressure.