From oracle-l-bounce@freelists.org Wed Jul 14 13:25:13 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6EIO6H28418 for ; Wed, 14 Jul 2004 13:24:16 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6EINh628324 for ; Wed, 14 Jul 2004 13:24:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 771CD72D342; Wed, 14 Jul 2004 13:03:54 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32419-94; Wed, 14 Jul 2004 13:03:54 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8CCE972D463; Wed, 14 Jul 2004 13:02:40 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 14 Jul 2004 13:00:47 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 47C8C72D82D for ; Wed, 14 Jul 2004 13:00:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31760-54 for ; Wed, 14 Jul 2004 13:00:44 -0500 (EST) Received: from tuscil_ex1.tuscil.com (exchange.tusc.com [65.174.243.31]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C045872D835 for ; Wed, 14 Jul 2004 13:00:31 -0500 (EST) Received: by tuscil_ex1 with Internet Mail Service (5.5.2658.3) id <36CJF61D>; Wed, 14 Jul 2004 13:25:27 -0500 Message-ID: From: Freeman Robert - IL To: "'Jonathan Lewis '" , "'oracle-l@freelists.org '" Subject: RE: HELP!!! ---- MViews... Date: Wed, 14 Jul 2004 13:25:27 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2658.3) x-iHateSpam-Checked: Checked by Spam Filter Service at 7/14/2004 12:29:15 PM X-Mailer: Checked by Spam Filter Service at 7/14/2004 12:29:15 PM X-MimeOLE: Checked by Spam Filter Service at 7/14/2004 12:29:15 PM X-Virus-Scanned: Checked by Spam Filter Service at 7/14/2004 12:29:15 PM Content-type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 5104 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: FREEMANR@tusc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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! :-) Thanks again Janathan! Robert -----Original Message----- From: oracle-l-bounce@freelists.org To: oracle-l@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 ----- Original Message ----- From: "Freeman Robert - IL" To: 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: 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 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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@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 -----------------------------------------------------------------