Hello,All:
I am playing Oracle Enterprise Edition 8.1.7 and want to get familiar with it espacially materialized views and query re-write.
First,
I used SQL-Plus and defined a materialized view by using the following :
create materialized view first_summary
pctfree 0 tablespace oem_repository
storage(initial 2k next 2k pctincrease 0)
build immediate
refresh complete
enable query rewrite
as
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '90' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
(This is actually the first query of TPC-H Benchmark. )
It cost 3 minutes to complete the task -- Materialized view created.
Second:
Then still using SQL-Plus , I copied the exactly same query and supposed that Oracle could automatically re-write the query by using the just defined materialized view - first_summary and gave me the answer very quickly. However, I still needed to spend another 3 minutes to get the result.
Am I missing anything for activing the query re-write? Should I do the query by using another Oracle application rather than SQL-Plus ?
Many thanks in advance!
I am playing Oracle Enterprise Edition 8.1.7 and want to get familiar with it espacially materialized views and query re-write.
First,
I used SQL-Plus and defined a materialized view by using the following :
create materialized view first_summary
pctfree 0 tablespace oem_repository
storage(initial 2k next 2k pctincrease 0)
build immediate
refresh complete
enable query rewrite
as
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '90' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
(This is actually the first query of TPC-H Benchmark. )
It cost 3 minutes to complete the task -- Materialized view created.
Second:
Then still using SQL-Plus , I copied the exactly same query and supposed that Oracle could automatically re-write the query by using the just defined materialized view - first_summary and gave me the answer very quickly. However, I still needed to spend another 3 minutes to get the result.
Am I missing anything for activing the query re-write? Should I do the query by using another Oracle application rather than SQL-Plus ?
Many thanks in advance!