Oracle - Saltos aleatorios en las secuencias

Buenos dias,

Tengo una secuencia en Oracle creada de la siguiente manera:

CREATE SEQUENCE EMBARQUES.ID_PUERTO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

Y el trigger que llama a la secuencia:

CREATE OR REPLACE TRIGGER "EMBARQUES".TGR_AUT_PUERTOS
before insert on PUERTOS for each row
begin
select ID_PUERTO.NEXTVAL
into :new.ID_PUERTO
from Dual;
end;

Normalmente la secuencia se incrementa de uno en uno correctamente, pero a veces se producen saltos en la numeración y no sé muy bien por qué.

¿alguien me puede ayudar?

Muchas gracias

Es el comportamiento esperado.

Las secuencias te garantizan que generan un número único, pero no que es correlativo, ni siquiera te garantiza que en ese trigger una fila que tenga id_puerto mayor que otra se haya insertado posteriormente. En términos matemáticos: la secuencia no es una función monótona creciente con el tiempo.

El parámetro "CACHE 20" quiere decir que cuando en una sesión (una conexión a oracle) se pide un nextval para la secuencia, se reservan 20, el siguiente que pide la misma sesión se coge de esa reserva, pero si otra sesión necesita otro número, coge los siguientes 20.

A ver si me explico con un ejemplo (pongo la hora):

- 10:00 Un cliente se conecta a oracle, lo llamamo conexión A. Pide un número de secuencia y devuelve 1, pero el cliente de oracle ha reservado del 1 al 20.
- 10:05 Un segundo cliente se conecta a oracle, conexión B. Pide un número de secuencia y devuelve 21, el cliente de oracle de B ha reservado del 21 al 40.
- 10:10 El cliente con la conexión A pide otro número de secuencia, devuelve 2.
- 10:15 El cliente B pide un número de secuencia: devuelve 22
- El cliente A pide varios números, le devuelve 3, 4, ... 20
- 10:30 El cliente A pide otro número, le devuelve 41 y el cliente de oracle de la conexión A reserva del 41 al 60.
- 10:40 Los dos clientes se desconectan. Los números 23-40 y 42-60 se han perdido y nunca se darán a nadie.

Hay varias cosas:

- La secuencia de números dada es: 1, 21, 2, 22, 3, 4, 5, ..., 20, 41. Fíjate que el 21 se ha dado ANTES que el 2. NO PUEDES CONFIAR EN LA SECUENCIA PARA ORDENAR TEMPORALMENTE LAS FILAS.
- Han quedado dos agujeros en la secuencia: 23-40 y 42-60.

Espero que te haya aclarado las cosas

Oops! pequeño error:

Quien cachea la secuencia no es la conexión sino la instancia, así que lo dicho antes es cierto en entornos RAC (cluster) si los clientes están conectados a distinta máquina del cluster (de dónde habré sacado yo lo de las conexiones...) en fin.

Si quieres garantizar que la secuencia está ordenada en el tiempo, tienes que poner ORDER en lugar de noorder.

Los saltos se producen en realidad si haces un shutdown abort (por ejemplo, si apagas el sistema operativo sin hacer un shutdown antes de la base de datos).

Muchas gracias por la explicacion... yo tengo mis triggers con 20 cache y la opcion order, pero aun asi da brincos, que debo hacer para que el consecutivo sea consecutivo.... Gracias