Re: Remote Materialized View containing joins
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx, Roger.Xu@xxxxxxxx
- Date: Sat, 30 May 2009 13:38:36 -0700 (PDT)
Version?? If 10g and later (may be 10R2 and later... didn't check) you can use
the dbms_advisor.tune_mview procedure to generate the DDL needed for a fast
refreshable MVIEW. I have seen a couple of bugs in it, including it dropping
the refresh fast syntax in one case... which was kind of funny.
Give it a whirl.
Robert G. Freeman
Oracle ACE
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
http://pages.sssnet.com/messndal/church/parachurch.pdf
________________________________
From: Martin Berger <martin.a.berger@xxxxxxxxx>
To: Roger.Xu@xxxxxxxx
Cc: Jared Still <jkstill@xxxxxxxxx>; Rajeev Prabhakar <rprabha01@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx
Sent: Saturday, May 30, 2009 1:10:45 PM
Subject: Re: Remote Materialized View containing joins
have you checked
Determining the Fast Refresh Capabilities of a Materialized View
http://download.oracle.com/docs/cd/B28359_01/server.111/b28326/repmview.htm#sthref481
BEGIN
DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM oe.orders@xxxxxxxxxxxxxxxx o
WHERE EXISTS (SELECT * FROM oe.customers@xxxxxxxxxxxxxxxx c
WHERE o.customer_id = c.customer_id AND c.credit_limit > 500)');
END;
/
Query the MV_CAPABILITIES_TABLE to see the results.
hth,
Martin
On Fri, May 29, 2009 at 21:20, Xu, Roger <Roger.Xu@xxxxxxxx> wrote:
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_ACCNT_ID,
S_SRC.TMPL_ID,S_SRC.BU_ID,S_SRC.PERIOD_ID,S_SRC.PROMO_PRI_LST_ID,S_SRC.ACTIVE_FLG,
8 9
S_SRC.ADVERT_DESC_TEXT,S_SRC.COUPON_DROP_FLG,S_SRC.DISPLAY_FLG,S_SRC.CONSUME_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.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_CASE,
18
S_SRC_CHNL.ACTL_SPENT_AMT,S_SRC_CHNL.ACTL_INCR_CASES,S_SRC_CHNL.EST_BASELINE_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@xxxxxxxxxxx
26 left outer join
S_SRC_CHNL@xxxxxxxxxxx on S_SRC.ROW_ID = S_SRC_CHNL.PAR_ROW_ID
27 left outer join S_PRI_LST@xxxxxxxxxxx
on S_SRC.PROMO_PRI_LST_ID = S_PRI_LST.ROW_ID;
left outer join S_SRC_CHNL@xxxxxxxxxxx on
S_SRC.ROW_ID = S_SRC_CHNL.PAR_ROW_ID
*
ERROR at line 26:
ORA-12015: cannot create a fast refresh
materialized view from a complex query
Other related posts:
