emblem
Programmer
- Jun 25, 2002
- 26
I am trying to create this MView for a PeopleSoft CRM project. Oracle 10.2.0.4
The A table has 480,000 rows, but only 100 of them meet the A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER' condition. This SQL is hard part of a larger SQL executed constantly. Even with an index scan, it takes 3 sec. With an Mview, it should be << 1 sec.
but line 18 has nothing to do with rowid.
I have read rwijk's excellent mongraph about MVs at
but he does not mention this error.
Any suggestions appreciated.
Many thanks - emblem
The A table has 480,000 rows, but only 100 of them meet the A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER' condition. This SQL is hard part of a larger SQL executed constantly. Even with an index scan, it takes 3 sec. With an Mview, it should be << 1 sec.
Code:
CREATE MATERIALIZED VIEW LOG ON PS_RA_CMPGN_WAVE
WITH ROWID, SEQUENCE(BUSINESS_UNIT,RA_CAMPAIGN_ID,RA_CMPGN_WAVE_ID,RA_OFFER_ID)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON PS_RA_PROMO_OFFER
with ROWID, SEQUENCE(SETID,RA_PROMOTION_CODE, CAB_SOURCE_YEAR)
INCLUDING NEW VALUES;
1 CREATE MATERIALIZED VIEW SYSADM.PS_CAB_PROMCD_MATVW
2 REFRESH FAST ON COMMIT WITH ROWID
3 ENABLE QUERY REWRITE
4 AS
5 SELECT
6 A1.rowid
7 ,A.rowid
8 ,a1.setid
9 ,A.RA_CAMPAIGN_ID
10 ,A.RA_CMPGN_WAVE_ID
11 , A.RA_OFFER_ID
12 , count(*)
13 from PS_RA_CMPGN_WAVE A
14 ,PS_RA_PROMO_OFFER A1
15 WHERE A.BUSINESS_UNIT = A1.BUSINESS_UNIT and
16 A.RA_CAMPAIGN_ID = A1.RA_CAMPAIGN_ID and
17 A.RA_CMPGN_WAVE_ID = A1.RA_CMPGN_WAVE_ID and
18 A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER'
19 group by
20 a1.rowid
21 ,a.rowid
22 ,a1.setid
23 ,A.RA_CAMPAIGN_ID
24 ,A.RA_CMPGN_WAVE_ID
25* ,A.RA_OFFER_ID
26 /
A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER'
*
ERROR at line 18:
ORA-00904: "ROWID": invalid identifier
but line 18 has nothing to do with rowid.
I have read rwijk's excellent mongraph about MVs at
but he does not mention this error.
Any suggestions appreciated.
Many thanks - emblem