I'm trying to refactor a slow Oracle database, where the developers naively chose a VARCHAR2 UUD4 as the primary key for what is really an append only time series dataset. I've found that the uniqueness check for the primary key (frustratingly I can't quite convince the senior developer to drop that uniqueness constraint) and foreign key checks for UUID4s are the cause of poor performance.
This is the first time I've touched Oracle in my career, so I'm unfamiliar with how it works. In MySQL the primary key absolutely effects how the table is allocated physically, but I am getting mixed messages about how it affects things in Oracle. Obviously there are index-optimised-tables, but those have to explicitly set.
Looking at the index statistics for the primary key, the clustering factor is nearly as high as the row count. Did the random primary key have anything to do with that?
If I have a random primary key, does it affect how the row is physically allocated? For instance, would it mean a similarly random allocation of the row to a block?
If so, how can I change that so the database will try to cluster the writes to a smaller set of blocks (to reduce random IO)? Would moving to a 'clustered' identifier like a UUID1 improve insert performance?