This discussion is archived
1 Reply Latest reply: Dec 6, 2012 9:58 PM by 589360 RSS

Group by with big size data

978481 Newbie
Currently Being Moderated
hi,

I have a table with 4 500 000 000 rows and 7 coloumns. I tried to group my data with using 5 coloumns and other two coloumns are my aggregate columns.

For ex: The sql statment and output is given below. I group my data using Col1 to col5.

SELECT Col1 Col2 Col3 Col4 Col5 AVG(A) AVG(B)
FROM TABLE_A
GROUP BY Col1 Col2 Col3 Col4 Col5
AND COL1< 5

Col1 Col2 Col3 Col4 Col5 AVG(A) AVG(B)
1 1 1 1 2 3 4
....

When I execute the query , "ORA-01652" error is thrown , because the tablespace is not enough .

The question is How can I change my sql statment for solving this error ?
  • 1. Re: Group by with big size data
    589360 Newbie
    Currently Being Moderated
    Your temporary tablespace is not large enough to process the whole data set.

    Seems to be you are in a position that extending the TEMP tablespace is not possible too.

    Then you can try to segment the data set with some filters using Col1,Col2,Col3,Col4,Col5 . May be 1,000,000 rows in one segment.

    So you can AVG a single segment at a run. Store them in a table. After processing all the segments you can summarize the 4500 rows once more.

    Edited by: Sanjaya Balasuriya on Dec 7, 2012 11:28 AM

    Edited by: Sanjaya Balasuriya on Dec 7, 2012 11:28 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points