Stamatis Zampetakis created HIVE-29839:
------------------------------------------
Summary: Materialized view rewrite cannot explore all views
Key: HIVE-29839
URL: https://issues.apache.org/jira/browse/HIVE-29839
Project: Hive
Issue Type: Improvement
Components: Materialized views
Affects Versions: 4.2.1
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
The materialized view rewrite algorithm cannot explore the entire search space
(all available views) so in some cases it picks sub-optimal views.
{code:sql}
create materialized view mv1 as
select * from emps where commission > 300;
create materialized view mv2 as
select depts.name as dname, emps.salary
from emps
inner join depts on emps.deptno = depts.deptno
where emps.commission > 300;
explain cbo
select depts.name, sum(emps.salary)
from emps
inner join depts on emps.deptno = depts.deptno
where emps.commission > 300
group by depts.name;
{code}
Observe that both materialized views can be used to answer the aggregate query.
MV2 materializes a bigger part of the query so it should picked by the rewrite
algorithm in preference to MV1.
In current master (commit c56c3f5924c8fba851318087766ba380ed836d06) the rewrite
algorithm picks MV1 which is sub-optimal. Initially, I though there is a
problem with the cost model but that's not the case. Debugging the code shows
that the rewrite to MV1 is applied first and this blocks further rewritings
from happening. In other words, once the MV rules find and apply the first
rewriting subsequent rules cannot complete thus remaining view candidates are
not considered.
The rewrite algorithm relies on the VolcanoPlanner so in theory all possible
rewritings should be examined and the best should be picked according to the
cost model. With the current implementation once a materialized view is picked
it becomes "permanent" and blocks subsequent transformations.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
