Differences between Oracle normal view and materialized view ____oracle

Transferred from: http://blog.csdn.net/joshua_peng1985/article/details/6213593 Introduction

Materialized views are a special physical table, and the materialized view is relative to a normal view. Normal view is a virtual table, the application of a large limit, any query on the view, Oracle is actually converted into a query of the views SQL statements. This improves overall query performance without substantial benefits.

1, materialized view type: On DEMAND, on COMMIT
The difference is in the different refresh methods , on demand, as the name suggests, only when the materialized view "needs" is refreshed to refresh (refresh), that is, to update the materialized view to ensure consistency with the base table data; On commit is to say, Once the base table has a commit, a transaction commits, it refreshes immediately, updating the materialized view immediately, making the data consistent with the base table.

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 it to be done in this simplest, like normal view, so it is inevitable that the default value problem is involved. That is to say, the default value of Oracle's important definition parameters for materialized views is something we need to pay special attention to.
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 by the query, and get corroboration;
(2) Materialized view is also a kind of segment (segment), so it has its own physical storage attribute;
(3) Materialized views occupy the database disk space, which can be supported by the User_segment query results;
Create statement: Create materialized View mv_name as SELECT * FROM table_name
By default, if the Refresh method and refresh mode are not specified, Oracle defaults to force and demand.

3. How the materialized view data is updated with the base table.
Oracle offers two ways to manually refresh and refresh automatically, by default, by manual refresh. That is, we perform a manual implementation of a system-level stored procedure or package provided by Oracle to ensure that materialized views are 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 procedures or packages to implement.

4. On demand features of materialized views and their differences from on commit materialized views
The former does not refresh (either manually or automatically) without updating the materialized view, and the latter does not refresh to update the materialized view, as long as the base table has a commit.
To create a materialized view of a timed refresh:

Create materialized view Mv_name Refresh Force on demand start with Sysdate
next sysdate+1 (Specify materialized views once a day)

The materialized view created above is refreshed daily, but does not specify a refresh time, if you want to specify a refresh time (such as refresh once every night 10:00):

Create materialized view Mv_name 
Refresh Force on Demand 
start with Sysdate 
next to_date (concat (sys Date+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 does not differ from the materialized views created on demand above. Because on demand is the default, on commit materialized view, you need to add more parameters.

Note that it is not possible to specify an on COMMIT at the time of the definition, but with a parameter.
To create an on commit materialized view:

Create materialized view Mv_name Refresh force on commit as SELECT * FROM table_name

Note: In the actual creation process, the base table needs to have a PRIMARY key constraint, otherwise it will complain (ORA-12014)
6, materialized view of the refresh
Refresh: Refers to how materialized views are synchronized with the base table when a DML operation occurs on the base table. There are two modes of refreshing: On demand and on COMMIT. Above
There are four ways to refresh: FAST, COMPLETE, Force, and never.
The fast refresh takes an incremental refresh and refreshes only the modifications that have been made since the last refresh. The complete refresh completely refreshes the entire materialized view. If you choose force, Oracle will be refreshed to determine whether a quick refresh can be done, if you can use the fast method, otherwise use the complete method. Never refers to materialized views without any refreshes.

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 it at 10 o'clock every night:

Alter materialized view Mv_name 
Refresh Force on Demand 
start with Sysdate 
next to_date (Concat ( Sysdate+1, ' dd-mm-yyyy '), ' 22:00:00 '), ' dd-mm-yyyy hh24:mi:ss ')

7. Materialized views have the same characteristics as tables, so you can create indexes on them like tables, creating methods as well as tables.

8, materialized view Delete:
Although materialized views are managed with tables, in the Plsql tools that are often used, you cannot delete tables by deleting them (right-clicking on the table and not deleting materialized views), and you can use statements to implement:

Drop materialized View Mv_name

Other references the difference between a normal view and a materialized view

Normal view and materialized view are not a thing at all, said the difference is recklessly together, first understand the basic concept, the normal view is not stored any data, he only defined, in the query is converted to the corresponding definition of SQL to query, and materialized view is to transform the data into a table, the actual storage of data, so query data , there is no need to correlate a large number of tables, if the table is very large, in the temporary table space to do a lot of operations.
Three features of normal view:
1, is a simplified design , clearly encoded things, he does not mention High-performance, his presence will only degrade performance (such as a view of 7 table Association, another view of 8 tables, programmers do not know, feel very convenient, the two view Association to do a view, it is miserable), His existence has unfinished design convenience.
2, second, is security , in the authorization to other users or viewing angle, multiple table association only allowed to view, not allowed to modify, a single table can be with the READ only to control, of course, some projects based on the view to do object-oriented development, that is, in view to do instand of triggers , I personally do not stand the same, although the development of convenient, but not necessarily a good thing.
3, from different angles to see different dimensions, views can be divided into dimensions and permissions, and make a number of dimensions of the synthesis, that is, you can see from a different point of view, and the table is an entity, the general dimension is less (such as: People table and Identity Table Association, from the People table can view people's dimension statistics, from the identity, can see different kinds of identity have those people or how many people, and then another such as System View user_table, TAB, user_objects these views, different users will see is certainly not the same, looking at their own things.

Materialized views, which are used in OLAP systems, of course, a small subset of the OLTP system's ability to improve performance will take a little bit, because table associations are expensive, so many people in development are like handing this price to a regular dump, and Oracle certainly provides this functionality Convert the view (or a large SQL) information to the physical data store, and then provide a different strategy : Timing brush or timely brush, incremental brush or global brush, etc. can be selected according to the actual situation, in short you are poor table, not view. about the difference between refresh and indexing

They're not connected. Refresh I don't know if you're referring to the refresh of materialized views, because the concept of refreshing is very broad, you said here I understand the materialized view of the refresh, it has also been said, this is a strategy and method, in fact, it is through the table on the map associated with the creation of the corresponding log, According to the SQL synchronization of log information to materialized view, generally speaking: timing is generally a global brush, in a timely manner is generally local brush.

And the index says it's a lot, it can be said that the index is a course, about the point, the index generally has a normal index, bitmap index, unique index (and Full-text index What, generally not), in fact, careful study will find whether the index is a B + Tree based, And the way to store the same as the table, is a paragraph, but only the internal tree relationship.
1. The normal index is based on the B + tree to find the first (orderly at the time of the index), and then the current as the benchmark, backward order to find the health values that do not meet the criteria.
2, the bitmap is on the leaf node according to the bitmap type of leaf node value for 01 encoding storage (such as the field has 1, 2, 33 values, will be in the leaf node has three bitmaps, each bitmap according to the health value and ROWID order storage is 1, whether 2, is 3, so under the RBO statistics quickly, The CBO is generally considered a normal index.
3, also follow the B + Tree to find, just find it will no longer do any operation, because it is the only.

Because B + lookup is a table-like query, and get to the ROWID after the table query, so the cost of this process and the whole table scan calculation that the result is more accelerated, Oracle will choose to go to the index or take a full table scan, of course, for the CBO and Rbo choice of the way is not the same, specifically many, The CBO relies on the statistical information of the table, Rbo is dependent on the attempt.

about the B + Tree related content, you can refer to the following article:
from B-tree, plus, b* tree talking about R-Tree