Для автоматической генерации номеров строк в таблице можно использовать такой
объект базы данных, как последовательность. Последовательность – это
объект базы данных, который создается одним пользователем, но может совместно
использоваться несколькими пользователями. Основное назначение
последовательности – автоматическая генерация уникальных чисел, которые обычно
применяются для получения значений первичного ключа.
Создание последовательности
Синтаксис:
CREATE SEQUENCE последовательность
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
где
Последовательность
Имя последовательности.
INCREMENT BY n
Интервал между двумя последовательными номерами; n является целым числом. Если это
предложение опущено, приращение при генерации чисел равно 1.
START WITH n
Первое генерируемое число в последовательности. Если это
предложение опущено, последовательность начинается с 1.
MAXVALUE n
Максимальное значение, которое может генерировать
последовательность.
NOMAXVALUE
Максимальное значение по умолчанию, равное
.
MINVALUE n
Минимальное значение последовательности.
NOMINVALUE
Задает минимальное значение, равное 1.
CYCLE | NOCYCLE
Продолжается ли циклическая генерация чисел после достижения
максимального или минимального значения.
CACHE n | NOCACHE
Количество чисел, которые Oracle7
распределяет предварительно и хранит в памяти. По умолчанию сервер
хранит в кеш-памяти 20 значений.
Подчеркнутые параметры используются по умолчанию.
Пример. Создать последовательность S_DEPT_ID для
первичного ключа таблицы S_DEPT. Параметр CYCLE использоваться не должен.
Проверка параметров последовательности
Проверить значения параметров последовательности можно в таблице
USER_SEQUENCES словаря данных. Столбец LAST_NUMBER содержит следующее свободное
число.
Изменение последовательности
Изменение шага приращения, максимального и минимального значений, режима
циклической генерации значений и кэширования определяется командой
ALTER SEQUENCE.
Синтаксис:
ALTER SEQUENCE последовательность
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
Примечание. Для изменения параметров необходимо быть владельцем
последовательности или иметь для нее привилегию ALTER. Команда влияет
только на числа, генерируемые после изменения. Чтобы начать генерацию с
другого числа, необходимо удалить последовательность и создать заново.
Генерация значений последовательности
Псевдостолбец NEXTVAL генерирует следующее свободное число в
последовательности. При каждой ссылке на этот столбец он возвращает
уникальное значение – даже для разных пользователей. Псевдостолбец
CURRVAL выдает текущее число в последовательности. Чтобы CURRVAL содержал
значение, необходимо прежде сгенерировать значение последовательности используя
NEXTVAL.
Пример. Включение нового отдела под названием "Finance"
в регионе 2.
Пример. Просмотр текущего значения
последовательности S_DEPT_ID.
Примечания. Запись значений последовательности в
сверхоперативную память (кэш) ускоряет доступ к ним. При использовании
последовательностей возможные пропуски значений при генерации чисел по причинам:
откат транзакции, отказ системы, использование последовательности другим
пользователем или для другой таблицы.
Удаление последовательности
Удаление последовательности из словаря данных производится с помощью команды
DROP SEQUENCE. После удаления последовательности ссылки на нее невозможны.
Представление – это логический образ таблицы, созданный на основе реальной
таблицы или другого представления. Представление не содержит собственных
данных, а скорее является “окном”, через которое можно просматривать или
изменять данные из таблиц. Представление хранится в словаре данных как
команда SELECT.
Создание представлений
Представление создается командой CREATE VIEW с использованием
подзапроса. Синтаксис:
CREATE [OR REPLACE] [FORCE |
NOFORCE] VIEW представление
[(псевдоним[, псевдоним]…)]
AS подзапрос
[WITH CHECK OPTION [CONSTRAINT
ограничение]]
[WITH READ ONLY]
где
FORCE
Создание представления независимо от того, существуют ли базовые
таблицы.
NOFORCE
Создание представления только при условии существования базовых
таблиц (умолчанию).
представление
Имя представления.
псевдоним
Имена выражений, выбранных в запросе для представления.
подзапрос
Полная команда SELECT.
WITH CHECK OPTION
Режим, при котором добавлять или обновлять можно только строки,
доступные в представлении.
ограничение
Имя, присвоенное ограничению CHECK OPTION.
WITH READ ONLY
Запрет применения к данному представлению операций
DML.
Для подзапроса, участвующего при создании представления, действуют все
правила, определенные для подзапроса (см. команду SELECT
с подзапросом). Получить описание представления можно точно также, как и
описание таблицы (команда DESCRIBE среды SQL*Plus или команда
SELECT * FROM имя_представления).
Выборка данных из представления производится посредством команды SELECT со
ссылкой на это представление.
Рекомендуется использовать представление для:
ограничения доступа к базе
данных;
упрощения запросов;
достижения независимости данных
от приложений пользователя;
организации различных способов
показа одних и тех же данных.
Представления делятся на простые и сложные. В следующей
таблице приведены ограничения, связанные с простыми или сложными
представлениями.
Простые представления
Сложные представления
Количество таблиц
Одна
Одна или больше
Содержит функции
Нет
Да
Содержит группы данных
Нет
Да
Операции DML над
представлением
Да
Нет
Пример.Создается представление EMPVU45,
включающее учетный номер, фамилию и должность каждого служащего отдела номер 45.
Изменение представления
Изменить параметры представления можно командой CREATE
OR REPLACE VIEW. При этом будет создано новое представление с
тем же именем.
Пример. Изменение представления EMPVU45 с
добавлением псевдонимов для каждого столбца. Псевдонимы столбцов в команде
указаны в том же порядке, что и столбцы в подзапросе.
Пример. Создание сложного представления с
групповыми функциями для выборки данных из двух таблиц.
Правила выполнения операций DML над
представлением.
Операции DML могут быть выполнены только с простым представлением!
При этом даже для простого представления существуют некоторые ограничения.
Удаление строк невозможно, если представление содержит следующее:
ключевое слово DISTINCT.
Нельзя изменять данные в представлении, если оно содержит:
одно из вышеуказанных условий;
столбцы, описанные как
выражения;
псевдостолбец ROWNUM.
Нельзя добавлять данные в представление, если оно содержит:
одно из вышеуказанных условий;
какие-либо столбцы NOT NULL, не
выбранные представлением.
Параметр WITH READ ONLY запрещает операции DML над этим представлением.
Этот параметр актуален только для простого представления, сложное представление
всегда доступно пользователю только для выборки данных.
Предложение WITH CHECK OPTION позволяет задать
ограничения, накладываемые на представление. Условие для ограничения значений,
изменяемых или добавляемых через представление, содержится в предложении
WHERE подзапроса в определении представления.
При использовании такого представления необходимо следить за тем, чтобы
результаты операций DML оставались в пределах домена представления.
Пример. Попытка изменить номер отдела для
какой-либо строки в представлении закончится неудачей, т.к. при этом нарушится
ограничение CHECK OPTION.
Пример. Создание представления с параметром
READ ONLY. При попытке выполнить операцию DML
над какой-либо строкой представления сервер Oracle7 выдает сообщение об ошибке
ORA-01732.
Пример. Получение имени представления и его
параметров из таблицы словаря данных USER_VIEWS.
Удаление представления
Удалить представление можно с помощью команды DROP VIEW.
Удаление представления не вызывает потери данных.
Объект базы данных, обеспечивающий прямой и быстрый доступ к строкам в
таблице, называется индексом. Индексы используется сервером базы данных
для ускорения выборки строк с помощью указателя. При использовании индексов
уменьшается количество операций обмена с внешней памятью за счет использования
быстрого метода поиска данных. Индексы не зависят от таблицы, для которой
они были созданы.
Индекс можно создать автоматически (при описании ограничений, типа
PRIMARY KEY или UNIQUE) или
вручную (используя команду CREATE INDEX).
Как правило, для представления индексов используется структура B-дерева.
Каждый индекс состоит из значений столбцов и указателей (ROWID), организованных
в виде страниц (ветвей). При выполнении запроса осуществляется поиск по
ветвям дерева до обнаружения листа со значением, содержащим указатель.
Рисунок 13. Структура индекса в виде В-дерева
Рисунок 14. Поиск в В-дереве
Типы индексов:
уникальный – обеспечивает уникальность значений в столбце;
неуникальный – ускоряет запросы;
простой – в индексе использован только один столбец;
составной или сложный – в индексе использовано несколько столбцов.
Создание индекса
Синтаксис:
CREATE INDEX index
ON table (column [, column]…);
где
index
Имя создаваемого индекса.
table
Имя таблицы, на основе которой он создается.
column
Имя столбца (столбцов) для создания индекса.
Пример. Создание индекса для таблицы S_EMP на столбце
LAST_NAME.
В каких случаях оправдано создание индекса:
Столбец часто используется в
предложении WHERE или условии соединения.
Столбец имеет широкий диапазон
значений.
Столбец содержит большое
количество неопределенных значений.
Два или более столбцов часто
используются вместе в предложении WHERE или условии соединения.
Таблица большого размера, и
предполагается, что большинство запросов будут выбирать менее 10-15% строк.
Когда не следует создавать индекс:
Таблица небольшого размера.
Столбцы не очень часто
используются как параметры в условиях при запросе.
Большая часть запросов будет
выбирать более, чем 10-15% строк.
Таблица часто обновляется.
Определение индекса содержится в представлении словаря данных USER_INDEXES.
Представление USER_IND_COLUMNS содержит имя индекса, имя таблицы и имя столбца.
Удаление индекса
Удаление индекса из производится командой DROP INDEX.
Пример. Удаление индекса с именем
s_emp_last_name_idx.
.
