Создание физической модели хранилища данных

Создание индексов хранилища данных

Когда вы определяете PRIMARY KEY при создании таблицы, многие реляционные СУБД, в том числе СУБД семейства MS SQL Server, требуют обязательного создания уникального индекса первичного ключа. Индексы, так же как и таблицы, являются объектами реляционной <L (но не реляционной модели данных). Логически индексы представляют собой таблицу, в которой каждому значению индексируемой колонки ставится в соответствие некоторая информация, связанная с ее месторасположением на физическом носителе. Индексы предназначены для организации быстрого доступа к строкам таблицы и обеспечения контроля целостности данных (механизм индексов будет блокировать БД от повторного ввода строк в таблицу с одинаковыми значениями индексируемых атрибутов).

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

Синтаксис команды CREATE INDEX приведен ниже.

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}
<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]
<conjunct> ::=
    <disjunct> | <comparison>
<disjunct> ::=
        column_name IN (constant ,…)
<comparison> ::=
        column_name <comparison_op> constant
 <comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

Значения аргументов команды следующие.

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

Компонент Database Engine не позволяет создать уникальный индекс по столбцам, уже содержащим повторяющиеся значения, даже если параметру IGNORE_DUP_KEY присвоено значение ON. При попытке написания такого индекса компонент Database Engine выдает сообщение об ошибке. Прежде чем создавать уникальный индекс по такому столбцу или столбцам, необходимо удалить все повторяющиеся значения. Столбцы, используемые в уникальном индексе, должны иметь свойство NOT NULL, т.к. при создании индекса значения NULL рассматриваются как повторяющиеся.

• CLUSTERED. Создает индекс, в котором логический порядок значений ключа определяет физический порядок соответствующих строк в таблице. На нижнем (конечном) уровне такого индекса хранятся действительные строки данных таблицы. Для таблицы или представления в каждый момент времени может существовать только один кластеризованный индекс.

Если аргумент CLUSTERED не указан, создается некластеризованный индекс.

• NONCLUSTERED. Создание индекса, задающего логическое упорядочение для таблицы. Логический порядок строк в некластеризованном индексе не влияет на их физический порядок.
• index_name. Имя индекса. Имена индексов должны быть уникальными в пределах таблицы или представления, но не обязательно должны быть уникальными в пределах базы данных.
• Column. Колонка или колонки, на которых основан индекс. Имена одной или нескольких колонок для создания комбинированного индекса. Колонки, которые должны быть включены в составной индекс, указываются в скобках за аргументом table_or_view_name в порядке сортировки.

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

• [ ASC | DESC ]. Определяет сортировку значений заданного столбца индекса: по возрастанию или по убыванию. Значение по умолчанию — ASC.
• INCLUDE ( column [ ,... n ] ). Указывает неключевые столбцы, добавляемые на конечный уровень некластеризованного индекса. Некластеризованный индекс может быть уникальным или неуникальным.

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

