Некоторые особенности работы с последовательностями в Oracle

Циклические последовательности

У последовательностей есть полезная возможность – начинаться заново при достижении определенного значения. Такие последовательности называются циклическими и создаются они с использованием ключевого слова CYCLE. Существует распространенная ошибка использования этой возможности. Рассмотрим следующий пример:

CREATE SEQUENCE S1
START WITH 10
INCREMENT BY 1
MAXVALUE 20
CYCLE
CACHE 15
/

Sequence created.

Я создал последовательность S1, указав в качестве стартового значения число 10, в качестве максимального – 20, а также указав CYCLE — при достижении максимального значения последовательность должна начаться с начала.
Далее, я извлекаю из последовательности значения, пока не достигаю максимального:

select s1.nextval from dual
/

NEXTVAL
----------
20
1 row selected.

Следующее извлечение NEXTVAL из последовательности, в соответствии с моими ожиданиями, опять вернет значение 10, но тут то и кроется подвох:

select s1.nextval from dual
/

NEXTVAL
----------
1
1 row selected.

Вместо 10 мне вернулось значение 1. Почему это произошло? Все дело в том, что при создании последовательности не был указан параметр MINVALUE, который задает минимальное значение последовательности. Параметр START WITH только указывает, с какого значения начнется первый цикл. Поскольку MINVALUE не был указан, его значение было установлено в 1, что является значением по умолчанию. Это легко проверить:

select min_value from user_sequences
/

MIN_VALUE
----------
1
1 row selected.

Если мы хотим, чтобы наша последовательность при повторном цикле началась с 10, то правильной будет такая команда создания:

drop sequence s1
/

Sequence dropped.

CREATE SEQUENCE S1
START WITH 10
INCREMENT BY 1
MINVALUE 10
MAXVALUE 20
CYCLE
CACHE 15
/

Но такая команда не выполнится верно, а вернет ошибку:

Error at line 1
ORA-04013: number to CACHE must be less than one cycle

Причина в том, что величина кэша для значений, которая задается после ключевого слова CACHE, не может превышать длину цикла. Пока минимальное значение было 1, длина цикла составляла 20, что было больше. Когда мы сделали минимальное значение 10, кэш стал больше, что недопустимо. В итоге полностью правильным будет такой вариант:

CREATE SEQUENCE S1
START WITH 10
INCREMENT BY 1
MINVALUE 10
MAXVALUE 20
CYCLE
CACHE 10
/

Sequence created.

Извлечение значения последовательности при восстановлении БД

В предыдущем разделе я уже упомянул о кэше значений последовательности. При первом вызове метода NEXTVAL сервер Oracle закэширует в области SGA некоторое количество последующих значений. Это довольно полезная возможность, т.к. позволяет реже обращаться к словарю данных при извлечении из последовательности серий значений, что влечет за собой увеличение производительности зависимых операций.
Однако, полезно помнить, что при аварийной остановке и последующем восстановлении экземпляра БД, восстановится не то значение последовательности, которое было извлечено последним перед сбоем, а максимальное значение из кэша.
Для иллюстрации создадим последовательность со значением кэша 100 и извлечем текущее значение:

create sequence sq_cache_test
cache 100
/

Sequence created.

select sq_cache_test.nextval from dual
/

NEXTVAL
----------
1
1 row selected.

При извлечении значения NEXTVAL сервер Oracle закэшировал в SGA сто следующих значений последовательности SQ_CACHE_TEST. Далее проэмулируем внезапную остановку и восстановление экземпляра:

conn / as sysdba

Connected.

shutdown abort

ORACLE instance shut down.

startup

ORACLE instance started.

Total System Global Area 230162432 bytes
Fixed Size 1346456 bytes
Variable Size 201327720 bytes
Database Buffers 20971520 bytes
Redo Buffers 6516736 bytes
Database mounted.
Database opened.

Теперь извлечем очередное значение последовательности SQ_CACHE_TEST:

conn example/example

Connected.

select sq_cache_test.nextval from dual
/

NEXTVAL
----------
101
1 row selected.

Как видно, текущее значение последовательности действительно равняется последнему извлеченному до остановки экземпляра + величина кэша.

Извлечение значений последовательности в Oracle 11g

Удобным нововведением в 11 версии Oracle является возможность прямого присвоения значения последовательности переменным в блоках PL/SQL. В 10g и более ранних версиях нам приходилось выполнять такой код:

create sequence sq_test
/

declare
v pls_integer;
begin
-- some actions
select sq_test.nextval
into v
from dual;
-- some actions
end;
/

PL/SQL procedure successfully completed.

Минусом такого подхода является необходимость обработки курсора для запроса из DUAL. В 11g мы можем использовать такой, более естественный синтаксис:

declare
v pls_integer;
begin
-- some actions
v := sq_test.nextval;
-- some actions
end;
/

PL/SQL procedure successfully completed.

Данный механизм является более эффективным, т.к. отсутствует препроцессинг запроса. Однако, стоит отметить, переключение контекста все равно происходит, т.к. последовательность располагается в области SGA.