oracle 迁移物化视图,Oracle之物化视图

Formatted on 2012/3/28 11:26:08 (QP5 v5.149.1003.31008)*/

--删除日志

TRUNCATE TABLEmlog$_fe_fee;DROP MATERIALIZED VIEW LOG ONfe_fee;TRUNCATE TABLEmlog$_fe_order;DROP MATERIALIZED VIEW LOG ONfe_order;TRUNCATE TABLEmlog$_fe_job;DROP MATERIALIZED VIEW LOG ONfe_job;TRUNCATE TABLEmlog$_fi_acc_bill;DROP MATERIALIZED VIEW LOG ONfi_acc_bill;TRUNCATE TABLEmlog$_fi_acc_fee;DROP MATERIALIZED VIEW LOG ONfi_acc_fee;TRUNCATE TABLEmlog$_fe_fee_age;DROP MATERIALIZED VIEW LOG ONfe_fee_age;--创建基表日志

CREATE MATERIALIZED VIEW LOG ON fe_fee 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_fee 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;--创建物化视图

DROP MATERIALIZED VIEWmv_job_fee;CREATE MATERIALIZED VIEWmv_job_fee

BUILD IMMEDIATE

REFRESH FASTONDEMAND

STARTWITHSYSDATENEXT SYSDATE + 5/(60*24)AS

SELECTf.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 ,1 AS ord_canvassing,1 ASord_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_timeFROMfe_fee f, fe_order o, fe_job j, fi_acc_bill b, fi_acc_fee cWHERE 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 VIEWmv_order_cargo;CREATE MATERIALIZED VIEWmv_order_cargo

BUILD IMMEDIATE

REFRESH FASTONDEMAND

STARTWITHSYSDATENEXT SYSDATE + 10/(60*24)AS

SELECTj.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_numFROMfe_order o, fe_job jWHERE o.job_id = j.job_id(+);DROP MATERIALIZED VIEWmv_fee_age;CREATE MATERIALIZED VIEWmv_fee_age

BUILD IMMEDIATE

REFRESH FASTONDEMAND

STARTWITHSYSDATENEXT SYSDATE + 5/(60*24)AS

SELECTa.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_idFROMfe_fee_age a, fe_fee f, fe_order o, fe_job jWHERE a.fee_id = f.fee_id(+)AND a.job_id = j.job_id(+)AND a.order_id = o.order_id(+);

Oracle之物化视图

标签：创建   provider   sql   sql语句   查询   sig   can   通过   alter

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/ys-wuhan/p/6017110.html