Insert as select - why is it writing so much Undo tablespace data?
49976Dec 10 2009 — edited Dec 10 2009Environment: Oracle 10g R2, Sun solaris, ASM
I have table A with 92 million rows. Table B, which is identical to table A, has 5 million rows. Table A only has three index columns ( no composite indexes ) and the average row length is 109 bytes.
I issued the command: insert into A select * from B; in order to append the rows from B to A. This command has been executing for over 4 hours. I've been watching the tablespace usage in EM and I can see the undo tablespace usage grow to over 9 GB's and the query is not finished. Meanwhile, the index tablespace has not grown at all. There is no other activity on this instance.
I've performed similar inserts in the past and it hasn't taken nearly this long. According to my calculations, writing all of the 5 million rows to Undo would only require about half a gig. Also, we thought there might be a 'ghost' session or process that was causing problems earlier, so we shut everything down and rebooted the server. There is no other activity.
Could anyone explain why it would write so much undo data? It's almost as if Oracle is making a copy of table A ( 92 million rows ) first.
Thanks,
BAH