sum table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ohahaahr@hotmail.com - 31 Aug 2005 15:54 GMT

Hi !

I have a very big table with 10 million transactions, that contains
information about our sales (items, quantities, amounts, etc).

We run a lot af big (slow) querys, to get sales information.

A query could look like this:

Select st.itemnumber, sum(st.qty), sum(st.amount) from salestrans st
group by st.itemnumber, To_Char(st.salesdate,'YYYY-MM')

This (simplified) query gives us the monthly sale for a specific
article. Due to the size of the table, this query is slow.

Is there a method to make tables in oracle, maintaining the sums, for a
specific itemnumber, for a specific period ???

Can it be done with a materialized view, a trigger, or in some other
way???

We use oracle 10g.

Regards,
Ole

Reply to this Message

DA Morgan - 31 Aug 2005 16:08 GMT

> Hi !
>
[quoted text clipped - 21 lines]
> Regards,
> Ole

www.psoug.org
click on Morgan's Library
click on Materialized View

But you should look at getting the Partitioning option from
Oracle if you don't have it already. Then, perhaps, partition
by date range.

Signature

Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Reply to this Message

Mark D Powell - 31 Aug 2005 16:08 GMT

Ole, yes I think a materialized with query rewrite enabled may well be
useful to you especially if you can limit the query results to only
being through the end of business on the previous day. Then you could
refresh the materialized view just once daily.

HTH -- Mark D Powell --

Reply to this Message

ohahaahr@hotmail.com - 31 Aug 2005 16:32 GMT

Unfortunately the result of my query must be "updated" at all times. So
therefore a nightly refresh of the materialized view will not do.

I am quite new to oracle, so i was thinking, that maybe there was a
posibility to set up some "sum tables", that oracle would maintain for
me. But obviesly a feature like this does not exist.

Then I was thinking about making a trigger on my salestrans-table, and
then update a sumtable.

But i have never worked with triggers before, som i don't know if it
would work.

Regards,
Ole

Reply to this Message

Maxim Demenko - 31 Aug 2005 17:03 GMT

ohahaahr@hotmail.com schrieb:

> Unfortunately the result of my query must be "updated" at all times. So
> therefore a nightly refresh of the materialized view will not do.
[quoted text clipped - 11 lines]
> Regards,
> Ole

You can refresh materialized views on commit. It means, your aggregates
will be recalculated immediately after dml on the master table is
committed (docs state that refresh is even part of commit process). This
process is implemented by means of internal trigger (iirc as c code), so
if you try to implement similar functionality with plsql triggers , you
will probably never achieve as good performance as oracle it does.
However, it doesn't make sense in most cases, because
1) in heavy loaded system can lead to enormous resources consumption
2) there is *no* business* need to have runtime actual aggregates in
almost all the cases.
Be actual ( in terms of aggregates ) costs a lot of resources and can be
easily avoided. I would rethink the business needs and try to find a
compromiss between grade of freshness and resource consumption, so maybe
, (fast)refresh hourly or 4-5 times daily ( if 1 time per day is not
sufficient).

Best regards

Maxim

Reply to this Message

DA Morgan - 31 Aug 2005 22:53 GMT

> Unfortunately the result of my query must be "updated" at all times. So
> therefore a nightly refresh of the materialized view will not do.

And what does that have to do with materialized views? Materialized
views can be as current as you wish. The question is where you want to
take the pain ... in creating the MV (once) or in your queries (many
times).

> Regards,
> Ole

Read the concepts books at http://tahiti.oracle.com.

Signature

Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Reply to this Message