Stamatis Zampetakis created HIVE-28827:
------------------------------------------
Summary: 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
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)
