Recursive Subquery Factoring

  • Alex Reprintsev


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 a negative impact on the performance because the transformation engine doesn’t treat named queries in the same way as inline views. For example, on older versions Oracle could have merged an inline view but not the named query with exactly the same text.

Copyright information

© Alex Reprintsev 2018

Authors and Affiliations

  • Alex Reprintsev
    • 1
  1. 1.LondonUK

Personalised recommendations