[askdba] Re: Materialized View - On Commit

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

Hi Rigs,
        Thank you so much for your mail. I don't want to add one more
Tigger in my watch list. If we are not able to do then I will go for the
same.=20

Thank You
Mudhalvan M.M

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of Regis Biassala
Sent: Monday, September 27, 2004 6:28 PM
To: askdba@xxxxxxxxxxxxx
Cc: PNarayanan@xxxxxxxxxxxxxxxxxx
Subject: [askdba] Re: Materialized View - On Commit


Why can't you use a trigger if that solution of MV does not work !!?
Regis

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of Mudhalvan, Moovarkku
Sent: 27 September 2004 05:00
To: askdba@xxxxxxxxxxxxx
Cc: PNarayanan@xxxxxxxxxxxxxxxxxx
Subject: [askdba] Re: Materialized View - On Commit

Hi Narayan,
        Thank you so much for your mail. Yeah I got your point.=3D20

        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.=3D20

Thank You
Mudhalvan M.M
                =3D20

-----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=3D20 or even less than that. That way you will be able to refresh
this MV whenever any DML is issued on=3D20 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=3D20
values.

Hope this helps.


Best  Regards
P.V.Narayanan



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

                 My First table have Primary key as Entity_ID and Second

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

Here is the Commands=3D3D20

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

Any help or tips is appricated.

Thank You
Mudhalvan M.M







Other related posts: