Oracle Materialized View
The materialized view is like a real table for front-end database users. It has operations such as select that are similar to General tables, but it is actually a view, A view that is regularly refreshed by the system (the specific refresh time is defined when the materialized view is defined). materialized views can be used to implement all functions of the view, materialized views are not used for reading, which greatly improves the reading speed. They are especially suitable for extracting some information from the big data table and using the data link connection table. the specific syntax is as follows: the materialized view is like a real table for front-end database users. It has a select operation similar to the table, but it is actually a view, A view that regularly refreshes data (the specific refresh time is defined when the materialized view is defined). materialized views can be used to implement all functions of the view, materialized views are not used for reading, which greatly improves the reading speed. They are especially suitable for extracting some information from the big data table and using the data link connection table. the specific syntax is as follows: create materialized view an_user_base_file_no_charge
REFRESH COMPLETE START WITH SYSDATE
Next trunc (SYSDATE + 29) + 5.5/24
As
Select distinct user_no
From cw_arrearage t
Where (t. mon = dbms_tianjin.getLastMonth or
T. mon = add_months (dbms_tianjin.getLastMonth,-1) drop materialized view an_user_base_file_no_charge;
Chapter 2 materialized view 8.1.5 the Enterprise Edition/individual edition must support the required permissions: grant create materialized view, and GRANT the grant query rewrite directly. to rewrite the query, you must use CBO. 13.1 how the Materialized View works to set the COMPATIBLE parameter must be higher than 8.1.0QUERY _ REWRITE_ENABLED = TRUEQUERY_REWRITE_INTEGRETY = ENFORCED-the query is only overwritten by Oracle's mandatory and guaranteed constraints and rules; TRUSTED-in addition to Oracle's mandatory and guaranteed constraints and rules, the query can also be rewritten using any relationships between user-defined data; STALE_TOLERATED-even if Oracle knows that the data in the materialized view has expired (not synchronized with fact tables, etc.), the query is overwritten. The user who creates the materialized view must have the grant query rewrite permission directly, and cannot inherit from the role. Full-text matching of internal mechanism: Starting FROM the FROM clause, the optimizer compares the text after comparison, and then compares the SELECT list with the general Rewriting Method: data fully associated with compatible groups compatible with aggregation compatible 13.2 make sure that the materialized view constraints take into account the amount of data in the real environment, you can set the constraints such as primary key, foreign key, and non-empty to NOVALIDATE, and adjust QUERY_REWRITE_INTEGRITY to TRUSTED, in this way, the goal of "spoofing" the database can be achieved. However, you must note that if such constraints cannot be guaranteed to be true and valid, the query may cause inaccurate results after rewriting. A dimension indicates the relationship between the columns in an existing table, the materialized view formed after joining a fact table can be used to merge to the "upper" table (equivalent to associating a fact table with a column representing a higher merging relationship ). Standard Syntax: create dimension every LEVEL day IS time_hierarchy.day LEVEL mmyyyy IS every LEVEL yyyy IS every time_rollup (day child of mmyyyy child of yyyy) ATTRIBUTE mmyyyyDETERMINES mon_yyyy; 13.3 DBMS_OLAP Estimation) size: DBMS_OLAP.ESTIMATE_SUMMARY_SIZE (view name, view definition, estimated NUMBER of rows, estimated NUMBER of bytes). The last two parameters are NUMBER-type output parameters. Dimension validity check DBMS_OLAP.VALIDATE_DIMENSION (view name, user name, FALSE, FALSE); SELECT * FROM dimension table name where rowin in (SEELCT bad_rowid from mview $ _ EXCEPTION ); the selected trip is a row that does not conform to the dimension definition. The recommended materialized view must first add a suitable foreign key. The package uses the foreign key to determine the relationship between tables rather than dimensions. DBMS_OLAP.RECOMMEND_MV (fact table name, 1000000000, ''); the second parameter indicates the available space size of the materialized view. A large number can be input. The third parameter is used to specify the materialized view to be retained. If it is null, other materialized views are not considered. Run "C: \ oracle \ RDBMS \ demo \ sadvdemo" and run "DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS 13.4". The final result shows that Materialized View row locks are not implemented when the OLTP system is designed to update fact tables, this affects system concurrency.
