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

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

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

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

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

Пример. Создадим локальный секционированный индекс для таблицы Sales (рис. 11.2). Ключом секционирования этой таблицы является колонка 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 из наших предыдущих примеров.

Пример. В качестве ключа секционирования для индекса используем колонку 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,
);

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

Пример. В качестве колонки секционирования для индекса выбрана колонка 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.

Пример. Секционирование представлений с помощью ограничения 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 не оценивается для каждой строки запроса. Такие предикаты исключают вставку в таблицы строк, не соответствующих критерию предиката. Строки, соответствующие предикату секционирования, извлекаются из базы данных быстрее.

Пример. Секционирование представлений с помощью предложения 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';

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

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

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

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

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

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