Создание физической модели базы данных: проектирование производительности

Исключительно индексные таблицы и другие типы индексов на основе B-Tree

Исключительно индексная таблица (index-organized table) может создаваться на основе значений одной или нескольких колонок. Если требования к данным в запросе удовлетворяются на основе информации из связанного с этими данными индекса, то доступ к базовой таблице не осуществляется.

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

Исключительно индексная таблица в СУБД Oracle создается с помощью команды SQL CREATE TABLE, как показано в примере 20.2.

Пример 20.2.

Предположим, что требуется в отдельной таблице БД сохранять и отслеживать проблемы, возникающие по выполнении всех проектов, а так же запоминать, кто обращался и как часто возникали проблемы. Создадим исключительно индексную таблицу "Проект индексированный" (Proj_Index) для решения этой задачи, как показано ниже:

CREATE TABLE Proj_Index
( projno	char(8) NOT NULL,
 t_person	char(32) NOT NULL,
t_frequency	integer,
t_problem	varchar2(512),
   CONSTRAINT pk_ndx PRIMARY KEY( projno, t_person) )
ORGANIZATION INDEX
TABLESPACE ts_ndx1
PCTTHRESHOLD 20
INCLUDING   t_frequency
OVERFLOW TABLESPACE ts__of_ndx1;

Команда CREATE TABLE не отличается ничем от других команд создания таблиц — до тех пор, пока не встретится предложение ORGANIZATION INDEX, которое указывает СУБД на создание исключительно индексной таблицы. Для размещения индекса на диске указывается табличное пространство. Параметр PCTTHRESHOLD говорит, что оставшуюся часть строки нужно сохранять в заданном табличном пространстве — сегменте переполнения, если данная строка превышает размер физической страницы базы данных на указанное число процентов. Параметр INCLUDING определяет имя колонки, с которой строка индексной таблицы делится на две части: индексную и переполнения. Эта колонка может быть частью первичного ключа таблицы или неключевой колонкой. Все неключевые колонки, которые следуют за указанной колонкой, размещаются в сегменте переполнения, который определяется ключевым словом OVERFLOW.

В СУБД семейства MS SQL Server нет предопределенной возможности создавать исключительно индексные таблицы. Однако в ряде практических случаев в СУБД этого семейства можно создавать структуры, аналогичные исключительно индексным таблицам. Для этого может быть использован некластеризованный индекс с включенными колонками (опция INCLUDE команды CREATE INDEX ).

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

Для примера 20.2 команды создания структуры, подобной исключительно индексной таблице по своему назначению, на диалекте SQL MS SQL Server будут иметь вид, как в примере ниже.

Пример. 20.3.

Сначала создаем таблицу "Проект индексированный" (Proj_Index), как на рис 20.3.

CREATE TABLE Proj_Index (
   projno	char(8) NOT NULL,
   t_person	char(32) NOT NULL,
   t_frequency	integer,
   t_problem	varchar(512),
   CONSTRAINT pk_ndx PRIMARY KEY( projno, t_person) )
GO

Затем создаем некластеризованный индекс с ключом "Номер проекта" (projno) и с тремя неключевыми колонками "Фамилия сотрудника" (t_person), "Частота проблемы" (t_frequency) и "Описание проблемы" (t_problem).

CREATE NONCLUSTERED INDEX IX_Proj
    ON Proj_Index (projno)
    INCLUDE (t_person, t_frequency, t_problem);
GO

В семействе СУБД Oracle предусмотрено еще несколько типов индексов, которые позволяют улучшить традиционные для всех СУБД индексы со структурой B-Tree. К таким модификациям, помимо исключительно индексных таблиц, относятся битовые индексы, индексы с обращением ключа, индексы на основе значения функций.

Каждый бит так называемого битового (bitmap) индекса относится к идентификатору строки ROWID (который в Oracle создается и хранится для каждой строки и используется во внутренней организации индексов ) в табличном объекте. Если некоторая строка содержит данное ключевое значение, то в индексе для этого значения сохраняется единица. Такая организация индекса может в некоторых случаях значительно повысить производительность выборки данных, т. к. для извлечения строк с определенными значениями индекса СУБД нужно лишь найти все единицы, отвечающие ключу. Физически такой индекс организован на основе структуры B-Tree, но задача сводится к поиску данной строки за счет одной операции чтения битовой индексной структуры. Этот тип индекса очень эффективен для индексирования колонок с небольшим кардинальным числом — пол, цвет и т.д. Если значений у колонки будет много, то объем ввода-вывода будет возрастать.

Пример 20.4.

Для нашего учебного примера можно построить битовый индекс для таблицы "Служащий" (EMPLOYEE) по колонке "Номер отдела" (DEPNO):

CREATE BITMAP INDEX emp_ndx ON EMPLOYEE (DEPNO);

В СУБД семейства MS SQL Server возможность создания битовых индексов средствами диалекта SQL отсутствует.

В индексе с обращением ключа (reverse-key index) применяется обращение байтов индексируемой колонки числового типа. Этот прием позволяет получать равномерное распределение значений колонок среди блоков-листков индекса со структурой B-Tree, который хорошо подходит для индексирования колонок с последовательной нумерацией или нумерацией с заданным шагом. Заметим, что такие индексы применяются только для возвращения отдельных строк, и с их помощью нельзя выполнить поиск значений в некотором диапазоне. В СУБД Oracle нельзя применить опцию REVERSE к битовым индексам и к исключительно индексным таблицам.

Пример 20.5.

