Создание индексов связанных с ограничением целостности

Oracle обеспечивает выполнение ограничения целостности UNIQUE или PRIMARY KEY для таблицы, создавая уникальный индекс для уникального или первичного ключа. Этот индекс создается автоматически, когда включается ограничение целостности. Когда выполняется CREATE TABLE или ALTER TABLE, для создания индекса не надо предпринимать никаких действий, но при желании можно указать предложение USING INDEX, чтобы контролировать его создание.

Чтобы включить ограничение целостности UNIQUE или PRIMARY KEY, создавая, таким образом, связанный с ним индекс, владелец таблицы должен иметь квоту табличного пространства, где будет храниться этот индекс, или системную привилегию UNLIMITED TABLESPACE. Индекс, связанный с ограничением целостности всегда получает имя этого ограничения, если вы не укажете иное.

Параметры хранения для индексов связанных с ограничением целостности UNIQUE или PRIMARY KEY, можно устанавливать с помощью предложения USING INDEX.

http://www.all-oracle.ru/content/view/?part=1&id=102

25. Последовательности (инструкция CREATE/DROP SEQUENCE)

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

Без генератора последовательностей порядковые номера можно создавать лишь программным способом. Новое значение первичного ключа можно было бы получать выбором последнего программно вычисленного значения и наращиванием его. Этот метод требует блокировки во время выполнения транзакции и заставляет одновременно работающих пользователей ожидать очередного значения первичного ключа; такое ожидание известно как СЕРИАЛИЗАЦИЯ (буквально – "выстраивание в очередь"). Если есть в пользовательских приложениях такие программные конструкции, то их нужно заменять на обращения к последовательностям. Последовательности устраняют сериализацию и улучшают конкурентные способности приложений.

Рассмотрим, как создавать, изменять и удалять последовательности с помощью команд SQL.

Общий синтаксис создания:

CREATE SEQUENCE [schema].sequence_name

[INCREMENT BY increment_num]

[START WITH start_num]

[MAXVALUE maximum_num | NOMAXVALUE]

[MINVALUE minimum_num | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE cache_num | NOCACHE]

[ORDER | NOORDER];

где,

sequence_name – имя последовательности;

increment_num – шаг последовательности, по умолчанию, это 1. Абсолютное значение этого параметра должно быть меньше, чем разница между конечным и начальным значениями;

start_num – целочисленное значение с которого начинается отсчет, по умолчанию это 1;

maximum_num – максимальное значение последовательности; значение maximum_num должно быть больше или равно значению start_num, и больше, чем значение minimum_num;

NOMAXVALUE – устанавливает максимальное значение равным 1027 для возрастающей последовательности, или –1 для убывающей. NOMAXVALUE используется по умолчанию;

minimum_num - минимальное значение последовательности; minimum_num должно быть меньше либо равно start_num, и меньше, чем maximum_num;

NOMINVALUE – определяет минимальное значение равное 1 для возрастающей последовательности и -1026 для убывающей. NOMINVALUE используется по умолчанию;

CYCLE – подразумевает, что последовательность начинает генерировать значения по кругу, при достижении максимального или минимального значения. При обращении к последовательности, когда она достигла максимального значения, следующее сгенерированное значение будет минимальным значением последовательности. В ситуации с убывающей последовательностью, при достижении минимального значения, следующее сгенерированное будет максимальным.

NOCYCLE – указывает прекратить генерацию значений при достижении максимума или минимума последовательности. NOCYCLE используется по умолчанию.

cache_num – количество значений сохраняемых в памяти. По умолчанию это 20. Минимальное количество кэшированных значений – 2, максимальное значение высчитывается по формуле:

CEIL(maximum_num -minimum_num)/ABS(increment_num);

NOCACHE – отключает кэширование. Это не позволит базе данных выделить некоторое количество значений заблаговременно, что позволит избежать пробелов в последовательности, но увеличит затраты системных ресурсов. Пробелы могут возникнуть при остановке базы данных, когда кэшированные значения теряются. Если опции CACHE и NOCACHE не указываются, то по умолчанию кэшируется 20 значений.

ORDER – обеспечивает генерацию значений в порядке запросов. Как правило, ORDER используется в среде Real Application Clusters. NOORDER – не дает таких гарантий. По умолчанию используется NOORDER.

Например, следующая команда создает последовательность, которую можно использовать для генерации номеров сотрудников для столбца sno таблицы staff:

CREATE SEQUENCE sno_sequence

INCREMENT BY 1

START WITH 1

MAXVALUE 99999

MINVALUE 1

NOCYCLE

CACHE 10;

