Home » SQL & PL/SQL » SQL & PL/SQL » Problem: Do I need to use materialized view for this? (oracle 9i)
Problem: Do I need to use materialized view for this? [message #324802] Wed, 04 June 2008 02:25 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Problem: Table A is a transaction table. It contains all the queue details based on differentlocations.Here we are showing the counts for the queues like, give me the count for the queues in last 59 minutes,60-120 minutes In last 6-12 hrs..and so on.for all locations.

E.g, Say location A may have counts 30
and Location B may have counts 20.But in the next moment user may not get the same count as its a volatile data since, the queues are moving from one location to another.

What I am going to do is, I am making a snapshot of table A, say its Snap_A.And here, I want to refresh update the data after every 15 min...What should be the best approach? Do I need to use Materialized view??
Pleae make a note that table A may contain millions of data.



Re: Problem: Do I need to use materialized view for this? [message #324808 is a reply to message #324802] Wed, 04 June 2008 02:57 Go to previous messageGo to next message
Manesh
Messages: 4
Registered: June 2008
Location: Mumbai
Junior Member

Hi Veddata,
what i understand is that you have a table which contains location wise count in queue which is highly dynamic. Have not understood though what is the problem you are facing. Could you pls elaborate.

Regards,

M
Re: Problem: Do I need to use materialized view for this? [message #324811 is a reply to message #324808] Wed, 04 June 2008 03:15 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member


Here problem is that, as the queue flow is dynamic the count that one may get may be changed in next minute.As queues are moving from one location to other.So one who is seeinng the queue count as 30 (say) for location A, 20 for Location B may be 25 for location A, and 10 for Location B ( there are more locations).So here confusion will be created.
Thanks for your response.

So plan is something like that I am going to show 15 min deferred data from table A ( contains queue code,location code...).

For that do I need to make snapshot table SNAP_A (say) or I can do it using materialised view.

If I create a new table say snap_A( which contains the snapshot of data from table A), then I have to refresh the data after
every 15 min ( as snap_A will contain static data).
Table A may contain millions of data...
If we create a new table then we have to take records which are not more than one month old to decrease the datbase load.

What should be the best approach to do that?


What would be the best approach to do this? Hope now you have understood my problem...
Re: Problem: Do I need to use materialized view for this? [message #324832 is a reply to message #324808] Wed, 04 June 2008 04:53 Go to previous message
Manesh
Messages: 4
Registered: June 2008
Location: Mumbai
Junior Member

Hi Veddeta
From the way I see it, your problem firstly is dealing with huge volumes of transaction data. On a daily basis you could have an archival process moving out data to another table and maintain only one week / one months data (as per what is required) in the live transaction table. This can either be a daily / weekly / monthly process. The more the frequency of running this process, obviously the less time it would take for each iteration.
This way you have lesser transactional data to deal with, on which you could either have a snapshot or a view defined as per requirements. In case any historic data needs to be revisited, the new archived table can be referred to. Hope this helps to address you problem.

Regards,

M.
Previous Topic: How to know what are the constraints defined on a table?
Next Topic: Difference while summing
Goto Forum:
  


Current Time: Wed Dec 07 20:42:13 CST 2016

Total time taken to generate the page: 0.29857 seconds