Maintaining summaries in a highly concurrent fashion

I was involved in designing a highly-concurrent OLTP system last year (more than 300K users during peak hours) which were allowing users to play online and win some prizes. As part of the prize winning logic we had to maintain count of prizes won in order to be able to display it in real-time.

The table contained won prizes looked basically like this:

SQL> create table winners
  2  (
  3   prize_id number primary key,
  4   user_id  number
  5  );
 
Table created

That is -- we were storing prize_id among with the user_id who won it. There were a couple of millions prizes available for winning. You can accomplish won prize counting in a number of ways:

• Count it each time.
  Do this only if you partner up with the same folks who sell you hardware and Oracle CPU licenses.
  
  
• Create on-commit materialized view with count.
  Though much better than previous KIWI'ish approach, it brings a bit of overhead for maintaining a materialized view log plus, if you don't take special cares, may blocks users during commit. Nevertheless, this is usually a valid approach, especially if you want to leverage features like query rewrite. We didn't really need it and, to make things more interesting, someone was winning a prize every so milliseconds. Do simple things (insert into winners, mview log, update summaries table, delete from mview log) a lot of times and wander how quickly it starts to add up.
  
  
• Maintain summaries yourself.
  This is relatively easy to do, especially when application does nothing but calls a set of PL/SQL APIs (which means you can do whatever you want to archive the required output).

However, doing a simple update winners_cnt set cnt=cnt+1 is going to serialize all the winners so we need an easy way to spread stuff out. The simplest, yet very efficient, way to archive this is to do something like this:

SQL> create table winners_cnt_t
  2  (
  3   slot_id number  primary key,
  4   cnt  number
  5  ) organization index;
 
Table created

SQL> insert into winners_cnt_t
  2   select level-1, 0
  3    from dual
  4    connect by level <= 11;
 
11 rows inserted
 
SQL> commit;
 
Commit complete

Make sure your slots count is a prime number. Then you can hide it behind the view to make things transparent:

create view winners_cnt as
 select sum(cnt) cnt
  from winners_cnt_t;

After that, all you have to do is maintain number of winners the following way...

update winners_cnt_t
 set cnt=cnt+1
 where slot_id=mod(p_prize_id, 11);

...which will distribute updates evenly among all rows in the table. You can vary number of slots to match your degree of concurrency.