Shulou(Shulou.com)05/31 Report--

How to understand the working principle of completely refreshed materialized view in Oracle12c, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a simpler and easier way.

What is a materialized view?

Materialized views are database objects used to replicate data and improve the performance of query operations.

To quote a sentence from Darl Kuhn, the answer is: "periodically empty and load tables of query results"

We can use a small example to simulate the simplest process of completely refreshing the materialized view.

Two definitions are defined before the experiment:

Main table: SH.costs, sample table in example

Base table: SH.mv_costs, the physical table that holds the query result set, that is, the place where the "simulated materialized view" actually holds the data.

In experiment 1, the simulation completely refreshes the materialized view

1. The main table is SH.costs

SH@pdb1 > select * from costs where rownum select promo_id,sum (unit_cost) cost from costs group by promo_id

PROMO_ID COST

--

999 9168179.93

350 242949.08

351 258268

3. Use CTAS to create a table to hold the result set of common queries, which is now equivalent to the base table of the materialized view.

SH@pdb1 > create table mv_costs as select promo_id,sum (unit_cost) cost from costs group by promo_id

4. The process of performing a complete refresh of the base table of the materialized view is simulated as follows. First delete all data in the base table, and then insert the query result set of the source table into the base table.

SH@pdb1 > delete mv_costs

SH@pdb1 > commit

SH@pdb1 > insert into mv_costs select promo_id,sum (unit_cost) cost from costs group by promo_id

SH@pdb1 > commit

5. Query the data of the materialized view base table.

SH@pdb1 > select * from mv_costs

PROMO_ID COST

--

999 9168179.93

350 242949.08

351 258268

Experiment 2, create a completely refreshed materialized view

At the end of the simulation, let's create a real materialized view, a completely refreshed materialized view as in the above simulation example.

SH@pdb1 >

Create materialized view mv_costs_1

Segment creation immediate

Refresh

Complete

On demand

As select promo_id,sum (unit_cost) cost from costs group by promo_id

From the following sql statement, you can see that the materialized view we created consists of two parts: the view and the base table. The base table is the place where the query result set is really stored.

SH@pdb1 > col object_name for A20

SH@pdb1 > select object_name,object_type from user_objects where object_name like 'MV_COSTS_1' order by object_name

OBJECT_NAME OBJECT_TYPE

MV_COSTS_1 TABLE

MV_COSTS_1 MATERIALIZED VIEW

Perform a complete refresh by hand, the command execution effect and the delete mv_costs, insert into mv_costs select... It's the same.

SH@pdb1 > exec dbms_mview.refresh ('MV_COSTS_1','C')

This is the answer to the question about how to understand the completely refreshed materialized view in Oracle12c. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.