Basic concepts and usage of Plsql_material view materialized views (concept)

2014-06-08 Baoxinjian

1. usage

A materialized view is a database object that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data tables.

materialized views store data based on remote tables and can also be referred to as snapshots. For replication, materialized views allow you to maintain a copy of the remote data locally , which is read-only.

If you want to modify the local copy, you must use the Advanced Replication feature. When you want to extract data from a table or view, you can extract it from a materialized view.

for data warehouses, the materialized views created are typically aggregated views, single-table aggregation views, and connection views.

To achieve data synchronization between two databases, there can be a time difference.

1. How to Refresh

Dbms_refresh

Dbms_

2. Specific Applications

(1). build mview log log file in source database

Create materialized view Log on W_1;

----Note: (Test is a table name or view name, a materialized view on a view, see materialized view based on a view

----Create materialized View statements:

(2). Create Materializad view syntax in statistics

Create materialized View Mv_test

----Mvtest for materialized view names

Build Immediate

The build data----created is the build deferred

Refresh Fast

----Incremental Refresh

On commit

----submitted when the base table is updated, this sentence is invalid for the view

With rowID

----here to create a materialized view based on ROWID, corresponding to the primary key

As

Select * from TEST;

----Generating materialized view data statements

(3). Refresh on Call

Dbms_refresh.refresh (' W_1 ')

3. Syntax

1. Basic syntax

4. Case Studies

Sql> SELECT * from V$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.4.0-production

PL/SQL Release 9.2.0.4.0-production

CORE 9.2.0.3.0 Production

TNS for Linux:version 9.2.0.4.0-production

Nlsrtl Version 9.2.0.4.0-production

sql> ALTER SESSION SET nls_date_format = ' yyyy-mm-dd HH24:MI:SS ';

The session has changed.

Sql> CREATE TABLE T1 (ID PRIMARY KEY, NAME) as SELECT ROWNUM, tname from TAB;

The table is created.

Sql> CREATE TABLE T2 (ID PRIMARY KEY, NAME) as SELECT ROWNUM, tname from TAB;

The table is created.

Sql> CREATE TABLE T3 (ID PRIMARY KEY, NAME) as SELECT ROWNUM, tname from TAB;

The table is created.

Sql> CREATE materialized VIEW LOG on T1;

The manifested view log has been created.

Sql> CREATE materialized VIEW LOG on T2;

The manifested view log has been created.

Sql> CREATE materialized VIEW LOG on T3;

The manifested view log has been created.

Sql> CREATE materialized VIEW mv_t1 REFRESH FAST as SELECT * from T1;

The manifested view has been created.

Sql> CREATE materialized VIEW mv_t2 REFRESH FAST as SELECT * from T2;

The manifested view has been created.

Sql> CREATE materialized VIEW mv_t3 REFRESH FAST as SELECT * from T3;

The manifested view has been created.

Sql> EXEC Dbms_refresh. Make (' Rep_test ', ' mv_t1,mv_t2,mv_t3 ', sysdate, ' sysdate + 1 ')

The PL/SQL process has completed successfully.

Sql> INSERT into T1 VALUES (, ' A ');

1 rows have been created.

Sql> INSERT into T2 VALUES (, ' A ');

1 rows have been created.

Sql> INSERT into T3 VALUES (, ' A ');

1 rows have been created.

Sql> EXEC Dbms_refresh. REFRESH (' Rep_test ')

The PL/SQL process has completed successfully.

If the table T2 is modified:

sql> ALTER TABLE T2 MODIFY NAME VARCHAR2 (32);

The table has changed.

Sql> INSERT into T1 VALUES (101, ' B ');

1 rows have been created.

Sql> INSERT into T2 VALUES (101, Lpad (' B ', +, ' B '));

1 rows have been created.

Sql> INSERT into T3 VALUES (101, ' B ');

1 rows have been created.

Sql> COMMIT;

Submit complete.

Sql> SELECT mview_name, Last_refresh_date, staleness from User_mviews;

Mview_name last_refresh_date staleness

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

Mv_t1 2008-01-23 19:22:43 Needs_compile

Mv_t2 2008-01-23 19:22:43 Needs_compile

Mv_t3 2008-01-23 19:22:43 Needs_compile

Sql> EXEC Dbms_refresh. REFRESH (' Rep_test ')

BEGIN Dbms_refresh. REFRESH (' rep_test '); END;

Thanks and regards