Oracle materialized view test

Materialized views are a special physical table. ldquo; Materialized rdquo; (Materialized) views are relatively common views. A common view is a virtual table, and its application has many limitations,

Materialized views are a special physical table. ldquo; Materialized rdquo; (Materialized) views are relatively common views. A common view is a virtual table, and its application has many limitations,

Oracle materialized view test

Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Any query of the view, Oracle, is actually converted to the query of the view SQL statement. This improves the overall query performance without substantial benefits. Materialized views include the on demand and on commit materialized views. As the name suggests, the on demand materialized views are refreshed only when the materialized views are refreshed (REFRESH), that is, the materialized views are updated ,, to ensure data consistency between the base table and the base table. on commit means that once the base COMMIT, that is, the transaction is committed, the materialized view is refreshed immediately, and the data and the base table are consistent. The materialized view created by ORACLE is in the on demand mode by default.

E: \> sqlplus "/as sysdba"

SQL * Plus: Release 10.2.0.4.0-Production on Tuesday November 26 10:16:39 2013

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn scott/cat
Connected.

SQL> create materialized view mv_test as select * from dept1; -- create a materialized view ON DEMAND

The materialized view has been created.

SQL> select mv. REFRESH_MODE from user_mviews mv where mv. MVIEW_NAME = 'mv _ test ';

REFRES
------
DEMAND

SQL> analyze table mv_test compute statistics; -- analyze the Materialized View

The table has been analyzed.

SQL> analyze table dept1 compute statistics; -- analysis table

The table has been analyzed.

SQL> select t. TABLE_NAME, t. NUM_ROWS from user_tables t where t. TABLE_NAME in ('mv _ test', 'dept1'); -- view the number of rows in the current base table and materialized view

TABLE_NAME NUM_ROWS
----------------------------------------
DEPT1 6
MV_TEST 6

One of the most important functions and features of materialized views is that their data changes with the changes of the base table (or the base table, master table, TESTCF in this example), and the base table data increases, more Materialized View data will be displayed, and less Materialized View data will be deleted from the base table.
But how to update it? Or how does the Materialized View data update with the base table? Oracle provides two methods: manual refresh and automatic refresh. Manual refresh is used by default. Automatic Refresh can be implemented through JOB or other methods.

SQL> create view v_test as select * from dept1; -- create a common view

The view has been created.

SQL> select * from v_test;

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz

You have selected 6 rows.

SQL> insert into dept1 (deptno, dname, loc) values (70, 'test', 'test'); -- insert a test Data

One row has been created.

SQL> select * from v_test; -- normal view data has changed

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 wang gongzuo
60 zhuzhzu zhuzhuz
70 test

You have selected 7 rows.

For more details, please continue to read the highlights on the next page:

Related reading:

Oracle materialized view creation reports ORA-00942 error resolution

Refresh the materialized view of Oracle stored procedures

Use of Oracle Materialized View

Materialized View for two-way synchronization of Oracle database tables

Oracle materialized view application notes