Rapid refresh of materialized views (1)

Rapid refresh of materialized views (1)

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

Complete refresh deletes all records in the table (if a single table is refreshed, truncate may be used), and then generates a materialized view based on the definition of the query statement in the materialized view. Fast refresh uses the incremental refresh mechanism to only refresh all operations on the base table after the last refresh to the materialized view. When the force method is adopted, Oracle automatically determines whether the conditions for quick refresh are met. If the conditions are met, hybriddb for MySQL performs quick refresh. Otherwise, hybriddb for MySQL performs 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.

Refer:

Rapid refresh of Materialized View (2): http://yangtingkun.itpub.net/post/468/16456

Rapid refresh of materialized views (3): http://yangtingkun.itpub.net/post/468/16496

All types of quick refresh materialized views must meet the following 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.

Only the connected materialized views are 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.

Next, let's take a look at an example that meets the quick refresh condition, and test the above six conditions in turn to see what errors will occur.

First, create a test environment:

SQL> Create Table dim_a (ID number primary key, name varchar2 (30 ));

The table has been created.

SQL> Create Table dim_ B (ID number primary key, name varchar2 (30 ));

The table has been created.

SQL> Create Table fact (ID number, aid number, bid number, num number );

The table has been created.

SQL> ALTER TABLE fact add constraint fk_fact_aid foreign key (AID) References dim_a (ID );

The table has been changed.

SQL> alter table fact add constraint fk_fact_bid foreign key (BID) References dim_ B (ID );

The table has been changed.

SQL> insert into dim_a select rownum, 'A' | rownum from user_objects;

74 rows have been created.

SQL> insert into dim_ B select rownum, 'B' | rownum from user_objects;

74 rows have been created.

SQL> insert into fact select rownum, MOD (rownum, 6) + 1, MOD (rownum, 5) + 1, rownum * 2
2 from user_objects;

74 rows have been created.

SQL> commit;

Submitted.

Next, we will create an example that can be successfully refreshed quickly. This materialized view only contains connections:

SQL> Create materialized view log on dim_a with rowid;

The materialized view log has been created.

SQL> Create materialized view log on dim_ B with rowid;

The materialized view log has been created.

SQL> Create materialized view log on fact with rowid;

The materialized view log has been created.

SQL> Create materialized view mv_fact refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID;

The materialized view has been created.

Next we will observe the error message in violation of the above six conditions.

1. Include sysdate/rownum or raw/long raw data type.

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num, sysdate time
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-12054: unable to set on commit refresh properties for materialized views

The error message is that the on commit attribute cannot be set. Remove the on commit statement and try again:

SQL> Create materialized view mv_fact_err refresh fast
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num, sysdate time
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-12015: cannot create a fast refresh materialized view from a complex Query

As expected, an error is reported when the materialized view for quick refresh is created. However, the prompt information is not clear and it is difficult to locate the specific problem. The error message returned when rownum is used is the same as that returned when sysdate is used.

You can get more detailed error information through the explicit _ mview process of the dbms_mview package. For specific use of this package can refer to: http://blog.itpub.net/post/468/13318

