MATERIALIZED VIEWS

When the table on which we are creating is having a primary key then use the below script.

CREATE MATERIALIZED VIEW TTL_SMS_PINLESS_MV

BUILD IMMEDIATE

REFRESH FAST

NEXT SYSDATE + 4/24

AS

SELECT

ID SMS_ID,

MDN MDN,

MSG RESPONSE_MSG,

LAST_UPDATED_DATE REQUEST_DATE,

TIPPS_MSG SUBSCRIPTION_STATUS

FROM

SMS_PINLESS@CRME2E_TO_CCSMS;

If the table not having any primary key then use the below script.

CREATE MATERIALIZED VIEW TTL_PPM_CIRCLE_MAPPING_MV

REFRESH COMPLETE WITH ROWID

AS

SELECT "PPM_CIRCLE_MAPPING_REF"."PPM_CIRCLE" "PPM_CIRCLE","PPM_CIRCLE_MAPPING_REF"."PPM_CIRCLE_DESC" "PPM_CIRCLE_DESC","PPM_CIRCLE_MAPPING_REF"."CRM_CIRCLE" "CRM_CIRCLE","PPM_CIRCLE_MAPPING_REF"."MSS_CIRCLE" "MSS_CIRCLE"

FROM "PPM_CIRCLE_MAPPING_REF"@PPMSIT.US.ORACLE.COM "PPM_CIRCLE_MAPPING_REF";

Refreshing a Materialized View

begin

dbms_mview.refresh('mv name','F/C');

end;

Altering a materialized View

ALTER MATERIALIZED VIEW PREP_DATA_USAGE_RECORDS_MV

REFRESH COMPLETE

START WITH SYSDATE

NEXT SYSDATE+0.1/24

alter MATERIALIZED VIEW PREP_DATA_USAGE_RECORDS_MV compile;

Drop Materialized View

drop materialized view MVIEW_NAME

UPDATABLE MATERIALIZED VIEWS:

Read-Only Materialized Views
You can make a materialized view read-only during creation by omitting the FOR UPDATE
clause or disabling the equivalent option in the Replication Management tool.
Read-only materialized views use many of the same mechanisms as updatable
 materialized views, except that they do not need to belong to a materialized view group.

In addition, using read-only materialized views eliminates the possibility of
a materialized view introducing data conflicts at the master site or master
materialized view site, although this convenience means that updates cannot
be made at the remote materialized view site. The following is an example of a
read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees AS
SELECT * FROM hr.employees@orc1.world;
Updatable Materialized Views
You can make a materialized view updatable during creation by including the FOR UPDATE
 clause or enabling the equivalent option in the Replication Management tool. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.

Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an example of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
SELECT * FROM hr.departments@orc1.world;

The following statement creates a materialized view group:

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'hr_repg',
master => 'orc1.world',
propagation_mode => 'ASYNCHRONOUS');
END;
/

The following statement adds the hr.departments materialized view to the materialized view group, making the materialized view updatable:

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'hr_repg',
sname => 'hr',
oname => 'departments',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
/

You can also use the Replication Management tool to create a materialized view group and add a materialized view to it.

--------------------------------------------------------------------------------

Note:

Do not use column aliases when you are creating an updatable materialized view. Column aliases cause an error when you attempt to add the materialized view to a materialized view group using the CREATE_MVIEW_REPOBJECT procedure.
An updatable materialized view based on a master table or master materialized view that has defined column default values does not automatically use the master's default values.
Updatable materialized views do not support the DELETE CASCADE constraint.

--------------------------------------------------------------------------------

See Also:

"Materialized View Groups" for more information
Oracle9i SQL Reference for more information about column aliases


Writeable Materialized Views
A writeable materialized view is one that is created using the FOR UPDATE clause but is not part of a materialized view group. Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable read-only materialized views are allowed.

--------------------------------------------------------------------------------

Note:

Most of the documentation about materialized views only refers to read-only and updatable materialized views because writeable materialized views are rarely used.


BEGIN

DBMS_REPCAT.CREATE_MATERIALIZED VIEW_REPOBJECT(

gname => '"REP_GP1"',

sname => '"PUBS"',

oname => '"STORE"',

type => 'SNAPSHOT',

min_communication => TRUE);

END;

/

What is difference between View and Materialized View in Database or SQL?

Difference between View and Materialized view is one of the popular SQL interview question, much like truncate vs delete,  correlated vs noncorrelated subquery or primary key vs unique key.  This is one of the classic question which keeps appearing in SQL interview now and then and you simply can’t afford not to learn about them. Doesn’t matter if you are a programmer, developer or DBA, this SQL questions is common to all. Views are concept which not every programmer familiar of, it simply not in the category of CRUD operation or database transactions or SELECT query, its little advanced concept for average programmer. Views allows a level of separation than original table in terms of access rights but it always fetch updated data. Let’s see What is View in database, What is materialized View and difference between view and materialized view in database.

What is View in database

Views are logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. Performance of the view depend upon our select query. If we want to improve the performance of view we should avoid to use join statement in our query or if we need multiple joins between table always try to use index based column for joining as we know index based columns are faster than non index based column. View allow to store definition of the query in the database itself.

What is Materialized View in database

Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL

Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.