Query Rewrite

[JasonXie]

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.

http://www.tpc.org

)

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!

 

[JamesGordon]

Set query_rewrite_enabled=truein the init.ora file or via alter session...

 

[JasonXie]

Many thanks.