[askdba] Materialized View - On Commit

  • From: "Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
  • To: <askdba@xxxxxxxxxxxxx>
  • Date: Mon, 27 Sep 2004 12:03:07 +0900

Dear DBAs,
=09
        Good Morning. I am trying to create Metrialized View based on
Primary Key.=20

        My First table have Primary key as Entity_ID and Second table
have Primary key as combination of ENTITY_ID and LOAN_ID_KEY=20

Here is the Commands=20

- To create materialized view log for Entity Table

CREATE MATERIALIZED VIEW LOG ON tbl_entity WITH PRIMARY KEY

- To create materialized view log for Entity_loan  Table

CREATE MATERIALIZED VIEW LOG ON tbl_entity_loan WITH PRIMARY KEY

- To create materialized View

CREATE MATERIALIZED VIEW entity_entity_loan=20
REFRESH fast ON COMMIT ENABLE query REWRITE=20
 AS SELECT
ENTLOAN.LOAN_ID_KEY AS LOAN_ID
,MIN(NVL(ENTITY.ENTITY_NAME_LOCAL, ENTITY.ENTITY_NAME_ENGLISH)) AS
ENTITY_NAME_LOCAL=20
,MIN(NVL(ENTITY.ENTITY_NAME_ENGLISH, ENTITY.ENTITY_NAME_LOCAL)) AS
ENTITY_NAME_ENGLISH
FROM=20
TBL_ENTITY_LOAN ENTLOAN, TBL_ENTITY ENTITY=20
WHERE
ENTLOAN.ENTITY_ID =3D ENTITY.ENTITY_ID
GROUP BY ENTLOAN.LOAN_ID_KEY

But I was not able to create the same. I need ON COMMIT. Because this
View need to refreshed whenever update on TBL_ENTITY Table.=20

Any help or tips is appricated.

Thank You
Mudhalvan M.M

Other related posts: