You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by JiaTao Tao <ta...@gmail.com> on 2021/04/27 13:51:25 UTC
Do we have plans to do more work about CTE
Seems we have very limited optimize about CTE current, do we have any plan
to improve this?
Regards!
Aron Tao
Re: Do we have plans to do more work about CTE
Posted by Julian Hyde <jh...@gmail.com>.
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 <ta...@gmail.com> 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 <jh...@gmail.com> 于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 <ta...@gmail.com> wrote:
>>>
>>> Seems we have very limited optimize about CTE current, do we have any
>> plan
>>> to improve this?
>>>
>>>
>>> Regards!
>>>
>>> Aron Tao
>>
>>
Re: Do we have plans to do more work about CTE
Posted by JiaTao Tao <ta...@gmail.com>.
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 <jh...@gmail.com> 于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 <ta...@gmail.com> wrote:
> >
> > Seems we have very limited optimize about CTE current, do we have any
> plan
> > to improve this?
> >
> >
> > Regards!
> >
> > Aron Tao
>
>
Re: Do we have plans to do more work about CTE
Posted by Julian Hyde <jh...@gmail.com>.
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 <ta...@gmail.com> wrote:
>
> Seems we have very limited optimize about CTE current, do we have any plan
> to improve this?
>
>
> Regards!
>
> Aron Tao