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.
Edit
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?
commit
"1000 times in 1 second" for very long. It is aspeed of light
thing. I'd love to see a system that proves me wrong.