Records Fetch Taking More time Through Views!!!!

From: Praveen <spraveen2001_at_yahoo.com>
Date: 21 Oct 2003 12:02:27 -0700
Message-ID: <98d8ec76.0310211102.78c2b933_at_posting.google.com>

Hi All,

I have a schema which contains the financial data on yearly and monthly basis.
In this schema, i have 2 materialized views (mat_views1) which gets the 2003 year data from another schema as 2003 data will not exists in the current schema. and another(mat_views2) which get some other related data from other schema and that is also on yearly wise(like 2004, 2005...). And at the same time, some financial data is coming from the current schema and that is also on yearly basis. Now the requirement is, we need to display the financial data on each year, means 2004, 2005, 2006.. So, i created a view which gets the data for 2004,2005,2006,2007,2008 from mat_views2. for each year, one query, year as hardcoded. the same thing is happening while getting the data from current schema for yearly wise. here also, i have seperate query for each year.

All the data is getting for a perticular Organization by passing ORG_ID. Here the problem is..i have created view and it is working fine for individual orgs. But, they have one option called "select All orgs", then they will send all orgs to the view. The views is taking lot of time...i tried to trace it using tkprof..here is output i got...

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse        1      1.26       1.27          0          0          0  
        0
Execute      2      0.00       0.00          0          0          0  
        0
Fetch       12    278.79     290.04     809508    6957061     531564  
      156

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

---

total       15    280.05     291.31     809508    6957061     531564  
      156

Here, Fetch is taking lot of time. Can we make the fetch faster? or it will take that much time only as i'm using seperate query to get the for each year?
create view rep_view as
select org_id,
(

select sum(value)
from mat_views2 where
org_id = org.org_id
and year = 2004
) y_2004,
(

select sum(value)
from mat_views2 where
org_id = org.org_id
and year = 2004
) y_2005,

.
.
.

from
(select org_id from org a) org;
Received on Tue Oct 21 2003 - 21:02:27 CEST