| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> HELP!!! ---- MViews...
Ok... I'm trying to build an MView that will fast refresh (on commit would
be nice but it's not required). Looking at the Oracle documentation, it
seems this should be ok, but I keep getting this error:
ORA-12015: cannot create a fast refresh materialized view from a complex query
This is in 9.2.0.4
I have MView logs created on each table with ROWID and the non pk coumn names listed as well as including new values as seen in this example:
create materialized view log on bag_tag_flt_leg with rowid, ( dep_rte_type_cd, scan_on_ind, flt_leg_actv_ind) including new values ;
It's got a UNION ALL in it, but I beleive that this is supported (at least the doc's say so). I've also added counts for each aggregate expression. I've run this through dbms_mview.explain_mview and I get no usable results.
Tis is the statement I'm trying to get to fast refresh, anyone see anything that seems out of place?
create materialized view mv_test_rf
build immediate
refresh fast
-- on commit
as
SELECT
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,
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_one
FROM
BAG_TAG_FLT_LEG,
OPS_FLT_LEG
WHERE
-- Join OPS_FLT_LEG and BAG_TAG_FLT_LEG tables
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
-- Select active flight legs scheduled to depart yesterday and
today
-- and actually departing since 23:00 2 days ago
BAG_TAG_FLT_LEG.flt_leg_lcl_dep_dt BETWEEN TRUNC(SYSDATE - 1)
AND TRUNC(SYSDATE) AND
BAG_TAG_FLT_LEG.flt_leg_actv_ind = '1' AND
OPS_FLT_LEG.lcl_out_dtm >= TRUNC(SYSDATE - 1) - 1/24 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,
'D',
BAG_TAG_FLT_LEG.dep_rte_type_cd
UNION ALL
SELECT
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,
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_two
FROM
SCHED_FLT_LEG,
BAG_TAG_FLT_LEG,
OPS_FLT_LEG
WHERE
-- Join SCHED_FLT_LEG and BAG_TAG_FLT_LEG tables
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
-- Join OPS_FLT_LEG and SCHED_FLT_LEG tables
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
-- Select active flight legs scheduled to arrive yesterday and
today
-- and actually arriving since 21:00 2 days ago
SCHED_FLT_LEG.lcl_arr_dtm BETWEEN TRUNC(SYSDATE - 1)
AND TRUNC(SYSDATE + 1) AND
BAG_TAG_FLT_LEG.flt_leg_actv_ind = '1' AND
OPS_FLT_LEG.lcl_in_dtm >= TRUNC(SYSDATE - 1) - 3/24 AND
OPS_FLT_LEG.active_ind = 1
GROUP BY
TRUNC(SCHED_FLT_LEG.lcl_arr_dtm),
BAG_TAG_FLT_LEG.dest_arpt_cd,
BAG_TAG_FLT_LEG.carr_iata_cd,
'A',
BAG_TAG_FLT_LEG.arr_rte_type_cd
Robert
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jul 14 2004 - 12:15:08 CDT
 |
 |