Oracle materialized view code example, oracle Materialized View

Oracle materialized view code example, oracle Materialized View

Materialized View of Oracle

Recently, oracle materialized views have been updated based on the project business needs. You can search for information on this topic on the Internet to facilitate improvement. The content is as follows:

Materialized View is a special physical table. The Materialized view is relative to a common view. A common view is a virtual table, which has many application limitations. Oracle converts any View query to a view SQL statement query. This improves the overall query performance without substantial benefits.

1. materialized view types: on demand and ON COMMIT

The difference between the two is that the REFRESH method is different. As the name suggests, the REFRESH is performed only when the materialized view is refreshed, that is, the materialized view is updated, to ensure data consistency between the base table and the base table. on commit indicates that once the base table has a COMMIT, that is, the transaction is committed, the data is refreshed immediately, and the materialized view is updated immediately, so that the data and the base table are consistent.

2. materialized on demand view

The creation of materialized views is complex and requires parameter settings optimization, especially for large production database systems. However, Oracle allows you to do this in the simplest way, similar to a common view, so it will inevitably involve default values. That is to say, we need to pay special attention to the handling of the default values of important definition parameters for materialized views in Oracle.

Features of materialized views:

(1) materialized view is a physical table (and not just a physical table) in a sense, which can be queried by user_tables. (2) materialized views are also segments, so they have their own physical storage properties. (3) materialized views occupy the disk space of the database. This is evidenced by the query results of user_segment; create statement: create materialized view mv_name as select * from table_name by default, if the refresh method and refresh mode are not specified, the default values of Oracle are FORCE and DEMAND. How does the Materialized View data update with the base table? Oracle provides two methods: manual refresh and automatic refresh. Manual refresh is used by default. That is to say, we can manually execute a system-level stored procedure or package provided by an Oracle database to ensure data consistency between the Materialized View and the base table. This is the most basic refresh method. Auto-Refresh means that Oracle will create a job through which the same stored procedure or package is called for implementation. The characteristics of the on demand materialized view and its differences with the on commit materialized view, that is, the former does not update the materialized view without refreshing (manual or automatic), while the latter updates the materialized view without refreshing, -- as long as the base table has a COMMIT. Create a materialized view with timed refresh: create materialized view mv_name refresh force on demand start with sysdate next sysdate + 1 (specify that the materialized view is refreshed once a day) the previously created materialized view is refreshed every day, however, the refresh time is not specified. If you want to specify the refresh time (for example, refresh regularly at every night ): create materialized view mv_name refresh force on demand start with sysdate next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy '), '22:00:00 '), 'dd-mm-yyyy hh24: mi: ss ')

3. materialized on commit View

The creation of the on commit materialized view is not much different from the materialized view created on demand. Because on demand is the default, you need to add another parameter to the on commit materialized view. It should be noted that you cannot specify only on commit when defining it, but you still need to include a parameter. Create on commit materialized view: create materialized view mv_name refresh force on commit as select * from table_name Note: during actual creation, the base table requires a primary key constraint; otherwise, an error is reported (ORA-12014)

4. Refresh the Materialized View

Refresh: Specifies the mode in which the materialized view is synchronized with the base table after the DML operation is performed on the base table. There are two refresh modes: on demand and on commit. (As mentioned above) there are four refresh Methods: FAST, COMPLETE, FORCE, and NEVER. 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. You can modify the refresh method of the created materialized view. For example, you can change the refresh method of the materialized view mv_name to refresh every night: alter materialized view mv_name refresh force on demand start with sysdate next to_date (concat (to_char (sysdate + 1, 'dd-mm-yyyy '), '22:00:00 '), 'dd-mm-yyyy hh24: mi: ss ')

5. materialized views have the same features as tables. Therefore, we can create indexes for tables in the same way.

6. Delete materialized views:

Although materialized views are managed together with tables, in PLSQL tools that are frequently used, you cannot delete a table by deleting it. (You cannot right-click the table and select 'drop' to delete the materialized view.) You can use the following statement to delete the table: drop materialized view mv_name.

