Home » Server Options » Replication » Refresh On Commit not refreshing accurately
Refresh On Commit not refreshing accurately [message #124651] Mon, 20 June 2005 15:04
suzanne0412
Messages: 1
Registered: June 2005
Location: Portland Oregon
Junior Member
My environment looks like this, I'm running Oracle 9.2.0.4 on Sun -
CREATE TABLE ORACLE.CD_RMIS_LIMITS
(
RMIS_ID NUMBER NOT NULL,
COVERAGE_TYPE VARCHAR2(90) NOT NULL,
LIMITDESCRIPTION VARCHAR2(90) NOT NULL,
LIMITAMOUNT NUMBER NOT NULL,
INSERTED DATE NOT NULL
)

ALTER TABLE ORACLE.CD_RMIS_LIMITS
ADD CONSTRAINT PK_CD_RMIS_LIMITS
PRIMARY KEY (RMIS_ID,COVERAGE_TYPE,LIMITDESCRIPTION)

CREATE TABLE ORACLE.CD_RMIS_QR_LIMITS
(
DESCRIPTION VARCHAR2(75) NOT NULL
)

CREATE MATERIALIZED VIEW LOG ON cd_rmis_limits
WITH ROWID, SEQUENCE (rmis_id, limitamount, coverage_type, limitdescription)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON cd_rmis_qr_limits
WITH ROWID, SEQUENCE (description)
INCLUDING NEW VALUES;

create materialized view cd_rmis_cargo_limit_view
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
as
select rmis_id, max(limitamount) as cargo_limit
from cd_rmis_limits, cd_rmis_qr_limits
where coverage_type='CARGO'
and limitdescription = description
group by rmis_id;

I'm able to trace my DML through the base table (cd_rmis_limits) to the materialized view log but the transactions don't seem to be going any further then the transaction log. This isn't consistent (some DML works, some doesn't). Here is are the results from a set of queries -

The base table
select * from oracle.cd_rmis_limits where rmis_id = 2969 and coverage_type = 'CARGO'
RMIS_ID : 2969
COVERAGE_TYPE : CARGO
LIMITDESCRIPTION : CARGO LIMIT
LIMITAMOUNT : 250000
INSERTED : 17-jun-2005 10:43:11
-----------------
RMIS_ID : 2969
COVERAGE_TYPE : CARGO
LIMITDESCRIPTION : DEDUCTIBLE
LIMITAMOUNT : 2500
INSERTED : 17-jun-2005 10:43:11
-----------------
The materialized view log
select * from oracle.mlog$_cd_rmis_limits where rmis_id = 2969 and coverage_type = 'CARGO'
RMIS_ID : 2969
LIMITAMOUNT : 100000
COVERAGE_TYPE : CARGO
LIMITDESCRIPTION : CARGO LIMIT
M_ROW$$ : AAACf8AAFAAAw0MABn
SEQUENCE$$ : 880244
SNAPTIME$$ : 01-jan-4000 00:00:00
DMLTYPE$$ : D
OLD_NEW$$ : O
CHANGE_VECTOR$$ : 00
-----------------
RMIS_ID : 2969
LIMITAMOUNT : 2500
COVERAGE_TYPE : CARGO
LIMITDESCRIPTION : DEDUCTIBLE
M_ROW$$ : AAACf8AAFAAAw0MABo
SEQUENCE$$ : 880245
SNAPTIME$$ : 01-jan-4000 00:00:00
DMLTYPE$$ : D
OLD_NEW$$ : O
CHANGE_VECTOR$$ : 00
-----------------
RMIS_ID : 2969
LIMITAMOUNT : 250000
COVERAGE_TYPE : CARGO
LIMITDESCRIPTION : CARGO LIMIT
M_ROW$$ : AAACf8AAFAAA6d0ABQ
SEQUENCE$$ : 880254
SNAPTIME$$ : 01-jan-4000 00:00:00
DMLTYPE$$ : I
OLD_NEW$$ : N
CHANGE_VECTOR$$ : FE
-----------------
RMIS_ID : 2969
LIMITAMOUNT : 2500
COVERAGE_TYPE : CARGO
LIMITDESCRIPTION : DEDUCTIBLE
M_ROW$$ : AAACf8AAFAAA6d0ABR
SEQUENCE$$ : 880255
SNAPTIME$$ : 01-jan-4000 00:00:00
DMLTYPE$$ : I
OLD_NEW$$ : N
CHANGE_VECTOR$$ : FE
-----------------
The limitdescription of 'DEDUCTIBLE' doesn't appear in the cd_rmis_qr_limit table therefore that transaction is not supposed to be processed by the materialized view. However, the limitdescription of 'CARGO' does appear in the cd_rmis_qr_limit table so therefore this DML should be processed by the materialized view which would make the limitamount = 250000. When I do a select on the materialized view I get -

select * from oracle.cd_rmis_cargo_limit_view where rmis_id = 2969
RMIS_ID : 2969
CARGO_LIMIT : 100000
-----------------
This CARGO_LIMIT value is an old amount and it should have been updated.

Has anybody run into a problem with a materialized view doing a REFRESH ON COMMIT inconsistently?
Previous Topic: Materialized View with ORDIMAGE
Next Topic: procedure refresh materialized view submit a job
Goto Forum:
  


Current Time: Thu Mar 28 05:34:53 CDT 2024