Re: sum table

ohahaahr_at_hotmail.com wrote:
> 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

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.

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