Re: re-indexing per statement or per row?

It's telling you that Oracle can generate one undo record per block modified, and doesn't always generate one undo record per row modified.

Which means that when you have a statement that inserts multiple rows, the index can have multiple rows modified in one atomic block change - i.e. the index isn't necessarily modified one row at a time.

If you check your session statistics for the insert /*+ append */, you will (probably, perhaps dependent on version) find that Oracle has reported a sort of the N rows that you inserted so that it can maximise the number of times it gets the benefit of being able to insert multiple index entries into the same block under the same undo record.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Niall Litchfield wrote in message
<3cb18cb0$0$238$ed9e5944_at_reading.news.pipex.net>...


>As you say interesting, results below on 9i. The 20,000 undo records for

the


>10000 statements makes sense (this is the same table with one index). The

>figures for the single statements are harder to explain - though they

>clearly show the performance advantage. Perhaps I'm just displaying my

>ignorance here.

>