Home » SQL & PL/SQL » SQL & PL/SQL » Regarding materialized view
Regarding materialized view [message #184714] Thu, 27 July 2006 09:24 Go to next message
sureshd_2k
Messages: 1
Registered: July 2006
Junior Member
select
TTB_Record.col_Employee_ID col_Employee_ID ,

TTB_Record.col_Full_Name col_Full_Name ,

TTB_Record.col_Country_Code col_Country_Code ,
PrevRecord.col_Country_Code prv_Country_Code ,
TTB_Record.col_Last_Update_Dt col_Last_Update_Dt
from
ESPA_V1_0.ttb_Employee TTB_Record,
ESPA_V1_0.ttb_Employee PrevRecord
where
TTB_Record.col_Employee_ID = PrevRecord.col_Employee_ID
AND
TTB_Record.col_Country_Code <> PrevRecord.col_Country_Code
AND
PrevRecord.col_Last_Update_Dt =
(
Select Nvl ( Max ( PrevSub.col_Last_Update_Dt ), TTB_Record.col_Last_Update_Dt )
from ESPA_V1_0.ttb_Employee PrevSub
where
PrevSub.col_Employee_ID = TTB_Record.col_Employee_ID
AND
PrevSub.col_Last_Update_Dt < TTB_Record.col_Last_Update_Dt
)

Tha above query taking 8 min for execution.
Now i created a materialized view on that query but it is taking refereshing 1.30 min.

create materialized view mvw_change_employees as (i have given above query)

Could please tell me how to write materialized view in optimised way.

Re: Regarding materialized view [message #184752 is a reply to message #184714] Thu, 27 July 2006 14:39 Go to previous message
shobanasuha
Messages: 2
Registered: July 2006
Location: California
Junior Member
There is a wonderful article about writing materialized views in optimized way. Try http://www.akadia.com/services/ora_materialized_views.html for more details.

Hope this solves your problem

regards
Shobana
Previous Topic: Identifying duplicate rows in a column
Next Topic: help in converting date...
Goto Forum:
  


Current Time: Thu Dec 08 22:33:12 CST 2016

Total time taken to generate the page: 0.09510 seconds