Turn: Oracle materialized View Learning notes

Recently learned a materialized view, just as the manager is not, the results of the study posted for everyone to study it.
Let's take a look at the general meaning of materialized view, feel the definition of Baidu is good

Baidu Code

1. Materialized views, which are used to pre-compute and save time-consuming operations such as table joins or aggregates, so that time-consuming operations can be avoided when queries are executed, resulting in quick results. 　　Materialized views are similar in many respects to indexes: the purpose of using materialized views is to improve query performance; Materialized views are transparent to the application, adding and removing materialized views does not affect the correctness and validity of the SQL statements in the application; Materialized views need to occupy storage space, and materialized views should be refreshed when the base table changes. There are three kinds of materialized views: aggregating materialized views, including connected materialized views, nested materialized views. However, the limitations of the fast refresh of the three materialized views are very different, while others are not very significant.
2. http://baike.baidu.com/view/3208435.htm

My understanding is a view of the data stored in the table, he is only nominally called the view, in fact, the effect of the ordinary table is similar, mainly for query use. At present I understand that it should not be possible to insert or delete data. Materialized view data can be updated, as the following will be said. The normal view records the query SQL statement, actually uses the query from the entity table to obtain the data, and the materialized view has its own storage space, reducing the access to the entity table.


1. How to create a materialized view

SQL code

2. --Create
4. CREATE materialized VIEW Mv_view as {SELECT * from }
6. --Delete
8. DROP materialized VIEW Mv_view as {SELECT * from }
10. -for example
12. Create materialized view Mv_sm_userinfo
14. Refresh force on demand
16. Start with to_date (' 19-01-2012 11:00:00 ', ' dd-mm-yyyy hh24:mi:ss ') next to_date (Concat (To_char ( Sysdate+1,' dd-mm-yyyy '),' 11:00:00 '),' dd-mm-yyyy hh24:mi:ss ')
18. As
20. Select Sm_user.pk_user,sm_user.user_name,sm_user.user_code,sm_user. Password,sm_user.identity_no,sm_user.memo,
22. Sm_org.pk_org,sm_org.org_code,sm_org.org_name
24. From sm_user,sm_org where sm_user.pk_org = sm_org.pk_org
26. --This materialized view uses a timed update function

2. How to make a materialized view update periodically
On DEMAND manual refresh, auto Refresh
The on Commit base table is automatically updated when a commit is made
Timed Updates

SQL code

2. --Specifies that materialized views are refreshed once a day
4. CREATE materialized VIEW mv_view REFRESH Force on DEMAND
6. START with sysdate
8. NEXT sysdate+1
10. As
12. ......
14. --Specify materialized views refreshed every night at 10:00.
16. CREATE materialized VIEW mv_view REFRESH Force on DEMAND
18. START with sysdate
20. NEXT to_date (CONCAT (To_char (sysdate+1,' dd-mm-yyyy '),' 22:00:00 '),' dd-mm-yyyy HH24:MI:SS ')
22. As

3. How to set up a log for materialized views

SQL code

2. --Grammar
4. CREATE materialized VIEW LOG on table_name
6. With Sequence,rowid
8. (NUM1,NUM2,NUM3,NUM4,NUM5,NUM6,NUM7
10. ) including NEW VALUES;
14. --sm_user Creating a Log
16. Create materialized view Log on sm_user with Sequence,rowid (Pk_user,user_name,user_code,   PASSWORD,IDENTITY_NO);
18. --sm_org Creating a Log
20. Create materialized view Log on sm_org with sequence,rowid (pk_org,org_code,org_name);
26. View Logs
28. SELECT * FROM mlog$_sm_org
30. SELECT * FROM mlog$_sm_user
32. --Query log
34. SELECT * FROM dba_snapshot_logs
36. --Querying materialized views
38. SELECT * FROM dba_snapshot_logs
40. --Querying Oracle version information
42. SELECT * from gv$version;

4. How to update materialized views using Java thread timing
You need to create a stored procedure to update the materialized view and then invoke the stored procedure using a Java thread. (At present I understand)

SQL code

2. --Stored procedures
4. Create or replace procedure mv_refresh_userinfo is
6. Begin
8. Dbms_mview.refresh (' mv_sm_userinfo ');
10. End Mv_refresh_userinfo;
14. --thread call from stored procedure
16. Private int count = 0;
18. Connection Orclcon;
22. public void Run () {
24. if (orclcon==null) {
26. Jdbcdao jdbc = new Jdbcdao ();
28. Orclcon = Jdbc.getmaterviewconnection ();
30. }
34. try {
36. callablestatement proc = Orclcon.preparecall ("{Call Mv_refresh_userinfo ()}");
38. Boolean result = Proc.execute ();
40. count++;
42. System.  out.println ("Call Mv_refresh_userinfo ()" + result + "; already running:" + count);
44. } catch (SQLException e) {
46. E.printstacktrace ();
48. }
52. }

5. Other information
Materialized View creation parameters
(1) BUILD
Build IMMEDIATE generates data when a materialized view is created
Build DEFERRED does not generate data when it is created, and then generates the data later as needed.
The default is build IMMEDIATE.

(2) REFRESH
FAST incremental refresh uses materialized view logs to send data rows that have been modified by the main table into the materialized view.
Complete refreshes the entire view completely, and if the request is fully refreshed, Oracle completes the full refresh even if the incremental refresh is available.
Force if an incremental refresh is available, Oracle will complete the incremental refresh, otherwise the full refresh will be completed if the Refresh method (FAST, complete, or force) is not specified.
The default option is force.

(3) On
On DEMAND refers to materialized views that are refreshed when required by the user.
On commit indicates that the materialized view is refreshed at the same time that the DML operation of the base table is committed.
The default is on DEMAND.

(4) START with
Notifies the database of the time to complete the first replication from the primary table to the local surface.

(5) NEXT
Describes the time interval for refreshes
Based on the time of the next refresh = the time + time interval at which the last execution was completed.
To ensure that the user needs to refresh at a point in time, the trunc () command is generally used to take the time to the number of days, and then add time.

http://highill.iteye.com/blog/1357896

Turn: Oracle materialized View Learning notes