1 2 Previous Next 15 Replies Latest reply: Jun 2, 2008 3:20 AM by Jonathan Lewis Go to original post RSS
      • 15. Re: a lot of INSERTs with incremented sequence causes performance degradation
        Jonathan Lewis
        can someone explain me how PK is verified (a lot of IOs)?
        I've observed performance degradation when many
        inserts is done to 200mln rows table with huge PK index.
        Looks like whole index is read for pk veryfication , or maybe I'm wrong.
        Database is 10.2.0.3 EE.
        To check the PK Oracle inserts the row into the table then does a simple "index unique scan" to find the index leaf block where the PK entry should be inserted. This should take no more than 3 or 4 block visits. For a very large index you might expect one of those visits (the leaf block) to become a physical I/O request.

        If you are inserting data in primary key order (e.g. a sequence number for the key as suggested by the thread title) then you wouldn't normally expect even that one I/O because the necessary block would either be in memory from the previous insert, or it would be "newed" without being read. However, if you've defined the index as a "reverse" index then one read per key is much more likely.

        There are other reasons why you might see a lot of I/O as you do your insert - but before considering them, it would be helpful to hear what makes you sure that the I/O relates to the primary key index.

        Regards
        Jonathan Lewis
        http://jonathanlewis.wordpress.com
        http://www.jlcomp.demon.co.uk
        1 2 Previous Next