SQL> begin
2 dbms_mview.explain_mview ('select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num, rownum row_id
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID ');
7 end;
8/

The PL/SQL process is successfully completed.

SQL> select msgtxt from mv_capabilities_table where capability_name = 'refresh _ fast_after_insert ';

Msgtxt
---------------------------------------
Reference rownum in the Materialized View

By using this method, you can obtain more accurate error information.

Before testing another constraint, we should note that the constraints in the Oracle document cannot include raw and long raw. According to my test results, it is estimated that the document has encountered an error, the values are long and long raw.

SQL> ALTER TABLE dim_a add (col_raw RAW Raw (10) default '1 ');

The table has been changed.

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num, A. col_raw
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID;

The materialized view has been created.
SQL> Update dim_a set col_raw = '2 ';

74 rows have been updated.

SQL> commit;

Submitted.

SQL> select distinct col_raw from mv_fact_err;

Col_raw
--------------------
02
SQL> drop materialized view mv_fact_err;

The materialized view has been deleted.

SQL> ALTER TABLE dim_a drop (col_raw );

The table has been changed.

SQL> ALTER TABLE dim_a add (col_long long default '1 ');

The table has been changed.

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num, A. col_long
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-00997: Invalid use of long data type

SQL> ALTER TABLE dim_a drop (col_long );

The table has been changed.

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

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, count (Num) count_num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID
7 group by F. rowid, A. rowid, B. rowid, F. ID, A. Name, B. Name;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
The materialized view log on ORA-32401: "yangtk". "dim_ B" has no new value

No matter whether a connection exists or not, as long as clustering operations are included, the clustering materialized view must be refreshed quickly. This error message is one of the conditions that need to be met to refresh the clustered materialized view quickly.

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

SQL> drop materialized view mv_fact;

The materialized view has been deleted.

SQL> Create materialized view mv_fact refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 and F. Bid = B. ID;

The materialized view has been created.

SQL> drop materialized view mv_fact;

The materialized view has been deleted.

SQL> ALTER TABLE fact drop constraint fk_fact_aid;

The table has been changed.

SQL> ALTER TABLE dim_a drop primary key;

The table has been changed.

SQL> Create materialized view mv_fact refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 and F. Bid = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-12052: unable to quickly refresh the materialized view yangtk. mv_fact

The error message is not very clear, but the cause of this error has been analyzed in the Oracle error message document.

ORA-12052 cannot fast refresh Materialized ViewString.String

Cause:Either rowids of certain tables were missing in the definition or the inner table of an outer join did not have unique constraints on join columns.

Action:Specify the force or complete option. If this error occurred during creation, the materialized view definition may have been changed. Refer to the documentation on materialized views.

SQL> Delete mv_capabilities_table;

13 rows have been deleted.

SQL> begin
2 dbms_mview.explain_mview ('select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 and F. Bid = B. ID ');
7 end;
8/

The PL/SQL process is successfully completed.

SQL> select msgtxt from mv_capabilities_table where capability_name = 'refresh _ fast_after_insert ';

Msgtxt
----------------------------------------------------------
There is no unique constraint on the join columns of the internal table.

SQL> ALTER TABLE dim_a add primary key (ID );

The table has been changed.

SQL> ALTER TABLE fact add constraint fk_fact_aid foreign key (AID) References dim_a (ID );

The table has been changed.

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 be connected by and, and can only be operated by "=:

SQL> Create materialized view mv_fact refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 or F. Bid = B. ID;

The materialized view has been created.

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 or F. Bid = B. ID;
Where F. Aid = A. ID (+)
*
Error is located in row 5th:
ORA-01719: The External join operator (+) is not allowed in the OR or in operand)

This error prompt is very obvious, but the following can only be used to locate the cause of the error through the explain_mview process.

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 and F. bid! = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-12054: unable to set on commit refresh properties for materialized views

SQL> Create materialized view mv_fact_err refresh fast
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 and F. bid! = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-12015: cannot create a fast refresh materialized view from a complex Query

SQL> Delete mv_capabilities_table;

15 rows have been deleted.

SQL> begin
2 dbms_mview.explain_mview ('select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID (+)
6 and F. bid! = B. id ');
7 end;
8/

The PL/SQL process is successfully completed.

SQL> select msgtxt from mv_capabilities_table where capability_name = 'refresh _ fast_after_insert ';

Msgtxt
------------------------------------------------------------
Operator join predicates except equal sign (=)
External join in the Materialized View
One or more connections exist in the materialized view.

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

SQL> Create materialized view mv_fact_err refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 or F. Bid = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-12052: unable to quickly refresh the materialized view yangtk. mv_fact_err

The error information is listed above, and the cause of the error is clear.

SQL> Delete mv_capabilities_table;

17 rows have been deleted.

SQL> begin
2 dbms_mview.explain_mview ('select F. rowid f_rowid, A. rowid a_rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 or F. Bid = B. ID ');
7 end;
8/

The PL/SQL process is successfully completed.

SQL> select msgtxt from mv_capabilities_table where capability_name = 'refresh _ fast_after_insert ';

Msgtxt
---------------------------------------------------
The select list does not have the rowid of all slave tables.

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

SQL> drop materialized view log on dim_a;

The materialized view log has been deleted.

SQL> drop materialized view mv_fact;

The materialized view has been deleted.

SQL> Create materialized view mv_fact refresh fast on commit
2 select F. rowid f_rowid, A. rowid a_rowid, B. rowid B _rowid, F. ID,
3 A. Name a_name, B. Name B _name, num
4 From Fact F, dim_a A, dim_ B
5 where F. Aid = A. ID
6 and F. Bid = B. ID;
From Fact F, dim_a A, dim_ B
*
Error is located in row 4th:
ORA-23413: Table "yangtk". "dim_a" without materialized view logs

This error description is very clear and can be used to identify the cause of the error.