Oracle 12.2.0.1.0
So I have a table that's sub partitioned. It's partitioned by time (CREATED_DATE) and this timestamp value is generated by a before insert trigger.
I have multiple processes inserting data into this table and so I have a column let's call it. UNIQUE_ID which is also generated
using a before insert trigger that uses a sequence (this is a RAC environment where I'm using only 1 node for these writes) with NOORDER=Y on this sequence.
between the CREATED_DATE and the UNIQUE_ID value I'm seeing some inconsistencies.
ie. I'm seeing a value that has a more recent CREATED_DATE value (by microseconds) write into the database with a lower UNIQUE_ID value than what's already there.
So these triggers exist to ensure that I have a guarantee that the next write to the db for the UNIQUE_ID column is a higher value than the previous write.
I need that guarantee regardless of the number of writing processes there are.
Where I do notice the inconsistency in the above is when the previous value was from a different process than the value I'm noticing has the lower UNIQUE_ID.
This surprises me because everything is suppose to be managed by the RAC node and not dependent on the writing processes themselves.
also note that UNIQUE_ID is not the primary key of this table another column is and that column is populated by the writing processes hilo sequence generator in hibernate ORM so there's no guarantee with that value that the next written value is higher because that value is "selected" from the sequence from the writing processes prior to the commit;