Undo (rollback) volume

A recent poster on the comp.databases.oracle.server had a question about the amount of undo generated when inserting into a table. In outline the problem was this:

My table size is 350MB, whereas the transaction involving the insert statement has taken 2,500 MB. There are no indexes, only data.

There are plenty of questions you could ask before producing a sensible answer to this question, but on the face of it the results are a little strange.

Subquery Selectivity

The optimizer in 10g (possibly release 2 only) has an important fix that may be enough to make some people decide to accelerate their migration programs. It’s to do with filter subqueries.

To get things started, here’s a little query with its execution path: