The table contained won prizes looked basically like this:
SQL> create table winnersThat 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:
3 prize_id number primary key,
4 user_id number
- 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).
SQL> create table winners_cnt_tMake sure your slots count is a prime number. Then you can hide it behind the view to make things transparent:
3 slot_id number primary key,
4 cnt number
5 ) organization index;
SQL> insert into winners_cnt_t
2 select level-1, 0
3 from dual
4 connect by level <= 11;
11 rows inserted
create view winners_cnt asAfter that, all you have to do is maintain number of winners the following way...
select sum(cnt) cnt
update winners_cnt_t...which will distribute updates evenly among all rows in the table. You can vary number of slots to match your degree of concurrency.
where slot_id=mod(p_prize_id, 11);