"Go" materialized view of Oracle

Original address: http://www.cnblogs.com/Ronger/archive/2012/03/28/2420962.html

Materialized views are a special physical table, and the materialized view is relative to the normal view. The normal view is a virtual table, the limitations of the application are large, any query on the view, Oracle is actually converted into a query of the views SQL statement. This has no substantial benefit in improving overall query performance.
1. Types of materialized views: on DEMAND, on COMMIT

The difference between the two is that the Refresh method is different, on demand as the name implies, only in the materialized view "need" is refreshed, only to refresh (refresh), that is, to update the materialized view to ensure consistency with the base table data, and on commit is that once the base table has a commit, that is, the transaction commits , the materialized view is immediately refreshed to make the data and the base table consistent.
2. On demand materialized view

The creation of materialized views is inherently complex and requires optimization of parameter settings, especially for large production database systems. But Oracle allows this to be done in the simplest way, similar to a normal view, so it inevitably involves a default value problem. This means that the default value of Oracle's important definition parameters for materialized views is something we need to pay special attention to.

Features of materialized views:

(1) Materialized view in a sense is a physical table (and not just a physical table), which can be user_tables query out, and get support;

(2) Materialized view is also a kind of segment (segment), so it has its own physical storage properties;

(3) Materialized views Occupy database disk space, which can be supported by user_segment query results;

Creating statements: Create materialized view mv_name as SELECT * FROM table_name by default, Oracle defaults to force and demand if no Refresh method and refresh mode are specified.
How does the materialized view's data update with the base table? Oracle provides two ways to manually refresh and refresh automatically, by default, by manual refresh. That is to say, by manually executing a system-level stored procedure or package provided by Oracle, the materialized view is consistent with the base table data. This is the most basic way to refresh. Automatic refresh, in fact, Oracle will create a job, through the job to invoke the same stored procedure or package, to implement.

The characteristics of On demand materialized view and its difference from on commit materialized view, that is, the former does not refresh (manual or automatic) does not update materialized view, the latter does not refresh will also update materialized view, as long as the base table has a commit.

Create materialized views of timed refreshes: Create materialized view mv_name Refresh Force on demand start with Sysdate next sysdate+1 (Specify materialized view refreshed once per day)

The materialized views created above are refreshed daily, but no refresh time is specified, if you want to specify a refresh time (such as a timed refresh once every night 10:00): 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. On commit materialized view

The creation of the on commit materialized view differs little from the materialized view created on demand above.   Because on demand is the default, on commit materialized view, you need to add another parameter. It is important to note that only on COMMIT cannot be specified at the time of definition, and that a parameter is included.

Create on commit materialized view: Create materialized view mv_name Refresh force on commit as SELECT * FROM table_name NOTE: In the actual creation process, the base table needs to have a primary key Constraint, otherwise it will be an error (ORA-12014)

4. Refresh of materialized view

Refresh: Refers to when the materialized view is synchronized with the base table when a DML operation occurs on the base table. There are two modes of refresh: On demand and on COMMIT. As described above

There are four ways to refresh: FAST, complete, force, and never. 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.

For a materialized view that has already been created, you can modify its refresh mode, such as modifying the materialized view Mv_name to refresh once every night 10 o'clock: 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. The materialized view has the same characteristics as the table, so we can create an index for it, like a table, and create the same method as the table.
6. Deletion of materialized views:

Although materialized views are managed together with tables, in the frequently used Plsql tool, it is not possible to delete a table by deleting it (right-clicking ' drop ' on the table does not delete the materialized view), which can be implemented using the statement: Drop materialized View Mv_name

Materialized View creation parameters

(1) Build build IMMEDIATE builds the data build DEFERRED when creating materialized views, and generates data when it is created, and then builds the data later as needed. The default is build IMMEDIATE.
(2) Refresh FAST Incremental Refresh uses materialized view logs to send the data rows that the main table has modified to 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 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 when the first replication from the primary table to the local surface is completed.
(5) Next describes the refresh interval based on the time of the next refresh = the time + time interval at which the last execution 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.

The specific sample code looks like this:

/*Formatted on 2012/3/28 11:26:08 (QP5 v5.149.1003.31008)*/
--Delete Log
TRUNCATETABLE Mlog$_fe_fee;
DROP materializedVIEWLOGOn Fe_fee;
TRUNCATETABLE Mlog$_fe_order;
DROP materializedVIEWLOGOn Fe_order;
TRUNCATETABLE Mlog$_fe_job;
DROP materializedVIEWLOGOn Fe_job;
TRUNCATETABLE Mlog$_fi_acc_bill;
DROP materializedVIEWLOGOn Fi_acc_bill;
TRUNCATETABLE Mlog$_fi_acc_fee;
DROP materializedVIEWLOGOn Fi_acc_fee;
TRUNCATETABLE Mlog$_fe_fee_age;
DROP materializedVIEWLOGOn Fe_fee_age;

--Create a base table log
CREATE materializedVIEWLOGOn Fe_feeWith ROWID, SEQUENCE (job_id, order_id) including NEWVALUES;
CREATE materializedVIEWLOGOn Fe_orderWith ROWID, SEQUENCE (order_id) including NEWVALUES;
CREATE materializedVIEWLOGOn Fe_jobWith ROWID, SEQUENCE (job_id) including NEWVALUES;
CREATE materializedVIEWLOGOn Fi_acc_billWith ROWID, SEQUENCE (bill_id) including NEWVALUES;
CREATE materializedVIEWLOGOn Fi_acc_feeWith ROWID, SEQUENCE (fee_id) including NEWVALUES;
CREATE materializedVIEWLOGOn Fe_fee_ageWith ROWID, SEQUENCE (job_id, order_id) including NEWVALUES;

--Creating materialized views
DROP materializedVIEW Mv_job_fee;


CREATE materializedVIEW Mv_job_fee
BUILD IMMEDIATE
REFRESH FAST
On DEMAND
STARTWith Sysdate
NEXT sysdate+5/(60*24)
As
SELECT 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.oper ord_oper, O.bill Ord_bill,
O.sales Ord_sales, o.cust_id ord_cust_id, O.bill_no bill_no,1As Ord_canvassing,1As 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_time
 From Fe_fee F, Fe_order O, Fe_job J, Fi_acc_bill B, Fi_acc_fee c
 WHERE 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 materializedVIEW Mv_order_cargo;

CREATE materializedVIEW Mv_order_cargo
BUILD IMMEDIATE
REFRESH FAST
On DEMAND
STARTWith Sysdate
NEXT sysdate+10/(60*24)
As
SELECT 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.oper, 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_num
 From Fe_order O, Fe_job J
 WHERE o.job_id= j.job_id (+);

DROP materializedVIEW Mv_fee_age;

CREATE materializedVIEW Mv_fee_age
BUILD IMMEDIATE
REFRESH FAST
On DEMAND
STARTWith Sysdate
NEXT sysdate+5/(60*24)
As
SELECT a.rowid ai, F.rowid fi, J.rowid ji, O.rowid oi, a.fee_id,
a.job_id, a.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.oper ord_oper, 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_id
 From Fe_fee_age A, Fe_fee F, Fe_order O, Fe_job J
WHERE a.fee_id = f.fee_id (+)
and a.job_id = j.job_id (+)
and a.order_id = o.order_id (+);         

"Go" materialized view of Oracle