Oracle Materialized View-general understanding of query rewrite 3

In query rewrite, we can see that this technology is inseparable from one thing, the materialized view, mview for short. this is the first Technology launched by Oracle in 8i. In addition to using query rewrite, mview also plays an important role in master-slave replication, here we are mainly concerned about query rewrite. Oracle is getting stronger and stronger in query rewrite. in Oracle 8i, it is basically the query rewrite of text match, there is a large update in 9i/10g. It also supports general query rewrite (except for text match). If you can try the last SQL statement, you cannot rewrite in 8i, in 9i, you can:

SQL> DESC mvtest8
Name null? Type
-------------------------------------------------------
Owner varchar2 (30)
Object_name varchar2 (128)

SQL> Create materialized view mv8_test
2 enable query rewrite
3
4 select owner, count (*) rowcnt from mvtest8
5 group by owner;

SQL> select count (*) from mvtest8 where owner = 'sys ';

When using mview to implement query rewrite, you need to have a basic understanding of mview. The main points are as follows:

1. The design of mview can meet the requirements of SQL rewrite.
2. For mview refresh, mivew is an intermediate table and the data may not be updated. It is also important for a large amount of data refresh methods.

In fact, there are some conflicts between these two points. To implement the first point, complicated mview definitions may help improve the execution efficiency after query rewrite, however, for a complex mview, all records may be copied for each refresh. For mview refresh, mview can be incrementally refreshed when there is a mview log, in general, the following conditions must be met for incremental Refresh:

1. In mview SQL, functions with uncertain return values, such as sysdate and rownum, cannot be included.
2. When the mview SQL statement only includes a summary, it is best to add grouping functions such as Count (*) and count (field.
3. mview SQL only includes join.

The syntax for creating mview and mview log is not mentioned here. mview Refresh has three options: full refresh; fast: Incremental refresh using mview log; force: if you cannot use fast, use full. there are two main types of refresh time: On Demand: manual refresh (crontab, job, schedule, etc.), on commit: refresh immediately after commit (must be fast, otherwise you are miserable ).