Home » Server Options » Replication » what is Materialized View example please
what is Materialized View example please [message #43946] Fri, 10 October 2003 07:12 Go to next message
sree hari
Messages: 10
Registered: July 2002
Junior Member
i want to know what is Materialized View.
how it can be usefull ?
Re: what is Materialized View example please [message #43947 is a reply to message #43946] Fri, 10 October 2003 07:32 Go to previous messageGo to next message
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
Re: what is Materialized View example please [message #43948 is a reply to message #43947] Fri, 10 October 2003 07:37 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Oops, create the test table t as follows

create table t as select * from scott.emp;

-Thiru
Previous Topic: Database Server replication using Veritas VVR
Next Topic: please help me
Goto Forum:
  


Current Time: Thu Mar 28 03:45:19 CDT 2024