Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: HELP!!! ---- MViews...

RE: HELP!!! ---- MViews...

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Wed, 14 Jul 2004 13:25:27 -0500
Message-ID: <F5E885BEF9540D47A7BDC03CF1688087066D68E3@tuscil_ex1>


Darn, missed the sysdate one, and I think you are right, that violates this restriction from the docs:

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! :-)

Thanks again Janathan!

Robert

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
To: oracle-l_at_freelists.org
Sent: 7/14/2004 12:27 PM
Subject: Re: HELP!!! ---- MViews...

  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

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

Robert



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org 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_at_freelists.org
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
-----------------------------------------------------------------
Received on Wed Jul 14 2004 - 13:25:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US