Индексация в субд

Понятие индекса

Основная проблема в СУБД – это поиск нужных данных за минимальное время.

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

Сортировка данных в таблице невозможно, т.к. критериев поиска моет быть несколько.

Поэтому для таблицы с данными создаются специальные таблицы для каждого критерия поиска, которые называются ИНДЕКСЫ.

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

МЕТОДЫ ОРГАНИЗАЦИИ ИНДЕКСА

Первичный ключ

1. Плотным индексом(некластерный индекс) Индексно прямой файл

2. Неплотным индексом(кластерный индекс) Индексно последовательный файл

3. Б-деревья

Вторичный ключ

1. Инвертируемые списки

Кластерный индекс

Алгоритм удаления записи:

Начало => поиск индексного блока => удаление записи в индексном блоке => запись индексного блока => чтение блока основного файла => пометить запись на удаление в блоке => запись блока основного файла => конец

Некластерный индекс

Алгоритм добавления данных

Начало = поиск индексного блока = чтение блока основной области = добавление записи в блок основной области = переполнение – (да) перестроение индекса - (нет) запись блока основной области = конец.

Алгоритм удаления записи

Начало = поиск индексного блока = чтение блока основной памяти = удаление записи в блоке основной области = запись блока основной области = конец.

Индекс Б-дерево

Со второго уровня используется неплотный индекс

ОПЕРАТОР СОЗДАНИЯ ИНДЕКСА.

СУБД всегда создает индекс для первичного ключа таблицы.

Для создания индексов для других полей используется оператор SQL.

CREATE [UNIQUE] INDEX имя_индекса

ON имя_таблицы (имя_столбци [ASC|DESC][….])

Пример. Создать индекс для таблицы ЗАКАЗ по вторичному ключу

CREATE INDEX Заказ_FK_инд

ON Заказ (MFR, КодТов)

В каждой СУБД оператор создания индексов содержит дополнительные предложения специфические для каждой СУБД.

ОПЕРАТОР СОЗДАНИЯ ИНДЕКСА

Оператор для создания индекса в transact – SQL

CREATE [UNIDUE] создание уникального индекса

[CLASTEED|NOCLASTERED] создание неплотного или плотного индекса

INDEX имя_индекса

ON {имя_таблицы|имя_представления}

(имя_столбца[ASC|DESC][….]) по возрастанию или убыванию

WITH

[<параметры>][….n]]]

[ON имя_файла_группы] расположение индекса в файлах ОС БД

<параметры> - это резервирование на каждой странице индекса

свободного пространства

{PAD_INDEX|FILLFACTOR = %запол. | степень заполнения свободного пространства.

IGNORE_DUP_KEY|DROP_EXISTING|

STATIDTICS_NORECOMPUTE|SORT_IN_TEMPDB

}

7. Представления. Создание и использование представлений. Индексированные представления.

Представления

Создание и использование представлений. Индексированные представления.

Представление (view)

• Представление или вид – это хранение определения оператора SELECT, задающего строки и столбцы, которые нужно выбрать впоследствии в одной или нескольких таблицах.

• Фактически является сохраненными запросами.

• Использование представлений обусловлено следующими причинами:

1. Для уменьшения видимой сложности БД при распределенной обработке.

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

3. Для использования дополнительных индексов в базе данных с целью увеличения быстродействия.

• Вид можно создать либо при помощи утилиты Enterprise Manager или при помощи команд.

CREATE VIWE <имя_представления>

AS <SELECT_выражение>

[WITH CHECK OPTION]

WITH ENCRYPTION – зашифровка программного кода представления.

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

CREATE VIEW q1 (a, b, c)

a,b,c – новые имена полей в представлении

AS SELECT

IFAM, TIMA, TOTCH FROM TEACHERS

СВЕДЕНИЯ О ПРЕДСТАВЛЕНИИ МОЖНО ПОЛУЧИТЬ ИСПОЛЬЗУЯ:

1 – EXEC sp_helptext <имя_представления>

2 – Системную таблицу syscomments

SELEKCT sc.text

FROM syscomments sc

