If the primary key on a index-organized table is not incremental, wouldn't this create bottlenecks as data volume grows under OLTP loads?If anything an index on an incremental column makes a bigger bottleneck.
Remember a (B-tree) index is an ordered data structure. So if the values you insert are always increasing, these all have to go at the right hand edge of the index.
If the current max value for ID = 999 and you have three sessions inserting the following values:
Session 1: 1000
Session 2: 1001
Session 3: 1002
They all need to access the rightmost block of an index on ID to add their entries. Only one process at a time can do this. So this gives you an index "hot spot", reducing write concurrency.
Whereas if the sessions insert these values:
Session 1: 1
Session 2: 500
Session 3: 1000
It's likely they will all insert into different blocks of an index. So you can have greater write throughput.
Wouldn't the data being inserted needed to be sorted and inserted in the middle of the leaves?Yes, the database needs to insert new entries in the appropriate leaf block. Which is likely to be "in the middle".
Wouldn't this cause the engine to have to order the pages on the disk, which would be very time and resource consuming? No. An index is a logically ordered data structure. Each leaf block has a pointer to the previous and next leaf.
But consecutive leaves could be "anywhere" on the disk. They don't have to be physically stored next to each other.
If you insert a new entry in the "middle" of an index, Oracle Database locates the corresponding leaf block. If there's space in the block, it adds the entry and you're done.
If the block is full, it does a 50/50 block split. It creates a new leaf block and puts half the entries there. That's it. No other entries are affected. No other data movement takes place.
This is
relatively expensive. But it's less likely many sessions will try and access these blocks at the same time. So you're still likely to get greater write concurrency than an index on increasing values.
Wouldn't this also impede read loads during page locking operations (reads / writes concurrency)?I'm not sure what you're getting at here. But in Oracle Database readers and writers never block each other.
You seem to be conflating general theory about indexes, how they work in other databases and how they might impact your application.
Instead of guessing, read the Oracle Database Concepts Guide on Indexes and Index-Organized Tables to see how they work:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/indexes-and-index-organized-tables.html#GUID-797E49E6-2DCE-4FD4-8E4A-6E761F1383D1 Richard Foote's "Oracle B-Tree Index Internals: Rebuilding The Truth" also contains a lot of detail relevant to your questions, covering block splits, etc.:
https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf And finally, test your proposed design! Ideally compare two (or more) designs.
After your tests check if they meet your performance needs. If yes, stop worrying about all this and get on with building your app ;)
If no, come back with your findings and we'll see how we can help.