Числовые ключи, содержащие последовательные числа, есть, в частности, в таблице "Служащий" (EMPLOYEE) – "Номер служащего" (EMPNO). Можно создать для этой таблицы дополнительный индекс с обращением ключа для извлечения записи о сотруднике. Заметим, что для этой колонки уже есть индекс первичного ключа.

CREATE INDEX dep_ndx ON EMPLOYEE  (EMPNO) REVERSE;

В процессе эксплуатации администратор базы данных может перестроить этот индекс с помощью команды ALTER INDEX:

ALTER INDEX EMPLOYEE  REBUILD NOREVERSE;

В СУБД семейства MS SQL Server возможность создания индексов с обращением значения ключа средствами диалекта SQL отсутствует.

Если в предложении WHERE применяется функция по индексированной колонке, то обычно СУБД не используют этот индекс при организации доступа к строкам таблицы. Но при создании индекса на основе значения функции (function-based index), которая является той же функцией, что и в предложении WHERE, СУБД как семейства Oracle, так и семейства MS SQL Server использует такой индекс для считывания строк, удовлетворяющих критерию отбора.

В СУБД семейства MS SQL Server вычисляемые колонки могут иметь свойство PERSISTED. Это означает, что компонент Database Engine хранит вычисленные значения в таблице и обновляет их при обновлении любых колонок, от которых зависит вычисляемый столбец. Компонент Database Engine использует эти материализованные значения, когда создает индекс по колонке и когда запрос обращается к индексу.

Для индексации вычисляемой колонки она должна быть детерминированной и точной. Если используется свойство PERSISTED, список типов индексируемых вычисляемых колонок расширяется и включает следующие типы:

Пример 20.6.

В качестве примера использования индексов по вычисляемой колонке приведем пример моделирования хеш-индекса в MS SQL Server.

Предположим, что никакого индекса для колонки "Фамилия" (ENAME) в таблице "Служащий" (EMPLOYEE) создано не было. Тогда, если в предложении WHERE команды SELECT задан предикат поиска по колонке "Фамилия" (ENAME), то СУБД будет сканировать таблицу. Если таблица содержит около 1000000 строк, то операция сканирования будет увеличивать время обработки запроса.

Создадим хеш-индекс для колонки "Фамилия" (ENAME) с помощью функции checksum(), для этого добавим в таблицу вычисляемую колонку, как показано ниже.

alter table EMPLOYEE
add ENameHash as checksum(EName); 
go

Далее создадим индекс по этой колонке, как показано ниже.

create index IDX_ENames on EMPLOYEE (ENameHash);
go

При наличии в БД такого индекса СУБД будет его использовать при обработке запроса описанного в данном примере.

В СУБД семейства MS SQL Server можно создавать и использовать фильтрованные индексы, для создания таких индексов в команде CREATE INDEX предусмотрена возможность использования предложения WHERE.

Применение предложения WHERE <filter_predicate> создает отфильтрованный индекс путем указания строк для включения в индекс. Отфильтрованный индекс должен быть некластеризованным индексом для таблицы. Также создается статистика фильтрации для строк данных отфильтрованного индекса.

Предикат фильтра использует простую логику сравнения и не может ссылаться на вычисляемый столбец, столбец определяемого пользователем типа, столбец пространственного типа данных или столбец типа hierarchyID. Сравнения с помощью литералов NULL с операторами сравнения недопустимы. Вместо этого используются операторы IS NULL и IS NOT NULL.

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

Пример 20.7.

Создадим фильтрованный индекс в таблице "Служащий" (EMPLOYEE) для колонки "Штрафы" (FINE), чтобы быстро получать списки оштрафованных сотрудников.

CREATE NONCLUSTERED INDEX IDX_FINE
    ON EMPLOYEE (EName, FINE)
    WHERE FINE > 0;
GO

В настоящем разделе мы рассмотрели различные типы индексов и примеры их создания. В следующем разделе остановимся подробнее на параметрах проектирования индексов.

О некоторых параметрах проектирования индексов

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

Кардинальностью колонки (cardinality) таблицы называется число дискретных различных значений колонки, которые встречаются в строках таблицы. Например, если в таблице "Служащий" (EMPLOYEE) мы заводим колонку для указания пола – "Пол" (SEX), то кардинальность этой колонки есть 2, так в природе у людей существует только два пола — мужской и женский. Для колонки первичного ключа кардинальность будет равна числу строк в таблице.

Причина, по которой кардинальность колонки важна для проектирования индексов, состоит в том, что кардинальность индексируемой колонки определяет число уникальных входов, которые должны сохраняться в индексе, т.е. число записей в индексе. Так, для индексируемой колонки "Пол" (SEX) будут существовать два уникальных входа, которые будут повторяться много раз в индексе. При предположении равновероятного распределения пола сотрудников на 100000 строк в таблице "Служащий" (EMPLOYEE) каждый вход индекса будет повторяться 50000 раз. СУБД вряд ли будут принимать решение об использовании такого индекса при построении плана запроса.

Определить кардинальность потенциальной колонки индексирования в существующей таблице БД достаточно просто.

SELECT COUNT (DISTINCT колонка) FROM таблица;

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

Способ, с помощью которого СУБД оценивает действие кардинальности, состоит в использовании фактора селективности выборки (selectivity factor). Фактор селективности выборки индекса определяется как величина, обратная кардинальности индексной колонки:

Фактор селективности оценивает потенциальный объем операций ввода-вывода. Чем меньше фактор селективности, тем меньше требуется операций ввода-вывода для получения результирующего множества строк таблицы. СУБД оценивает эту величину, чтобы решить, применять индекс для доступа к строкам таблицы или нет.

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

Хорошими кандидатами для индексирования обычно являются:

Факторы, влияющие на низкую эффективность индексов:

Плохими кандидатами для индексирования обычно являются:

Следует соблюдать следующие общие правила при создании индексов.

К сожалению, часто проектировщики принимают крайне неудачные решения об индексировании. Это приводит к тому, что в ХД появляется слишком много индексов. В результате тратится много времени на поддержку этих индексов, дисковое пространство расходуется неэффективно, СУБД "путается" в выборе подходящего индекса или не использует их вовсе. Поэтому необходимо помнить о двух основных принципах построения индекса:

На рис 20.4 приведены рекомендации по выбору колонок для создания индексов.

Повышение производительности запросов: секционирование

Секционирование

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

Секционирование (partitioning) — это способ физического распределения таблиц и индексов среди двух или более табличных пространств (СУБД Oracle) или в одной или более файловых группах (СУБД MS SQL Server) в зависимости от значений ключевых колонок таблиц с целью повышения производительности операций ввода-вывода. Таким образом, секционирование – это разбиение таблицы на группы, с сохранением для всех групп общего определения структуры. Табличное пространство (СУБД Oracle) или файловая группа (СУБД MS SQL Server) – это физическое месторасположение таблиц БД в файловой структуре операционной системы.

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

В реализации секционирования одним из важных понятий является колонка таблицы, относительно значений которой СУБД будет делать физическое разнесение таблицы по различным файловым группам на жестких дисках. Эти колонка называется ключом секционирования (partition key).

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

Мы отдельно рассмотрим секционирование таблиц для семейств СУБД Oracle и MS SQL Server.

Секционирование таблиц в СУБД семейства Oracle

В СУБД семейства Oracle поддерживается несколько видов секционирования: секционирование по диапазону, хеш-секционирование, составное секционирование, а также различные виды секционирования индексов.

Секционирование по диапазону

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

Секционирование по диапазону базируется на упорядочении строк таблицы в секциях на основе значения колонок ключа секционирования. Концептуально таблица, секционированная по диапазону, устроена, как на рис 20.5.

Для создания секционированных таблиц используется команда SQL CREATE TABLE с предложением PARTITION. В СУБД Oracle ключ секционирования не может иметь тип LONG.

Пример 20.8.

Рассмотрим систему обработки заказов. Предположим, что в ней есть таблица "Продажи" (Sales), в которой сохраняются данные о количестве, времени и цене продаж для каждого покупателя. Можно использовать секционирование по диапазону, а именно по кварталу, для представления этой таблицы в базе данных. Предположим, что мы имеем четыре определенных ранее табличных пространства c именами ts_01, ts_02, ts_03, ts_04, распределенные по четырем дискам, как показано на рисунке ниже.

Фрагмент скрипта определяет таблицу "Продажи" (Sales) с физическим размещением секций, как на рис 20.5:

CREATE TABLE Sales
(
s_customer_id		number(6),
s_amt			number(9,2),
s_date			date)
PARTITION BY RANGE (s_date)
     (PARTITION st_q01 VALUES LESS THAN ('01-apr-2002')
	TABLESPACE ts_01,
     PARTITION st_q02 VALUES LESS THAN ('01-jul-2002')
	TABLESPACE ts_02,
     PARTITION st_q03 VALUES LESS THAN ('01-oct-2002')
	TABLESPACE ts_03,
     PARTITION st_q04 VALUES LESS THAN (MAXVALUE)
	TABLESPACE ts_04
);

