Use the dbms_mview.explain_mview procedure. It will show you the reason why
your snapshot is not fast refreshable. You can fast refresh a MView based
off of another MView.....Even joining them.
Here is an example of populating the mv_capabilities_table table:
-- Run this to create the mv_capabilities_table if
-- it dosen't exist.
-- @?/rdbms/admin/utlxmv.sql
-- Clear out from previous runs.
truncate table mv_capabilities_table;
declare
v_sql varchar2(4000);
begin
-- This is the SELECT clause of your create mview command.
-- do not include the create materialzed view stuff in it.
v_sql:='SELECT /*+ INDEX(OPS_FLT_LEG ix_ops_flt_leg_test)
INDEX(BAG_TAG_FLT_LEG ix_bag_tag_flt_leg_test)
USE_NL(OPS_FLT_LEG) USE_NL(BAG_TAG_FLT_LEG) */
trunc(BAG_TAG_FLT_LEG.flt_leg_lcl_dep_dt) AS summ_dt,
BAG_TAG_FLT_LEG.orig_arpt_cd AS arpt_iata_cd,
BAG_TAG_FLT_LEG.carr_iata_cd,
''D'' AS ind,
ops_flt_leg.lcl_out_dtm as lcl_dtm,
BAG_TAG_FLT_LEG.dep_rte_type_cd AS rte_type_cd,
COUNT(*) AS bag_tag_tot_cnt,
SUM(BAG_TAG_FLT_LEG.scan_on_ind) AS bag_tag_scan_ind_cnt,
count(bag_tag_flt_leg.scan_on_ind) as count_scan
FROM
bdscds_stage.BAG_TAG_FLT_LEG,
ods_stage.OPS_FLT_LEG
WHERE
OPS_FLT_LEG.flt_nbr = BAG_TAG_FLT_LEG.flt_nbr AND
OPS_FLT_LEG.carr_iata_cd = BAG_TAG_FLT_LEG.carr_iata_cd AND
OPS_FLT_LEG.flt_lcl_orig_dt = BAG_TAG_FLT_LEG.flt_lcl_orig_dt
AND
OPS_FLT_LEG.orig_arpt_cd = BAG_TAG_FLT_LEG.orig_arpt_cd AND
BAG_TAG_FLT_LEG.flt_leg_actv_ind = ''1'' AND
OPS_FLT_LEG.active_ind = 1
GROUP BY
BAG_TAG_FLT_LEG.flt_leg_lcl_dep_dt,
BAG_TAG_FLT_LEG.orig_arpt_cd,
BAG_TAG_FLT_LEG.carr_iata_cd,
ops_flt_leg.lcl_out_dtm,
BAG_TAG_FLT_LEG.dep_rte_type_cd
UNION ALL
SELECT /*+ INDEX(OPS_FLT_LEG ix_ops_flt_leg_test)
INDEX(BAG_TAG_FLT_LEG ix_bag_tag_flt_leg_test)
INDEX(SCHED_FLT_LEG ix_sched_flt_leg_test)
USE_NL(SCHED_FLT_LEG) USE_NL(BAG_TAG_FLT_LEG)
USE_NL(OPS_FLT_LEG) */
trunc(SCHED_FLT_LEG.lcl_arr_dtm) AS summ_dt,
BAG_TAG_FLT_LEG.dest_arpt_cd AS arpt_iata_cd,
BAG_TAG_FLT_LEG.carr_iata_cd,
''A'' AS ind,
ops_flt_leg.lcl_in_dtm as lcl_dtm,
BAG_TAG_FLT_LEG.arr_rte_type_cd AS rte_type_cd,
COUNT(*) AS bag_tag_tot_cnt,
SUM(BAG_TAG_FLT_LEG.scan_off_ind) AS bag_tag_scan_ind_cnt,
count(bag_tag_flt_leg.scan_off_ind) as count_scan
FROM
ods_stage.SCHED_FLT_LEG,
bdscds_stage.BAG_TAG_FLT_LEG,
ods_stage.OPS_FLT_LEG
WHERE
SCHED_FLT_LEG.flt_nbr = BAG_TAG_FLT_LEG.flt_nbr AND
SCHED_FLT_LEG.carr_iata_cd = BAG_TAG_FLT_LEG.carr_iata_cd AND
SCHED_FLT_LEG.flt_lcl_orig_dt = BAG_TAG_FLT_LEG.flt_lcl_orig_dt
AND
SCHED_FLT_LEG.orig_arpt_cd = BAG_TAG_FLT_LEG.orig_arpt_cd AND
SCHED_FLT_LEG.dest_arpt_cd = BAG_TAG_FLT_LEG.dest_arpt_cd AND
OPS_FLT_LEG.flt_nbr = SCHED_FLT_LEG.flt_nbr AND
OPS_FLT_LEG.carr_iata_cd = SCHED_FLT_LEG.carr_iata_cd AND
OPS_FLT_LEG.flt_lcl_orig_dt = SCHED_FLT_LEG.flt_lcl_orig_dt AND
OPS_FLT_LEG.dest_arpt_cd = SCHED_FLT_LEG.dest_arpt_cd AND
BAG_TAG_FLT_LEG.flt_leg_actv_ind = ''1'' AND
OPS_FLT_LEG.active_ind = 1
GROUP BY
SCHED_FLT_LEG.lcl_arr_dtm,
BAG_TAG_FLT_LEG.dest_arpt_cd,
BAG_TAG_FLT_LEG.carr_iata_cd,
ops_flt_leg.lcl_in_dtm,
BAG_TAG_FLT_LEG.arr_rte_type_cd';
-- Now, explain the view. 'TEST' is the
-- statement_id for this run.
dbms_mview.explain_mview(v_sql, 'TEST');
end;
/
-- Some column formatting
column capability_name format a20
column related_text format a30
column msgtxt format a40
set lines 132
-- Query the mv_capabilities_table
select capability_name, possible, related_text, related_num, msgno, msgtxt
from mv_capabilities_table;
/*
Partial results look like this....
CAPABILITY_NAME P MSGNO MSGTXT
------------------------- - ----- ----------------------------------
REFRESH_FAST_AFTER_INSERT N 2088 mv omits at least one grouping key from
the select list
In this case, we can't fast refresh our MView because there is a problem
with the group by (it wasn't the same as the select list).
*/
Hope this helps,
Robert
Author
The Portable DBA (comming soon from Oracle Press!)
Oracle Database 10g New Features
Oracle9i RMAN Backup and Recovery
Oracle9i New Features
-----Original Message-----
From: Dan Petric
To: LazyDBA Discussion
Sent: 8/3/2004 10:08 AM
Subject: Replication.
Hi list. I have a problem with two layers of snapshots.
I have a set of tables. on this tables I have a set of snapshots (1:1,
created with select *, fast refresh on demand, on another machine).
I need another set of snapshots, created as joins on snapshots from
first set, also fast refresh on demand.
My problem is that all my tries result in "cannot fast refresh
materialized view....".
I'm able to create just COMPLETE refresh snapshots. please, if you
could,
give me an example, or a link to such an example, or any hints to build
such snapshots based on joins on another snapshots. And, it's a must,
fast refresh.
Regards,
Dan.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page