Getting Started with Oracle database-materialized view syntax

First, Oracle materialized view syntax

Create materialized view [view_name]
Refresh [Fast|complete|force]
[
On [Commit|demand] | Start with (start_time) next (next_time)
]
As
{Create a query statement for materialized views}

These are the syntax commonly used by Oracle to create materialized views (materialized view, MV), which have the following meanings:

1. Refresh [Fast|complete|force] View mode:
Fast: Incremental refresh. Assuming that the time of the previous refresh is T1, when you refresh the materialized view using fast mode, only the data that has changed from the main table to the current time period is added to the view T1. In order to record this change, a materialized view Log table is also required to establish an incremental refresh materialized view. Create materialized view Log on main table name.
Complete: Refresh all. Equivalent to re-executing the query statement that created the view.
Force: This is the default data refresh method. When you can use fast mode, the data refresh will be in fast mode, otherwise use the complete method.

2, MV Data refresh time:
On demand: Refresh when the user needs to refresh, this requires the user to manually refresh the data (you can also use the job timed refresh)
On commit: Refreshes the data in the MV immediately when data is submitted in the main table;
Start ... : Starts at a specified time and is refreshed once at intervals (specified by next);

Oracle's materialized views provide powerful capabilities to pre-compute and save the results of more time-consuming operations such as table joins or aggregates, so that when you execute a query, you can avoid these time-consuming operations and get results quickly. Materialized views are similar in many respects to indexes: the purpose of using materialized views is to improve query performance; Materialized views are transparent to the application, adding and removing materialized views does not affect the correctness and validity of the SQL statements in the application; Materialized views need to occupy storage space, and materialized views should be refreshed when the base table changes.

Materialized views can be divided into the following three types: a materialized view containing aggregates, a materialized view that contains only connections, and a nested materialized view. The limitations of the fast refresh of three materialized views are very different, but not the other way. You can specify several options when you create a materialized view, following a brief description of several major choices:

1. Creation mode (build Methods): Includes build IMMEDIATE (immediate) and build DEFERRED (delay). Build immediate generates data when a materialized view is created, and build deferred does not generate data when it is created, and generates data as needed later. The default is build IMMEDIATE.

2, query Rewrite: including enable query Rewrite and disable query Rewrite two kinds. Indicates whether the materialized view that was created supports query rewriting, respectively. Query rewriting means that when querying a materialized view's base table, Oracle automatically determines whether the query materialized views can be used to get results, and if so, avoids aggregation or join operations and reads data directly from the materialized views that have been computed. The default is disable QUERY REWRITE.

3. Refresh: Refers to when the materialized view is synchronized with the base table when a DML operation occurs on the base table. There are two modes of refresh: On demand and on COMMIT. On demand refers to materialized views that are refreshed when required by the user and can be manually dbms_mview. Refresh, or refresh by job timing. On commit indicates that the materialized view is refreshed at the same time that the DML operation of the base table is committed. There are four ways to refresh: FAST, complete, force, and never. Fast refresh takes an incremental refresh and refreshes only the changes made since the last refresh. Complete refreshes the entire materialized view with a full refresh. If you choose force Mode, Oracle will determine whether it can be refreshed quickly, or if it can be fast, or else complete. Never refers to materialized views without any refreshes. The default value is force on DEMAND.

When you create a materialized view, you can specify an order BY statement so that the resulting data is saved in a certain order. However, this statement is not written into the definition of materialized views and is not valid for subsequent refreshes.

4. Materialized View log: If you need to make a quick refresh, you need to establish a materialized view log. Materialized view logs can be built as ROWID or primary key types, depending on the need for a fast refresh of different materialized views. You can also choose whether to include sequence, including NEW values, and a list of specified columns.

You can indicate that the on Prebuild table statement establishes a materialized view on an existing sheet. In this case, the materialized view and the table must have the same name. When you delete a materialized view, a table with the same name is not deleted. Query rewriting for this materialized view requires that the parameter query_rewrite_integerity must be set to trusted or stale_tolerated.

Materialized views can be partitioned. and partition-based materialized views can support partition change tracking (PCT). Materialized views with this feature can still be quickly refreshed when the base table is partitioned for maintenance operations. For clustered materialized views, you can use cube or rollup in the group by list to create aggregated materialized views of different levels.

Getting Started with Oracle database-materialized view syntax