[askdba] Re: Materialized View - On Commit - askdba

[askdba] Re: Materialized View - On Commit

From: "Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
To: <askdba@xxxxxxxxxxxxx>
Date: Mon, 27 Sep 2004 13:00:04 +0900
Hi Narayan,
        Thank you so much for your mail. Yeah I got your point.=20

        My concerns is I don't want to refresh the View Every one min or
3 Min. It has to be refreshed only when any updates on TBL_ENTITY Table.


        For your information. If I am not using more than one field as
primary key then it is working fine with the SQL I sent you already.=20

Thank You
Mudhalvan M.M
                =20

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of PV Narayanan
Sent: Monday, September 27, 2004 12:46 PM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: Materialized View - On Commit


Hi Mudhalvan,
You can try creating the materialized view (MV) with refresh rate 1
minute=20
or even less than that.
That way you will be able to refresh this MV whenever any DML is issued
on=20
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=20
values.

Hope this helps.


Best  Regards
P.V.Narayanan



"Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>=20
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,
=3D09
                 Good Morning. I am trying to create Metrialized View=20
based on
Primary Key.=3D20

                 My First table have Primary key as Entity_ID and Second

table
have Primary key as combination of ENTITY_ID and LOAN_ID_KEY=3D20

Here is the Commands=3D20

- 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=3D20
REFRESH fast ON COMMIT ENABLE query REWRITE=3D20
 AS SELECT
ENTLOAN.LOAN_ID_KEY AS LOAN_ID ,MIN(NVL(ENTITY.ENTITY_NAME_LOCAL,
ENTITY.ENTITY_NAME_ENGLISH)) AS ENTITY_NAME_LOCAL=3D20
,MIN(NVL(ENTITY.ENTITY_NAME_ENGLISH, ENTITY.ENTITY_NAME_LOCAL)) AS
ENTITY_NAME_ENGLISH FROM=3D20 TBL_ENTITY_LOAN ENTLOAN, TBL_ENTITY
ENTITY=3D20 WHERE ENTLOAN.ENTITY_ID =3D3D 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.=3D20

Any help or tips is appricated.

Thank You
Mudhalvan M.M
[askdba] Re: Materialized View - On Commit

Other related posts:
