[askdba] Re: Materialized View - On Commit
- From: PV Narayanan <PNarayanan@xxxxxxxxxxxxxxxxxx>
- To: askdba@xxxxxxxxxxxxx
- Date: Mon, 27 Sep 2004 07:45:38 +0400
Hi Mudhalvan,
You can try creating the materialized view (MV) with refresh rate 1 minute
or even less than that.
That way you will be able to refresh this MV whenever any DML is issued on
the table.
materialized view created with refresh option every 3 minutes - for each
sec
--- change value to 1/86400 - For every hour - 1/24
create materialized view LOG
PCTFREE 0 TABLESPACE xxxxxx
STORAGE (INITIAL 20480K NEXT 20480K PCTINCREASE 0)
build immediate
using index tablespace applsysx
refresh force with rowid
START WITH to_date('26-SEP-2004 11:43:02 AM','dd-Mon-yyyy HH:MI:SS AM')
--- NEXT sysdate + 1/1440
NEXT sysdate + 3/1440
enable query rewrite
as
select * from tbl_entity;
You may substitute appropriate values for primary key and for other
values.
Hope this helps.
Best Regards
P.V.Narayanan
"Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
Sent by: askdba-bounce@xxxxxxxxxxxxx
27/09/2004 07:03 AM
Please respond to
askdba@xxxxxxxxxxxxx
To
<askdba@xxxxxxxxxxxxx>
cc
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:
