Тема: Использование индексов и ключей

Лекция 4

Индексы представляют создания указателя на нужные данные. Он выполняет в базе данных ту же роль, что и предметный (алфавитный) указатель в справочнике. Индекс в БД – это список важных значений со ссылками на страницы таблиц БД, на которых содержится нужная информация.

Индексы – это еще один объект БД для создания которого требуется дополнительная память. Вся информация, находящаяся в таблицах и индексах, сохраняется в виде страниц. Преимущество индексов заключается в том, что индексы сокращают количество операций ввода-вывода, которые необходимы для доступа к некоторому фрагменту данных и выборки нужного значения.

Для того, чтобы индекс правильно работал важно выбрать правильный столбец для индекса. Один из столбцов, указанных в операторе SELECT, должен быть первым столбцом индекса, иначе индекс не будет использоваться. Для создания индекса можно также использовать столбцы таблицы, которые являются внешними ключами таблицы, а также те столбцы, которые используются в директивах ORDER BY и GROUP BY.

Нельзя создавать индекс на основе столбца для которого определен тип данных BIT, TEXT, IMAGE.

Максимальный размер индекса равен 900 байт, поэтому не разрешается использовать столбцы типа CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY и VARBINARY.

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

Индексы можно создавать в Enterprises Manager или в Query Analyzer.

1) Action → All Tasks → Manage Indexes. Щелкнуть на кнопку NEW, для вызова диалогового окна CREATE New Index, в котором вводится имя нового индекса и выбирается столбец для него.

2) Создание индекса с помощью команды

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

имя_таблицы (столбец 1, [столбец 2, …])

CREATE INDEX I1 ON STUDENTS (SFAM)

CREATE INDEX I2 ON STUDENTS (SFAM, SIMA)

Для использования указанного индекса применяется команда SELECT:

SELECT перечень_столбцов_таблицы FROM

имя таблицы (INDEX = имя_индекса)

SELECT * FROM STUDENTS(INDEX = I1) – выводит записи упорядоченные по фамилии.

Вместо имени можно использовать номер индекса. Если n = 0, то выводится сама таблица, если n = 1, то выводится кластерный индекс, если он есть в таблице. Все остальные значения определяют порядковые номера индексов таблицы.

При создании индекса в операторе CREATE INDEX можно использовать следующие опции:

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

CREATE UNIQUE INDEX I3 ON STUDENTS (SNUM)

2) CLUSTERED – создает кластерный индекс, который определяет такой порядок, который соответствует физическому расположению данных в таблице.

CREATE UNIQUE CLUSTERED INDEX I4

ON TEACHERS (TNUM).

3) NONCLUSTERED, если не установлено, то принимается по умолчанию.

4) IGNORE_DUP_KEY – позволяет иметь повторяющиеся записи в таблице, для которой определен уникальный индекс.

Для отображения информации об индексах используются следующие процедуры:

1. sp_helpindex выдает информацию об индексах для указанной таблицы

sp_helpindex имя_таблицы

2. sp_statistics – возвращает суммарные сведения об индексах.

sp_statistics имя_таблицы

Удалить индекс можно с использованием Enterprise Manager:

1) Action → All Tasks → Manage Indexes → выбрать индекс и нажать кнопку Delete.

2) удаление с использованием команды

DROP INDEX имя_таблицы.имя_индекса

DROP INDEX STUDENTS.I1

С помощью одного оператора можно удалить несколько индексов, перечислив их через запятую.

Определение ключей

Основное понятие, которое используется при контроле целостности информации является ключ. Различают первичные и внешние ключи.

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

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

В столбце для первичного ключа не разрешается использовать повторяющиеся и NULL значения.

Создавать ключи можно несколькими способами:

1) через Enterprise Manager:

меню Action → Design Table → выбрать поле → убрать флажок из колонки Allow Nulls → нажать кнопку с изображением ключа.

2) с использованием команды CREATE TABLE …

/* при создании имя ключа не указывается, задается самим сервером */

CREATE TABLE table_a

(column_a INT PRIMARY KEY)

/*явно укажем имя ключа при создании таблицы */

CREATE TABLE table_b

(column_b INT CONSTRAINT pk_column_b PRIMARY KEY)

имя ключа

/* создадим внешний ключ, ссылающийся на таблицу TABLE_A */

CREATE TABLE table_c

(column_c INT FOREIGN KEY(COLUMN_C)

REFERENCES table_a (column_a))

/* создадим первичный ключ из нескольких столбцов */

CREATE TABLE table_d

(column_d1 INT CONSTRAINT pk_d_columns

PRIMARY KEY (column_d1, column_d2), column_d2 INT).

/* создадим внешний ключ, ссылающийся на первичный ключ, состоящий из нескольких столбцов */

CREATE TABLE table_e

(column_e1 INT FOREIGN KEY (column_e1, column_e2)

REFERENCES table_d (column_d1, column_d2), column_e2 INT).

3. С использованием команды ALTER TABLE …

/* добавим первичный ключ без указания его имени */

ALTER TABLE table_a ADD PRIMARY KEY (column_a)

/* добавим первичный ключ с явным указанием его имени */

ALTER TABLE table_b ADD CONSTRAINT pk_column_b PRIMARY KEY (column_b)

/* создадим внешний ключ, ссылающийся на таблицу table_a */

ALTER TABLE table_c ADD FOREIGN KEY (column_c) REFERENCES table_a (column_a).

В качестве первичного ключа лучше определять поля числового типа, например, порядковый номер.

Отображение информации о ключах

Информация о ключах отображается с помощью следующих системных процедур:

1) sp_helpconstraint имя таблицы

2) sp_help – выводит общую информацию о таблицах БД, часть этой информации относится и к ключам.

sp_help имя_таблицы

3) sp_pkeys – выводит информацию о первичных ключах таблицы

sp_pkeys имя_таблицы

4) sp_fkeys – выводит информацию о внешних ключах таблицы

sp_fkeys имя_таблицы

Удаление ключей

1) через Enterprise Manager через меню Action → Design Table → нажать кнопку с изображением ключа.

2) с использованием команды ALTER TABLE … DROP CONSTRAINT

ALTER TABLE table_name DROP

CONSTRAINT constraint_name, где constraint_name – имя первичного или внешнего ключа.

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

Определение условий проверки

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

CREATE TABLE STUDENTS

(SNUM INT NOT NULL UNIQUE,

SFAM CHAR(20) NOT NULL,

.

. не нулевые уникальные значения

.

)

Если в поле оценка могут быть введены только оценки 1,2,3,4,5, то это можно записать командой

CREATE TABLE USP

(UNUM INT,

OCENKA INT CHECK IN (1,2,3,4,5),

UDATE.DATE,

SNUM INT NOT NULL,

PNUM INT NOT NULL,

UNIQUE (SNUM,PNUM))

__________________________________________________________________

Расставить ключи в таблицах:

1) STUDENTS → SNUM;

2) USP → UNUM;

3) PREDMET → PNUM;

4) TEACHERS → TNUM.

Дата добавления: 2014-01-20