SQL Language (MOSC)

MOSC Banner

Materialized view with group by don't have actual record after refresh

edited Dec 21, 2013 12:17AM in SQL Language (MOSC) 3 commentsAnswered
Oracle version is 11.2.0.3 on linux 64-bit.
Below test. Materialized view have 0 records after this test, but must have 1 record.
Maybe somebody know how to fix this problem?

create table person_documents (id number primary key, link_id number, person_id number, document_id number)
/
CREATE MATERIALIZED VIEW LOG ON person_documents WITH PRIMARY KEY,
  ROWID,
  SEQUENCE ( document_id, person_id, link_id ) INCLUDING NEW VALUES
/
CREATE MATERIALIZED VIEW MV_TEST  REFRESH FAST ON COMMIT
as SELECT document_id, min(person_id) min_person_id, max(person_id) max_person_id from person_documents where link_id = 1 group by 
document_id
/
insert into person_documents values (1,2,1,1)
/
commit
/
update person_documents set link_id = 1

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center