Restrictions on quick refresh of Oracle Materialized View

 

An Introduction to materialized views:

Http://blog.csdn.net/tianlesoftware/article/details/4713553

 

On Lao Yang's blog, I saw some column descriptions, sorted them out, and reproduced them. The original Article link is as follows:

 

Rapid refresh of materialized views (1)

Http://yangtingkun.itpub.net/post/468/14245

 

Rapid refresh of materialized views (2)

Http://yangtingkun.itpub.net/post/468/16456

 

Rapid refresh of materialized views (3)

Http://yangtingkun.itpub.net/post/468/16496

 

 

Materialized views can be refreshed in three ways: complete, fast, and force:

(1) A complete refresh operation deletes all records in the table (if a single table is refreshed, truncate may be used ), then, the materialized view is regenerated based on the definition of the query statement in the materialized view.

(2) fast refresh uses the incremental refresh mechanism to only refresh all operations on the base table since the last refresh to the materialized view.

(3) force mode, which is the default mode. Oracle automatically determines whether the quick refresh condition is met. If yes, it performs a quick refresh. Otherwise, it performs a full refresh.

 

Apparently, quick refresh is the first choice for Materialized View refresh. However, not all materialized views can be refreshed quickly. Only materialized views that meet certain conditions can be refreshed quickly.

 

The restrictions for quick refresh vary depending on the query. The following summarizes the restrictions for quick refresh for different types of materialized views.

 

Supplement:

According to Lao Yang, these restrictions are based on the Oracle 9i version. With the upgrade of the Oracle version, these restrictions become wider and wider.

 

 

1. All types of quick refresh materialized views must meet the conditions

1. materialized views cannot contain references to non-repeated expressions, such as sysdate and rownum;

2. materialized views cannot contain references to long and long raw data types.

 

2. Only the connected materialized view is included

1. All the conditions for quick refresh of materialized views must be met;

2. Group by statements or aggregate operations cannot be included;

3. If the where statement contains an external connection, the unique constraint must exist in the connection column of the table in the connection;

4. If no external connection is included, there is no restriction on the where statement. If an external connection is included, the where statement can only use and can only use "=.

5. The rowid of all tables in the From Statement List must appear in the SELECT statement list.

6. All Tables in the from Statement List must create a materialized view log based on the rowid type.

 

 

Iii. materialized view containing Aggregation

1. All the conditions for quick refresh of materialized views must be met;

2. materialized view logs must be created for all tables queried by materialized view. materialized view logs must meet the following requirements:

(1) contains all columns in the Materialized View query statement, including columns in the select list and columns in the where statement;

(2) rowid and including new values must be specified;

(3) If basic operations include both insert, update, and delete operations (not including insert operations only), the materialized view log should include sequence.

 

3. Permitted Aggregate functions include sum, Count, AVG, stddev, variance, Min, and Max;

 

4. You must specify count (*);

If you do not add count (*), the materialized view for quick refresh will be successfully created. However, by executing the explain_mview process, you can find that count (*) can refresh the insert operation quickly, however, the update and delete operations may cause a quick refresh failure.

 

5. If the aggregate function except count is specified, count (expr) must also exist;

For example, if sum (a) is included, both count (a) must be included ).

 

6. If variance (expr) or stddev (expr) is specified, sum (expr) must be specified except count (expr;

Oracle recommends that sum (expr * expr) be included ).

 

7. The select list must contain all group by columns;

 

8. If the materialized view is in the following situation, the quick refresh only supports regular DML insertion and direct loading. This type of Materialized View is also called the insert-only materialized view;

Materialized views include min or Max Aggregate functions;

Materialized View contains sum (expr), but does not include count (expr );

The materialized view does not contain count (*).

 

Note: If this materialized view is created and the refresh mechanism is on commit, there may be potential problems. If an update or delete statement occurs, the materialized view will not be automatically refreshed and no error will be reported unless it is completely refreshed manually.

 

9. If it contains inline views, outer joins, self joins, or grouping set, the compatibility setting must be above 9.0;

 

10. If the materialized view is created on the view or subquery, the view must be fully merged.

 

11. If there is no external connection, there is no restriction on the where statement. If an external connection is included, the where statement must only include and connections and "=" operations. For clustered materialized views that contain external connections, quick refresh supports modifying the outer table. The Inter table's join column must have a unique constraint.

 

12. materialized views that contain rollup, cube, and grouping set must meet the following restrictions:

The SELECT statement list should contain the grouping identifier: it can be the grouping_id function of all columns in the group by expression, or the grouping function of each column in the group by expression;

 

For example, if the group by statement is group by cube (A, B), the Select list should include grouping_id (a, B), grouping (A), and grouping (B ).

 

Group by cannot generate repeated grouping.

For example, group by a, rollup (a, B) does not support fast refresh because it contains repeated grouping :( A), (A, B), ().

 

Iv. materialized view containing Union all

1. The Union all operation must be performed on the top layer of the query. One exception can be found: Union all is on the second layer, while the first layer query statement is select * from;

2. Each query block connected by the Union all operation should meet the restrictions of fast refresh;

3. The select list must contain a maintenance column called the Union all identifier. The identifier column of each union all branch should contain different constant values;

4. external connections, remote database tables, and query of clustered materialized view definitions that can only be inserted are not supported;

5. Refresh Based on partition change tracking (PCT) is not supported;

6. Set the compatibility to 9.2.0.

 

5. nested Materialized View

Each layer of the nested materialized view must meet the restrictions of rapid refresh;

Quick refresh on commit is not supported for nested materialized views that contain both aggregation and connection.

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940