Oracle Normal view and solid view comparisonSource: Rank Blog | 2013-07-30 Oracle Normal and solid views compared to normal views, the entity view differs in that the manifested view manages the stored data, occupying the physical space of the database. The results of the manifested view are saved in a normal data table, and when queries are made to the entity view, the base table of the entity view is not queried, but the result table of the entity view is queried directly, and then the data in the Mview table is updated by the periodic refresh mechanism. First we need to create a table and then write a SELECT statement. SQL> CREATE TABLE Xjzhang_table1 (a varchar2 (ten), B number (ten)); tables have been created. SQL> CREATE TABLE Xjzhang_table2 (a varchar2 (ten), B number (ten)); tables have been created. Insert data into two tables SQL> insert into xjzhang_table1 values (' AAA ', ' 00001 '); has created 1 rows. SQL> INSERT into xjzhang_table1 values (' BBB ', ' 00002 '); has created 1 rows. SQL> INSERT into xjzhang_table2 values (' Aa1 ', ' 00002 '); created 1 rows. SQL> INSERT into xjzhang_table2 values (' bb1 ', ' 00003 '); created 1 rows. SQL> commit; submission completed. Then we create a view with the name xjzhang_view sql> CREATE View Xjzhang_view as select Xjzhang_table1.a,xjzhang_ table2.b from Xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b; view has been created. Then we query the view SQL> select * from Xjzhang_view; a   B -------------------- bbb 2 then we'll write a query for the statement SQL> Select xjzhang_table1.a,xjzhang_table2.b from Xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b ; a B -------------------- bbb 2 You can see that the result of our query view is consistent with the result of querying the SELECT statement, stating that the view is a description of the SELECT statement that queries one or more tables. Check out the view we created select object_name,object_type,created,status from Dba_objects where object_name= ' Xjzhang_view '; object_name object_type CREATED s tatus -------------------------------------------------------xjzhang_view view 24-6 -09 valid Let's start by creating a table table named Xjzhang_table3 and inserting data into the table sq L> CREATE TABLE Xjzhang_table3 (a varchar2 (ten), b number (5)); table has been created. SQL> INSERT INTO Xjzhang_table3 valUEs (' AAA ', ' 00001 '); has created 1 rows. SQL> INSERT into xjzhang_table3 values (' BBB ', ' 00002 '); has created 1 rows. SQL> commit; submission completed. Below we start to create the entity view (the entity view we created here is not automatically refreshed but needs to be manually refreshed) SQL> create materialized view Xjzhang_mat_view as SELECT * FROM xjzhang_table3; The manifested view has been created. We look at the entity view we created, the name of the entity view is xjzhang_mat_view sql> select * from Xjzhang_mat_view; a B -------------------- aaa  1 BBB &NBS p;2 entity view in a sense it is a physical table that can be queried by dba_tables to justify sql>select Table_name,tablespace_name,status from Dba_ Tables where table_name= ' Xjzhang_mat_view '; table_name &NBSP ; Tablespace_name STATUS ----------------------------- --------------------------------------- xjzhang_mat_view SYSTEM valid Let's look at the view you just created XJZ Hang_view sql> Select Table_name,tablespace_name,status from dba_tables where table_name= ' XJZHANG_VIEW '; Unselected rows You can see that the normal view is not recorded in Dba_tables, there is no corresponding tablespace The entity view consumes a certain amount of storage space, because it holds the result set of the query, it is also a segment that can be dba_segments In Query out SQL> select Segment_name,segment_type,tablespace_name from dba_segments where Segment_name= ' Xjzhang_mat _view ' segment_name segment_type tablespace_name ---------- ----------------------------------------------------------xjzhang_mat_view TABLE & nbsp SYSTEM Also we use dba_segments to query the normal view we created SQL> select Segment_name,segment_type, Tablespace_name from dba_segments where segment_name= ' Xjzhang_view '; unselected rows You can see that normal views are not recorded in Dba_segments. Let's update the information in the Xjzhang_table3 table and look at the change information for the entity view SQL> INSert into XJZHANG_TABLE3 values (' CCC ', ' 00003 '); has created 1 lines. SQL> commit; submission completed. Query information for this table SQL> select * from Xjzhang_table3; a B -------------- ------ AAA  1 BBB  2 CCC  3&N BSP; table record added one line We'll query the entity View information SQL> SELECT * from Xjzhang_mat_view; a b -------------------- aaa  1 BBB 2 You can see that the information for the entity view has not changed, because we did not specify the Refresh method and refresh mode of the view when we created the entity view, so we created the view of the entity, the default Refresh method and refresh mode for Force demand we can Dba_ Mviews this view queries the entity View information we created SQL> Select A.mview_name,a.refresh_mode,a.refresh_method from Dba_mviews a where A.mview_name= ' Xjzhang_mat_view '; mview_name RE Fresh_mode refresh_method ---------------------------------------------------------- xjzhang_mat_view DEMAND FORCE&N The default here is manual refresh, so here we update the entity view SQL> EXEC Dbms_mview. The REFRESH (' Xjzhang_mat_view ') pl/sql process has completed successfully. Then we'll query the entity view again SQL> SELECT * from Xjzhang_mat_view; a B ------ -------------- aaa  1 BBB  2 CCC 3 This shows that the data of the base table changes, then the contents of the entity view will be written to the corresponding storage space. We can also create a view of entities that are automatically updated, and we create a table of tables SQL> CREATE TABLE Xjzhang_table4 (a varchar2 (ten), b number (5)); tables have been created. SQL> INSERT into xjzhang_table4 values (' AAA ', ' 00001 '); has created 1 rows. SQL> commit; submission completed. Then we create the entity view SQL> create materialized view xjzhang_mat_view1 Refresh force on commit as SELECT * from Xjzhang_ Table4; create materialized view Xjzhang_mat_view1 Refresh force on commit as SELECT * from Xjzhang_table4 *&nbs p; line 1th appearsError: ORA-12054: Unable to set on COMMIT Refresh property for materialized view Fast refresh of materialization view takes an incremental mechanism to refresh only the data that has changed on the base table. Therefore, fast refresh is the preferred way to refresh materialized views. However, fast refreshes have more constraints and are more so for materialized views that use on commit mode for quick refreshes. The quick refresh mechanism for materialized views that contain aggregation and include connections is not the same, and there are additional requirements for fast refreshes of multi-layered, nested materialized views. So many restrictions are generally difficult to remember, and when the materialized view fails, Oracle's error message is too simple to pinpoint the cause of the problem. Dbms_mview provided by Oracle. The Explain_mview process can help you quickly locate the cause of the problem. oracle provides the Dbms_mview. The Explain_mview process can help you quickly locate the cause of the problem, let's try out the Oracle-provided package . using the Explain_mview process first to establish the Mv_capabilities_table table Create the steps below We execute a script to finish creating the SQL> @?/rdbms/admin/utlxmv.sql table that was created. Then we execute this package SQL> begin 2 dbms_mview.explain_mview (' select * from Xjzhang_table4 '); 3 end; The  4 / PL/SQL process has completed successfully. Then we pass the select Capability_name, possible, msgtxt from mv_capabilities_table where capability_name like ' refresh% ' This script query results SQL> Select Capability_name, Possible, msgtxt from Mv_capabilities_table 2 where capability_name like ' refresh% '; Capability_name P msgtxt ,------------------------------ -------------------------------refresh_complete  N primary KEY constraints in the main table refresh_fast &NB Sp n refresh_fast_after_insert N Details table does not have a manifested view log refresh_fast_after_onetab_dml N View the reasons for disabling refresh_fast_after_in SERT refresh_fast_after_any_dml n view Disable Refresh_fast_ after_on etab_dml reason refresh_fast_pct N PCT is not possible in the manifested view of any from table has selected 6 rows. We can see that the first refresh_complete N Main table does not have any primary key constraints we create a primary key for XJZHANG_TABLE4 SQL> ALTER TABLE Xjzhang_ Table4 Add (Constraint Xjzhang_pri primary key (b)); table has been changed. then we created the entity view again SQL> create materialized view xjzhang_mat_view1 Refresh force on commit as SELECT * from Xjzhan g_table4; The manifested view has been created. can look at the successful creation, we now insert data into the table to see how the entity view changes We first query the records in the table and the records in the entity view SQL> select * from Xjzhang_table4; a &NBsp B -------------------- aaa 1 SQL> select * from Xjzhang_mat_ View1; a B -------------------- aaa 1 Then we insert a record into the table SQL> insert into xjzhang_table4 values (' AfD ', ' 00002 '); has created 1 rows. SQL> commit; submission completed. Let's check the contents of the Entity view SQL> select * from Xjzhang_mat_view1; a B --- ----------------- aaa 1 materialized view why no change refresh: Refers to a DML operation that occurs when the base table has been When materialized views are synchronized with the base table in which way. There are two modes of refresh: On demand and on COMMIT. On demand refers to materialized views that are refreshed when required by the user and can be manually dbms_mview. Refresh, or refresh by job timing. On commit indicates that the materialized view is refreshed at the same time that the DML operation of the base table is committed. There are four ways to refresh: FAST, complete, force, and neve*. Fast refresh takes an incremental refresh and refreshes only the changes made since the last refresh. Complete refreshes the entire materialized view with a full refresh. If you choose force Mode, Oracle will determine whether it can be refreshed quickly, or if it can be fast, or else complete. Never refers to materialized views without any refreshes. The default value is force on demand through the above paragraph we know that there are generally two types of refreshes: on DEMAND and on COMMIT&NBsp;on demand refers to the materialized view when the user needs to refresh, you can manually through the Dbms_mview. Refresh, and so on, this method is what we say the use of manual refresh, and on commit is our long-said automatic refresh, and refresh the method has four kinds of fast, complete, force and never we chose force Note that Oracle is a selective refresh, if you can use fast, or use complete we use manual refresh execute dbms_mview. Refresh (' Xjzhang_mat_view1 ', ' C '); c represents a full refresh f represents a quick refresh and a forced 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 completed successfully. SQL> SELECT * from Xjzhang_mat_view1; a B --------------------& NBSP;AAA  1 AFD 2 We delete the entity view SQL> drop Materialized view xjzhang_mat_view1; manifested views have been removed. Then we re-create the entity view using the FAST method SQL> create materialized view Xjzhang_mat_view1 refresh FAST on commit as SELECT * FR Om xjzhang_table4; create materialized View XjzhAng_mat_view1 refresh fast on commit as SELECT * xjzhang_table4 * 1th line error: ORA-23413: Table "SYS". " Xjzhang_table4 "No manifested view Log Error Prompt requires a solid view log We create a solid view log SQL> the Create materialized view log on Xjzhang_ Table4 with ROWID, sequence (A, b) including new values; manifested view log created. Then we create the entity view again 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&NB sp;* 1th Line error: ORA-23415: "SYS". " Xjzhang_table4 "The Entity view Log does not record primary key prompt error, according to the prompt error our primary key is invalid SQL> ALTER TABLE XJZHANG_TABLE4 modify constraint Xjzhang_pri disable; table has been changed. Then we create the materialized view again fast sql> create materialized view Xjzhang_mat_view1 refresh FAST on commit as SELECT * from XJ Zhang_table4; create materialized view Xjzhang_mat_view1 refresh fast on commit as SELECT * from xjzhang_table4 * the 1th line appears error: ORA-12014: Table ' Xjzhang_table4 ' does not contain a PRIMARY KEY constraint This time the system also hints that it does not contain a PRIMARY KEY constraint, we delete the log for the Entity View SQL> drop materialized view Log on Xjzhang_ table4; The manifested view log has been deleted. When we create the Entity view Log, we set the primary key SQL> create materialized view Log on xjzhang_table4 with PRIMARY KEY; The manifested view log has been created. then we created the entity view again SQL> create materialized view Xjzhang_mat_view1 refresh fast on commit as SELECT * from Xjzhang _table4; The manifested view has been created. can see that the creation is successful and if it needs to be updated automatically, we need to create an automated JOB.
Differences between normal views and entity attempts in Oracle
