MVIEW

https://docs.oracle.com/en/middleware/goldengate/core/19.1/coredoc/limitations-support-materialized-views-1.html

Oracle GoldenGate Microservices Documentation

 

Limitations of Support for Materialized Views

Materialized views are supported by Extract with the following limitations.

• Materialized views created WITH ROWID are not supported.
• The materialized view log can be created WITH ROWID.
• The source table must have a primary key.
• Truncates of materialized views are not supported. You can use a DELETE FROM statement.
• DML (but not DDL) from a full refresh of a materialized view is supported. If DDL support for this feature is required, open an Oracle GoldenGate support case.
• For Replicat the Create MV command must include the FOR UPDATE clause
• Either materialized views can be replicated or the underlying base table(s), but not both.

 

 

 

직역

 

1. Materialized View WITH ROWID

Materialized views created WITH ROWID are not supported.

• ROWID 기반 MV는 GoldenGate에서 추적하거나 복제할 수 없음
• 즉, OGG로 DML을 적용할 MV는 ROWID 없이 PRIMARY KEY 기반이어야 함

 

2. Materialized View Log

The materialized view log can be created WITH ROWID.

• MV LOG는 ROWID 기반으로 만들어도 됨
• FAST REFRESH를 위해서는 MV LOG가 있어야 하고, 여기서는 허용됨

 

 3. Source Table Primary Key

The source table must have a primary key.

• OGG가 FAST REFRESH를 지원하려면 원본 테이블에 PK 필요
• PK 없으면 UPDATE/DELETE를 처리할 수 없음 → ORA-01732, Key column missing 오류와 관련

 

 4. TRUNCATE 제한

Truncates of materialized views are not supported. You can use a DELETE FROM statement.

• TRUNCATE는 DDL이므로 MV에 적용 불가
• TRUNCATE 후 FAST REFRESH 불가 → 우리가 본 ORA-32321
• 해결책: DELETE FROM + COMMIT 후 FAST REFRESH 가능

 

 5. DML vs DDL

DML (but not DDL) from a full refresh of a materialized view is supported.

• MV 자체의 **REFRESH(INSERT/UPDATE/DELETE)**는 지원
• ALTER MV 같은 DDL은 OGG에서 자동 적용 안 됨 → 직접 DB에서 처리해야 함

 

 6. Replicat FOR UPDATE

For Replicat the Create MV command must include the FOR UPDATE clause

• MV를 Replicat 대상으로 지정하려면 FOR UPDATE 옵션 필요
• 그래야 OGG가 UPDATE/DELETE를 MV에서 처리할 수 있음

 

 7. MV vs Base Table

Either materialized views can be replicated or the underlying base table(s), but not both.

• MV와 원본 테이블을 동시에 복제하면 충돌
• 구조 설계 시 하나만 선택해야 함:
  1. Base Table 복제 → MV는 Refresh만
  2. MV 복제 → Base Table 복제 안 함