Специальные аспекты работы с БД. Процедура индексирования.

Основным преимуществом использования индексов является значительное ускорение процесса выборки и извлечения данных

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

Индекс выполняет роль оглавления таблицы. В некоторых системах, например PARADOX, индексы хранятся в индексных файлах, хранимых отдельно от обычных файлов.

Индексный файл является файлом особого типа, в котором каждая запись состоит из 2-х значений:

1. данные,

2. указатель номера записи.

Как уже было отмечено, основное преимущество использования индексов- ускорение процесса выборки данных

Если индексирование организовано на основе ключевого поля, то индекс называют первичным, иначе вторичным.

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

Индексы можно использовать двумя способами

1. для последовательного доступа к индексированному файлу.

2. Индексы могут использоваться для прямого доступа к отдельным записям файла на основе заданного значения индексирования.

К достоинствам использования индексов относится ускорение процесса извлечения данных за счёт уменьшения числа дисковых операций ввода вывода.

К недостаткам - сам индекс занимает место на устройстве хранения информации, следовательно, при создании таблиц проектировщик должен решать - индексировать или нет.

Синтаксис создания индексов Create index <имя индекса>on <имя табл>,(<имя столбца>,<имя столбца>,)

 

 

USP
SN	PNAME	SNAME	OCENKA
	Физика	Иванов
	Химия	Петров
	Физика	Сидоров
	Физика	Цветков
	Математика	Казанцев

 

PN

Математика

Физика

Физика

Физика

Химия

 

Например

 

Предположим нужно выполнить запрос на поиск студентов, сдавших физику. Для этого упорядочим специальный файл по алфавиту. Для поиска возможны две стратегии:

1. из таблицы USP выбрать строки, у которых PN= ▒ФИЗИКА▓

2. использовать индексный файл PN, согласно указателям извлечь все соответствующие записи из файла USP.

Если доля студентов, сдавших физику невелика по отношению к общему количеству студентов, то не надо делать всего перебора таблицы. Поиск будет прекращен после извлечения следующего предмета - химия. Даже если потребуется просмотреть файл предметов полностью на это потребуется минимум операций ввода √ вывода, поскольку размер файла PN меньше чем размер файла USP.

Здесь файл предметов называют индексным файлом. Файл USP индексирован по отношению к файлу предмета. Индексный файл является хранимым файлом особого типа, в котором каждая запись состоит как минимум из двух значений 1- данные; 2 - указатель номер записи.

При этом данные необходимы для индексированного поля из файла USP, а указатели - для связи с соответственной записью индексированного файла USP. Если индексирование организованно на основе ключевого поля, например, поля SN - то индекс называют первичным, если на основе PNAME - вторичным. Основным недостатком индексирования является замедление процесса обновления данных, т.к. при каждом добавлении новой записи в файл USP потребуется добавить новый индекс в индексный файл PN.

Индексы можно использовать двумя способами:

1. для последовательного доступа к индексированному файлу. Например, индекс PN определяет доступ к записям файла USP согласно алфавитному перечню предметов

2. индексы могут использоваться для прямого доступа к отдельным записям USP, на основе заданного значения индексного поля.

Хранимый файл может иметь несколько индексов, например, индекс PN и индекс ▒оценка▓. Индексы PN и ▒оценка▓ могут как совместно так и раздельно использоваться для наиболее эффективного доступа к данным USP.

Например

При запросе студентов, сдавших физику на 5, согласно индексу PN будут найдены записи - 3412, 3414, 3415, согласно индексу ▒оценка▓ будут найдены - 3413, 3414. затем производится сравнение на совпадение индексов PN и ▒оценка▓ и запись 3413 рассматриваться не будет.

 

оценка	PN
	математика физика физика физика химия

 

Такой способ также ускоряет поиск, комбинированный индекс можно использовать и по раздельности.

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

Для создания индекса

Create index UNIQUE <имя индекса > on < имя таблицы>

Create index PNIND on USP (PN);

Однако индекс PNIND не будет уникальным. Для создания уникальных (не содержащих повторное значение индексов) используют ключевое слово UNIQUE в команде Create index.

ТРИГГЕРЫ

 

Триггер БД - хранимый блок PL/SQL, который связан с таблицей. Oracle автоматически исполняет триггер когда указанное предложение PL/SQL выдается по соответствующей таблице. Прежде чем может быть создан триггер, пользователь должен выполнить скрипт файл DBMSSTDX.SQL его местоположение может варьироваться в зависимости от ОС.

Синтаксис Создание триггера

CREATE [ORREPLACE] TRIGGER имя триггера

BEFORE/ALTER

[{DELETE|INSERT|UPDATE}] ON имя_табл

[REFERENCING {OLD/NEW}]

FOR EACH ROW

[WHEN]

блок PL/SQL (begin √end)

Разделы тела триггера:

OR REPLASE - пересоздаёт триггер, если он уже существует;

BEFORE - указывает что ORACLE возбуждает триггер перед исполнением предложение триггера;

ALTER - возбуждает триггер после исполнения предложение триггера;

REFERENCING - специфицирует корреляционные имена;

FOR EACH ROW- триггер строк, если нет этой строки, тогда это триггер предложений;

WHEN - ограничение триггера содержит условие SQL которое должно быть удовлетворенно, чтобы ORACLE возбудил триггер;

Триггер БД - хранимая процедура, связанная с таблицей. ORACLE автоматически возбуждает и исполняет триггер, когда выдаётся предложение триггера, т.е. предложение SQL которое специфицировано или описано в триггере.

Триггер используется для следующих целей:

- Для автоматической генерации вычисляемых значений столбцов

- Для реализации сложных проверок защиты и ограничений

- Для реализации регистраций

Триггеры - являются именованными блоками PL\SQL с разделами:

1. объявления

2. выполняемый раздел

3. раздел исключительной ситуации

В отличии от процедуры,которую надо вызывать явным образом, триггер выполняется не явно когда происходит событие, запускающее этот триггер. Акт выполнения триггера называется его активизацией. запускается триггер операциями DML выполняемой над БД. Oracle автоматически исполняет триггер когда указанное предложение SQL выдвигается по соответственной таблице.

Для каждой таблицы можно определить 3 типа триггеров:

1. триггеры ввода

2. триггеры добавления

3. триггеры удаления

Триггеры можно использовать для:

1. отслеживания модификаций данных

2. журнализации (регистрация событий)

3. реализация комплексных организационных правил

4. автоматическое вычисление столбцов

5. осуществление сложных процедур защиты данных и т.д.

Ключевые слова и параметры

 

or replace - пересоздают триггер если от уже существует, т.е. можно изменить определение триггера без удаления и создания его заново

before - Oracle возбуждает триггер перед исполнением предложения триггера

after - Oracleвозбуждает триггер после исполнением предложения триггера

delete - Oracleвозбуждает триггер каждый раз когда delete удаляет строку из таблицы

update - указывает что Oracleвозбуждает триггер каждый раз когда выполняется update

of - изменяет значение в одном из столбцов указанных в фразе of.

Если of опущено то изменяется в любом столбце таблицы.

On - определяет имя таблицы по которой создается триггер

referensinq- определяет корреляционные имена, можно использовать корреляционные имена в блоке PL/ SQL в фразе when для того, чтобы обращаться конкретно к старому или новому значению текущей стоки.

for each row - указывает, что триггер представляет собой триггер строк,Oracle возбуждает триггер строк 1 раз для одной строки, затрагиваемой предложением триггера и удовлетворяющей необязательным ограничением триггера, которое определенно в фразе when.

When - ограничение триггера и содержит условие SQL, которое должно быть удовлетворено, что бы Oracle возбудил триггер.

 

Компоненты триггера

 

1. предложение триггера, определение предложения триггера указывает какие команды SQLбудут заставлять Oracle возбуждать этот триггер. INSERT, DELETE,UPDATE должны описать хотя бы одну из этих команд

2. ограничения триггера - when, которое должно быть удовлетворено, что бы Oracle возбудил триггер.

3.действие триггера - его описывает блок PL\SQL, который Oracle исполняет при возбуждении триггера. Каждый раз когда выдается предложение триггера Oracle вычислить условия ограничения триггера, если оно удовлетворенно, то Oracle возбуждает триггер использует действия триггера.

 

Типы триггеров.

 

Можно создать различные типы триггеров. Тип триггера определяет:

1. когда Oracle возбуждает триггер по отношению к исполнению предложения триггера

2. сколько раз Oracle возбуждает триггер для данного предложения

3 триггера определяются следующими служебными словами:

before - перед исполнением

after- после исполнения

for each row для каждой строки

		for each row
before	триггер предложения before: Oracle возбуждает его один раз перед использованием предложения триггера	триггер предложения before: Oracle возбуждает его перед модификацией каждой строки
after	триггер предложения after: Oracle возбуждает его один раз после использованием предложения триггера	триггер предложения after: Oracle возбуждает его после модификацией каждой строки

 

 

Можно создать всего до 12 триггеров по одному из четырех типов триггеров для каждой из команд (INSERT,DELETE,UPDATE)