• WHERE <filter_predicate>. Создает отфильтрованный индекс путем указания строк для включения в индекс. Отфильтрованный индекс должен быть некластеризованным индексом для таблицы. Также создается статистика фильтрации для строк данных отфильтрованного индекса.
• ON partition_scheme_name ( column_name ). Задает схему секционирования, которая определяет файловые группы, соответствующие секциям секционированного индекса. Схема секционирования должна быть уже создана в базе данных с помощью инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. Аргумент column_name задает столбец, по которому будет секционирован индекс. Этот столбец должен соответствовать типу данных, длине и точности аргумента функции секционирования, которую использует схема partition_scheme_name. Аргумент column_name может указывать на столбцы, не входящие в определение индекса. Можно указать любой столбец базовой таблицы, за исключением случая секционирования индекса UNIQUE, когда столбец column_name должен быть выбран из используемых в уникальном ключе. Это ограничение дает возможность компоненту Database Engine проверять уникальность значений ключа только в одной секции.
• ON filegroup_name. Создает заданный индекс в указанной файловой группе. Если местоположение не указано и таблица или представление не секционированы, индекс использует ту же файловую группу, что и базовая таблица или базовое представление. Файловая группа должна существовать.
• ON "default". Создает заданный индекс в файловой группе, используемой по умолчанию.
• [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" }]. Указывает размещение данных FILESTREAM для таблицы при создании кластеризованного индекса. Предложение FILESTREAM_ON позволяет перемещать данные FILESTREAM в другую файловую группу FILESTREAM или схему секционирования.
• <object>::= Полное или неполное имя индексируемого объекта.
• database_name. Имя базы данных.
• schema_name. Имя схемы, к которой принадлежит таблица или представление.
• table_or_view_name. Имя индексируемой таблицы или представления.
• <relational_index_option>::= Указывает параметры, которые должны использоваться при создании индекса.
• PAD_INDEX = { ON | OFF }. Определяет заполнение индекса. Значение по умолчанию — OFF.
• FILLFACTOR = fillfactor. Указывает, на сколько процентов должен компонент Database Engine заполнить страницы конечного уровня при создании или перестройке индекса. fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию — 0. Если fillfactor равен 100 или 0, компонент Database Engine создает индексы с полностью заполненными страницами конечного уровня.
• SORT_IN_TEMPDB = { ON | OFF }. Указывает, сохранять ли временные результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.
• IGNORE_DUP_KEY = { ON | OFF }. Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа.
• STATISTICS_NORECOMPUTE = { ON | OFF }. Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.
• DROP_EXISTING = { ON | OFF }. Указывает, что названный существующий кластеризованный или некластеризованный индекс удаляется и перестраивается. Значение по умолчанию — OFF.
• ONLINE = { ON | OFF }. Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
• ALLOW_ROW_LOCKS = { ON | OFF }. Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.
• ALLOW_PAGE_LOCKS = { ON | OFF }. Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.
• MAXDOP = max_degree_of_parallelism. Переопределяет параметр конфигурации максимальной степени параллелизма на время операций с индексами. MAXDOP можно применять для ограничения числа процессоров, используемых в одновременном выполнении планов. Максимальное число процессоров — 64.
• DATA_COMPRESSION. Задает режим сжатия данных для указанного индекса, номера секции или диапазона секций. Доступные параметры: NONE — индекс или заданные секции не сжимаются; ROW — для индекса или заданных секций производится сжатие строк; PAGE — для индекса или заданных секций производится сжатие страниц.
• ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ). Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если индекс не секционирован, аргумент ON PARTITIONS создаст ошибку. Если не указано предложение ON PARTITIONS, то параметр DATA_COMPRESSION применяется ко всем секциям секционированного индекса.

Предложение CREATE INDEX определяет имя индекса ; предложение ON определяет имя таблицы и колонок, для которой и по которым строится индекс ; ключевое слово UNIQUE указывает, что индексируемые значения колонок должны быть уникальными для таблицы, т. е. исключается дублирование значений в индексируемой колонке. Таблица должна быть уже создана и содержать определения индексируемых столбцов. Спецификация UNIQUE опциональна, и вы можете также создавать и неуникальные индексы.

Для диалекта SQL СУБД семейства MS SQL Server индексы на колонки с ограничением первичного ключа и UNIQUE создаются автоматически. Поэтому проектировщику ХД нужно создать индексы для тех колонок, для которых он считает наличие индекса целесообразным.

Колонками – кандидатами для создания дополнительных индексов являются в нашем случае, хотя это можно и оспорить, "Наименование товара" (Name) таблицы измерения "Товар" (Product) и "Фамилия продавца" (Empl_LName) таблицы измерения "Продавцы" (Employee). Создадим эти индексы, как показано ниже.

CREATE UNIQUE CLUSTERED INDEX Idx1 ON Product(Name);
go
CREATE UNIQUE CLUSTERED INDEX Idx2 ON Employee (Empl_LName);
go

После выполнения вышеперечисленных действий задачу создания физической модели в первом приближении можно считать законченной. Теперь можно запустить разработанный скрипт для созданной БД и считать, что ХД создано.

Резюме

В этой лекции мы рассмотрели принципы разработки физической модели ХД. Создание физической модели ХД состоит в моделировании и создании объектов для хранения данных в БД конкретной СУБД. Эта задача сводится к моделированию и созданию таблиц и объектов в БД, в которых будет храниться информация о сущностях предметной области ХД. Решая эту задачу, проектировщик отображает отношения логической модели данных ХД в таблицы и индексы БД. Для выполнения этой задачи используется подмножество команд SQL – язык определения данных DDL (Data Definition Language).

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

Сначала создаются таблицы БД. Напомним, что таблицы в реляционных СУБД состоят из одной или более колонок или полей. Колонки представляют собой поименованные ячейки в записи, которые содержат значения. Колонки определяются посредством спецификации, которая вводит формат колонки и ее характеристики, задаваемые с помощью ограничений.

Общий алгоритм построения физической модели ХД включает в себя следующие действия.

• Разработка физической модели ХД:
  • определение базовых таблиц БД;
  • определение колонок в таблицах ;
  • определение типов данных для колонок;
  • назначение первичных ключей таблицам ;
  • задание ограничений NOT NULL на значения колонок;
  • создание связей между таблицами.
• Разработка скрипта создания ХД:
  • формирование команд CREATE TABLE для таблиц ХД;
  • определение ограничений на колонки таблиц ХД;
  • формирование дополнительных индексов командой CREATE INDEX.