To-Date Calculations

What direction would you recommend to support to-date calculations using an Oracle 8i Data Server? For example, order amount is captured weekly and a report needs to also show month-to-date and year-to-date order amount totals.

The direction I have taken is to use a view on top of a view on the table that has the weekly order amounts. I was told by the developers that there is no built-in feature/function in Oracle Reports and Oracle Discoverer to help with this. Therefore, views were used because it simplified access for Oracle Reports and Oracle Discoverer, plus a special table did not have to be built with precalculated values.

The concern being performance as the use of to-date calculations grows. There are nearly 4 million rows in the base table and the views can generate multiple rows from each row in the table. The current Discoverer report uses a subset of that data and runs in 20 seconds (potential to be reduced to 12 seconds with some Discoverer query tuning), which is goodness. If it takes 12 seconds at best for a subset of the data, it would not take much to turn into minutes and minutes are a concern.

The last ditch solution is considered to be building an aggregated table of the precalculated values. The views would be used to generate the data for this table.

Short of the last ditch solution, what would you do? How have you supported the need for to-date calculations? Received on Sun Oct 01 2000 - 17:11:26 CDT