Oracle SQL Revealed: Executing Business Logic in the Database Engine

© Alex Reprintsev 2018

Alex ReprintsevOracle SQL Revealedhttps://doi.org/10.1007/978-1-4842-3372-6_6

6. Recursive Subquery Factoring

Alex Reprintsev¹ 

(1)

London, UK

 

A subquery factoring clause (sometimes also referred to as a “with clause” or CTE – common table expression) was introduced in Oracle 9.2. At that time it did not allow us to define recursive subqueries and was mainly used to decompose the logic into named queries – factor out subqueries and reference them by names in the main query. CBO can decide whether to materialize results of factored out subqueries or plug them in as inline views. In the former case it can improve performance if the named query is referenced multiple times in the main query, while in the latter case it may have ...