Oracle: Extract WITH clause Subqueries into Chained Functions Pipelined; Efficiency Comparison
Clash Royale CLAN TAG #URR8PPP Oracle: Extract WITH clause Subqueries into Chained Functions Pipelined; Efficiency Comparison Have legacy complex SQL script in form of WITH A AS (...<SUB_QA>...), B AS (...<SUB_QB>...), C AS (...<SUB_QC>...), ... SELECT ... FROM A LEFT JOIN B LEFT JOIN C LEFT JOIN ... ON .... Trying to extract the SUB_QA, SUB_QB ... into functions, since each of them will be used by multiple scripts; and each of the calling script will pass one or more variable with different values to them (e.g. the 'last_name' in sample code below) . So far, I got the following: Create Sample Data: --------PERSON table------------ DROP TABLE Test_Persons; CREATE TABLE Test_Persons ( PersonID int, LastName varchar2(255), FirstName varchar2(255) ); INSERT INTO Test_Persons (PersonID,LastName,FirstName) values(1,'LN_1','FN_1'); INSERT INTO Test_Persons (PersonID,LastName,Firs...