Oracle v11.2, Solaris 10, 64-bit, Locally managed tablespaces on regular old filesystem datafiles.
I have a lookup table that I refresh daily with a process like:
1. Populate a temporary table TEMP_TABLE with the new data
2. Delete the main table with 'delete from MAIN_TABLE;'
3. Copy in the new data with 'insert into MAIN_TABLE select * from TEMP_TABLE;'
The table itself is about 330M in size and has 4.2mil rows. The reason for the delete/insert, instead of a truncate/drop-index/insert/create-index process is that the data MUST always be there, as either the old or the new set... but never as an empty set, or partial set. Up to now we have avoided having two tables and flip-flopping with a synonym, but it may come to that if I can't sort this problem out.
There are 5 indexes on this table, and they are constantly growing. One of them ended up at 24G! (in spite of the table only being 330M)
I can reset the problem by rebuilding the indexes online, but that seems like only a temporary fix.
Can anyone explain why Oracle is keeping old index blocks when the entire table has been deleted?
Alternatively, is there a better process to use instead of delete/insert that will maintain my "always needed" requirement?