Re: Remote Materialized View containing joins

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Sat, 30 May 2009 21:10:45 +0200
Message-ID: <cd8f74560905301210h5ddffe20o596623bf832c069c_at_mail.gmail.com>

have you checkedDetermining the Fast Refresh Capabilities of a Materialized View
http://download.oracle.com/docs/cd/B28359_01/server.111/b28326/repmview.htm#sthref481

<http://download.oracle.com/docs/cd/B28359_01/server.111/b28326/repmview.htm#sthref481>

BEGIN
  DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM oe.orders_at_orc1.example.com o     WHERE EXISTS (SELECT * FROM oe.customers_at_orc1.example.com 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_at_dpsg.com> 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_at_BTPMQ.WORLD
>
> 26 left outer join S_SRC_CHNL_at_BTPMQ.WORLD on S_SRC.ROW_ID =
> S_SRC_CHNL.PAR_ROW_ID
>
> 27 left outer join S_PRI_LST_at_BTPMQ.WORLD on S_SRC.PROMO_PRI_LST_ID =
> S_PRI_LST.ROW_ID;
>
> left outer join S_SRC_CHNL_at_BTPMQ.WORLD 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
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat May 30 2009 - 14:10:45 CDT