My preferred terminology is to call a WITH clause a WITH clause. (I know that the SQL standard calls it a CTE. But I see CTEs as an optimizer concept, not a piece of syntax.)
During optimization, there are several sources of common table expressions, including WITH clauses, view expansion, code that is copy-pasted from one part of a query to another, and sub-queries of a query that are similar and can be answered using the same query. We introduced the Spool relational operator in CALCITE-481 [1], and it implements my idea of a CTE. There are some links from that case to ideas for how to figure out whether to materialize a CTE as a temporary table. Julian [1] https://issues.apache.org/jira/browse/CALCITE-481 <https://issues.apache.org/jira/browse/CALCITE-481> > On Apr 29, 2021, at 3:01 AM, JiaTao Tao <[email protected]> wrote: > > Hi Julian > Sorry for the late reply. What I mean CTE here stands "Common Table > Expressions", aka with clause. > The main optimization about this is to decide whether to inline CTE or > not(materialize CTE to reuse, if the query uses it multi-time) in a CBO way. > More details can be seen in this paper(Orca team published): Optimization > of Common Table Expressions in MPP Database Systems > > Regards! > > Aron Tao > > > Julian Hyde <[email protected]> 于2021年4月28日周三 上午2:44写道： > >> Can you define CTE? (I know of a few concepts that could be described as >> such.) Give an example SQL query and the kind of optimization you had in >> mind. >> >>> On Apr 27, 2021, at 6:51 AM, JiaTao Tao <[email protected]> wrote: >>> >>> Seems we have very limited optimize about CTE current, do we have any >> plan >>> to improve this? >>> >>> >>> Regards! >>> >>> Aron Tao >> >>
