Geek Answers Handbook

How to quickly select data from Oracle

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 added the game more than N times a day.

I actually choose the number of games that were played today 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 really like this: is there a better way to do this?

Thanks in advance with.

+1
source share
4 answers

Date conversions are a little pointless and do not allow the use of any index in this column; you can simplify this bit with dt_game > trunc(sysdate).

+6
source

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

, 9 , 10..

, 9 , , . 11 . , .

+4

, , . , 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),
);

max_games_per_day , 5 . dt_game_notime , . , , , 5 .

+2

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

+1
source

More articles:


All Articles