How to fastly select data from Oracle

Question

Hello, i have the following Oracle table:

GAMES    
    id_game int
    id_user int
    dt_game DATE

Before creating a new record i must check that the same user has not inserted a game more that N times a day.

I'm actually selecting the number of games played today in this way:

select count(1) as num from GAMES 
where id_user=ID_USER and
to_char(dt_game,'DD/MM/YYYY')=to_char(sysdate,'DD/MM/YYYY')

I don't like it too much: is there a better way to do it?

Thanks in advance c.

Answer 1

The date conversions are a bit pointless and prevent any index on that column being used; you could simplify that bit with dt_game > trunc(sysdate).

Answer 2

There is a problem (related to concurrency) with checking the table and counting the rows before inserting a new row.

Let us say the user currently has played 9 games and you want to limit the rows to 10..

if you open two different sessions, then the both of them will see the currently uncommitted value of 9 games and hence both the games are allowed, By the time the transaction is complete, the user would have played 11 games..which is against the rule that you are trying to enforce.

Answer 3

In Oracle you can create a function based index on id_user, to_char(dt_game,'DD/MM/YYYY') to improve lookup performance.

Answer 4

You can use a unique constraint to ensure that a fixed maximum number of records may be inserted for a particular day. For example, if N=5:

CREATE TABLE GAMES (
  id_game         NUMBER NOT NULL,
  id_user         NUMBER NOT NULL,
  dt_game         DATE   NOT NULL,
  user_game_count NUMBER NOT NULL,
  CONSTRAINT max_games_per_day
  CHECK (user_game_count BETWEEN 1 AND 5),
  CONSTRAINT dt_game_notime
  CHECK (dt_game = TRUNC(dt_game)),
  CONSTRAINT games_unique
  UNIQUE (id_user, dt_game, user_game_count),
);

The max_games_per_day constraint means that it can only take one of 5 different values. The dt_game_notime constraint means that we won't get various time values. The unique constraint, finally, ensures that for any particular date and user, they can only insert up to 5 rows.