Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sum table

Re: sum table

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 31 Aug 2005 08:08:35 -0700
Message-ID: <1125500872.425776@yasure>


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)
Received on Wed Aug 31 2005 - 10:08:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US