ON sc.id = so.id JOIN sysobject so

Where so.name = <’имя представления’>

ПЕРЕД ИСПОЬЗОВАНИЕМ WITH ENCRYPTION РЕКОМЕНДУЕТСЯ ОХРАНЯТЬ ИСХОДНЫЙ КОД, ДЛЯ СОХРАНЕНИЕЯ ВОЗМОЖНОСТИ ЕГО РЕДАКТИРОВАНИЯ.

На применение оператора SELECT при создании представления в SQL накладываются ограничения:

• Нельзя определить вид на основе временной таблицы или переменной типа TABLE;

• Нельзя использовать в SELECT директиву INTO, так как она направляет строки в другую таблицу, а не на монитор.

SELECT Shipper/* , Link.Addres

INTO NEW Shippers

FROM Shippers JOIN Link ON

Shippers.ID = Link.ID

• В определение вида нельзя включить опцию ORDER BY, кроме как с ключевым словом ТОР в предложении SELECT.

ОПЦИЯ WITH CHECK OPTION – ВЫПОЛНЯЕТ ОГРАНИЧЕНИЕ НА ВСТАВКУ ДАННЫХ В ПРЕДСТАВЛЕНИИ. ВСТАВЛЯЕМЫЕ ИЛИ ОБНОВЛЯЕМЫЕ СТРОКИ ДОЛЖНЫ УДОВЛЕТВОРЯТЬ КРИТЕРИЯМ ПАРАМЕТРА WHERE, ИСПОЛЬЗУЕМОГО В ОПЕРАТОРЕ SELECT, НА КОТОРОМ ОСНОВАНО ПРЕДСТАВЛЕНИЕ.

USE Northwind

GO

--содержит только грузоотправителей штата Oregon

Create viev q2

AS

SELECT Shipper_ID, CompanyName, Phone

FROM Shippers

WHERE Phone Lice ‘(503)%’

WITH CHECK OPTION

GO

UPDATE q2

SET Phone = ‘[phone]’

WHERE ShipperID = 1

• В утилите ЕМ существует ошибка, касающаяся редактировании и вставки данных через представление

• При открытии представления через ЕМ добавления и обновления информации осуществляет непосредственно в таблице, на которой основано данное представление.

• Операция WITH CHECK OPTION игнорируется.

• Команда ALTER VIEW позволяющее изменить существующие представление с сохранением прав доступа пользователей к нему.

• При использовании ALTER VIEW, если не воспользоваться операцией WITH ENCRYPTION повторно для зашифрованного ранее представления, то измененное представление зашифровано не будет.

DROP VIEW <имя_представления> - удаление представлений

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

• Простые виды основаны на одной таблице, сложные – на нескольких.

• К представлениям применимы операторы INSERT, UPDATE, DELETE. Модифицированное представление основывается только на одной таблице.

• Если представление содержит только объединение таблиц, то применение названных операторов невозможно ( необходимо воспользоваться триггерами типа INSTEAD OF)

Модифицируемое представление основывается только на одной таблице:

• Не содержит полей, основанных на агрегатных функциях.

• Не содержит GROUP BY или HAVING в своем определении.

• Не содержит DISTINCT.

• Желательно, не использует в своем определении подзапросы.

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

Индексированные представления

• Опция SCHEMABINDING – осуществляет привязку элементов, на которых основано представление (таблицы или другие представления) к самому представлению. Благодаря чему никто не сможет вносить изменения непосредственно в столбцы объекта, используемые представлением (при помощи оператора ALTER), или удалять объекты, на которых базируется представление до тех пор, пока не будет удалено связанное схемой представление.

• При использовании опции SCHERMABINDING имена базовых таблиц и представлений в операторе SELECT необходимо использовать из двух частей – owner.object.

8. Понятие сценария и пакета. T-SQL. Управляющие операторы T-SQL.

9. Хранимые процедуры. Создание, преимущества и недостатки использования. Компиляция и перекомпиляция хранимых процедур. Объявления параметров хранимых процедур. Значения, возвращаемые хранимыми процедурами (выходные параметры, Return). Рекурсия.