Предложение PARTITION BY RANGE (s_date) указывает СУБД Oracle выполнить секционирование таблицы по ключу секционирования s_date. Предложения вида (PARTITION st_q01 VALUES LESS THAN ('01-apr-2002') TABLESPACE ts_01 определяют имя секции st_q01 и ее размещение в соответствующем табличном пространстве ts_01.

Чтобы получить доступ к строкам таблицы, расположенным в определенной секции, и узнать о продажах в третьем квартале, можно использовать команду SELECT, как показано ниже:

SELECT s_customer_id, s_amt FROM Sales PARTITION (st_q03);

Мы видим, что для того, чтобы получить доступ к строкам секции таблицы, нужно указать опцию PARTITION (имя секции) после имени таблицы в предложении FROM.

Администратор базы данных может легко удалять, добавлять, перемещать, расщеплять, усекать и изменять секции с помощью команды ALTER TABLE. Удалить отдельную секцию можно также, удалив соответствующее ей табличное пространство.

Хеш-секционирование

Хеш-секционирование (hash partitioning) означает равномерное распределение строк таблицы по назначенным табличным пространствам в зависимости от значения ключа секционирования, который в данном случае хешируется. Этот вид секционирования удобно применять для строк, у которых распределение значений ключа секционирования неравномерно или плохо группируется. Для принятия решения о создании хеш-секционированной таблицы необходимо достаточно точно представлять размер этой таблицы, поскольку встроенные в СУБД Oracle алгоритмы хеширования используют этот размер для вычисления позиции строки на физической странице базы данных. Определение размера таблицы может привести к большому числу коллизий, т.е. к попаданию строк с различными значениями ключа на одну и ту же страницу, что приводит к поддержке цепочек переполнения и дополнительному вводу-выводу.

Пример 20.9.

Рассмотрим ту же, что и в примере 20.8, таблицу "Продажи" (Sales) и ту же схему (рис 20.5) табличных пространств. Однако используем в качестве ключа секционирования "Идентификатор покупателя" ( s_customer_id ). Отметим, что распределение значений этой колонки может быть очень неравномерно. Фрагмент кода SQL для создания хеш-секционированной таблицы "Продажи" (Sales) можно написать так:

CREATE TABLE Sales
(
s_customer_id		number(6),
s_amt			number(9,2),
s_date			date)
PARTITION BY HASH (s_customer_id)
     (PARTITION q01 TABLESPACE ts_01,
     PARTITION q02 TABLESPACE ts_02,
     PARTITION q03  TABLESPACE ts_03,
     PARTITION q04 TABLESPACE ts_04
);

Предложение PARTITION BY HASH (s_customer_id) указывает СУБД Oracle выполнить секционирование таблицы по ключу секционирования s_customer_id. Предложения вида (PARTITION q01 TABLESPACE ts_01 определяют имя секции st_q01 и ее размещение в соответствующем табличном пространстве ts_01.

Составное секционирование

Составное секционирование (composite partittioning) является комбинацией секционирования по диапазону и хеш-секционирования. Это означает, что таблица сначала распределяется среди табличных пространств на основе диапазона значений ключа секционирования, далее каждая из полученных секций диапазонов делится на подчиненные секции, или подсекции, и затем строки равномерно распределяются среди подчиненных секций по значению хеш-ключа.

Пример 20.10.

Рассмотрим ту же, что и в предыдущем примере, таблицу "Продажи" (Sales) и ту же схему табличных пространств. В качестве ключа секционирования по диапазону используем колонку "Дата продажи" (s_date). В качестве ключа хеш-секционирования — "Идентификатор покупателя" (s_customer_id). Однако теперь каждая секция по диапазону будет разделена на предопределенное число подсекций. Фрагмент кода SQL для создания таблицы "Продажи" (Sales) с составным секционированием можно написать так:

CREATE TABLE Sales
(
s_customer_id		number(6),
s_amt			number(9,2),
s_date			date)
PARTITION BY RANGE (s_date)
SUB PARTITION BY HASH (s_customer_id)
SUB PARTITION 4
STORE IN (ts_01, ts_02, ts_03, ts_04)
     (PARTITION q01 VALUES LESS THAN ('01-apr-2002'),
     PARTITION q02 VALUES LESS THAN ('01-jul-2002'),
     PARTITION q03 VALUES LESS THAN ('01-oct-2002'),
     PARTITION q04 VALUES LESS THAN (MAXVALUE)
);

Секции q01, q02, q03, q04 будут содержать строки с диапазоном дат, которые определены в предложениях типа PARTITION q02 VALUES LESS THAN ('01-jul-2002') и будут распределены в табличных пространствах ts_01, ts_02, ts_03, ts_04. Предложение SUB PARTITION 4 предписывает СУБД Oracle разбиение каждой секции на четыре логические единицы, а предложение SUB PARTITION BY HASH (s_customer_id) распределяет строки заданного диапазона среди этих четырех подчиненных секций.

Секционирование индексов в СУБД семейства Oracle

В СУБД Oracle предусмотрено секционирование индексов (index partitioning), которое означает преднамеренное распределение индексов таблиц по назначенным табличным пространствам в соответствии с ключом секционирования. Секционирование индексов может быть глобальным и локальным.

Локально секционированный индекс имеет такой же ключ секционирования, то же количество табличных пространств и те же правила секционирования, что и отвечающая ему базовая таблица. Глобально секционированный индекс содержит предложение PARTITION BY RANGE, в котором задаются параметры секционирования, отличные от параметров секционирования соответствующей базовой таблицы.

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

Индексы могут быть секционированы и в случае, когда индексируемая таблица не секционируется. В этом случае по умолчанию предполагается, что индекс является глобальным секционированным индексом. В СУБД Oracle не предусмотрена поддержка глобальных непрефиксных секционированных индексов.

В локально секционированном индексе ключевые значения одной секции индекса соответствуют строкам таблицы из одной ее секции.

Пример 14/11.

Создадим локальный секционированный индекс для таблицы "Продажи" (Sales) из примера 20.8. Ключом секционирования этой таблицы является колонка "Дата продажи" (s_date). Фрагмент кода создания индекса приведен ниже:

CREATE INDEX sales_ndx ON Sales (s_date)
LOCAL
(PARTITION st_i_q01 TABLESPACE ts_01,
     PARTITION st_i_q02 TABLESPACE ts_02,
     PARTITION st_i_q03 TABLESPACE ts_03,
     PARTITION st_i_q04  TABLESPACE ts_04
);

Локально секционированный индекс называется равносекционированным (equi-partitioned), если он имеет то же число секций и те же правила секционирования, что и его базовая таблица. Обратите внимание, что в примере при создании индекса не использовалось предложение PARTITION BY RANGE. СУБД Oracle автоматически берет структуру секционирования для индекса из структуры секционирования базовой таблицы Sales. Также можно опустить и предложения типа PARTITION st_i_q02 TABLESPACE ts_02. Если опущено PARTITION, то СУБД Oracle автоматически создаст имена секций. Если опущено TABLESPACE, то СУБД Oracle автоматически разместит секции в тех же табличных пространствах, в которых находятся соответствующие секции базовой таблицы.

Глобально секционированный индекс имеет структуру секций, отличную от структуры секций базовой таблицы данного индекса. В качестве примера создадим глобально секционированный индекс для таблицы "Продажи" (Sales) из примера 20.8.

Пример 20.12.

В качестве ключа секционирования для индекса возьмем колонку "Идентификатор покупателя" (s_customer_id). В фрагменте кода ниже для секций индекса используются другие индексные пространства с именами ts_i_01, ts_i_02, ts_i_03. Число секций индекса не совпадает с числом секций базовой таблицы для этого индекса.

CREATE INDEX sales_ndx ON Sales (s_customer_id)
GLOBAL
PARTITION BY RANGE (s_customer_id)
(PARTITION st_i_q1 VALUES LESS THAN (10000)
TABLESPACE ts_i_01,
     PARTITION st_i_q2 VALUES LESS THAN (20000)
TABLESPACE ts_i_02,
     PARTITION st_i_q3 VALUES LESS THAN (MAXVALUE)
TABLESPACE ts_i_03,
);

Локально секционированный индекс может быть создан по колонке, отличной от ключа секционирования базовой таблицы индекса. В примере 20.13 создается такой непрефиксный индекс для таблицы "Продажи" (Sales) из примера 20.8.

Пример 20.13.

В качестве колонки секционирования для индекса взята колонка "Идентификатор покупателя" (s_customer_id), а для секций индекса выбраны другие табличные пространства ts_i_01, ts_i_02, ts_i_03, ts_i_04, отличающиеся от табличных пространств секций базовой таблицы индекса.

CREATE INDEX sales_ndx_1 ON Sales (s_customer_id)
LOCAL
(PARTITION st_i_q01 TABLESPACE ts_i_01,
     PARTITION st_i_q02 TABLESPACE ts_i_02,
     PARTITION st_i_q03 TABLESPACE ts_i_03,
     PARTITION st_i_q04  TABLESPACE ts_i_04
);

При принятии решения о секционировании индексов следует иметь в виду следующее.

Секционирование представлений

В СУБД Oracle есть возможность секционировать представления. Основная идея секционирования представлений проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования. Тогда можно создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования. Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования.

Секции представления могут быть определены предикатами секционирования, заданными либо при помощи ограничения CHECK, либо с использованием предложения WHERE. Покажем, как могут быть применены оба приема, на примере несколько модифицированной таблицы "Продажи" (Sales), которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года — Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.

Пример 20.14.

Секционирование представлений с помощью ограничения CHECK. С помощью команды ALTER TABLE можно добавить ограничения на колонку "Дата продажи" (s_date) каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам, как к одной, так и ко всем вместе.

ALTER TABLE Q1_Sales ADD CONSTRAINT C0 CHECK (s_date BETWEEN 'jan-1-2002' AND 'mar-31-2002');
ALTER TABLE Q2_Sales ADD CONSTRAINT C1 CHECK (s_date BETWEEN 'apr 1-2002' AND 'jun-30-2002');
ALTER TABLE Q3_Sales ADD CONSTRAINT C2 check (s_date BETWEEN   'jul-1-2002' AND 'sep-30-2002');
ALTER TABLE Q4_Sales ADD CONSTRAINT C3 check (s_date BETWEEN   'oct-1-2002' AND 'dec-31-2002');

CREATE VIEW sales_v AS
  SELECT * FROM Q1_Sales UNION ALL 
  SELECT * FROM Q2_Sales UNION ALL 
  SELECT * FROM Q3_Sales UNION ALL
  SELECT * FROM Q4_Sales;

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

Пример 20.15.

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

CREATE VIEW sales_v AS 
  SELECT * FROM Q1_Sales WHERE s_date BETWEEN  'jan-1-2002' AND 'mar-31-2002' 
UNION ALL 
  SELECT * FROM Q2_Sales WHERE s_date BETWEEN  'apr-1-2002' AND 'jun-30-2002' 
UNION ALL 
  SELECT * FROM Q3_Sales WHERE s_date BETWEEN 'jul-1-2002' AND 'sep-30-2002' 
UNION ALL 
  SELECT * FROM Q4_Sales WHERE s_date BETWEEN 'oct-1-2002' AND 'dec-31-2002';

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

У этого приема есть и достоинство по сравнению с использованием ограничения CHECK. Можно разместить секцию, соответствующую предикату WHERE, на удаленной базе данных. Фрагмент определения преставления приведен ниже.

SELECT * FROM east_sales@icp.ac.ru WHERE LOC = 'EAST'
UNION ALL
SELECT * FROM west_sales@ioc.ac.ru WHERE LOC = 'WEST';

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

Секционирование таблиц в СУБД семейства MS SQL Server

Создание секционированных таблиц

В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.

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

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

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

Функции секционирования

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

Строки данных могут сегментироваться по колонке любого типа, кроме следующих: text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), псевдонимы типов данных и пользовательские типы данных среды CLR. Однако функция секционирования должна распределять каждую строку данных только в одну секцию таблицы ; иными словами, в результате применения функции одна и та же строка не может принадлежать нескольким секциям одновременно.

Чтобы секционировать таблицу, в ней необходимо создать или выбрать колонку секционирования ( ключ секционирования ). Ключ секционирования можно создать в схеме таблицы в момент создания таблицы либо добавить позднее путем модификации таблицы. Столбец может принимать значения NULL, но все строки, содержащие значения NULL, будут по умолчанию помещаться в самую левую секцию таблицы. Этого можно избежать, указав при создании функции секционирования, что значения NULL должны помещаться в самую правую секцию таблицы. Выбор левой или правой секций – важное решение проектирования, проявляющееся при изменении схемы секционирования, добавлении дополнительных секций или удалении существующих.

При создании функции секционирования можно выбрать функции LEFT или RIGHT. Разница между секциями LEFT и RIGHT состоит в размещении данных по секциям. Функция LEFT распределяет данные по принципу от самого низкого значения до самой высокой величины (то есть по возрастанию). Функция RIGHT распределяет данные по принципу от самого высокого значения до самого низкого (то есть по убыванию). Рассмотрим пример.

Пример 20.16.

Возьмем следующие примеры определения функций секционирования с использованием LEFT и RIGHT:

CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT 
FOR VALUES (1,10,100)

CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT 
FOR VALUES (1,10,100)

В первой функции ( Left_Partition ) значения 1, 10 и 100 размещаются соответственно в первой, второй и третьей секциях. Во второй функции ( Right_Partition ) эти значения размещаются во второй, третьей и четвертой секциях.

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

Определить номер секции, в которую попадут те или иные данные, можно с помощью функции $PARTITION, как показано ниже:

SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)

