The difference between materialized and normal views

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 improves overall query performance and is not substantially better.

Materialized view: A database pair 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, or they can be called snapshots!

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.

3. 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, if no Refresh method and refresh mode are specified, Oracle defaults to force and demand.

4. 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 ')

5. 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 to

It is important to note that the base table needs to have a PRIMARY key constraint during the actual creation process, otherwise it will error (ORA-12014)

6. 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. There are four ways to refresh (as described above): 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 refresh

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 ')

7, materialized view has the same characteristics as table, so we can create indexes for it, create methods and table

8. 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

The difference between materialized and normal views