Reflection on materialized view of database

Yesterday after the code was submitted, there is no task today, I look at the code whether there is an omission, there is any problem, look at the previous writing database to the DTO encountered problems.

Table A contains several fields related to table B and table C found in the company database tables. According to the database design specification, if more than one field of Table B appears in table A, this violates the third paradigm, which increases the consumption of database memory, especially if the amount of data is large, the memory impact will still exist.

When I was an intern at a German company, leader told me that although I had to abide by the database design code, in some cases, I had to break these paradigms, such as the need to use space to change time.

Sacrifice space to change time, this in doing algorithm often use, as long as the sacrifice of space in a reasonable range, to win a lot of time to balance, or calculate the good optimization.

When it comes to the design of a few tables of the company's database, as mentioned above, I went to check a lap, asked a circle, (because this is a problem that can be solved by the view), found that the common view in fact with the establishment of a few tables of the connection relationship statement to query is the basic same time consumption.

Later asked about the existence of materialized views, Oracle itself is also supporting materialized views, then what is materialized view? What are the advantages and disadvantages of materialized views?

Mview Chinese name is materialized view (materialized views), compared to normal view, Mview difference is that Mview results will be saved in a normal data table, when the Mview query will not be created Mview base table query, Instead, the data in the Mview table is updated directly by querying the mview corresponding result table, and then by a regular refresh mechanism.

So the advantages of materialized view is very intuitive, the query speed relative to the normal view will be greatly improved.

Of course, according to its definition, the data is stored in the same way as the normal table, which takes up a lot of storage space.

There must be truth in the existence of things. So what are the advantages of materialized views compared to ordinary tables?

1. Materialized views have the advantages of view, the view can simplify the operation of the user, can hide the relationship between the data table (extended to provide security protection of confidential data), can be used to reconstruct the database to provide some logical independence, etc., can be referenced

http://blog.csdn.net/fm0517/article/details/5625949

2. Since it is a view, that is a query statement, so you can query out some of the data, such as select Top 1000, so that you do not need to store all the data, a slight reduction of the materialization of the shortcomings (this article for their own consideration of the situation, has not tried)

3. Since the data is stored, it can also be used as a database copy

The above is what I can understand, the following is to find the supplement:

The main functions of mview in practice are listed below:

• Reduce network burden: Distribute data from one database to several different databases via MV, reducing the network burden on a single database by accessing multiple databases.
• Build a distribution environment: Distribute data to multiple node databases by distributing them from a central database.
• Copy a subset of data: MV can be filtered at row-level/column-level so that you can replicate the part of the data you need.
• Support Offline Computing: MV does not require a dedicated database connection, and users can replicate the part of the data they need to their needs.

Refresh of Mview:

Oracle supports three modes of refresh, 1. Full refresh 2. Fast Refresh 3. Forced refresh

1. Full refresh: Delete all mview data and insert into Mview based on query generation when creating Mview

2. Fast refresh: Refresh only the data modified since the last refresh, soon (need to establish Mview Log)

3. Forced refresh: When the fast refresh cannot be done, the system will be fully refreshed, the combination is forced to refresh

Refresh statement:

exec dbms_mview.refresh (' mvt ', ' F '); F is fast C for complete, and a force is the default

Refresh Mview:

Create job to refresh, specify refresh statement when creating Mview start with: First refresh time, Next: Refresh Interval

Essential components of the Mview:

1. Base table

2. Hidden Mview Objects

3.MView table

4. Index of the base table

Index of 5.MView

For Mview that require a quick refresh:

6.MView Log

7. Maintenance of mlog$_ internal triggers

Reflection on materialized view of database