Чтобы изменить последовательность (за исключением начального номера) используется команда SQL ALTER SEQUENCE. Например, следующее предложение изменяет последовательность:

ALTER SEQUENCE sno_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE

CACHE 20;

Чтобы изменить начальную точку последовательности, надо удалить эту последовательность и заново создать ее и все необходимые привилегии.

Однажды определенная, последовательность может быть сделана доступной многим пользователям, которые могут обращаться к этой последовательности и вызывать ее приращения, не прибегая к ожиданию. ORACLE не ждет завершения транзакции, выполнившей приращение последовательности, для того чтобы осуществить очередное приращение этой последовательности.

Обращение к последовательности осуществляется в предложениях SQL через псевдостолбцы NEXTVAL и CURRVAL; каждый новый номер данной последовательности генерируется обращением к ее псевдостолбцу NEXTVAL, тогда как текущий номер последовательности можно извлекать неоднократно путем обращения к ее псевдостолбцу CURRVAL.

NEXTVAL и CURRVAL не являются зарезервированными или ключевыми словами; их можно использовать как имена псевдостолбцов в предложениях SQL, таких как SELECT, INSERT или UPDATE.

Чтобы сгенерировать и возвратить очередной номер данной последовательности, обратитесь к seq_name.NEXTVAL, где seq_name – имя последовательности.

Самым простым примером может быть выполнение такого запроса:

SELECT SEQ.NEXTVAL FROM DUAL;

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

INSERT INTO staff (bno, address, tel_no)

VALUES (sno_sequence.NEXTVAL, ‘Пушкина 12’, ‘313-23-23’);

или во фразе SET предложения UPDATE, например:

UPDATE staffs

SET bno = sno_sequence.NEXTVAL

WHERE bno=10112;

или в самом внешнем списке SELECT запроса или подзапроса, например:

SELECT sno_sequence.NEXTVAL FROM dual;

По определению, первое обращение к sno_sequence.NEXTVAL возвратит значение 1. Каждое последующее предложение, обращающееся к sno_sequence.NEXTVAL, возвратит очередной номер данной последовательности (2, 3, 4 и т.д.). Псевдостолбец NEXTVAL может генерировать столько новых номеров последовательности, сколько потребуется. Однако на одно предложение генерируется лишь один новый номер; иными словами, если в данном предложении SQL псевдостолбец NEXTVAL встречается несколько раз, то лишь для первого обращения будет возвращен новый номер последовательности, а все остальные обращения в этом предложении возвратят тот же самый номер. После того как очередной номер последовательности сгенерирован, этот номер доступен лишь сессии, сгенерировавшей его. Независимо от подтверждения или отката транзакций, все пользователи, обращающиеся к order_seq.NEXTVAL, получают уникальные значения. Поэтому, если несколько пользователей одновременно обращаются к одной и той же последовательности, каждый из них может получать номера этой последовательности с промежутками, потому что номера генерируются также другими пользователями.

Чтобы обратиться к текущему значению номера последовательности, которое уже было сгенерировано для вашей сессии, используйте обозначение seq_name.CURRVAL, где seq_name – имя последовательности. Псевдостолбец CURRVAL может использоваться лишь в том случае, если в текущей сессии уже было выдано обращение к seq_name.NEXTVAL для данной последовательности (не обязательно в текущей транзакции). CURRVAL можно использовать сколько угодно раз, в том числе несколько раз в одном и том же предложении. Очередной номер последовательности не будет сгенерирован, пока не будет выполнено очередное обращение к NEXTVAL.

NEXTVAL и CURRVAL могут использоваться во фразе VALUES предложения INSERT, в списке SELECT предложения SELECT, во фразе SET предложения UPDATE.

NEXTVAL и CURRVAL не могут использоваться в следующих местах: в подзапросе; в запросе, определяющем представление или моментальный снимок; в предложении SELECT с оператором DISTINCT; в предложении SELECT с фразой GROUP BY или ORDER BY; в предложении SELECT, скомбинированном с другим предложением SELECT одним из операторов множеств UNION, INTERSECT или MINUS; во фразе WHERE предложения SELECT; в выражении DEFAULT для столбца в предложении CREATE TABLE или ALTER TABLE; в условии ограничения CHECK.

Если последовательность больше не нужна, вы можете удалить ее с помощью команды SQL DROP SEQUENCE. Например, следующее предложение удаляет последовательность order_seq:

DROP SEQUENCE order_seq;

При удалении последовательности ее определение удаляется из словаря данных. Все синонимы для последовательности остаются, но возвращают ошибку при обращении к ним.