[
https://issues.apache.org/jira/browse/HIVE-28827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17936544#comment-17936544
]
Stamatis Zampetakis commented on HIVE-28827:
--------------------------------------------
This enhancement would be extremely useful also when CBO is used to rewrite a
query to use common table expressions (HIVE-28259) since in many cases the
plans of discovered CTEs are identical to parts of the main query plan.
> Enhance materialized view rewrites by considering plan equivalence
> ------------------------------------------------------------------
>
> Key: HIVE-28827
> URL: https://issues.apache.org/jira/browse/HIVE-28827
> Project: Hive
> Issue Type: Improvement
> Components: CBO
> Reporter: Stamatis Zampetakis
> Priority: Major
>
> Consider the following materialized view definition that is a union with
> three branches over table t1.
> {code:sql}
> create table t1(col0 int) STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> create materialized view mat1 as
> select col0 from t1 where col0 = 1
> union all
> select col0 from t1 where col0 = 2
> union all
> select col0 from t1 where UPPER('tag') = 'gat';
> {code}
> Observe that the last branch of the union does not have any results since the
> WHERE clause is always false and the optimizer is able to detect this case
> and drop the branch completely.
> Now consider the following query over t1 which could technically take
> advantage of the existing view mat1.
> {code:sql}
> select col0 from t1 where col0 = 1
> union all
> select col0 from t1 where col0 = 2;
> {code}
> The plan for the query below is in fact identical to the plan of the view
> definition but at the moment the query cannot be rewritten to use the
> existing materialized view.
> The goal of this ticket is to enhance the materialized view rewrite logic to
> be able to detect when a part of the query is identical to an existing view
> and do the rewrite.
> This is very similar in spirit with the text based materialized view rewrite
> feature (HIVE-25240/HIVE-24274) but operates on the plan level instead of
> the SQL level. Note that text based rewrite cannot be used in the scenario
> above since at the AST/SQL level we cannot derive that one of the union
> branches is redundant.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