Первая команда SELECT возвращает значение 2, вторая – значение 3.

Схемы секционирования

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

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

Пример 20.17.

Выполним присвоение файловых групп схеме секционирования. Сначала приведем пример размещения всех секций таблицы в одной файловой группе с именем PRIMARY.

CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition 
      ALL TO ([PRIMARY])

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

CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition 
      TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)

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

CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition 
      TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)

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

Пример 20.18.

Рассмотрим в качестве примера схему типа "звезда" с таблицей фактов "Продажи" (SALES), как показано на рис 20.6. Создадим секционированную таблицу "Продажи" (SALES).

Сначала мы должны создать функцию секционирования:

CREATE PARTITION FUNCTION
  MyPartitionFunctionLeft
  (date)
  AS RANGE LEFT
  FOR VALUES ('1/01/2005', '1/01/2007', '1/01/2009)

MyPartitionFunctionLeft — это название функции разделения, datetime — тип данных ключа секционирования, а RANGE LEFT указывает, как делить значения данных, которые связаны с датами FOR VALUES.

Ключ секционирования имеет тип данных .

Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES. Если дата продажи была 23 июня 2006 года, то строка будет храниться в секции 2 (P2).

Теперь создадим схему секционирования. Схема секционирования отображает секции на различные файловые группы (с именами MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4 ) , как показано в следующей команде:

CREATE PARTITION SCHEME
  MyPartitionScheme
  AS MyPartitionFunction
  TO (MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4)

MyPartitionScheme – это имя схемы секционирования, а имя MyPartitionFunction определяет функцию секционирования. Эта команда отображает данные в секции, которые связаны с одной или несколькими файловыми группами. Строки с данными со значениями колонки "Дата продажи" (Date_of_Event date) до 1/01/05 связаны с MyFilegroup1. Строки этой колонки со значениями, большими или равными 1/01/05 и до 1/01/07, назначены MyFilegroup2. Строки со значениями, большими или равными 1/01/07 и до момента 1/01/09, связаны с MyFilegroup3. Все остальные строки со значениями, большими или равными 1/01/09, связаны с MyFilegroup4.

Для каждого набора граничных значений (которые задаются условием FOR VALUES функции секционирования ) количество секций будет равно "Количество граничных значений" + 1 секция. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT, количество секций всегда будет равно "Количество граничных значений" + 1, вплоть до 1000 секций на таблицу.

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

CREATE TABLE SALES
  (Sales_ШВ bigint identity  (1, 1) primary not clustered NOT  NULL,
  Cust_ID              bigint               null,
   Prod_ID              bigint               null,
   Store_ID             bigint               null,
   REG_ID               char(10)             null,
   Time_of_Event        time                 null,
   Quantity             integer              not null,
   Amount               dec(8,2)             not null,
  Date_of_Event date  NOT NULL)
  ON MyPartitionScheme (Date_of_Event)

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

Можно объединять только две смежные секции. Чтобы слить две секции, выполните команду:

ALTER PARTITION FUNCTION
  MyPartitionFunction()
  MERGE RANGE ('1/01/2007')

Здесь секция 1 (P1) объединится с секцией P2. Это означает, что секция P2 будет содержать все строки со значением колонки "Дата продажи" (Date_of_Event) до значения даты 1/01/07. В системной таблице sys.partitions секции будут перенумерованы, начиная с единицы (не с нуля). Секции P1 и P2 станут P1, секция P3 станет P2 и P4 станет P3.

Секционирование индексов в СУБД семейства MS SQL Server

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

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

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

Пример. 20.19.

Создадим секционированный некластеризованный индекс на секционированной таблице "Продажи" (SALES) из предыдущего примера 20.18. Некластеризованный индекс выравнивается с таблицей; в качестве ключа некластеризованного индекса используется ключ секционирования таблицы.

CREATE NONCLUSTERED INDEX cl_multiple_partition ON SALES
(Date_of_Event)

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

CREATE PARTITION FUNCTION Index_Left_Partition (bigint) AS RANGE
LEFT
FOR VALUES (10, 50, 100)

Затем разместим все секции индекса в одной файловой группе с именем PRIMARY, выполним команду

CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition  ALL TO ([PRIMARY])

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

CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_partition(
Cust_ID) ON Index_primary_Left_Scheme (Cust_ID)

В этом некластеризованном индексе в качестве ключа индекса используется колонка "Идентификатор покупателя" (Cust_ID), которая не является ключом секционирования таблицы "Продажи" (SALES).

Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса.

Секционирование представлений в СУБД семейства MS SQL Server

Представление, или виртуальная таблица, которая предоставляет доступ к данным одной или более таблиц в СУБД MS SQL Server, создается командой CREATE VIEW, синтаксис которой приведен ниже.

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

schema_name задает имя схемы, которой принадлежит представление, view_name задает имя представления. Имена представлений должны соответствовать требованиям, предъявляемым к идентификаторам. Указывать имя владельца представления не обязательно.

column задает имя, которое будет иметь колонка в представлении. Имя колонки требуется только в тех случаях, когда колонка формируется на основе арифметического выражения, функции или константы, если две или более колонки могут по иной причине получить одинаковые имена (как правило, в результате соединения) или если колонке представления назначается имя, отличное от имени колонки, из которой она произведена. Назначать колонкам имена можно также в инструкции SELECT. Если column не указан, столбцам представления назначаются такие же имена, которые имеют столбцы в инструкции SELECT.

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

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

CHECK OPTION обеспечивает соответствие всех выполняемых для представления команд модификации данных критериям, заданным при помощи select_statement. Если строка изменяется посредством представления, предложение WITH CHECK OPTION гарантирует, что после фиксации изменений доступ к данным из представления сохранится.

Атрибуты представления ENCRYPTION, SCHEMABINDING, VIEW_METADATA обеспечивают дополнительные возможности управления представлением (см. техническую документацию по T-SQL).

Секционированное представление — это представление, определенное посредством объединения всех ( UNION ALL ) таблиц-элементов. Эти таблицы структурированы одинаковым образом, но хранимы отдельно в форме разных таблиц, либо в одном экземпляре SQL Server, либо в группе автономных экземпляров SQL Server, которые называются федеративными серверами баз данных.

При разработке схемы секционирования должно быть ясно, какие данные относятся к каждой секции. Например, данные таблицы "Покупатели" (Customers) распределены между тремя таблицами-элементами на трех серверах: таблицей "Покупатели 33" (Customers_33) на сервере Server1, таблицей "Покупатели 66" (Customers_66) на сервере Server2 и таблицей "Покупатели 99" (Customers_99) на сервере Server3. Секционированное представление на сервере Server1 определяется, как в примере 20.20.

Пример 20.20.

Приведем определение секционированного представления на сервере Server1. Первая команда SELECT относится к таблице, размещенной на сервере Server1, вторая команда SELECT — к таблице, размещенной на сервере Server2, третья команда SELECT — к таблице, размещенной на сервере Server3.

CREATE VIEW Customers
AS
SELECT * FROM CompanyData.dbo.Customers_33
UNION ALL
SELECT * FROM Server2.CompanyData.dbo.Customers_66
UNION ALL
SELECT * FROM Server3.CompanyData.dbo.Customers_99

Как правило, представление считают секционированным, если оно соответствует следующему формату:

SELECT <select_list1>
FROM T1
UNION ALL
SELECT <select_list2>
FROM T2
UNION ALL
...
SELECT <select_listn>
FROM Tn

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

Повышение производительности запросов: кластеры

Кластеризация таблиц

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

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

Кластеризация (Clustering) — это способ физического размещения рядом, на одной физической странице данных, строк, доступ к которым осуществляется при помощи одинакового значения колонки (ключа) с целью увеличения производительности. Такой ключ называется кластерным ключом. Значением кластерного ключа являются значения одинаковых по смыслу колонок строк кластеризуемых таблиц. Ключ может быть либо хеш-ключом, либо индексным ключом.

Если ключ является хеш-ключом, то физическое размещение определяется функцией преобразования ключа (хеширования) и мы имеем дело с таблицей хеширования, или хеш-кластером.

Если это индексный ключ, то для идентификации страницы данных в кластере используется индекс со структурой B-Tree, в котором строки, имеющие одинаковые значения ключа, размещаются либо в одной странице, либо в смежных страницах индекса. Такой кластер называется индексным кластером. Строки, которые хранятся в индексном кластере, не обязательно должны принадлежать одной таблице.

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

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

В СУБД семейства MS SQL Server кластеры не поддерживаются диалектом SQL. Все примеры, обсуждаемые ниже, ориентированы на использование СУБД семейства Oracle.

Кластеризация таблиц в СУБД семейства Oracle

В СУБД семейства Oracle поддерживаются различные типы кластеризации базовых таблиц. Рассмотрим использование кластеризации таблиц на примерах.

Пример 20.21.

Рассмотрим таблицы "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE), структура которых показана на рис 20.8.

Таблица "Служащий" (EMPLOYEE) описана в примере 20.1 настоящей лекции, а описание таблицы "Отдел" (DEPARTAMENT) приведено в табл. 20.2 ниже.

Обе таблицы некластеризованы и хранятся каждая на своих физических страницах. Предположим, что анализ запросов показывает, что для 80% запросов эти таблицы используются совместно, при этом соединение выполняется по колонке "Номер подразделения" (DEPNO). Можно построить кластер для этих двух таблиц.

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

После кластеризации по колонке "Номер отдела" (DEPNO) строки таблиц будут сохраняться совместно, разделяя одни и те же физические страницы базы данных.

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

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

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

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

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

Пример 20.22.

Создадим кластер для таблиц "Отдел" (DEPARTAMENT) и "Служащий" (EMPLOYEE). Для создания кластеров используется команда SQL CREATE CLUSTER, которая в нашем случае будет иметь вид:

CREATE CLUSTER emp_dept_c (DEPNO integer) 
SIZE 512,
-- TABLESPACE …
-- STORAGE …
INDEX;
CREATE TABLE DEPARTAMENT 
   ( 
   DEPNO 	integer NOT NULL, 
   DNAME 	char(20),
   LOC   		char(30),
   MANAGER  	char(20),
   PHONE  	char(15),
   CLUSTER emp_dept_c (DEPNO)
);

 CREATE TABLE EMPLOYEE
(
   EMPNO  	integer NOT NULL,
   ENAME  	char(40),
   LNAME  	char(20),
   DEPNO  	int NOT NULL,,
   JOB  		char(20),
   AGE  		date,
   HIREDATE  	date NOT NULL WITH DEFAULT,
   SAL  		dec(8,2),
   COMM  	dec(8,2),
   FINE  		dec(8,2),
   Biog 		text,
   Foto 		image,
   PRIMARY KEY (EMPNO),
   CLUSTER emp_dept_c (DEPNO) 
);

CREATE INDEX emp_dept_c_id ON CLUSTER emp_dept_c;

Параметр SIZE определяет размер кластера — фактически, максимальное число кластерных ключей, которые могут сохраняться на одной физической странице данных. Ключевое слово INDEX означает, что создаваемый кластер является индексным.

Предложение CLUSTER emp_dept_c (DEPNO) указывает СУБД, что таблица должна быть добавлена в кластер. Обратите внимание, что в таблице "Отдел" (DEPARTAMENT) снято ограничение первичного ключа на колонку "Номер подразделения" (DEPNO). Это связано с тем, что Oracle автоматически создает индекс на первичный ключ, а этот индекс в данном случае не нужен. Последнее предложение создает кластерный индекс для кластерного ключа кластера.

В одном из предыдущих разделов мы уже обсуждали вопрос использования таблиц хеширования, которые представляют на самом деле один из способов реализации кластера. Рассмотрим теперь подробно, как работать с хеш-кластером СУБД Oracle.

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

Пример 20.23.

Рассмотрим нашу учебную базу данных с целью создания хеш-кластера для таблицы "Служащий" (EMPLOYEE). Запрос на доступ к записям таблицы до и после кластеризации приведен ниже.

SELECT * FROM EMPLOYEE WHERE EMPNO= 997;

, потребуется 4 операции ввода-вывода, чтобы получить результирующую строку.

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

В хеш-кластере связанные строки (в данном случае имеющие одинаковое значение хеш-ключа) сохраняются также на одной физической странице базы данных на основе хешированных значений их общего ключа. В индексированных же таблицах и индексном кластере локализация результирующей строки выполняется с использованием значений ключа, которые хранятся в отдельном индексе (см. пример выше CREATE INDEX для кластерного индекса ).

Пример 20.24.

Создадим хеш-кластер для таблицы "Служащий" (EMPLOYEE) примера 20.21.

CREATE CLUSTER PERSONNEL (EMPNO integer) 
SIZE 512 
HASHKEYS 500 
-- STORAGE (INITIAL 100K  NEXT 50K  PCTINCREASE 10)
;

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

Параметр SIZE определяет максимальное число хеш-ключей, размещаемое на физической странице базы данных. Он равен оценке общего пространства в байтах, требуемого для сохранения среднего числа строк, которое связано с каждым значением хеш-ключа. Если доступного пространства на странице 1600 байт, а значение параметра –512 байт, то три значения хеш-ключа будут распределяться на физической странице.

С помощью предложения HASH IS можно переопределить хеш-функцию, которую СУБД Oracle использует по умолчанию.

Пример 20.25.

Если у нас есть хеш-кластер для таблицы EMPLOYEE и кластерный ключ определен как "Код домашнего адреса сотрудника" ( home_area_code number ), то вероятно, что будет случаться много коллизий в хеш-кластере, если город, где живут сотрудники, невелик. Для того чтобы избежать такой коллизии, можно переопределить встроенную хеш-функцию СУБД Oracle в команде CREATE CLUSTER, добавив предложение HASH IS:

CREATE CLUSTER personnel 
 (home_area_code  number,
home_prefix     number ) 
HASHKEYS 20
HASH IS MOD(home_area_code + home_suffix_tel, 101);

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

В заключение обсуждения кластеризации отметим следующее. Несмотря на то, что СУБД Oracle интенсивно применяет кластеры для доступа к системным таблицам БД, авторы рекомендуют проявлять осторожность при принятии решения о кластеризации таблиц при создании новой БД. Выигрыш в производительности может быть не слишком высок по сравнению с другими проектными решениями. Проектирование кластеров — "штучная" работа. Очень полезно знать статистику использования аналогичного кластера при эксплуатации аналогичного ХД, чтобы построить высокопроизводительный кластер. Следует придерживаться следующего эмпирического правила:

Резюме

В настоящей лекции мы рассмотрели три основных метода повышения производительности запросов, которые используют встроенные в СУБД механизмы и объекты: индексы, секции и кластеры.

Основная идея индексирования таблиц состоит в создании специальных объектов СУБД – индексов для повышения производительности выборки строк таблиц в запросах. Как правило, для первичных ключей таблиц СУБД создает индексы автоматически.

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

Основная идея секционирования таблиц — с помощью встроенных команд СУБД разбить таблицы большого объема на ряд физических фрагментов в соответствии с некоторым критерием секционирования, чтобы сократить объем ввода-вывода при обработке фрагментов. Секционирование очень часто применяется при работе с таблицами большого объема. Если в проектируемой базе данных предполагается наличие объектов большого объема (более 1 Гб), обязательно следует рассмотреть возможность использования техники секционирования.

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

В целом, большую таблицу стоит секционировать, если выполняются следующие два условия:

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

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

Однако нужно помнить, что проектирование кластеров – это "штучная" работа. Выигрыш в производительности может быть не слишком высок по сравнению с другими проектными решениями. Чтобы построить высокопроизводительный кластер для таблиц, полезно знать статистику использования аналогичного кластера при эксплуатации аналогичного ХД, а это не всегда бывает известно.