Create SEQUENCE
Последовательность SEQUENCE это объект базы данных, предназначенный для генерации целых чисел в соответствии с правилами, установленными при его создании. Генерируемые числа могут быть как положительные, так и отрицательные. Как правило, SEQUENCE используют для автоматической генерации значений первичных ключей. Последовательность является объектом базы данных, и генерируемое ею значения можно использовать для различных таблиц.
Синтаксис CREATE SEQUENCE
В общем виде синтаксис создания последовательности SEQUENCE для СУБД Oracle можно представить в следующем виде :
Несмотря на однозначное назначение SEQUENCE в различных СУБД имеются определенные различия, которые и будут рассмотрены в данной статье.
Тип генерируемого SEQUENCE значения
В Oracle для последовательности установлено максимальное значение равное 10 27 , минимальное значение соответственно -10 26 .
В СУБД PostgreSQL при генерации значения последовательностью используется тип bigint, определяемое 8-байтным числом в диапазоне от -9223372036854775808 до 9223372036854775807. В некоторых старых версиях поддерживается значение в диапазоне от -2147483648 до +2147483647.
В MS SQL тип генерируемого значения можно определить при помощи оператора [ built_in_integer_type | user-defined_integer_type]. Если тип данных не указан, то по умолчанию используется тип bigint. Синтаксис выражения CREATE SEQUENCE для СУБД MS SQL :
SEQUENCE СУБД MS SQL может быть определена с определенным типом. Допускаются следующие типы :
- tinyint — диапазон от 0 до 255;
- smallint — диапазон от -32 768 до 32 767;
- int — диапазон от -2 147 483 648 до 2 147 483 647.
- bigint — диапазон от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807
- decimal и numeric с масштабом 0.
- Любой определяемый пользователем тип данных (псевдоним типа), основанный на одном из допустимых типов.
Для SEQUENCE СУБД Apache Derby, аналогично MS SQL, может быть определен тип. Допускаются типы smallint, int, bigint. Синтаксис генератора последовательности SEQUENCE СУБД Apache Derby :
Атрибуты SEQUENCE
SCHEMA
SCHEMA определяет схему, в которой создается последовательность. Если SCHEMA опущена, то :
- Oracle создает последовательность в схеме пользователя.
- MSSQL и PostgreSQL создают последовательность в схеме, к которой подключено приложение. Для MS SQL Можно использовать SQL оператор "use" для подключения к определенной схеме.
SEQUENCE_NAME
SEQUENCE_NAME определяет имя создаваемой последовательности.
START WITH
START WITH start_num — это первое значение, возвращаемое объектом последовательности. Значение должно быть не больше максимального и не меньше минимального значения объекта последовательности. По умолчанию начальным значением для нового объекта последовательности служит минимальное значение для объекта возрастающей последовательности и максимальное — для объекта убывающей.
INCREMENT BY
INCREMENT BY increment_num — приращение генерируемого значения при каждом обращении к последовательности. По умолчанию значение равно 1, если не указано явно. Для возрастающих последовательностей приращение положительное, для убывающих — отрицательное. Приращение не может быть равно 0. Для PostgreSQL можно использовать только INCREMENT.
MAXVALUE maximum_num
MAXVALUE — максимальное значение maximum_num, создаваемое последовательностью. Если оно не указано, то применяется значение по умолчанию NOMAXVALUE.
MINVALUE minimum_num
MINVALUE — минимальное значение minimum_num, создаваемое последовательностью. Если оно не указано, то применяется значение по умолчанию NOMINVALUE.
NOMAXVALUE
NOMAXVALUE в Oracle определяет максимальное значение равное 10 27 , если последовательность возрастает, или -1, если последовательность убывает. По умолчанию принимается NOMAXVALUE.
В СУБД PostgreSQL при включении данного параметры в скрипт необходимо использовать следующий синтаксис : NO MAXVALUE. Значение по умолчанию равно 2 63 -1 или -1 для возрастающей или убывающей последовательности соответственно.
NOMINVALUE
NOMINVALUE в Oracle определяет минимальное значение равное 1, если последовательность возрастает, или -10 26 , если последовательность убывает.
В СУБД PostgreSQL при включении данного параметры в скрипт необходимо использовать следующий синтаксис : NO MINVALUE. Значение по умолчанию равно -2 63 -1 или 1 для убывающей или возрастающей последовательности соответственно.
CYCLE
Применение в скрипте CYCLE позволяет последовательности повторно использовать созданные значения при достижении MAXVALUE или MINVALUE. Т.е. последовательность будет повторно гененировать значения с начальной позиции (со START’a). По умолчанию используется значение NOCYCLE. Указывать CYCLE вместе с NOMAXVALUE или NOMINVALUE нельзя.
NOCYCLE
NOCYCLE указывает, что последовательность не сможет генерировать значения после достижения максимума или минимума.
CACHE cache_num
Оператор CACHE в скрипте позволяет создавать заранее и поддерживать в памяти заданное количество значений последовательности для быстрого доступа.
В СУБД PostgreSQL минимальное значение равно 1 и соответствует значению NOCACHE.
В СУБД Oracle минимальное значение равно 2.
ORDER
Данный оператор используется только в СУБД Oracle. Он гарантирует, что номера последовательности генерируются в порядке запросов. Если упорядочение нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию NOORDER, который не гарантирует, что номера последовательности генерируются в порядке запросов
Что такое сиквенс в базе данных
Use the CREATE SEQUENCE statement to create a sequence , which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.
Pseudocolumns for more information on using the CURRVAL and NEXTVAL
«How to Use Sequence Values» for information on using sequences
ALTER SEQUENCE or DROP SEQUENCE for information on modifying or dropping a sequence
To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege.
To create a sequence in another user’s schema, you must have the CREATE ANY SEQUENCE system privilege.
Генератор Hibernate Identity, Sequence и Table (Sequence)
В моем предыдущем посте я говорил о различных стратегиях определения базы данных. Этот пост будет сравнивать наиболее распространенные стратегии суррогатного первичного ключа:
- ИДЕНТИЧНОСТЬ
- ПОСЛЕДОВАТЕЛЬНОСТЬ
- СТОЛ (ПОСЛЕДОВАТЕЛЬНОСТЬ)
ИДЕНТИЧНОСТЬ
Тип IDENTITY (включенный в стандарт SQL: 2003 ) поддерживается:
- SQL Server
- MySQL (AUTO_INCREMENT)
- DB2
- HSQLDB
Генератор IDENTITY позволяет автоматически увеличивать столбец integer / bigint по требованию. Процесс приращения происходит вне текущей выполняемой транзакции, поэтому откат может в конечном итоге отбросить уже присвоенные значения (могут возникнуть пропуски значений).
Процесс приращения очень эффективен, поскольку он использует механизм облегченной внутренней блокировки базы данных, в отличие от более мощных транзакционных блокировок курса.
Единственным недостатком является то, что мы не можем знать вновь назначенное значение до выполнения инструкции INSERT. Это ограничение препятствует реализации стратегии очистки транзакций с обратной записью, принятой в Hibernate. По этой причине Hibernates отключает пакетную поддержку JDBC для объектов, использующих генератор IDENTITY.
В следующих примерах мы включим пакетную обработку JDBC Session Factory:
Давайте определим сущность, используя стратегию генерации IDENTITY:
Сохраняются 5 сущностей:
Выполняет один запрос за другим (пакетирование JDBC не задействовано):
Помимо отключения пакетной обработки JDBC, стратегия генератора IDENTITY не работает с моделью наследования « Таблица для каждого конкретного класса» , поскольку может быть несколько объектов подкласса, имеющих один и тот же идентификатор, и запрос базового класса приведет к получению объектов с одним и тем же идентификатором (даже если принадлежат к разным типам).
ПОСЛЕДОВАТЕЛЬНОСТЬ
Генератор SEQUENCE (определенный в стандарте SQL: 2003 ) поддерживается:
- оракул
- SQL Server
- PostgreSQL
- DB2
- HSQLDB
SEQUENCE — это объект базы данных, который генерирует инкрементные целые числа при каждом последующем запросе. SEQUENCES намного более гибки, чем столбцы IDENTIFIER, потому что:
- SEQUENCE не содержит таблиц, и одну и ту же последовательность можно назначить нескольким столбцам или таблицам
- ПОСЛЕДОВАТЕЛЬНОСТЬ может предварительно распределять значения для улучшения производительности
- ПОСЛЕДОВАТЕЛЬНОСТЬ может определять пошаговый шаг, что позволяет нам воспользоваться «объединенным» алгоритмом Хило
- ПОСЛЕДОВАТЕЛЬНОСТЬ не ограничивает пакетирование JDBC в Hibernate
- ПОСЛЕДОВАТЕЛЬНОСТЬ не ограничивает модели наследования Hibernate
Давайте определим сущность, используя стратегию генерации SEQUENCE:
Я использовал генератор «sequence», потому что не хотел, чтобы Hibernate выбрал SequenceHiLoGenerator или SequenceStyleGenerator от нашего имени.
Добавление 5 сущностей:
Создайте следующие запросы:
В этой таблице вставки группируются, но мы знаем, что перед вставкой сущностей есть 5 последовательных вызовов. Это можно оптимизировать с помощью алгоритма HILO .
СТОЛ (ПОСЛЕДОВАТЕЛЬНОСТЬ)
Существует еще одна независимая от базы данных альтернатива генерации последовательностей. Одна или несколько таблиц могут использоваться для хранения счетчика последовательности идентификаторов. Но это означает производительность записи и торговли для переносимости базы данных.
Хотя IDENTITY и SEQUENCES не требуют транзакций, используются ACID мандата таблицы базы данных для синхронизации нескольких одновременных запросов на генерацию идентификатора
Это стало возможным благодаря использованию блокировки на уровне строк, которая обходится дороже, чем генераторы IDENTITY или SEQUENCE.
Последовательность должна быть рассчитана в отдельной транзакции базы данных, и для этого требуется механизм IsolationDelegate , который поддерживает как локальные (JDBC), так и глобальные (JTA) транзакции.