Create a materialized view (graphic)

Create a materialized view (graphic)

Create Materialized View Details

I. What is a materialized view?

Materialized View is a database object that contains query results. It is a local copy of remote data or used to generate a summary table based on the sum of data tables. Materialized View stores data based on remote tables, also known as snapshots.

Ii. role,

In a similar statistical function, query operations are inevitable. If these query operations are frequent, the overall database performance is critical. The materialized view achieves real-time synchronization between remote data sources and local data, that is, regular refresh. By creating a materialized view locally, the query efficiency is greatly improved.

3. flowchart:

 

When you want to access data in another database table across local databases, dblink of the remote database must be created in the local database, dblink allows you to access data in a remote database table just like a local database. Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits. Therefore, you need to create a materialized view on a common view. The program can greatly improve the efficiency by accessing the materialized view.

 

4. Graphic steps (using PL/SQL)

1. Configure the network service name on the client

 

 

2. Create a DataBase Links

 

3. Create a Materialized View

 

Modify the SQL statements and add the view creation mode. The refresh method and synchronization interval are as follows:

Note:

There are two ways to create a Materialized View: on demand and on commit:

On demand indicates that the materialized view is refreshed when the user needs it. It can be refreshed manually or regularly by JOB.

On commit indicates that the Materialized View refreshes the base table while submitting the DML operation.

There are four refresh Methods: FAST, COMPLETE, FORCE, and NEVER.

FAST: Uses incremental refresh to refresh only the modifications made since the last refresh.

COMPLETE: Completely refresh the entire Materialized View

FORCE: Oracle determines whether a quick Refresh can be performed during refresh. If yes, it adopts the FAST method; otherwise, it adopts the COMPLETE method.

NEVER indicates that the materialized view is not refreshed.

The default value is force on demand.

 

4. Create a view (Statistics) on the Materialized View)

 

5. Create a materialized view on the statistics View

 

5. Use the following command to create an instance:

1. Create a DB link

-- Drop existing database link

Drop database link ZHUHAI. COM;

-- Create database link

Create database link ZHUHAI. COM

Connect to ZHUHAI

Using 'haikou ';

2. Create a Materialized View

Create materialized view MV_DBDIC

REFRESH FORCE ON DEMAND

START WITH SYSDATE

Next sysdate + (2 // (24*3600 ))

AS

SELECT "DBDIC ". "ID" "ID", "DBDIC ". "DBNUM" "DBNUM", "DBDIC ". "NAME" "NAME" FROM "DBDIC" @ ZHUHAI. COM "DBDIC ";

 

3. Create a view

Create or replace view countview

Select COUNT (*) COUNT

From MV_DBDIC;

4. Create a materialized view on the view created in step 3.

Create materialized view MV_COUNTVIEW

Refresh force on demand

As

Select *

From COUNTVIEW;

Vi. Notes:

1. Configure the network service name of the data source on the client first.

2. The source table of the data source must have a primary key.

 

Memo: if any comrade has any questions, please feel free to ask me and make progress together.

Qq: 843620202

 

 

How can a materialized view be created? What is the meaning of this parameter?

1. Create a common synonym [public synonym] For Your materialized view, so that all users can access your materialized view.
2. create public synonym synonym_name for Materialized View name;
3. When other users access synonym_name in the future, it is equivalent to accessing your materialized view. In addition, because the public alias is used, you do not need to grant special permissions.

Note: It is worth noting that the permission to create public synonym should be granted to the user,
Grant create public synonym to user;

---
The above is helpful to you.

Materialized View Creation

You must create a materialized view log for the table "zjhis". "xx_xx" to add the materialized view.

When creating a materialized view, you set On demand.
Therefore, it does not automatically refresh. Manual refresh or job creation is required for execution. This must be created manually.

If it is set to On commit
It will be automatically refreshed when the base table is updated. You do not need to create another job or manually refresh it.