Comparison between common and materialized views of Oracle

Compared with the general views, the general views and materialized views of Oracle differ from the general views in that the materialized views manage stored data and occupy the physical space of the database. The results of the materialized view are stored in a common data table. When querying the materialized view, the basic table for creating the materialized view is no longer queried, instead, you can directly query the results table corresponding to the materialized view and update the data in the MView table through a regular refresh mechanism. First, we need to create a table and then write a SELECT statement. SQL> create table xjzhang_table1 (a varchar2 (10), B number (10); the table has been created. SQL> create table xjzhang_table2 (a varchar2 (10), B number (10); the table has been created. Insert data SQL> insert into xjzhang_table1 values ('aaa', '000000') into two tables; 1 row has been created. SQL> insert into xjzhang_table1 values ('bbb ', '000000'); 1 row has been created. SQL> insert into xjzhang_table2 values ('aa1', '123'); 1 row has been created. SQL> insert into xjzhang_table2 values ('bb1', '123'); 1 row has been created. SQL> commit; submitted completely. Create a view named xjzhang_view SQL> create view xjzhang_view as select xjzhang_table1.a, created from xjzhang_table1, xjzhang_table2 where xjzhang_table1. B = created; the view has been created. Then we query the view SQL> select * from xjzhang_view; a B ---------- bbb 2. Then we write A query statement SQL> select distinct, xjzhang_table2. B from xjzhang_table1, xjzhang_table2 where limit = limit; a B ---------- bbb 2 we can see that the query view results are consistent with the query results of the SELECT statement, indicating that the view is the description of the SELECT statement for querying one or more tables. Query the created VIEW select object_name, object_type, created, status from dba_objects where object_name = 'xjzhang _ view '; OBJECT_NAME OBJECT_TYPE created status ----------------- certificate ------------ ------- XJZHANG_VIEW VIEW 24-6 month-09 VALID we also create a table named tables and insert data SQL> create table partition (a varchar2 (10 ), B number (5); the table has been created. SQL> insert into xjzhang_table3 values ('aaa', '000000'); 1 row has been created. SQL> insert into xjzhang_table3 values ('bbb ', '000000'); 1 row has been created. SQL> commit; submitted completely. Next, we will create an object view (the object view we created here is not automatically refreshed but needs to be refreshed manually) SQL> create materialized view xjzhang_mat_view as select * from xjzhang_table3; the object view has been created. Let's query the created object view. The object view is named xjzhang_mat_view SQL> select * from xjzhang_mat_view; a B ---------- aaa 1 bbb 2 object view in A sense, it is A physical table that can be queried through DBA_TABLES to demonstrate SQL> select TABLE_NAME, TABLESPACE_NAME, STATUS from dba_tables where table_name = 'xjzhang _ MAT_VIEW '; TABLE_NAME TABLESPACE_NAME STATUS ------------------------------ -------- XJZHANG_MAT_VIEW SYSTEM VALID The created VIEW xjzhang_view SQL> select TABLE_NAME, TABLESPACE_NAME, STATUS from dba_tables where table_name = 'xjzhang _ view'; the normal VIEW is not recorded in DBA_TABLES, no corresponding table space entity view occupies a certain amount of storage space. Because it stores the query result set, it is also a type of segment, you can query SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME from DBA_SEGMENTS where segment_name = 'xjzhang _ MAT_VIEW '; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME --------------------------- ----------- Using XJZHANG_MAT_VIEW table system we also use DBA_SEGMENTS to query the common view SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME from dba_segments where segment_name = 'xjzhang _ view '; the unselected rows show that the common view is not recorded in DBA_SEGMENTS. Next, we will update the information in table XJZHANG_TABLE3 and check the change information in the object view. SQL> insert into xjzhang_table3 values ('ccc ', '123'); 1 row has been created. SQL> commit; submitted completely. SQL> select * from xjzhang_table3; a B ---------- aaa 1 bbb 2 ccc 3 Table Records added A line we will query the object view information SQL> select * from xjzhang_mat_view; a B ---------- aaa 1 bbb 2 we can see that the information of the object view has not changed, because we did not specify the refresh method and refresh mode for this view when creating the object view, therefore, after this object view is created, the default refresh method and refresh mode of this view are force demand. We can use the dba_mviews view to query the information of the object view we created. SQL> select. mview_name,. refresh_mode,. refresh_method from dba_mviews a where. Mview_name = 'xjzhang _ MAT_VIEW '; MVIEW_NAME REFRESH_MODE REFRESH_METHOD ------------------------------ ------------------ XJZHANG_MAT_VIEW demand force is refreshed manually by default, so here we update the object view SQL> EXEC DBMS_MVIEW.REFRESH ('xjzhang _ MAT_VIEW ') PL/SQL process has been completed successfully. Then we will query the SQL> SELECT * FROM XJZHANG_MAT_VIEW; a B ---------- aaa 1 bbb 2 ccc 3 again, which indicates that the data in the base table has changed, the object view content will also be written to the corresponding bucket. We can also create an automatically updated object view. Similarly, we can create a table SQL> create table xjzhang_table4 (a varchar2 (10), B number (5); the table has been created. SQL> insert into xjzhang_table4 values ('aaa', '000000'); 1 row has been created. SQL> commit; submitted completely. Then we create the object view SQL> create materialized view into refresh force on commit as select * from xjzhang_table4; create materialized view into refresh force on commit as select * from xjzhang_table4 * row 1st with an error: ORA-12054: unable to set on commit refresh attribute for materialized views rapid refresh uses an incremental mechanism, while refreshing, only for the changed data ON the base table. Therefore, quick refresh is the first choice for Materialized View refresh. However, quick Refresh has many constraints, especially for materialized views that use the on commit mode for quick refresh. The quick refresh mechanism for materialized views that contain aggregation and connection is not the same, and there are additional requirements for quick refresh of materialized views with multi-layer nesting. So many restrictions are hard to remember. When a materialized view fails to be created, the error message provided by Oracle is too simple, and sometimes you cannot locate the cause of the problem accurately. The DBMS_MVIEW.EXPLAIN_MVIEW process provided by Oracle helps you quickly locate the cause of the problem. The DBMS_MVIEW.EXPLAIN_MVIEW process provided by Oracle can help you quickly locate the cause of the problem. Let's try the package provided by ORACLE. to use the EXPLAIN_MVIEW process, you must first create an MV_CAPABILITIES_TABLE table. The procedure is as follows: execute a script to complete the SQL creation> @? The/RDBMS/ADMIN/utlxmv. SQL table has been created. Then we execute this package SQL> begin 2 dbms_mview.explain_mview ('select * from xjzhang_table4 '); 3 end; 4/PL/SQL the process has been completed successfully. Then we can use the select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'refresh % 'script to query the results. SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'refresh % '; CAPABILITY_NAME p msgtxt upper-limit REFRESH_COMPLETE N the primary table does not have any primary key constraints REFRESH_FAST N Limit N The Details table does not have the materialized view log limit N view the reason for disabling REFRESH_FAST_AFTER_IN SERT limit N view the reason for disabling REFRESH_FAST_AFTER_ON ETAB_DML REFRESH_FAST_PCT N cannot select 6 rows from any table in the Materialized View. We can see that the first REFRESH_COMPLETE N master table does not have any primary key constraints. We create a primary key SQL> alter table xjzhang_table4 add (constraint xjzhang_pri primary key (B) for xjzhang_table4. Then we create the entity view SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4; the entity view has been created. We can see that the table has been successfully created. Now we can insert data into the table to view the changes in the object view. First, we can query the records in the table and the records in the object view SQL> select * from xjzhang_table4; a B ---------- aaa 1 SQL> select * from xjzhang_mat_view1; a B ---------- aaa 1 then we insert A record SQL> insert into xjzhang_table4 values ('afd ', '20140901'); 1 line has been created. SQL> commit; submitted completely. Let's look at the content of the object view. SQL> select * from xjzhang_mat_view1; a B ---------- aaa 1 materialized view why Refresh is not changed (Refresh): When the base table has DML operations, when to synchronize the materialized view with the base table. There are two refresh modes: on demand and on commit. On demand indicates that the materialized view is refreshed when the user needs it. You can manually refresh the materialized view by DBMS_MVIEW.REFRESH or by regularly refreshing the JOB. On commit indicates that the Materialized View refreshes the base table while submitting the DML operation. There are four refresh Methods: FAST, COMPLETE, FORCE, and NEVE *. FAST refresh uses incremental refresh. Only the modifications made since the last refresh are refreshed. COMPLETE refresh completely refreshes the entire materialized view. If the FORCE method is selected, Oracle determines whether a quick Refresh can be performed during refresh. If yes, it adopts the FAST method; otherwise, it adopts the COMPLETE method. NEVER indicates that the materialized view is not refreshed. The default value is force on demand. We know that there are two types of Refresh: on demand and on commit on demand, you can manually refresh by using DBMS_MVIEW.REFRESH and other methods. This method is also known as manual refresh, and on commit is also the automatic refresh we have long said, there are four refresh Methods: FAST, COMPLETE, FORCE, and NEVER. We chose FORCE, which indicates that ORACLE has a selective refresh. If we can use FAST, if COMPLETE is not used, EXECUTE DBMS_MVIEW.REFRESH ('xjzhang _ mat_view1 ', 'C') will be refreshed manually. C indicates that completely refresh F indicates fast and forcibly refresh SQL> select * from xjzhang_mat_view1; a B ------------ -------- Aaa 1 SQL> EXECUTE DBMS_MVIEW.REFRESH ('xjzhang _ mat_view1 ', 'C'); the PL/SQL process has been completed successfully. SQL> select * from xjzhang_mat_view1; a B ---------- aaa 1 afd 2 we delete this object view SQL> drop materialized view xjzhang_mat_view1; the materialized view has been deleted. Then we recreate the object view using the FAST method SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 * row 1st error: ORA-23413: Table "SYS ". "XJZHANG_TABLE4" without the materialized view log error message: we need to create the materialized view log SQL> create materialized view log on xjzhang_table4 with rowid, sequence (a, B) includin G new values; the materialized view log has been created. Then we create the object view FAST method SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 * row 1st error: ORA-23415: "SYS ". the materialized view log of "XJZHANG_TABLE4" does not record the primary key and prompts an error. According to the error message, the primary key fails. SQL> alter table xjzhang_table4 modify constraint xjzhang_pri disable; the table has been changed. Then we create the materialized view fast SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4 * row 1st error: ORA-12014: table 'xjzhang _ table4' does not contain the primary key constraint. This system prompts that the primary key constraint is not included. we delete the log SQL> DROP MATERIALIZED VIEW LOG ON xjzhang_table4; the materialized view log has been deleted. When creating the object view log, we set the primary key SQL> create MATERIALIZED VIEW LOG ON xjzhang_table4 WITH PRIMARY KEY; the MATERIALIZED VIEW LOG has been created. Then we create the entity view SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4; the entity view has been created. We can see that the JOB is successfully created. If Automatic update is required, we need to create an automatic JOB.