Materialized view - General questions

[samdba]

Hi Guys,

We are on Oracle 8173 on Windows.

I am new to the materialized view (MV) feature of 8i.

I want to create a MV with manual refresh, which I will refresh weekly.

Could someone provide me the query?

Also, then, How will I refresh the MV.

Thanks in Adv.

[TimHall]

To build the materialized view do something like:

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp;

Refresh the materialized view using:

SQL> EXEC DBMS_MVIEW.REFRESH('EMP_MV');

Cheers

[samdba]

Tim,

Thanks a lot.

It worked.

What is the diff. between REFRESH COMPLETE and FORCE?

When do we have to use QUERY REWRITE and UPDATAEBLE?

[TimHall]

COMPLETE - All rows deletes and replaced.
FAST - Only changes in base table are transfered to the MVIEW, rather than replacing all the rows.
FORCE - A FAST is performed, if this cannot be done a COMPLETE is done.

You'll need to define material view logs to use a fast refresh and there are a number of restrictions.

Updatable is used if you want the MVIEW to be updatable, and the changes to be refleacted in the base table.

Query rewrite is necessary if you want Oracle to rewrite SQL statements to take advantage of available MViews if they would improve performance.

See:

http://otn.oracle.com/docs/products/...994/mv.htm#721

Cheers

[samdba]

Hi Tim,

Great help!

Thank you very much.

Now, I think I will have to schedule the job to refresh this MV.

Correct?

[samdba]

Tim,

I see two instances for my materialized view in the DBA_OBJECTS.

One with object_type as TABLE and another with 'UNDEFINED'.

But, I can still I am able to refresh the MV.

Questions:

1. Why there are two instances for the MV with the same name?

2. Why it shows 'UNDEFINED' in object_type?

Thanks,

[TimHall]

One of the objects is the materialized view definition itself. The other is the actual table that holds all the rows. I'm not sure why your version does not list this properly. I'm using 9.2 and I get:

SQL> select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------ ------------------
EMP_MV TABLE
EMP_MV MATERIALIZED VIEW

2 rows selected.

SQL>

Cheers

[prakashs43]

quote
-----------
One of the objects is the materialized view definition itself. The other is the actual table that holds all the rows. I'm not sure why your version does not list this properly. I'm using 9.2 and I get:
-------------

there is lot of difference between 8i Materialized views and 9i materialised views

some of the features which i noticed is
8i does not support automatic refresh on its own . where as 9i does support . we need to do manual refresh in 8i .

i think in 8i it is a concept of snapshot which is called materialized views in 9i . ofcourse even in snapshots it creates materialized views.