Oracle之物化视图

Formatted on 2012/3/28 11:26:08 (QP5 v5.149.1003.31008) */ --删除日志 TRUNCATE TABLE mlog$_fe_fee; DROP MATERIALIZED VIEW LOG ON fe_fee; 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_fee; DROP MATERIALIZED VIEW LOG ON fi_acc_fee; TRUNCATE TABLE mlog$_fe_fee_age; DROP MATERIALIZED VIEW LOG ON fe_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 VIEW mv_job_fee; CREATE MATERIALIZED VIEW mv_job_fee BUILD IMMEDIATE REFRESH FAST ON DEMAND START WITH 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 , 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_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 MATERIALIZED VIEW mv_order_cargo; CREATE MATERIALIZED VIEW mv_order_cargo BUILD IMMEDIATE REFRESH FAST ON DEMAND START WITH 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 MATERIALIZED VIEW mv_fee_age; CREATE MATERIALIZED VIEW mv_fee_age BUILD IMMEDIATE REFRESH FAST ON DEMAND START WITH 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(+);

 

Oracle之物化视图

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