Re: sum table

ohahaahr_at_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.
>
> 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
>

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 Received on Wed Aug 31 2005 - 11:03:23 CDT