Materialized View Management of Oracle

11. materialized view log management the materialized view log is a table that maintains the history of operations on the materialized view. To create a materialized view log, you must

11. materialized view log management the materialized view log is a table that maintains the history of operations on the materialized view. To create a materialized view log, you must

1. Concept of Materialized View

Materialized View Management is an object used for summarizing, pre-computing, copying, or distributing data. It can be used in large databases to increase the SUM, COUNT, AVG, MIN involved, the query speed of tables such as MAX. As long as statistics are created on the Materialized View Management, the query optimizer automatically uses the materialized view management, this feature is called query rewrite (QUERY rewriting ). unlike a common view, the materialized view manages and stores data, occupying the physical space of the database.

Users who CREATE Materialized VIEW Management must have the following permissions: CREATE materialed VIEW, create table, create view, SELECT, etc, if it is created in other modes, you must have the create any materialized view and SELECT permissions on the table.

To QUERY the Materialized View Management in the rereference mode, you must have the global query rewrite or query rewrite permission for the referenced table.

If you plan to use the Materialized View Management, add QUERY_REWRITE_ENABLE = TRUE to the parameter file.

2. Considerations for creating a Materialized View:

Whether to fill in data after creation

How long does it take to refresh

Use the refresh type: COMPLE (full), FAST (FAST), FORCE (forced), NEVER (NEVER)

3. Create a Materialized View

Create materialized view TEST3

PCTFREE 0 TABLESPACE MTEST

STORAGE (INITIAL 1 m next 1 m pctincrease 0)

BUILD DEFERRED

REFRESH FAST ON COMMIT

ENABLE QUERY REWRITE

As select EMP_NO, SUM (QTY_PSC) AS QTY_PSC from bsempms group by EMP_NO;

4. Delete the Materialized View

Drop materialized view TEST3;

5. tools for managing and evaluating the Materialized View

To run DBMSSNAP. SQL and DBMSSUM. SQL, create packages DBMS_MVIEW and DBMS_OLAP for the materialized view.

DBMS_MVIEW is used to execute management activities.

DBMS_OLAP determines whether the materialized view can improve the query performance.

6. Methods for refreshing the Materialized View

EXECUTE DBMS_MVIEW.REFRESH ('test3', 'C ');

TEST3 is the name of the materialized view, and 'C' is one of the refresh types.

Refresh types include: C-full refresh

F-fast refresh and force refresh

EXECUTE DBMS_MVIEW.REFRESH_ALL

No parameters are accepted. Refresh all parameters.

7. Integrity between materialized views

When the materialized views are nested with each other, the data integrity problem is caused by the refreshing method and time of different materialized views. In this case, we recommend that you place the associated materialized view in the same refresh group for coordinated refresh.

The MAKE process is provided in the DBMS_REFRESH package to create a refresh group.

For example:

EXECUTE DBMS_REFRESH.MAKE

(NAME => 'test _ group ',

LIST => 'loc _ EMP, LOC_DPT ',

NEXT_DATE => SYSDATE,

INTERVAL => 'sysdate + 7 ');

Create a materialized view refresh group TEST_GROUP. The refresh cycle is 7 days.

Two materialized views, LOC_EMP and LOC_DPT, are passed to the process using one parameter.

,