can someone explain me how PK is verified (a lot of IOs)?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.
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.