Home » SQL & PL/SQL » SQL & PL/SQL » Resume Data
Resume Data [message #345229] Tue, 02 September 2008 12:03 Go to next message
mbalves
Messages: 20
Registered: May 2007
Junior Member
A newbie question, using an academic example: I have a system to product stocks management. My product table has more and less 10,000 records. On average, each product has 10,000 movements by year. So, in a year I will have more and less 100,000,000 movements (considerer that movements can be in one or in two movement tables). In the stocks management system, in each movement is necessary to consult the product stock (for example, to check the minimum security level).
My question is: in a performance point of view how I should create data resume:
a) Creating a view to data resume, a view that show for each product the amount in stock (select ProductId, sum(…), … from … group by ProductId);
b) Creating a field in product table to keep the amount of stock. This field must be maintenance creating triggers over movement tables.

Which is the best?

In a post in other topic, a member recommended me:
Use triggers for only 2 purposes:
- checking values/parameters
- logging
nothing else.

I usually create fields to resume data and maintenance it by using triggers but I have doubts about this practice.

A view over 100,000,000 with “group by” is not too much computational heavy? It is feasible if I tuning the command?
Which is ORACLE behavior in a non materialized view? ORACLE construct the view data in runtime or he already have resume data?
Re: Resume Data [message #345238 is a reply to message #345229] Tue, 02 September 2008 12:59 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In a post in other topic, a member recommended me:
Use triggers for only 2 purposes:
- checking values/parameters
- logging
nothing else.

I concur.
Triggers don't work in multiuser environment when they use aggregate data from other table unless you lock the tables.
Anyway, to achieve what you want you need to either lock the table (pessimistic way) or use a materialized view (optimistic way) like in Barbara's example at http://www.orafaq.com/forum/m/344192/102589/?#msg_344192

A (standard) view is just a named query text.

Regards
Michel
Previous Topic: Again? ORA-00911: invalid character Error
Next Topic: Get the value by dept
Goto Forum:
  


Current Time: Mon Dec 05 23:44:48 CST 2016

Total time taken to generate the page: 0.05249 seconds