Home » RDBMS Server » Performance Tuning » Improve performance with MV
Improve performance with MV [message #153737] Tue, 03 January 2006 22:19 Go to next message
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.

Re: Improve performance with MV [message #153843 is a reply to message #153737] Wed, 04 January 2006 06:21 Go to previous messageGo to next message
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
Re: Improve performance with MV [message #153846 is a reply to message #153843] Wed, 04 January 2006 06:27 Go to previous message
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
Member
I send off you
Attached file .txt
Greetings

williams

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


Current Time: Fri Mar 29 05:18:19 CDT 2024