|
|
| Re: what is Materialized View example please [message #43947 is a reply to message #43946] |
Fri, 10 October 2003 07:32   |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Its basically a summary table that stores summary info(pre-calculated) and is available to be queried immediately via QUERY_REWRITE feature of Oracle..
Unlike a conventional view,Mview stores data of its own and can be refreshed from the base tables either automatically or manually..
It enhances query performance drastically becos Oracle doesnt have to do the joins or summary on run time,but uses the precalculated data.
SQL> drop table t;
Table dropped.
SQL> create materialized view t_mview enable query rewrite as
2 select deptno,sum(sal) from t group by deptno;
Materialized view created.
SQL> analyze table t_mview compute statistics;
Table analyzed.
SQL> analyze table t compute statistics;
Table analyzed.
-- here,although we query against the base table for the summary info, Oracle internally rewrites the query to get the stored summary info from the materialised view we created earlier.
SQL> select deptno,sum(sal) from t group by deptno;
DEPTNO SUM(SAL)
---------- ----------
10 156729
20 58070
30 24796
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=20)
1 0 TABLE ACCESS (FULL) OF 'T_MVIEW' (Cost=2 Card=4 Bytes=20)
However you need to have QUERY_REWRITE_ENABLED=true and
query_rewrite_integrity=enforced set in your init.ora(spfile) for the query rewrite.
-- Alternatively you can also directly query from the materialized view ..
thiru@9.2.0:SQL>select * from t_mview;
DEPTNO SUM(SAL)
---------- ----------
10 156729
20 58070
30 24796
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
