OraFAQ Forum: Performance Tuning » Improve performance with MV

Home » RDBMS Server » Performance Tuning » Improve performance with MV

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Improve performance with MV [message #153737]

Tue, 03 January 2006 22:19

AlinaC
Messages: 45
Registered: November 2005
Location: India

Member

I have created updateable materialized view in Oracle 9i, to improve query performance.But I want to delete aged ( 4 months old) records from Materialized view but not from master tables. I’m able to delete aged records from that materialized view, but at next refresh all aged records are again being populated to the Materialized view along with new records. Please help me to solve this problem.

Regards,
Alina.

Report message to a moderator

Re: Improve performance with MV [message #153843 is a reply to message #153737]

Wed, 04 January 2006 06:21

wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile

Member

HI alina

A pleasure to greet you

Here plenty of information on the theme exists

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#31780
---------------------------------------------------------

In relation to your problem of attachment a possible solution
Always and when your board master have a field FEC_ULT_actualizacion, Than a date of recording and update of the record in the table

SQL> DESC TABLE_MASTER;
Name Null? Type
----------------------------------------- -------- -------------------
ID_SEXO NOT NULL CHAR(1)
N_SEXO NOT NULL VARCHAR2(10)
TRATAMIENTO CHAR(1)
TITULO VARCHAR2(5)
FEC_ULT_ACTUALIZACION DATE
COMENT VARCHAR2(2000)
ID_USUARIO VARCHAR2(40)

SQL>

Then, in the where of the select of the view materialized, the records would be branded to materialize

DROP MATERIALIZED VIEW MM2;
CREATE materialized VIEW MM2
AS SELECT *
FROM tabla_master
where FEC_ULT_ACTUALIZACION > ADD_MONTHS(SYSDATE, -4);

----------------------------------------------------------

Greetings

williams

Report message to a moderator

Re: Improve performance with MV [message #153846 is a reply to message #153843]

Wed, 04 January 2006 06:27

wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile

Member

I send off you
Attached file .txt
Greetings

williams

Attachment: view to alina.pl
(Size: 1.93KB, Downloaded 1370 times)

Report message to a moderator

Previous Topic:	long and short table scans
Next Topic:	perfstat report - explanation please

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 07:48:55 CST 2024