6

One of my (Oracle 10g) tables has grown over time to about 840 million rows. An index (DATE, NUMBER(38)) on it occupies 38GB at present. I'd like to change the index (I think it could make good use of COMPRESS, for example) but it fails with errors relating to TEMP getting full - it's 4GB at present.

Is there a way to build an index that will be larger than TEMP? I realise I could export the table, truncate it, create new and interesting indices and then reload, but that goes way beyond my capacity for pain...

Improve this question
1
  • Unless your dates aggregate really well, COMPRESS might not buy you much. Unless there are several NUMBERS for each distinct DATE, index compression adds overhead in both storage and processing. Have you considered instead partitioning the index?
    – Adam Musch
    Commented Apr 21, 2011 at 14:21

2 Answers 2

Reset to default
10

What is TEMP used for in Oracle? As a scratch area for doing sorts that won't fit into main memory. So the issue is that you need to do a big sort in order to create your index, and you don't have a large enough PGA for it. So your options are: a larger PGA or a "temporary temporary" tablespace; simply create one big enough, make it your user's temporary tablespace (ALTER USER xxx TEMPORARY TABLESPACE temptemp;) build the index, then set your user back and drop it.

Improve this answer
1

Sure you can. Explicitly set the sort_area_size for your session and a lot of PQ. Adding a few tempfiles might be easier.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.