Received: (qmail 11395 invoked from network); 29 May 2009 14:22:59 -0500
Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180)
by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 29 May 2009 14:22:48 -0500
Received: from localhost (localhost [127.0.0.1])
by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B89F7C52A7A;
Fri, 29 May 2009 15:22:45 -0400 (EDT)
X-Virus-Scanned: Debian amavisd-new at localhost.localdomain
Received: from turing.freelists.org ([127.0.0.1])
by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024)
with ESMTP id CCbhvdrA1YjA; Fri, 29 May 2009 15:22:45 -0400 (EDT)
Received: from turing.freelists.org (localhost [127.0.0.1])
by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 028B9C52A51;
Fri, 29 May 2009 15:22:44 -0400 (EDT)
Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 29 May 2009 15:20:30 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 107F7C529CE for ; Fri, 29 May 2009 15:20:30 -0400 (EDT)
Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kDy8-gNN190k for ; Fri, 29 May 2009 15:20:29 -0400 (EDT)
Received: from bcxobst01.dpsg.com (bcxobst01.dpsg.com [192.208.34.67]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 81B33C523DF for ; Fri, 29 May 2009 15:20:29 -0400 (EDT)
Received: from localhost (unknown [127.0.0.1]) by amavis.dpsg.com (Postfix) with ESMTP id 8D30A3401C; Fri, 29 May 2009 19:20:24 +0000 (GMT)
Received: from bcxobst01.dpsg.com ([127.0.0.1]) by localhost (amavis.dpsg.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15889-04; Fri, 29 May 2009 19:20:25 +0000 (GMT)
Received: from bcxoefe01.dpsg.net (unknown [10.224.100.82]) by bcxobst01.dpsg.com (Postfix) with ESMTP id 9BAC534017; Fri, 29 May 2009 19:20:25 +0000 (GMT)
Received: from bcxoemb03.dpsg.net ([10.224.100.54]) by bcxoefe01.dpsg.net with Microsoft SMTPSVC(6.0.3790.3959); Fri, 29 May 2009 13:20:28 -0600
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
Content-class: urn:content-classes:message
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C9E092.8633B445"
Subject: RE: Remote Materialized View containing joins
Priority: normal
Date: Fri, 29 May 2009 13:20:27 -0600
Message-ID: <3DC31002EB0BDD46B2B5730CEDF027704D8FB8@bcxoemb03.dpsg.net>
In-Reply-To:
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: Remote Materialized View containing joins
References: <3DC31002EB0BDD46B2B5730CEDF027704D8EF5@bcxoemb03.dpsg.net>
From: "Xu, Roger"
To: "Jared Still"
Cc: "Rajeev Prabhakar" ,
X-OriginalArrivalTime: 29 May 2009 19:20:28.0152 (UTC) FILETIME=[86671780:01C9E092]
X-TM-AS-Product-Ver: SMEX-8.0.0.1226-5.600.1016-16672.001
X-TM-AS-Result: No--16.379500-8.000000-31
X-TM-AS-User-Approved-Sender: No
X-TM-AS-User-Blocked-Sender: No
X-archive-position: 18163
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
X-original-sender: Roger.Xu@dpsg.com
Precedence: normal
Reply-to: Roger.Xu@dpsg.com
List-help:
List-unsubscribe:
List-software: Ecartis version 1.0.0
List-Id: oracle-l
X-List-ID: oracle-l
List-subscribe:
List-owner:
List-post:
List-archive:
X-list: oracle-l
------_=_NextPart_001_01C9E092.8633B445
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Hi list, how do I tweak so this can be fast refreshed? Thanks in
advance.
=20
SQL> CREATE MATERIALIZED VIEW campaign
2 TABLESPACE BTPMTBL
3 NOCACHE LOGGING NOPARALLEL BUILD IMMEDIATE
4 REFRESH FAST WITH ROWID ON DEMAND
5 AS
6 select
7
S_SRC.ROW_ID,S_SRC.PROD_ID,S_SRC.CTLG_CAT_ID,S_SRC.PAR_SRC_ID,S_SRC.PR_A
CCNT_ID,
S_SRC.TMPL_ID,S_SRC.BU_ID,S_SRC.PERIOD_ID,S_SRC.PROMO_PRI_LST_ID,S_SRC.A
CTIVE_FLG,
8 9
S_SRC.ADVERT_DESC_TEXT,S_SRC.COUPON_DROP_FLG,S_SRC.DISPLAY_FLG,S_SRC.CON
SUME_END_DT,
S_SRC.PROG_END_DT,S_SRC.SHIP_END_DT,S_SRC.FEATURE_FLG,S_SRC.PROMO_NUM,
10 11
S_SRC.OBJECTIVE,S_SRC.PACKAGE_DESC_TEXT,S_SRC.TMP_PRI_RDX_PCT,S_SRC.SRC_
CD,
12
S_SRC.CONSUME_START_DT,S_SRC.PROG_START_DT,S_SRC.SHIP_START_DT,S_SRC.TRG
T_QTY,
13 S_SRC.TMP_PRI_RDX_FLG,S_SRC.CREATED,S_SRC.STATUS_CD,
14 S_SRC.NAME,S_SRC.X_BRAND,S_SRC.X_CONTAINER_SIZE,
15
S_SRC.X_CONTAINER_TYPE,S_SRC.CREATED_BY,S_SRC.X_NEW_FLG,S_SRC.X_PACK_QTY
,
16 S_SRC.X_SETTLEMENT_DT,S_SRC.X_NUM_STORES,S_SRC_CHNL.ROW_ID
SRC_CHNL_ROW_ID,
17
S_SRC_CHNL.PROMO_ACV_PCT,S_SRC_CHNL.PAR_ROW_ID,S_SRC_CHNL.ACTL_BASELINE_
CASE,
18
S_SRC_CHNL.ACTL_SPENT_AMT,S_SRC_CHNL.ACTL_INCR_CASES,S_SRC_CHNL.EST_BASE
LINE_CASES,
19
S_SRC_CHNL.ATTRIB_01,S_SRC_CHNL.EST_INCR_CASES,S_SRC_CHNL.INCR_LIFT,
20 S_SRC_CHNL.TACTICS_CD,S_PRI_LST.NAME PRI_LST_NAME, S_SRC.SUB_TYPE,
21 S_SRC_CHNL.rowid CHNL_RID,
22 S_PRI_LST.rowid LST_RID,
23 S_SRC.rowid SRC_RID
24 from
25 S_SRC@BTPMQ.WORLD
26 left outer join S_SRC_CHNL@BTPMQ.WORLD on S_SRC.ROW_ID =3D
S_SRC_CHNL.PAR_ROW_ID
27 left outer join S_PRI_LST@BTPMQ.WORLD on S_SRC.PROMO_PRI_LST_ID =3D
S_PRI_LST.ROW_ID;
left outer join S_SRC_CHNL@BTPMQ.WORLD on S_SRC.ROW_ID =3D
S_SRC_CHNL.PAR_ROW_ID
*
ERROR at line 26:
ORA-12015: cannot create a fast refresh materialized view from a complex
query
=20
=20
=20
http://www.drpeppersnapplegroup.com/about/corporate to learn more about =
Dr Pepper Snapple Group=E2=80=99s commitment to corporate social =
responsibility.
Please be conscious of the environment and print this email only if =
absolutely necessary.=20
This e-mail (including any attachments) is confidential and may contain =
privileged information of Dr Pepper Snapple Group, Inc. and/or its =
subsidiaries ("Dr Pepper Snapple Group"). If you are not the intended =
recipient or receive it in error, you may not use, distribute, disclose =
or copy any of the information contained within it and it may be =
unlawful to do so. If you are not the intended recipient, please notify =
us immediately by returning this e-mail to us at =
mailto:mailerror@dpsg.com and destroy all copies. Any views expressed by =
individuals within this e-mail do not necessarily reflect the views of =
Dr Pepper Snapple Group. This e-mail does not constitute a binding =
offer, acceptance, amendment, waiver or other agreement, unless the =
intent that an e-mail will constitute such is clearly stated in the body =
of the email. Recipients are advised to subject this e-mail and =
attachments to their own virus checking, in keeping with good computing =
practice. Please note that e-mail received by Dr Pepper Snapple Group =
may be monitored in accordance with applicable law.
------_=_NextPart_001_01C9E092.8633B445
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Hi list, how do I tweak so this can =
be
fast refreshed? Thanks in advance.
SQL> CREATE MATERIALIZED VIEW =
campaign
2 TABLESPACE =
BTPMTBL
3 NOCACHE LOGGING =
NOPARALLEL BUILD
IMMEDIATE
4 REFRESH FAST WITH =
ROWID ON DEMAND
5 =
AS
6 =
select
7
S_SRC.ROW_ID,S_SRC.PROD_ID,S_SRC.CTLG_CAT_ID,S_SRC.PAR_SRC_ID,S_SRC.PR_AC=
CNT_ID,
S_SRC.TMPL_ID,S_SRC.BU_ID,S_SRC.PERI=
OD_ID,S_SRC.PROMO_PRI_LST_ID,S_SRC.ACTIVE_FLG,=
p>
8 9
S_SRC.ADVERT_DESC_TEXT,S_SRC.COUPON_DROP_FLG,S_SRC.DISPLAY_FLG,S_SRC.CONS=
UME_END_DT,
S_SRC.PROG_END_DT,S_SRC.SHIP_END_DT,=
S_SRC.FEATURE_FLG,S_SRC.PROMO_NUM,
10 11
S_SRC.OBJECTIVE,S_SRC.PACKAGE_DESC_TEXT,S_SRC.TMP_PRI_RDX_PCT,S_SRC.SRC_C=
D,
12
S_SRC.CONSUME_START_DT,S_SRC.PROG_START_DT,S_SRC.SHIP_START_DT,S_SRC.TRGT=
_QTY,
13
S_SRC.TMP_PRI_RDX_FLG,S_SRC.CREATED,S_SRC.STATUS_CD,
14 =
S_SRC.NAME,S_SRC.X_BRAND,S_SRC.X_CONTAINER_SIZE,=
15
S_SRC.X_CONTAINER_TYPE,S_SRC.CREATED_BY,S_SRC.X_NEW_FLG,S_SRC.X_PACK_QTY,=
16
S_SRC.X_SETTLEMENT_DT,S_SRC.X_NUM_STORES,S_SRC_CHNL.ROW_ID =
SRC_CHNL_ROW_ID,
17
S_SRC_CHNL.PROMO_ACV_PCT,S_SRC_CHNL.PAR_ROW_ID,S_SRC_CHNL.ACTL_BASELINE_C=
ASE,
18
S_SRC_CHNL.ACTL_SPENT_AMT,S_SRC_CHNL.ACTL_INCR_CASES,S_SRC_CHNL.EST_BASEL=
INE_CASES,
19
S_SRC_CHNL.ATTRIB_01,S_SRC_CHNL.EST_INCR_CASES,S_SRC_CHNL.INCR_LIFT,=
20 =
S_SRC_CHNL.TACTICS_CD,S_PRI_LST.NAME
PRI_LST_NAME, S_SRC.SUB_TYPE,
21 S_SRC_CHNL.rowid =
CHNL_RID,
22 S_PRI_LST.rowid =
LST_RID,
23 S_SRC.rowid =
SRC_RID
24 =
from
25 =
S_SRC@BTPMQ.WORLD
26 left outer join
S_SRC_CHNL@BTPMQ.WORLD on S_SRC.ROW_ID =3D =
S_SRC_CHNL.PAR_ROW_ID
27 left outer join =
S_PRI_LST@BTPMQ.WORLD
on S_SRC.PROMO_PRI_LST_ID =3D =
S_PRI_LST.ROW_ID;
left outer join =
S_SRC_CHNL@BTPMQ.WORLD on
S_SRC.ROW_ID =3D S_SRC_CHNL.PAR_ROW_ID
=
&=
nbsp; &n=
bsp; &nb=
sp; &nbs=
p;
*
ERROR at line =
26:
ORA-12015: cannot create a fast =
refresh
materialized view from a complex query
Click here to learn more about Dr Pepper Snapple Group=E2=80=99s =
commitment to corporate social responsibility.
Please be conscious of the =
environment and print this email only if absolutely =
necessary.
This e-mail (including any =
attachments) is confidential and may contain privileged =
information of Dr Pepper Snapple Group, Inc. and/or its =
subsidiaries ("Dr Pepper Snapple Group"). If you are not the intended =
recipient or receive it in error, you may not use, distribute, disclose =
or copy any of the information contained within it and it may be =
unlawful to do so. If you are not the intended recipient, please notify =
us immediately by returning this e-mail to us at mailerror@dpsg.com and destroy all copies. Any views expressed by individuals =
within this e-mail do not necessarily reflect the views of Dr =
Pepper Snapple Group. This e-mail does not constitute a binding offer, =
acceptance, amendment, waiver or other agreement, unless the intent =
that an e-mail will constitute such is clearly stated in the body of the =
email. Recipients are advised to subject this e-mail and =
attachments to their own virus checking, in keeping with good computing =
practice. Please note that e-mail received by Dr Pepper Snapple =
Group may be monitored in accordance with applicable law.
------_=_NextPart_001_01C9E092.8633B445--
--
http://www.freelists.org/webpage/oracle-l