I have a small table which can be explained as follows:
create table mytab ( primary_key_column number, a number, b number, c number, d number, e number, f number );
It will have very few rows (not more than 256 records) but they will be updated at a high rate (for example one row could have up to 1000 updates per second).
What recommended settings should I use for the table to be able to handle this load and avoid update contention? Or other suggestions for doing this?
I will be using Oracle 12c or newer.
Concerning the high nnumber of updates, as was asked in the comments. With pctfree and initrans I can perhaps get each row in a separate block, but there will still be a lot of updates for a given key. So any suggestions on how to handle that? I see only three ways forward on this.
1) don't update but write the increments to a separate table and then have a job summing them and do the update regularly. This will get me out of the update contention, but I will be doing a lot of inserts instead
2) change the primary key in some way, like
create table mytab( primary_key_column number, sub_key number, a number, ...
and then let updates choose a random "sub_key" for the given primary_key. Sub_keys could be like the numbers 0-99 to give each row only 1% of the updates.
Given that all values a-f are zero at start, the total values can then be gotten by summing over the primary key. It's ugly, but it could work.
- Might the In-memory feature solve the problem?
Any comments on this?