Darn, missed the sysdate one, and I think you are right, that violates this
restriction from the docs:
* The materialized view must not contain references to non-repeating
expressions like SYSDATE and ROWNUM.
I just missed it when looking through the code. Thanks for that, I 'm going
to remove that condition and see if that solves the problem... if so, then I
need to figure a way out of it! :-)
1) I can promise you that on commit refresh would not be nice ;)
2) I think the problem is probably with the SYSDATE
that appears in WHERE clause. I would be very
very surprised if that were legal, as the materialized
would have to maintain itself every second without
any change taking place on the user data.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users ' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
-- -- Original Message -- --
From: "Freeman Robert - IL " <FREEMANR@(protected) >
To: <oracle-l@(protected) >
Sent: Wednesday, July 14, 2004 6:17 PM
Subject: 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:
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
