http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_5.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_5.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_5.q index 1d97325..141c92e 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_5.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_5.q @@ -4,121 +4,121 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n6 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n6 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n6 compute statistics for columns; -create table cmv_basetable_2 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_2_n3 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n3 values (1, 'alfred', 10.30, 2), (3, 'calvin', 978.76, 3); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n3 compute statistics for columns; -CREATE MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE +CREATE MATERIALIZED VIEW cmv_mat_view_n6 ENABLE REWRITE TBLPROPERTIES ('transactional'='true') AS - SELECT cmv_basetable.a, cmv_basetable_2.c - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0; -analyze table cmv_mat_view compute statistics for columns; + SELECT cmv_basetable_n6.a, cmv_basetable_2_n3.c + FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) + WHERE cmv_basetable_2_n3.c > 10.0; +analyze table cmv_mat_view_n6 compute statistics for columns; -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n3 values (3, 'charlie', 15.8, 1); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n3 compute statistics for columns; -- CANNOT USE THE VIEW, IT IS OUTDATED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 join cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -- REBUILD EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n6; -- NOW IT CAN BE USED AGAIN EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 join cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -- NOW AN UPDATE -UPDATE cmv_basetable_2 SET a=2 WHERE a=1; +UPDATE cmv_basetable_2_n3 SET a=2 WHERE a=1; -- INCREMENTAL REBUILD CANNOT BE TRIGGERED EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -- MV CAN BE USED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 join cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -- NOW A DELETE -DELETE FROM cmv_basetable_2 WHERE a=2; +DELETE FROM cmv_basetable_2_n3 WHERE a=2; -- INCREMENTAL REBUILD CANNOT BE TRIGGERED EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -- MV CAN BE USED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 join cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -- NOW AN INSERT -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n3 values (1, 'charlie', 15.8, 1); -- INCREMENTAL REBUILD CAN BE TRIGGERED AGAIN EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n6 REBUILD; -- MV CAN BE USED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 join cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10; +SELECT cmv_basetable_n6.a +FROM cmv_basetable_n6 JOIN cmv_basetable_2_n3 ON (cmv_basetable_n6.a = cmv_basetable_2_n3.a) +WHERE cmv_basetable_2_n3.c > 10.10; -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n6;
http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_dummy.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_dummy.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_dummy.q index c9aeea8..be72d3b 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_dummy.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_dummy.q @@ -7,49 +7,49 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n0 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n0 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n0 compute statistics for columns; -create materialized view cmv_mat_view enable rewrite -as select a, b, c from cmv_basetable where a = 2; +create materialized view cmv_mat_view_n0 enable rewrite +as select a, b, c from cmv_basetable_n0 where a = 2; -select * from cmv_mat_view; +select * from cmv_mat_view_n0; -show tblproperties cmv_mat_view; +show tblproperties cmv_mat_view_n0; create materialized view if not exists cmv_mat_view2 enable rewrite -as select a, c from cmv_basetable where a = 3; +as select a, c from cmv_basetable_n0 where a = 3; select * from cmv_mat_view2; show tblproperties cmv_mat_view2; explain -select a, c from cmv_basetable where a = 3; +select a, c from cmv_basetable_n0 where a = 3; -select a, c from cmv_basetable where a = 3; +select a, c from cmv_basetable_n0 where a = 3; alter materialized view cmv_mat_view2 disable rewrite; explain select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n0 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n0 where d = 3) table2 on table1.a = table2.a); select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n0 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n0 where d = 3) table2 on table1.a = table2.a); explain @@ -59,30 +59,30 @@ alter materialized view cmv_mat_view2 enable rewrite; explain select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n0 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n0 where d = 3) table2 on table1.a = table2.a); select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n0 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n0 where d = 3) table2 on table1.a = table2.a); drop materialized view cmv_mat_view2; explain select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n0 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n0 where d = 3) table2 on table1.a = table2.a); select * from ( - (select a, c from cmv_basetable where a = 3) table1 + (select a, c from cmv_basetable_n0 where a = 3) table1 join - (select a, c from cmv_basetable where d = 3) table2 + (select a, c from cmv_basetable_n0 where d = 3) table2 on table1.a = table2.a); -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_multi_db.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_multi_db.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_multi_db.q index 9927e9d..5934174 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_multi_db.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_multi_db.q @@ -8,38 +8,38 @@ set hive.stats.column.autogather=true; create database db1; use db1; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n7 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n7 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n7 compute statistics for columns; create database db2; use db2; -create materialized view cmv_mat_view enable rewrite -as select a, b, c from db1.cmv_basetable where a = 2; +create materialized view cmv_mat_view_n7 enable rewrite +as select a, b, c from db1.cmv_basetable_n7 where a = 2; -select * from cmv_mat_view; +select * from cmv_mat_view_n7; -show tblproperties cmv_mat_view; +show tblproperties cmv_mat_view_n7; -create materialized view if not exists cmv_mat_view2 enable rewrite -as select a, c from db1.cmv_basetable where a = 3; +create materialized view if not exists cmv_mat_view2_n2 enable rewrite +as select a, c from db1.cmv_basetable_n7 where a = 3; -select * from cmv_mat_view2; +select * from cmv_mat_view2_n2; -show tblproperties cmv_mat_view2; +show tblproperties cmv_mat_view2_n2; create database db3; use db3; explain -select a, c from db1.cmv_basetable where a = 3; +select a, c from db1.cmv_basetable_n7 where a = 3; -select a, c from db1.cmv_basetable where a = 3; +select a, c from db1.cmv_basetable_n7 where a = 3; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_rebuild_dummy.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_rebuild_dummy.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_rebuild_dummy.q index 86e5a1e..1c1a45f 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_rebuild_dummy.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_rebuild_dummy.q @@ -5,145 +5,145 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n1 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n1 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n1 compute statistics for columns; -create table cmv_basetable_2 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_2_n0 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n0 values (1, 'alfred', 10.30, 2), (3, 'calvin', 978.76, 3); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n0 compute statistics for columns; EXPLAIN -CREATE MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE AS - SELECT cmv_basetable.a, cmv_basetable_2.c - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0 - GROUP BY cmv_basetable.a, cmv_basetable_2.c; - -CREATE MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE AS - SELECT cmv_basetable.a, cmv_basetable_2.c - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0 - GROUP BY cmv_basetable.a, cmv_basetable_2.c; +CREATE MATERIALIZED VIEW cmv_mat_view_n1 ENABLE REWRITE AS + SELECT cmv_basetable_n1.a, cmv_basetable_2_n0.c + FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) + WHERE cmv_basetable_2_n0.c > 10.0 + GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; + +CREATE MATERIALIZED VIEW cmv_mat_view_n1 ENABLE REWRITE AS + SELECT cmv_basetable_n1.a, cmv_basetable_2_n0.c + FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) + WHERE cmv_basetable_2_n0.c > 10.0 + GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -- USE THE VIEW EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n0 values (3, 'charlie', 15.8, 1); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n0 compute statistics for columns; -- CANNOT USE THE VIEW, IT IS OUTDATED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -- REBUILD EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n1 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n1 REBUILD; -- NOW IT CAN BE USED AGAIN EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -DELETE FROM cmv_basetable_2 WHERE a = 3; +DELETE FROM cmv_basetable_2_n0 WHERE a = 3; -- CANNOT USE THE VIEW, IT IS OUTDATED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -- REBUILD -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n1 REBUILD; -- NOW IT CAN BE USED AGAIN EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -- IRRELEVANT OPERATIONS -create table cmv_irrelevant_table (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_irrelevant_table_n0 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_irrelevant_table values +insert into cmv_irrelevant_table_n0 values (1, 'alfred', 10.30, 2), (3, 'charlie', 9.8, 1); -analyze table cmv_irrelevant_table compute statistics for columns; +analyze table cmv_irrelevant_table_n0 compute statistics for columns; -- IT CAN STILL BE USED EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n1; -- NOT USED ANYMORE EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; - -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 join cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; + +SELECT cmv_basetable_n1.a +FROM cmv_basetable_n1 JOIN cmv_basetable_2_n0 ON (cmv_basetable_n1.a = cmv_basetable_2_n0.a) +WHERE cmv_basetable_2_n0.c > 10.10 +GROUP BY cmv_basetable_n1.a, cmv_basetable_2_n0.c; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_time_window.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_time_window.q b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_time_window.q index fe3ddeb..492264c 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_time_window.q +++ b/ql/src/test/queries/clientpositive/materialized_view_create_rewrite_time_window.q @@ -4,97 +4,97 @@ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.strict.checks.cartesian.product=false; set hive.materializedview.rewriting=true; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_n3 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable values +insert into cmv_basetable_n3 values (1, 'alfred', 10.30, 2), (2, 'bob', 3.14, 3), (2, 'bonnie', 172342.2, 3), (3, 'calvin', 978.76, 3), (3, 'charlie', 9.8, 1); -analyze table cmv_basetable compute statistics for columns; +analyze table cmv_basetable_n3 compute statistics for columns; -create table cmv_basetable_2 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); +create table cmv_basetable_2_n1 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n1 values (1, 'alfred', 10.30, 2), (3, 'calvin', 978.76, 3); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n1 compute statistics for columns; -- CREATE VIEW WITH REWRITE DISABLED EXPLAIN -CREATE MATERIALIZED VIEW cmv_mat_view TBLPROPERTIES('rewriting.time.window'='300s') AS - SELECT cmv_basetable.a, cmv_basetable_2.c - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0 - GROUP BY cmv_basetable.a, cmv_basetable_2.c; +CREATE MATERIALIZED VIEW cmv_mat_view_n3 TBLPROPERTIES('rewriting.time.window'='300s') AS + SELECT cmv_basetable_n3.a, cmv_basetable_2_n1.c + FROM cmv_basetable_n3 JOIN cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) + WHERE cmv_basetable_2_n1.c > 10.0 + GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -CREATE MATERIALIZED VIEW cmv_mat_view TBLPROPERTIES('rewriting.time.window'='300s') AS - SELECT cmv_basetable.a, cmv_basetable_2.c - FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) - WHERE cmv_basetable_2.c > 10.0 - GROUP BY cmv_basetable.a, cmv_basetable_2.c; +CREATE MATERIALIZED VIEW cmv_mat_view_n3 TBLPROPERTIES('rewriting.time.window'='300s') AS + SELECT cmv_basetable_n3.a, cmv_basetable_2_n1.c + FROM cmv_basetable_n3 JOIN cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) + WHERE cmv_basetable_2_n1.c > 10.0 + GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n3; -- CANNOT USE THE VIEW, IT IS DISABLED FOR REWRITE EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n3.a +FROM cmv_basetable_n3 join cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) +WHERE cmv_basetable_2_n1.c > 10.10 +GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n3.a +FROM cmv_basetable_n3 JOIN cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) +WHERE cmv_basetable_2_n1.c > 10.10 +GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -insert into cmv_basetable_2 values +insert into cmv_basetable_2_n1 values (3, 'charlie', 15.8, 1); -analyze table cmv_basetable_2 compute statistics for columns; +analyze table cmv_basetable_2_n1 compute statistics for columns; -- ENABLE FOR REWRITE EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE; +ALTER MATERIALIZED VIEW cmv_mat_view_n3 ENABLE REWRITE; -ALTER MATERIALIZED VIEW cmv_mat_view ENABLE REWRITE; +ALTER MATERIALIZED VIEW cmv_mat_view_n3 ENABLE REWRITE; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n3; -- CAN USE THE MATERIALIZED VIEW, AS TIME WINDOW IS HUGE -- WE GET OUTDATED RESULTS EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n3.a +FROM cmv_basetable_n3 join cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) +WHERE cmv_basetable_2_n1.c > 10.10 +GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n3.a +FROM cmv_basetable_n3 JOIN cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) +WHERE cmv_basetable_2_n1.c > 10.10 +GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -- REBUILD EXPLAIN -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n3 REBUILD; -ALTER MATERIALIZED VIEW cmv_mat_view REBUILD; +ALTER MATERIALIZED VIEW cmv_mat_view_n3 REBUILD; -DESCRIBE FORMATTED cmv_mat_view; +DESCRIBE FORMATTED cmv_mat_view_n3; -- CAN USE IT AGAIN EXPLAIN -SELECT cmv_basetable.a -FROM cmv_basetable join cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n3.a +FROM cmv_basetable_n3 join cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) +WHERE cmv_basetable_2_n1.c > 10.10 +GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -SELECT cmv_basetable.a -FROM cmv_basetable JOIN cmv_basetable_2 ON (cmv_basetable.a = cmv_basetable_2.a) -WHERE cmv_basetable_2.c > 10.10 -GROUP BY cmv_basetable.a, cmv_basetable_2.c; +SELECT cmv_basetable_n3.a +FROM cmv_basetable_n3 JOIN cmv_basetable_2_n1 ON (cmv_basetable_n3.a = cmv_basetable_2_n1.a) +WHERE cmv_basetable_2_n1.c > 10.10 +GROUP BY cmv_basetable_n3.a, cmv_basetable_2_n1.c; -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n3; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_describe.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_describe.q b/ql/src/test/queries/clientpositive/materialized_view_describe.q index b5bd467..d137861 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_describe.q +++ b/ql/src/test/queries/clientpositive/materialized_view_describe.q @@ -1,59 +1,59 @@ set hive.vectorized.execution.enabled=false; -create table cmv_basetable (a int, b varchar(256), c decimal(10,2)); +create table cmv_basetable_n8 (a int, b varchar(256), c decimal(10,2)); -insert into cmv_basetable values (1, 'alfred', 10.30),(2, 'bob', 3.14),(2, 'bonnie', 172342.2),(3, 'calvin', 978.76),(3, 'charlie', 9.8); +insert into cmv_basetable_n8 values (1, 'alfred', 10.30),(2, 'bob', 3.14),(2, 'bonnie', 172342.2),(3, 'calvin', 978.76),(3, 'charlie', 9.8); -create materialized view cmv_mat_view +create materialized view cmv_mat_view_n8 comment 'this is the first view' -tblproperties ('key'='foo') as select a, c from cmv_basetable; +tblproperties ('key'='foo') as select a, c from cmv_basetable_n8; -describe cmv_mat_view; +describe cmv_mat_view_n8; -describe extended cmv_mat_view; +describe extended cmv_mat_view_n8; -describe formatted cmv_mat_view; +describe formatted cmv_mat_view_n8; -show tblproperties cmv_mat_view; +show tblproperties cmv_mat_view_n8; -select a, c from cmv_mat_view; +select a, c from cmv_mat_view_n8; -drop materialized view cmv_mat_view; +drop materialized view cmv_mat_view_n8; -create materialized view cmv_mat_view2 +create materialized view cmv_mat_view2_n3 comment 'this is the second view' stored as textfile -tblproperties ('key'='alice','key2'='bob') as select a from cmv_basetable; +tblproperties ('key'='alice','key2'='bob') as select a from cmv_basetable_n8; -describe formatted cmv_mat_view2; +describe formatted cmv_mat_view2_n3; -select a from cmv_mat_view2; +select a from cmv_mat_view2_n3; -drop materialized view cmv_mat_view2; +drop materialized view cmv_mat_view2_n3; -create materialized view cmv_mat_view3 +create materialized view cmv_mat_view3_n0 comment 'this is the third view' row format delimited fields terminated by '\t' -as select * from cmv_basetable; +as select * from cmv_basetable_n8; -describe formatted cmv_mat_view3; +describe formatted cmv_mat_view3_n0; -select a, b, c from cmv_mat_view3; +select a, b, c from cmv_mat_view3_n0; -select distinct a from cmv_mat_view3; +select distinct a from cmv_mat_view3_n0; -drop materialized view cmv_mat_view3; +drop materialized view cmv_mat_view3_n0; dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/t; -create materialized view cmv_mat_view4 +create materialized view cmv_mat_view4_n0 comment 'this is the last view' stored as textfile location '${system:test.tmp.dir}/t' -as select a from cmv_basetable; +as select a from cmv_basetable_n8; -describe formatted cmv_mat_view4; +describe formatted cmv_mat_view4_n0; -select a from cmv_mat_view4; +select a from cmv_mat_view4_n0; -drop materialized view cmv_mat_view4; +drop materialized view cmv_mat_view4_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q index 7b90f80..e2e4384 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_1.q @@ -7,178 +7,178 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table emps ( +create table emps_n3 ( empid int, deptno int, name varchar(256), salary float, commission int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), +insert into emps_n3 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250); -analyze table emps compute statistics for columns; +analyze table emps_n3 compute statistics for columns; -create table depts ( +create table depts_n2 ( deptno int, name varchar(256), locationid int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); -analyze table depts compute statistics for columns; +insert into depts_n2 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); +analyze table depts_n2 compute statistics for columns; -create table dependents ( +create table dependents_n2 ( empid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into dependents values (10, 'Michael'), (10, 'Jane'); -analyze table dependents compute statistics for columns; +insert into dependents_n2 values (10, 'Michael'), (10, 'Jane'); +analyze table dependents_n2 compute statistics for columns; -create table locations ( +create table locations_n2 ( locationid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into locations values (10, 'San Francisco'), (10, 'San Diego'); -analyze table locations compute statistics for columns; +insert into locations_n2 values (10, 'San Francisco'), (10, 'San Diego'); +analyze table locations_n2 compute statistics for columns; -alter table emps add constraint pk1 primary key (empid) disable novalidate rely; -alter table depts add constraint pk2 primary key (deptno) disable novalidate rely; -alter table dependents add constraint pk3 primary key (empid) disable novalidate rely; -alter table locations add constraint pk4 primary key (locationid) disable novalidate rely; +alter table emps_n3 add constraint pk1 primary key (empid) disable novalidate rely; +alter table depts_n2 add constraint pk2 primary key (deptno) disable novalidate rely; +alter table dependents_n2 add constraint pk3 primary key (empid) disable novalidate rely; +alter table locations_n2 add constraint pk4 primary key (locationid) disable novalidate rely; -alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely; -alter table depts add constraint fk2 foreign key (locationid) references locations(locationid) disable novalidate rely; +alter table emps_n3 add constraint fk1 foreign key (deptno) references depts_n2(deptno) disable novalidate rely; +alter table depts_n2 add constraint fk2 foreign key (locationid) references locations_n2(locationid) disable novalidate rely; -- EXAMPLE 1 -create materialized view mv1 enable rewrite as -select * from emps where empid < 150; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select * from emps_n3 where empid < 150; +analyze table mv1_n2 compute statistics for columns; explain select * -from (select * from emps where empid < 120) t -join depts using (deptno); +from (select * from emps_n3 where empid < 120) t +join depts_n2 using (deptno); select * -from (select * from emps where empid < 120) t -join depts using (deptno); +from (select * from emps_n3 where empid < 120) t +join depts_n2 using (deptno); -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 2 -create materialized view mv1 enable rewrite as +create materialized view mv1_n2 enable rewrite as select deptno, name, salary, commission -from emps; -analyze table mv1 compute statistics for columns; +from emps_n3; +analyze table mv1_n2 compute statistics for columns; explain -select emps.name, emps.salary, emps.commission -from emps -join depts using (deptno); +select emps_n3.name, emps_n3.salary, emps_n3.commission +from emps_n3 +join depts_n2 using (deptno); -select emps.name, emps.salary, emps.commission -from emps -join depts using (deptno); +select emps_n3.name, emps_n3.salary, emps_n3.commission +from emps_n3 +join depts_n2 using (deptno); -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 3 -create materialized view mv1 enable rewrite as -select empid deptno from emps -join depts using (deptno); -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select empid deptno from emps_n3 +join depts_n2 using (deptno); +analyze table mv1_n2 compute statistics for columns; explain -select empid deptno from emps -join depts using (deptno) where empid = 1; +select empid deptno from emps_n3 +join depts_n2 using (deptno) where empid = 1; -select empid deptno from emps -join depts using (deptno) where empid = 1; +select empid deptno from emps_n3 +join depts_n2 using (deptno) where empid = 1; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 4 -create materialized view mv1 enable rewrite as -select * from emps where empid < 200; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select * from emps_n3 where empid < 200; +analyze table mv1_n2 compute statistics for columns; explain -select * from emps where empid > 120 -union all select * from emps where empid < 150; +select * from emps_n3 where empid > 120 +union all select * from emps_n3 where empid < 150; -select * from emps where empid > 120 -union all select * from emps where empid < 150; +select * from emps_n3 where empid > 120 +union all select * from emps_n3 where empid < 150; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 5 - NO MV, ALREADY UNIQUE -create materialized view mv1 enable rewrite as -select empid, deptno from emps group by empid, deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select empid, deptno from emps_n3 group by empid, deptno; +analyze table mv1_n2 compute statistics for columns; explain -select empid, deptno from emps group by empid, deptno; +select empid, deptno from emps_n3 group by empid, deptno; -select empid, deptno from emps group by empid, deptno; +select empid, deptno from emps_n3 group by empid, deptno; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 5 - NO MV, ALREADY UNIQUE -create materialized view mv1 enable rewrite as -select empid, name from emps group by empid, name; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select empid, name from emps_n3 group by empid, name; +analyze table mv1_n2 compute statistics for columns; explain -select empid, name from emps group by empid, name; +select empid, name from emps_n3 group by empid, name; -select empid, name from emps group by empid, name; +select empid, name from emps_n3 group by empid, name; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 5 -create materialized view mv1 enable rewrite as -select name, salary from emps group by name, salary; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select name, salary from emps_n3 group by name, salary; +analyze table mv1_n2 compute statistics for columns; explain -select name, salary from emps group by name, salary; +select name, salary from emps_n3 group by name, salary; -select name, salary from emps group by name, salary; +select name, salary from emps_n3 group by name, salary; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 6 -create materialized view mv1 enable rewrite as -select name, salary from emps group by name, salary; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select name, salary from emps_n3 group by name, salary; +analyze table mv1_n2 compute statistics for columns; explain -select name from emps group by name; +select name from emps_n3 group by name; -select name from emps group by name; +select name from emps_n3 group by name; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 7 -create materialized view mv1 enable rewrite as -select name, salary from emps where deptno = 10 group by name, salary; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n2 enable rewrite as +select name, salary from emps_n3 where deptno = 10 group by name, salary; +analyze table mv1_n2 compute statistics for columns; explain -select name from emps where deptno = 10 group by name; +select name from emps_n3 where deptno = 10 group by name; -select name from emps where deptno = 10 group by name; +select name from emps_n3 where deptno = 10 group by name; -drop materialized view mv1; +drop materialized view mv1_n2; -- EXAMPLE 9 -create materialized view mv1 enable rewrite as +create materialized view mv1_n2 enable rewrite as select name, salary, count(*) as c, sum(empid) as s -from emps group by name, salary; -analyze table mv1 compute statistics for columns; +from emps_n3 group by name, salary; +analyze table mv1_n2 compute statistics for columns; explain -select name from emps group by name; +select name from emps_n3 group by name; -select name from emps group by name; +select name from emps_n3 group by name; -drop materialized view mv1; +drop materialized view mv1_n2; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_2.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_2.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_2.q index 6f66a85..a49726c 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_2.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_2.q @@ -7,158 +7,158 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table emps ( +create table emps_n0 ( empid int, deptno int, name varchar(256), salary float, commission int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), +insert into emps_n0 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250); -analyze table emps compute statistics for columns; +analyze table emps_n0 compute statistics for columns; -create table depts ( +create table depts_n0 ( deptno int, name varchar(256), locationid int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); -analyze table depts compute statistics for columns; +insert into depts_n0 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); +analyze table depts_n0 compute statistics for columns; -create table dependents ( +create table dependents_n0 ( empid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into dependents values (10, 'Michael'), (10, 'Jane'); -analyze table dependents compute statistics for columns; +insert into dependents_n0 values (10, 'Michael'), (10, 'Jane'); +analyze table dependents_n0 compute statistics for columns; -create table locations ( +create table locations_n0 ( locationid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into locations values (10, 'San Francisco'), (10, 'San Diego'); -analyze table locations compute statistics for columns; +insert into locations_n0 values (10, 'San Francisco'), (10, 'San Diego'); +analyze table locations_n0 compute statistics for columns; -alter table emps add constraint pk1 primary key (empid) disable novalidate rely; -alter table depts add constraint pk2 primary key (deptno) disable novalidate rely; -alter table dependents add constraint pk3 primary key (empid) disable novalidate rely; -alter table locations add constraint pk4 primary key (locationid) disable novalidate rely; +alter table emps_n0 add constraint pk1 primary key (empid) disable novalidate rely; +alter table depts_n0 add constraint pk2 primary key (deptno) disable novalidate rely; +alter table dependents_n0 add constraint pk3 primary key (empid) disable novalidate rely; +alter table locations_n0 add constraint pk4 primary key (locationid) disable novalidate rely; -alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely; -alter table depts add constraint fk2 foreign key (locationid) references locations(locationid) disable novalidate rely; +alter table emps_n0 add constraint fk1 foreign key (deptno) references depts_n0(deptno) disable novalidate rely; +alter table depts_n0 add constraint fk2 foreign key (locationid) references locations_n0(locationid) disable novalidate rely; -- EXAMPLE 16 -create materialized view mv1 enable rewrite as -select empid, depts.deptno from emps -join depts using (deptno) where depts.deptno > 10 -group by empid, depts.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n0 enable rewrite as +select empid, depts_n0.deptno from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 10 +group by empid, depts_n0.deptno; +analyze table mv1_n0 compute statistics for columns; explain -select empid from emps -join depts using (deptno) where depts.deptno > 20 -group by empid, depts.deptno; +select empid from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 20 +group by empid, depts_n0.deptno; -select empid from emps -join depts using (deptno) where depts.deptno > 20 -group by empid, depts.deptno; +select empid from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 20 +group by empid, depts_n0.deptno; -drop materialized view mv1; +drop materialized view mv1_n0; -- EXAMPLE 17 -create materialized view mv1 enable rewrite as -select depts.deptno, empid from depts -join emps using (deptno) where depts.deptno > 10 -group by empid, depts.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n0 enable rewrite as +select depts_n0.deptno, empid from depts_n0 +join emps_n0 using (deptno) where depts_n0.deptno > 10 +group by empid, depts_n0.deptno; +analyze table mv1_n0 compute statistics for columns; explain -select empid from emps -join depts using (deptno) where depts.deptno > 20 -group by empid, depts.deptno; +select empid from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 20 +group by empid, depts_n0.deptno; -select empid from emps -join depts using (deptno) where depts.deptno > 20 -group by empid, depts.deptno; +select empid from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 20 +group by empid, depts_n0.deptno; -drop materialized view mv1; +drop materialized view mv1_n0; -- EXAMPLE 18 -create materialized view mv1 enable rewrite as -select empid, depts.deptno from emps -join depts using (deptno) where emps.deptno > 10 -group by empid, depts.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n0 enable rewrite as +select empid, depts_n0.deptno from emps_n0 +join depts_n0 using (deptno) where emps_n0.deptno > 10 +group by empid, depts_n0.deptno; +analyze table mv1_n0 compute statistics for columns; explain -select empid from emps -join depts using (deptno) where depts.deptno > 20 -group by empid, depts.deptno; +select empid from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 20 +group by empid, depts_n0.deptno; -select empid from emps -join depts using (deptno) where depts.deptno > 20 -group by empid, depts.deptno; +select empid from emps_n0 +join depts_n0 using (deptno) where depts_n0.deptno > 20 +group by empid, depts_n0.deptno; -drop materialized view mv1; +drop materialized view mv1_n0; -- EXAMPLE 19 -create materialized view mv1 enable rewrite as -select depts.deptno, emps.empid from depts -join emps using (deptno) where emps.empid > 10 -group by depts.deptno, emps.empid; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n0 enable rewrite as +select depts_n0.deptno, emps_n0.empid from depts_n0 +join emps_n0 using (deptno) where emps_n0.empid > 10 +group by depts_n0.deptno, emps_n0.empid; +analyze table mv1_n0 compute statistics for columns; explain -select depts.deptno from depts -join emps using (deptno) where emps.empid > 15 -group by depts.deptno, emps.empid; +select depts_n0.deptno from depts_n0 +join emps_n0 using (deptno) where emps_n0.empid > 15 +group by depts_n0.deptno, emps_n0.empid; -select depts.deptno from depts -join emps using (deptno) where emps.empid > 15 -group by depts.deptno, emps.empid; +select depts_n0.deptno from depts_n0 +join emps_n0 using (deptno) where emps_n0.empid > 15 +group by depts_n0.deptno, emps_n0.empid; -drop materialized view mv1; +drop materialized view mv1_n0; -- EXAMPLE 20 -create materialized view mv1 enable rewrite as -select depts.deptno, emps.empid from depts -join emps using (deptno) where emps.empid > 10 -group by depts.deptno, emps.empid; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n0 enable rewrite as +select depts_n0.deptno, emps_n0.empid from depts_n0 +join emps_n0 using (deptno) where emps_n0.empid > 10 +group by depts_n0.deptno, emps_n0.empid; +analyze table mv1_n0 compute statistics for columns; explain -select depts.deptno from depts -join emps using (deptno) where emps.empid > 15 -group by depts.deptno; +select depts_n0.deptno from depts_n0 +join emps_n0 using (deptno) where emps_n0.empid > 15 +group by depts_n0.deptno; -select depts.deptno from depts -join emps using (deptno) where emps.empid > 15 -group by depts.deptno; +select depts_n0.deptno from depts_n0 +join emps_n0 using (deptno) where emps_n0.empid > 15 +group by depts_n0.deptno; -drop materialized view mv1; +drop materialized view mv1_n0; -- EXAMPLE 23 -create materialized view mv1 enable rewrite as -select depts.name, dependents.name as name2, emps.deptno, depts.deptno as deptno2, dependents.empid -from depts, dependents, emps -where depts.deptno > 10 -group by depts.name, dependents.name, emps.deptno, depts.deptno, dependents.empid; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n0 enable rewrite as +select depts_n0.name, dependents_n0.name as name2, emps_n0.deptno, depts_n0.deptno as deptno2, dependents_n0.empid +from depts_n0, dependents_n0, emps_n0 +where depts_n0.deptno > 10 +group by depts_n0.name, dependents_n0.name, emps_n0.deptno, depts_n0.deptno, dependents_n0.empid; +analyze table mv1_n0 compute statistics for columns; explain -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 -group by dependents.empid; - -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno) -where depts.deptno > 10 -group by dependents.empid; - -drop materialized view mv1; +select dependents_n0.empid +from depts_n0 +join dependents_n0 on (depts_n0.name = dependents_n0.name) +join emps_n0 on (emps_n0.deptno = depts_n0.deptno) +where depts_n0.deptno > 10 +group by dependents_n0.empid; + +select dependents_n0.empid +from depts_n0 +join dependents_n0 on (depts_n0.name = dependents_n0.name) +join emps_n0 on (emps_n0.deptno = depts_n0.deptno) +where depts_n0.deptno > 10 +group by dependents_n0.empid; + +drop materialized view mv1_n0; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q index ba87d80..fe00860 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_3.q @@ -7,109 +7,109 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table emps ( +create table emps_n9 ( empid int, deptno int, name varchar(256), salary float, commission int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), +insert into emps_n9 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250); -analyze table emps compute statistics for columns; +analyze table emps_n9 compute statistics for columns; -create table depts ( +create table depts_n7 ( deptno int, name varchar(256), locationid int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); -analyze table depts compute statistics for columns; +insert into depts_n7 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); +analyze table depts_n7 compute statistics for columns; -create table dependents ( +create table dependents_n5 ( empid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into dependents values (10, 'Michael'), (10, 'Jane'); -analyze table dependents compute statistics for columns; +insert into dependents_n5 values (10, 'Michael'), (10, 'Jane'); +analyze table dependents_n5 compute statistics for columns; -create table locations ( +create table locations_n5 ( locationid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into locations values (10, 'San Francisco'), (10, 'San Diego'); -analyze table locations compute statistics for columns; +insert into locations_n5 values (10, 'San Francisco'), (10, 'San Diego'); +analyze table locations_n5 compute statistics for columns; -alter table emps add constraint pk1 primary key (empid) disable novalidate rely; -alter table depts add constraint pk2 primary key (deptno) disable novalidate rely; -alter table dependents add constraint pk3 primary key (empid) disable novalidate rely; -alter table locations add constraint pk4 primary key (locationid) disable novalidate rely; +alter table emps_n9 add constraint pk1 primary key (empid) disable novalidate rely; +alter table depts_n7 add constraint pk2 primary key (deptno) disable novalidate rely; +alter table dependents_n5 add constraint pk3 primary key (empid) disable novalidate rely; +alter table locations_n5 add constraint pk4 primary key (locationid) disable novalidate rely; -alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely; -alter table depts add constraint fk2 foreign key (locationid) references locations(locationid) disable novalidate rely; +alter table emps_n9 add constraint fk1 foreign key (deptno) references depts_n7(deptno) disable novalidate rely; +alter table depts_n7 add constraint fk2 foreign key (locationid) references locations_n5(locationid) disable novalidate rely; -- EXAMPLE 34 -create materialized view mv1 enable rewrite as -select empid deptno from emps -join depts using (deptno); -analyze table mv1 compute statistics for columns; +create materialized view mv1_n5 enable rewrite as +select empid deptno from emps_n9 +join depts_n7 using (deptno); +analyze table mv1_n5 compute statistics for columns; explain -select empid deptno from emps -join depts using (deptno) where empid = 1; +select empid deptno from emps_n9 +join depts_n7 using (deptno) where empid = 1; -select empid deptno from emps -join depts using (deptno) where empid = 1; +select empid deptno from emps_n9 +join depts_n7 using (deptno) where empid = 1; -drop materialized view mv1; +drop materialized view mv1_n5; -- EXAMPLE 35 -create materialized view mv1 enable rewrite as -select cast(empid as BIGINT) from emps -join depts using (deptno); -analyze table mv1 compute statistics for columns; +create materialized view mv1_n5 enable rewrite as +select cast(empid as BIGINT) from emps_n9 +join depts_n7 using (deptno); +analyze table mv1_n5 compute statistics for columns; explain -select empid deptno from emps -join depts using (deptno) where empid > 1; +select empid deptno from emps_n9 +join depts_n7 using (deptno) where empid > 1; -select empid deptno from emps -join depts using (deptno) where empid > 1; +select empid deptno from emps_n9 +join depts_n7 using (deptno) where empid > 1; -drop materialized view mv1; +drop materialized view mv1_n5; -- EXAMPLE 36 -create materialized view mv1 enable rewrite as -select cast(empid as BIGINT) from emps -join depts using (deptno); -analyze table mv1 compute statistics for columns; +create materialized view mv1_n5 enable rewrite as +select cast(empid as BIGINT) from emps_n9 +join depts_n7 using (deptno); +analyze table mv1_n5 compute statistics for columns; explain -select empid deptno from emps -join depts using (deptno) where empid = 1; +select empid deptno from emps_n9 +join depts_n7 using (deptno) where empid = 1; -select empid deptno from emps -join depts using (deptno) where empid = 1; +select empid deptno from emps_n9 +join depts_n7 using (deptno) where empid = 1; -drop materialized view mv1; +drop materialized view mv1_n5; -- EXAMPLE 38 -create materialized view mv1 enable rewrite as -select depts.name -from emps -join depts on (emps.deptno = depts.deptno); -analyze table mv1 compute statistics for columns; +create materialized view mv1_n5 enable rewrite as +select depts_n7.name +from emps_n9 +join depts_n7 on (emps_n9.deptno = depts_n7.deptno); +analyze table mv1_n5 compute statistics for columns; explain -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno); +select dependents_n5.empid +from depts_n7 +join dependents_n5 on (depts_n7.name = dependents_n5.name) +join emps_n9 on (emps_n9.deptno = depts_n7.deptno); -select dependents.empid -from depts -join dependents on (depts.name = dependents.name) -join emps on (emps.deptno = depts.deptno); +select dependents_n5.empid +from depts_n7 +join dependents_n5 on (depts_n7.name = dependents_n5.name) +join emps_n9 on (emps_n9.deptno = depts_n7.deptno); -drop materialized view mv1; +drop materialized view mv1_n5; http://git-wip-us.apache.org/repos/asf/hive/blob/38d3b8e1/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q b/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q index eaba93d..4b097cd 100644 --- a/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q +++ b/ql/src/test/queries/clientpositive/materialized_view_rewrite_4.q @@ -7,172 +7,172 @@ set hive.strict.checks.cartesian.product=false; set hive.stats.fetch.column.stats=true; set hive.materializedview.rewriting=true; -create table emps ( +create table emps_n5 ( empid int, deptno int, name varchar(256), salary float, commission int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into emps values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), +insert into emps_n5 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500), (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250); -analyze table emps compute statistics for columns; +analyze table emps_n5 compute statistics for columns; -create table depts ( +create table depts_n4 ( deptno int, name varchar(256), locationid int) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into depts values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); -analyze table depts compute statistics for columns; +insert into depts_n4 values (10, 'Sales', 10), (30, 'Marketing', null), (20, 'HR', 20); +analyze table depts_n4 compute statistics for columns; -create table dependents ( +create table dependents_n3 ( empid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into dependents values (10, 'Michael'), (10, 'Jane'); -analyze table dependents compute statistics for columns; +insert into dependents_n3 values (10, 'Michael'), (10, 'Jane'); +analyze table dependents_n3 compute statistics for columns; -create table locations ( +create table locations_n3 ( locationid int, name varchar(256)) stored as orc TBLPROPERTIES ('transactional'='true'); -insert into locations values (10, 'San Francisco'), (10, 'San Diego'); -analyze table locations compute statistics for columns; +insert into locations_n3 values (10, 'San Francisco'), (10, 'San Diego'); +analyze table locations_n3 compute statistics for columns; -alter table emps add constraint pk1 primary key (empid) disable novalidate rely; -alter table depts add constraint pk2 primary key (deptno) disable novalidate rely; -alter table dependents add constraint pk3 primary key (empid) disable novalidate rely; -alter table locations add constraint pk4 primary key (locationid) disable novalidate rely; +alter table emps_n5 add constraint pk1 primary key (empid) disable novalidate rely; +alter table depts_n4 add constraint pk2 primary key (deptno) disable novalidate rely; +alter table dependents_n3 add constraint pk3 primary key (empid) disable novalidate rely; +alter table locations_n3 add constraint pk4 primary key (locationid) disable novalidate rely; -alter table emps add constraint fk1 foreign key (deptno) references depts(deptno) disable novalidate rely; -alter table depts add constraint fk2 foreign key (locationid) references locations(locationid) disable novalidate rely; +alter table emps_n5 add constraint fk1 foreign key (deptno) references depts_n4(deptno) disable novalidate rely; +alter table depts_n4 add constraint fk2 foreign key (locationid) references locations_n3(locationid) disable novalidate rely; -- EXAMPLE 10 -create materialized view mv1 enable rewrite as +create materialized view mv1_n3 enable rewrite as select name, salary, count(*) as c, sum(empid) as s -from emps group by name, salary; -analyze table mv1 compute statistics for columns; +from emps_n5 group by name, salary; +analyze table mv1_n3 compute statistics for columns; explain select name, count(*) as c, sum(empid) as s -from emps group by name; +from emps_n5 group by name; select name, count(*) as c, sum(empid) as s -from emps group by name; +from emps_n5 group by name; -drop materialized view mv1; +drop materialized view mv1_n3; -- EXAMPLE 11 -create materialized view mv1 enable rewrite as +create materialized view mv1_n3 enable rewrite as select name, salary, count(*) as c, sum(empid) as s -from emps group by name, salary; -analyze table mv1 compute statistics for columns; +from emps_n5 group by name, salary; +analyze table mv1_n3 compute statistics for columns; explain select salary, name, sum(empid) as s, count(*) as c -from emps group by name, salary; +from emps_n5 group by name, salary; select salary, name, sum(empid) as s, count(*) as c -from emps group by name, salary; +from emps_n5 group by name, salary; -drop materialized view mv1; +drop materialized view mv1_n3; -- EXAMPLE 25 -create materialized view mv1 enable rewrite as -select empid, emps.deptno, count(*) as c, sum(empid) as s -from emps join depts using (deptno) -group by empid, emps.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n3 enable rewrite as +select empid, emps_n5.deptno, count(*) as c, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +group by empid, emps_n5.deptno; +analyze table mv1_n3 compute statistics for columns; explain -select depts.deptno, count(*) as c, sum(empid) as s -from emps join depts using (deptno) -group by depts.deptno; +select depts_n4.deptno, count(*) as c, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +group by depts_n4.deptno; -select depts.deptno, count(*) as c, sum(empid) as s -from emps join depts using (deptno) -group by depts.deptno; +select depts_n4.deptno, count(*) as c, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +group by depts_n4.deptno; -drop materialized view mv1; +drop materialized view mv1_n3; -- EXAMPLE 27 -create materialized view mv1 enable rewrite as -select empid, emps.deptno, count(*) as c, sum(empid) as s -from emps join depts using (deptno) -where emps.deptno >= 10 group by empid, emps.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n3 enable rewrite as +select empid, emps_n5.deptno, count(*) as c, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +where emps_n5.deptno >= 10 group by empid, emps_n5.deptno; +analyze table mv1_n3 compute statistics for columns; explain -select depts.deptno, sum(empid) as s -from emps join depts using (deptno) -where emps.deptno > 10 group by depts.deptno; +select depts_n4.deptno, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +where emps_n5.deptno > 10 group by depts_n4.deptno; -select depts.deptno, sum(empid) as s -from emps join depts using (deptno) -where emps.deptno > 10 group by depts.deptno; +select depts_n4.deptno, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +where emps_n5.deptno > 10 group by depts_n4.deptno; -drop materialized view mv1; +drop materialized view mv1_n3; -- EXAMPLE 28 -create materialized view mv1 enable rewrite as -select empid, depts.deptno, count(*) + 1 as c, sum(empid) as s -from emps join depts using (deptno) -where depts.deptno >= 10 group by empid, depts.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n3 enable rewrite as +select empid, depts_n4.deptno, count(*) + 1 as c, sum(empid) as s +from emps_n5 join depts_n4 using (deptno) +where depts_n4.deptno >= 10 group by empid, depts_n4.deptno; +analyze table mv1_n3 compute statistics for columns; explain -select depts.deptno, sum(empid) + 1 as s -from emps join depts using (deptno) -where depts.deptno > 10 group by depts.deptno; +select depts_n4.deptno, sum(empid) + 1 as s +from emps_n5 join depts_n4 using (deptno) +where depts_n4.deptno > 10 group by depts_n4.deptno; -select depts.deptno, sum(empid) + 1 as s -from emps join depts using (deptno) -where depts.deptno > 10 group by depts.deptno; +select depts_n4.deptno, sum(empid) + 1 as s +from emps_n5 join depts_n4 using (deptno) +where depts_n4.deptno > 10 group by depts_n4.deptno; -drop materialized view mv1; +drop materialized view mv1_n3; -- EXAMPLE 29 -create materialized view mv1 enable rewrite as -select depts.name, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -group by depts.name; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n3 enable rewrite as +select depts_n4.name, sum(salary) as s +from emps_n5 +join depts_n4 on (emps_n5.deptno = depts_n4.deptno) +group by depts_n4.name; +analyze table mv1_n3 compute statistics for columns; explain -select dependents.empid, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (depts.name = dependents.name) -group by dependents.empid; +select dependents_n3.empid, sum(salary) as s +from emps_n5 +join depts_n4 on (emps_n5.deptno = depts_n4.deptno) +join dependents_n3 on (depts_n4.name = dependents_n3.name) +group by dependents_n3.empid; -select dependents.empid, sum(salary) as s -from emps -join depts on (emps.deptno = depts.deptno) -join dependents on (depts.name = dependents.name) -group by dependents.empid; +select dependents_n3.empid, sum(salary) as s +from emps_n5 +join depts_n4 on (emps_n5.deptno = depts_n4.deptno) +join dependents_n3 on (depts_n4.name = dependents_n3.name) +group by dependents_n3.empid; -drop materialized view mv1; +drop materialized view mv1_n3; -- EXAMPLE 32 -create materialized view mv1 enable rewrite as -select dependents.empid, emps.deptno, count(distinct salary) as s -from emps -join dependents on (emps.empid = dependents.empid) -group by dependents.empid, emps.deptno; -analyze table mv1 compute statistics for columns; +create materialized view mv1_n3 enable rewrite as +select dependents_n3.empid, emps_n5.deptno, count(distinct salary) as s +from emps_n5 +join dependents_n3 on (emps_n5.empid = dependents_n3.empid) +group by dependents_n3.empid, emps_n5.deptno; +analyze table mv1_n3 compute statistics for columns; explain -select emps.deptno, count(distinct salary) as s -from emps -join dependents on (emps.empid = dependents.empid) -group by dependents.empid, emps.deptno; +select emps_n5.deptno, count(distinct salary) as s +from emps_n5 +join dependents_n3 on (emps_n5.empid = dependents_n3.empid) +group by dependents_n3.empid, emps_n5.deptno; -select emps.deptno, count(distinct salary) as s -from emps -join dependents on (emps.empid = dependents.empid) -group by dependents.empid, emps.deptno; +select emps_n5.deptno, count(distinct salary) as s +from emps_n5 +join dependents_n3 on (emps_n5.empid = dependents_n3.empid) +group by dependents_n3.empid, emps_n5.deptno; -drop materialized view mv1; +drop materialized view mv1_n3;