[askdba] Re: Materialized View - On Commit
- From: "Patricia Eyenga" <patricia.e@xxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>, <askdba@xxxxxxxxxxxxx>
- Date: Mon, 27 Sep 2004 05:19:08 -0700
Instead of "refresh fast" try "fast refresh"
_______________________________________
Patricia Eyenga
President
On-Pointe Database Services Co.
Tel: 202-327-0877
Fax: 301-552-2290
Web: http://www.odsc.net
Email: patricia.e@xxxxxxxx
----------------------------------------
From: "Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
Sent: Sunday, September 26, 2004 8:01 PM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Materialized View - On Commit
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:
