"Oracle" materialized view

Materialized view: A special view with physical storage, just like a Table object. Materialized views and tables can be queried directly. It is one of the effective methods to optimize the query performance and improve the efficiency of the system, which is the function of the distributed query through the read-write separation mechanism.

Create:

Create View  [fast|complete|force][on[Commit|demand]|  with next  (next_time)][enable query rewrite  ]as {Create a query statement for materialized views}

To create a materialized view:

--The materialized view log needs to be created using the fast Refresh method:SCOTT>CreateMaterializedView Log  onEMP;--To create a materialized view:SCOTT>CreateMaterializedViewEmp_mview2Build Immediate3Refresh Fast on Commit  4Enable query rewrite5   as  6  Select *  fromEmp

To delete a materialized view:

SCOTT>dropview  Emp_mview; SCOTT>dropviewlog on EMP;

Creating materialized views requires create materialized View permissions:

SYS>Grantcreateview to Scott;

Query rewriting: When querying the base table of a materialized view, Oracle automatically determines whether the results can be obtained by querying materialized views. The optimizer automatically calculates whether materialized views can meet query requirements and improve performance. If requirements are met and performance is improved, the optimizer overrides the query and uses materialized views to obtain the results. Query rewriting is transparent to the user.

To turn on the query rewriting feature, set the query_rewrite_enabled parameter to true:

SYS>show parameter query_rewrite_enabledname                                 TYPE        VALUE--------------- --------------------------------------------------------------query_rewrite_enabled                string      TRUE

How the materialized view is refreshed:

• On commit: The materialized view is refreshed immediately after the base table updates the data submission.
• On demand: Manually call the Dbms_mview.refresh process to refresh the materialized view.

Complete: Full refresh to re-execute the SQL statement that created the materialized view.

Fast: Incremental refresh, updating only the changed data, to create a materialized view log.

Force: Use Fast update first, or complete if it fails.

Never: not refreshed.

"Oracle" materialized view