>>-- Run Tests
>>	set schema cat.tpcd;

--- SQL operation complete.
>>	obey TESTMV515(TEST1);
>>
>>-- MV with several select predicates on different tables and some on the same table
>>log;
>>
>>obey ./TPCD/CREATEMVS(MV5);
>>create mv mv4q5
+>	refresh on request 
+>	initialize on create
+>	store  by (n_name)
+>	as
+>	select n_name, sum(l_extendedprice*(1-l_discount)) as revenue,count(*) rows_in_group
+>	from customer,orders,lineitem,supplier,nation, region
+>	where
+>	    c_custkey = o_custkey
+>	    and o_orderkey = l_orderkey
+>	    and l_suppkey = s_suppkey
+>	    and c_nationkey= s_nationkey
+>	    and s_nationkey = n_nationkey
+>	    and n_regionkey = r_regionkey
+>	    and r_name in ('ASIA','AMERICA','EUROPE','MIDDLE EAST','AFRICA')
+>	    and o_orderdate >= date '1994-01-01'
+>	    and o_orderdate < date '1994-01-01' + interval '3' year
+>	group by n_name;

--- SQL operation complete.
>>ALTER MV mv4q5 attribute all mvs allowed;

--- SQL operation complete.
>>
>>-------------------------
>>
>>set param ?mv_name 'MV4Q5';
>>execute select_mvs_used;

TABLE_NAME                                USED_OBJECT_TYPE  USAGE_ORIGIN  PREDICATES
----------------------------------------  ----------------  ------------  -----------

CUSTOMER                                  BT                U                      -1
LINEITEM                                  BT                U                      -1
NATION                                    BT                U                      -1
SUPPLIER                                  BT                U                      -1
ORDERS                                    BT                U                       4
REGION                                    BT                U                       5

--- 6 row(s) selected.
>>execute select_text_table;

MV_NAME                                   USED_NAME                                 PREDICATES   (EXPR)
----------------------------------------  ----------------------------------------  -----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MV4Q5                                     CUSTOMER                                           -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q5                                     SUPPLIER                                           -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q5                                     LINEITEM                                           -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q5                                     ORDERS                                              4  ((CAT.TPCD.ORDERS.O_ORDERDATE >=  DATE '1994-01-01') AND (CAT.TPCD.ORDERS.O_ORDERDATE <  DATE '1997-01-01'))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
MV4Q5                                     NATION                                             -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q5                                     REGION                                              5  ((((((CAT.TPCD.REGION.R_NAME = 'AFRICA') or (CAT.TPCD.REGION.R_NAME = 'AMERICA')) or (CAT.TPCD.REGION.R_NAME = 'ASIA')) or (CAT.TPCD.REGION.R_NAME = 'EUROPE')) or (CAT.TPCD.REGION.R_NAME = 'MIDDLE EAST')))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

--- 6 row(s) selected.
>>
>>obey ./TPCD/CREATEMVS(MV9);
>>create mv mv4q9 
+>    refresh on request 
+>initialized on refresh
+>    as
+>	select nation, yr, sum(amount) as sum_profit
+>	from (select
+>         	n_name as nation, 
+>         	extract(year from o_orderdate) as yr,
+>         	((l_extendedprice*(1-l_discount)) - (ps_supplycost*l_quantity)) as amount
+>     from part,supplier,lineitem,partsupp,orders, nation
+>     where 
+>         s_suppkey  = l_suppkey
+>         and ps_suppkey = l_suppkey
+>         and ps_partkey = l_partkey
+>         and p_partkey  = l_partkey
+>         and o_orderkey = l_orderkey
+>         and s_nationkey = n_nationkey
+>         and p_name like '%green%'
+>    ) as profit
+>	group by nation, yr;

*** WARNING[12112] A secondary index CAT.TPCD.MV4Q9_585131784_3845 was created for the materialized view.

--- SQL operation completed with warnings.
>>ALTER MV mv4q9 attribute all mvs allowed;

--- SQL operation complete.
>>
>>-------------------------
>>
>>set param ?mv_name 'MV4Q9';
>>execute select_mvs_used;

TABLE_NAME                                USED_OBJECT_TYPE  USAGE_ORIGIN  PREDICATES
----------------------------------------  ----------------  ------------  -----------

LINEITEM                                  BT                D                      -1
NATION                                    BT                D                      -1
ORDERS                                    BT                D                      -1
PARTSUPP                                  BT                U                      -1
SUPPLIER                                  BT                U                      -1
PART                                      BT                U                       5

--- 6 row(s) selected.
>>execute select_text_table;

MV_NAME                                   USED_NAME                                 PREDICATES   (EXPR)
----------------------------------------  ----------------------------------------  -----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MV4Q9                                     SUPPLIER                                           -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q9                                     LINEITEM                                           -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q9                                     ORDERS                                             -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q9                                     PART                                                5  ((CAT.TPCD.PART.P_NAME like '%green%'))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
MV4Q9                                     PARTSUPP                                           -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
MV4Q9                                     NATION                                             -1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

--- 6 row(s) selected.
>>-- Clean and exit
>>log;
