Oracle Database fragmented Knowledge-materialized view (GO)

Materialized views are a special physical table, and the materialized view is relative to the normal view. The normal view is a virtual table, the limitations of the application are large, any query on the view, Oracle is actually converted into a query of the views SQL statement. This has no substantial benefit in improving overall query performance.
1. Types of materialized views: on DEMAND, on COMMIT

The difference between the two is that the Refresh method is different, on demand as the name implies, only in the materialized view "need" is refreshed, only to refresh (refresh), that is, to update the materialized view to ensure consistency with the base table data, and on commit is that once the base table has a commit, that is, the transaction commits , the materialized view is immediately refreshed to make the data and the base table consistent.
2. On demand materialized view

The creation of materialized views is inherently complex and requires optimization of parameter settings, especially for large production database systems. But Oracle allows this to be done in the simplest way, similar to a normal view, so it inevitably involves a default value problem. This means that the default value of Oracle's important definition parameters for materialized views is something we need to pay special attention to.

Features of materialized views:

(1) Materialized view in a sense is a physical table (and not just a physical table), which can be user_tables query out, and get support;

(2) Materialized view is also a kind of segment (segment), so it has its own physical storage properties;

(3) Materialized views Occupy database disk space, which can be supported by user_segment query results;

Creating statements: Create materialized view mv_name as SELECT * FROM table_name by default, Oracle defaults to force and demand if no Refresh method and refresh mode are specified.
How does the materialized view's data update with the base table? Oracle provides two ways to manually refresh and refresh automatically, by default, by manual refresh. That is to say, by manually executing a system-level stored procedure or package provided by Oracle, the materialized view is consistent with the base table data. This is the most basic way to refresh. Automatic refresh, in fact, Oracle will create a job, through the job to invoke the same stored procedure or package, to implement.

The characteristics of On demand materialized view and its difference from on commit materialized view, that is, the former does not refresh (manual or automatic) does not update materialized view, the latter does not refresh will also update materialized view, as long as the base table has a commit.

Create materialized views of timed refreshes: Create materialized view mv_name Refresh Force on demand start with Sysdate next sysdate+1 (Specify materialized view refreshed once per day)

The materialized views created above are refreshed daily, but no refresh time is specified, if you want to specify a refresh time (such as a timed refresh once every night 10:00): Create materialized view mv_name Refresh Force on Demand start With Sysdate next to_date (Concat (To_char (sysdate+1, ' dd-mm-yyyy '), ' 22:00:00 '), ' dd-mm-yyyy hh24:mi:ss ')
3. On commit materialized view

The creation of the on commit materialized view differs little from the materialized view created on demand above.   Because on demand is the default, on commit materialized view, you need to add another parameter. It is important to note that only on COMMIT cannot be specified at the time of definition, and that a parameter is included.

Create on commit materialized view: Create materialized view mv_name Refresh force on commit as SELECT * FROM table_name NOTE: In the actual creation process, the base table needs to have a primary key Constraint, otherwise it will be an error (ORA-12014)

4. Refresh of materialized view

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. As described above

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.

For a materialized view that has already been created, you can modify its refresh mode, such as modifying the materialized view Mv_name to refresh once every night 10 o'clock: Alter materialized view mv_name refresh Force on Demand start With Sysdate next to_date (Concat (To_char (sysdate+1, ' dd-mm-yyyy '), ' 22:00:00 '), ' dd-mm-yyyy hh24:mi:ss ')
5. The materialized view has the same characteristics as the table, so we can create an index for it, like a table, and create the same method as the table.
6. Deletion of materialized views:

Although materialized views are managed together with tables, in the frequently used Plsql tool, it is not possible to delete a table by deleting it (right-clicking ' drop ' on the table does not delete the materialized view), which can be implemented using the statement: Drop materialized View Mv_name

Materialized View creation parameters

(1) Build build IMMEDIATE builds the data build DEFERRED when creating materialized views, and generates data when it is created, and then builds the data later as needed. The default is build IMMEDIATE.
(2) Refresh FAST Incremental Refresh uses materialized view logs to send the data rows that the main table has modified to the materialized view. Complete refreshes the entire view completely, and if the request is fully refreshed, Oracle completes the full refresh even if the incremental refresh is available. Force if an incremental refresh is available, Oracle will complete the incremental refresh, otherwise the full refresh will be completed if the Refresh method (FAST, complete, or force) is not specified. The default option is force.
(3) On DEMAND refers to materialized views that are refreshed when required by the user. On commit indicates that the materialized view is refreshed at the same time that the DML operation of the base table is committed. The default is on DEMAND.
(4) START with notifies the database when the first replication from the primary table to the local surface is completed.
(5) Next describes the refresh interval based on the time of the next refresh = the time + time interval at which the last execution completed. To ensure that the user needs to refresh at a point in time, the trunc () command is generally used to take the time to the number of days, and then add time.

(Transferred from Http://www.cnblogs.com/Ronger/archive/2012/03/28/2420962.html)

Oracle Database fragmented Knowledge-materialized view (GO)