Materialized View creation Parameters

(1) build immediate is to generate data build deferred when creating the materialized view, and then generate data later as needed. The default value is build immediate. (2) refresh fast incremental REFRESH uses the materialized view log to send the modified data row of the master table to the materialized view. The COMPLETE is completely refreshed and the entire view is re-generated. If the request is completely refreshed, oracle will completely refresh, even if the incremental refresh is available. FORCE if incremental refresh is available, the incremental refresh will be completed. 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 demand indicates that the materialized view is refreshed when the user needs it. On commit indicates that the Materialized View refreshes the base table while submitting the DML operation. The default value is on demand. (4) start with, which indicates the time when the database completes the first copy from the master table to the local table. (5) NEXT indicates the Refresh Interval Based on the NEXT refresh time = the last execution completion time + interval. To ensure refresh at the user's desired time point, the TRUNC () command is generally used to set the time to the number of days, and then add the time.

The sample code is as follows:

/* Formatted on 11:26:08 (QP5 v5.149.1003.31008) */-- delete the truncate table mlog $ _ fe_logs; drop materialized view log on fe_logs; truncate table mlog $ _ fe_order; drop materialized view log on fe_order; truncate table mlog $ _ fe_job; drop materialized view log on fe_job; truncate table mlog $ _ fi_acc_bill; drop materialized view log on fi_acc_bill; truncate table mlog $ _ fi_acc_aliz; drop materialized view log on fi_acc_fee; truncate table mlog $ _ fe_fee_age; drop materialized view log on fe_fee_age; -- CREATE a base table log create materialized view log on fe_tables with rowid, SEQUENCE (job_id, order_id) including new values; create materialized view log on fe_order with rowid, SEQUENCE (order_id) including new values; create materialized view log on fe_job with rowid, SEQUENCE (job_id) including new values; create materialized view log on fi_acc_bill with rowid, SEQUENCE (bill_id) including new values; create materialized view log on fi_acc_ing with rowid, SEQUENCE (fee_id) including new values; create materialized view log on fe_fee_age with rowid, SEQUENCE (job_id, order_id) including new values; -- create materialized view drop materialized view mv_job_fee; create materialized view mv_job_feeBUILD immediaterefresh faston demandstart with sysdatenext sysdate + 5/(60*24) ASSELECT f. ROWID fi, j. ROWID ji, o. ROWID oi, B. ROWID bi, c. ROWID ci, f. fee_id, f. job_id, f. order_id, f. fee_type, f. fee_code, f. unit_price, f. quantity, f. currency, f. fx_rate, f. cust_id, f. invoice_num, f. is_confirm, f. blunt_flag, f. verify_balance, f. is_agreement, f. fiscal_period, f. attribute, f. continue, f. remark, f. security, f. create_by, f. create_time, f. pay_type, f. sharing_type, f. bill_id, f. direction, f. profit_loses, f. unit, f. relation_cust, f. amount, f. fx_amout, f. modified_by, f. modified_date, f. proportion, f. job_period, o. quantity ord_quantity, o. gross_weight ord_gross_weight, o. volume ord_volume, o. charge_weight ord_charge_weight, o. custom_num ord_custom_num, o. pay_type ord_pay_type, o. pay_type2 ord_pay_type2, o. teu ord_teu, o. cust_service ord_cust_service, o. specified ord_category, o. bill ord_bill, o. sales ord_sales, o. cust_id ord_cust_id, o. bill_no, 1 AS ord_canvassing, 1 AS ord_agent_type, j. dept_id job_dept_id, j. job_type job_job_type, j. firm job_firm, j. way_bill job_way_bill, j. loading job_loading, j. discharging job_discharging, j. etd job_etd, j. eta job_eta, j. flight_num job_flight_num, j. provider job_provider, j. carrier job_carrier, j. voyage job_voyage, j. quantity job_quantity, j. gross_weight job_gross_weight, j. volume job_volume, j. charge_weight job_charge_weight, j. teu job_teu, j. fee_lock job_fee_lock, j. lock_time job_lock_time, j. auditor job_auditor, j. archiveno job_archiveno, j. archived_by job_archived_by, j. archived_time job_archived_time, j. oversea_agent job_oversea_agent, j. container_info job_container_info, j. container_num job_container_num, j. proj_id job_proj_id, j. route job_route, B. book_date bill_book_date, B. commit_flag bill_commit_flag, B. pay_period bill_pay_period, B. invoice_rise, c. confirm_amount, c. confirm_timeFROM fe_0000f, fe_order o, fe_job j, fi_acc_bill B, fi_acc_policcwhere f. job_id = j. job_id (+) AND f. order_id = o. order_id (+) AND f. bill_id = B. bill_id (+) AND f. fee_id = c. fee_id (+); drop materialized view mv_order_cargo; create materialized view mv_order_cargoBUILD immediaterefresh faston demandstart with sysdatenext sysdate + 10/(60*24) ASSELECT j. ROWID ji, o. ROWID oi, o. order_id, o. job_type, o. cust_id, o. dept_id, o. firm, o. job_id, o. quantity, o. gross_weight, o. volume, o. charge_weight, o. custom_num, j. loading, j. discharging, o. pay_type, o. pay_type2, o. teu, o. cust_service, o. category, o. bill, o. sales, o. booking_type, o. route, o. assign_agent, j. way_bill, j. etd, j. eta, j. flight_num, j. provider, j. carrier, j. voyage, j. quantity job_quantity, j. gross_weight job_gross_weight, j. volume job_volume, j. charge_weight job_charge_weight, j. teu job_teu, j. job_period, j. oversea_agent, j. container_info, j. container_numFROM fe_order o, fe_job jWHERE o. job_id = j. job_id (+); drop materialized view mv_fee_age; create materialized view mv_fee_ageBUILD immediaterefresh faston demandstart with sysdatenext sysdate + 5/(60*24) ASSELECT. ROWID ai, f. ROWID fi, j. ROWID ji, o. ROWID oi,. fee_id,. job_id,. order_id, f. fee_type, f. fee_code, f. unit_price, f. quantity, f. currency, f. fx_rate, f. cust_id, f. invoice_num, f. is_confirm, f. blunt_flag, f. verify_balance, f. is_agreement, f. fiscal_period, f. attribute, f. continue, f. remark, f. security, f. create_by, f. create_time, f. pay_type, f. sharing_type, f. bill_id, f. direction, f. profit_loses, f. unit, f. relation_cust, f. amount, f. fx_amout, f. modified_by, f. modified_date, f. proportion, f. job_period, o. quantity ord_quantity, o. gross_weight ord_gross_weight, o. volume ord_volume, o. charge_weight ord_charge_weight, o. custom_num ord_custom_num, o. pay_type ord_pay_type, o. pay_type2 ord_pay_type2, o. teu ord_teu, o. cust_service ord_cust_service, o. specified ord_category, o. bill ord_bill, o. sales ord_sales, o. cust_id ord_cust_id, j. dept_id job_dept_id, j. job_type job_job_type, j. firm job_firm, j. way_bill job_way_bill, j. loading job_loading, j. discharging job_discharging, j. etd job_etd, j. eta job_eta, j. flight_num job_flight_num, j. provider job_provider, j. carrier job_carrier, j. voyage job_voyage, j. quantity job_quantity, j. gross_weight job_gross_weight, j. volume job_volume, j. charge_weight job_charge_weight, j. teu job_teu, j. fee_lock job_fee_lock, j. lock_time job_lock_time, j. auditor job_auditor, j. archiveno job_archiveno, j. archived_by job_archived_by, j. archived_time job_archived_time, j. oversea_agent job_oversea_agent, j. container_info job_container_info, j. container_num job_container_num, j. proj_id job_proj_idFROM fe_fee_age a, fe_0000f, fe_order o, fe_job jWHERE. fee_id = f. fee_id (+) AND. job_id = j. job_id (+) AND. order_id = o. order_id (+ );