Технология Клиент-Сервер 1999'3
	Home Поиск Издания Контакты k-press.ru

Продолжается подписка на наши издания! Вы не забыли подписаться?

Подход к разбиению больших таблиц на разделы

Разбиение больших таблиц на разделы (partition table) является одной из важнейших особенностей ORACLE 8, которая позволяет значительно повысить производитель ность приложений и упростить поддержку базы данных самой по себе. Под разбиением таблицы или индекса на разделы понимается создание отдельных физических мест хранения данных при едином их логическом имени.

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

Все разделы таблицы или же индекса имеют одни и те же логические атрибуты, хотя их физические атрибуты могут отличаться. К примеру, все разделы в таблице имеют те же названия колонок и определение для ограничителей (constrain). Аналогичным образом все разделы для индекса могут использовать в своем определении тот же набор колонок. Однако параметры хранения (storage) и другие физические атрибуты, такие как PCTFREE, PCTUSED, INITRANS и др., могут быть различными для разных разделов одной и той же таблицы или индекса.

Каждый раздел хранится в отдельном сегменте. Более того, вы можете хранить их даже в отдельных табличных пространствах (tablespace). После создания таблицы или индекса с разделами вы можете использовать команды ALTER TABLE и ALTER INDEX для модификации параметров таблицы в целом или же ее отдельного раздела.

Одна из главных особенностей ORACLE 8 состоит в том, что он может распознавать таблицы с разделами и оптимизировать выполнение SQL-команды за счет использования не всей таблицы целиком, а только того раздела, где находятся необходимые данные. Для каждого SQL-оператора, в зависимости от указываемого критерия поиска, ненужные разделы будут отсечены от поиска. К примеру, если в SQL-операторе вы указываете поиск данных по продаже и отправке только по первому кварталу, то очевидно, что нет необходимости поиска по трем остальным. Такое "интеллектуальное" отсечение ненужных данных позволяет значительно уменьшить количество анализируе мой информации, что, в свою очередь, повышает производительность базы данных в целом.

Основная цель данной статьи состоит в поиске компромисса между двумя достаточно противоречивыми подходами к разбиению таблицы на разделы. С одной стороны, разбиение таблицы на большое число разделов приведет к очевидному улучшению производительности SQL-оператора. Но с другой стороны, что также очевидно, управление и поддержка большего числа разделов таблицы требует больших усилий со стороны как разработчиков, так и администратора базы данных. Возникает резонный вопрос: где находится оптимальное решение и как оценить тот или иной сценарий разбиения таблицы на разделы до его реализации. В данной статье как раз и предлагается один из подходов к решению данной проблемы и предлагается критерий для оценки того или иного сценария разбиения таблицы. К примеру. Предположим, что мы имеем таблицу с двумя колонками: SALEDATE и SHIPDATE, в которой собрана информация об ордерах компании за много лет. Для построения будущих отчетов в основном будут использоваться SQL-запросы, которые будут искать различного рода суммарную информацию либо по кварталу, либо по месяцу, либо по конкретному дню, с использованием двух вышеуказанных колонок. Возникает естественное желание создать таблицу с разделами, в которой ключ разбиения (КР, partition key) будет композитного типа, использующий вышеуказанные колонки. В данном случае мы можем создать либо таблицу со 144 разделами, либо с 16. Разбиение таблицы на принципах помесячного хранения данных в разделах приведет к более быстрому построению отчетов за счет того, что в каждом разделе будет храниться информация только типа: ордер был оформлен в марте, а отгрузка произведена в апреле. Тогда как если мы сделаем поквартальное разбиение, то в каждом разделе будет храниться информация типа: ордер был оформлен в первом квартале, а отгрузка была произведена во втором. Т ак как количество данных в каждом разделе при первом разбиении меньше, чем при втором, то и время формирования отчета будет меньше. Но, с другой стороны, очевидно также, что администрирование таблицы с 16 разделами намного легче, чем со 144. Где компромисс?

На мой взгляд, критерием для оценки того или иного сценария разбиения таблицы на разделы должно быть время ответа(ВО) (response time) на основные SQL-запросы. Понятно, что надо уметь оценить ВО до того, как будет произведено разбиение таблицы на разделы. Один из подходов к решению проблемы приведен в данной работе.

Описание теста и расчетных формул

Для иллюстрации нашего подхода мы использовали ORACLE 8.0.5, работающий на сервере HP с 4 CPU. Наша основная таблица, которую предполагалось разбить на разделы, имела 1000800 строк со средней длиной одной строки порядка 150 байт. Так как нас в будущем будут интересовать только различного рода суммарные характеристики той или иной колонки, то мы будем оценивать ВО только для полного сканирования (ПС) (full scan) таблицы. ВО для нашей основной таблицы составляло 14.9 сек. Таблица имела две колонки: SALEDATE и SHIPDATE. Используя их мы собираемся разбить нашу основную таблицу на разделы, то есть эти колонки станут композитным КР для будущей разделенной таблицы. Далее мы постараемся оценить с точки зрения ВО три сценария разбиения таблицы на разделы: поквартально, помесячно и смешанный. Нас будут интересовать три основных типа SQL-запросов, производящих ПС того или иного раздела/разделов или же всей таблицы, по отношению к трем вышеописанным сценариям разбиения. Ниже мы приводим эти типы SQL запросов.

Первый тип запроса представляет из себя поиск данных за квартал:

SELECT SUM(...) FROM TEST_BIG
WHERE     TO_CHAR(Saledate,'MM') IN (`01',02','03')
      AND TO_CHAR(Shipdate,'MM') IN (`04',05','06') ...;   

(SQL_1)

Второй тип запроса представляет из себя поиск данных за месяц:

SELECT SUM(...) FROM TEST_BIG
WHERE     TO_CHAR(Saledate,'MM') =  `01'                 AND TO_CHAR(Shipdate,'MM') =  `04' ...;

(SQL_2)

Третий тип запроса представляет собой поиск данных за конкретный день:

SELECT SUM(...) FROM TEST_BIG
WHERE Saledate  =  TO_DATE(`01/01/98','mm/dd/yy') 
  AND Shipdate  = TO_DATE(`05/01/98','mm/dd/yy') ...;

(SQL_3)

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

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

Таблица 1

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

Таблица 2

Мы будем пользоваться довольно простой формулой для вычисления ВО при ПС того или иного раздела будущей таблицы.

RT = (ETFS /NRBT)*NRP                (1)

Где:

NRBT = Числу рядов в исходной (не разделенной) таблице

ETFS = ВО для ПС исходной таблицы (14.9 сек). Данное значение для ВО мы получили, выполнив запрос типа

SELECT COUNT(*)  FROM TEST_BIG;

NRP = Числу рядов в разделе

Формулу (1) мы будем использовать для обоих типов разбиения таблицы на разделы: как помесячно, так и поквартально.

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

RT(D,Q) = RT(Q) * RT (D,Big) / ETFS     (2)

Где:

RT(Q) = ВО для сканирования раздела таблицы, разделенной по квартальному принципу. Это значение вы можете вычислить, используя формулу (1), где NRP будет равняться числу рядов для соответствующего квартала, данные для которых вы можете видеть в Таблице 1.

RT (D,Big) = ВО для получения информации для конкретного из исходной таблицы (не разделенной). Данное значение для ВО мы получили, выполнив запрос типа SQL_3.

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

RT(D,Q) = RT( М  )  * RT (D,Big) / ETFS (3)

Где RT( М ) = ВО для сканирования раздела таблицы разделенной по месячному принципу. Это значение вы можете вычислить, используя формулу (1), где NRP будет равняться числу рядов для соответствующего месяца, данные для которых вы можете видеть в таблице 2.

Соотношения (1)-(3) носят эмпирический характер. Чтобы показать их состоятельность, ниже, в таблице 3 мы приводим результаты сравнения реальных данныхтолько для ВО с расчетными ВО.

Таблица 3

Сравнение различных типов разбиения таблицы на разделы

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

Используя соотношение (1), мы вычислили ВО для SQL_1. Ясно, что это время будет одинаково для обоих типов разбиения, так как количество рядов которых необходимо просканировать будет одно и тоже. Результаты этих вычислений приведены в таблице 3 в графе А.

В таблицах 4 _ 5 приведены результаты вычисления ВО для SQL_2 , соответственно для помесячного и покварталь ного разбиение.

Мы использовали здесь формулу (1) со значениями для рядов из Таблиц 1 и 2 соответственно. Понятно, что ВО для SQL_2 , например для II квартала, будет одинаково, так как для получения данных для одного месяца необходимо просканировать весь раздел для II квартала, независимо от того, о каком месяце мы хотим получить данные. Здесь мы привели данные только для первых 6 месяцев, из соображений экономии места в статье.

В Таб. 6 _ 7 приведены результаты вычисления ВО для SQL_3 , соответственно для поквартального и помесячно го разделения.

Мы использовали здесь формулы (2) и (3) соответственно.

Таблица 4

Таблица 5

Далее, если предположить, что 10 секунд являются критическим значением для ВО по всем трем типам SQL запросов, то, как видно из таблицы 6, мы можем достигнуть этого уровня, если сделаем разделенную таблицу и разобьем ее, исходя из принципа поквартального разделения, для кварталов I, II и III. Для II квартала необходимо использовать принцип помесячного разбиения.

Таблица 6

 Таблица 7

Результаты тестирования и выводы

Как мы уже упоминали ранее, наша основная цель состоит в том, чтобы создать таблицу с разделами, в которой присутствовали бы колонки SALEDATE и SHIPDATE с типом данных DATE и одна дополнительная колонка, которая бы содержала бы в себе КР. Так как работать с композитным КР нелегко , то в определении значения для диапазона разбиения (range partition) мы использовали определенную хэш-функцию. С нашей точки зрения, приводимая ниже хэш-функция весьма полезна для работы с данными типа DATE.

Ниже приводится SQL-команда для создания разделенной таблицы и ее заполнения.

create table test_big_part_opt (
ORD_ID     NUMBER,
C1          VARCHAR2(60),
C2          VARCHAR2(60),
C3          VARCHAR2(60),
C4          NUMBER(15,2),
saledate     date,
shipdate     date,
Key_Q_M          number)
 PARTITION BY RANGE (Key_Q_M)
 (partition S_11Q VALUES LESS THAN (110304),
 partition S_12Q VALUES LESS THAN (120307),
 partition S_22Q_44M VALUES LESS THAN (220405),
 partition S_22Q_45M VALUES LESS THAN (220406),
 partition S_22Q_55M VALUES LESS THAN (220506),
 partition S_22Q_56M VALUES LESS THAN (220507),
 partition S_22Q_66M VALUES LESS THAN (220607),
 partition S_23Q VALUES LESS THAN (230611),
 partition S_33Q VALUES LESS THAN (330911),
 partition S_34Q VALUES LESS THAN (340913),
 partition S_41Q VALUES LESS THAN (411204),
 partition S_44Q VALUES LESS THAN (441213)
);
     
insert into test_big_part_opt
( 
select 
ORD_ID,
C1,
C2,
C3,
C4,
saledate,
shipdate,
floor(TO_NUMBER(TO_CHAR(saledate,'MM'))/3+0.8)*100000 + 
floor(TO_NUMBER(TO_CHAR(shipdate,'MM'))/3+0.8)*10000 + 
TO_NUMBER(TO_CHAR(saledate,'MM'))*100 + 
TO_NUMBER(TO_CHAR(shipdate,'MM'))*1
from test_big
where
TO_CHAR(saledate,'MM') in ("01",'02','03')
AND
TO_CHAR(shipdate,'MM') in ("01",'02','03')
);

Эта команда вставляет данные только для первого квартала (раздела).

Ниже, в таблице 8 вы можете видеть результаты реального ВО для всех трех типов SQL-запросов.

Таблица 8

Для определения КР мы использовали хэш-функцию в следующем виде:

-- Порядковый номер месяца для SALEDATE
KEY_Q_M = floor(<Month>/3+0.8)*100000
+ floor((<Month>/3+0.8)*10000 --Порядковый номер месяца для SHIPDATE
+ (<Month>/*100 --Порядковый номер месяца для SALEDATE
+ (<Month>*1 --Порядковый номер месяца для SHIPDATE

К примеру, если вы хотите найти число рядов в таблице test_big_part_opt для апреля по колонке SALEDATE и мая по колонке SHIPDATE, то SQL-запрос будет выглядеть следующим образом:

SELECT count(*) from TEST_BIG_PART_OPT
WHERE KEY_Q_M = floor(4/3+0.8)*100000 + floor(5/3+0.8)*10000 + 4*100 + 5*1;

В данном случае значение хэш-функции будет равно 220405.

Вышеописанная идея использования композитных КР для колонок, использующих данные типа DATE, а также трансформации их в одномерные КР, используя хэш-функцию, на наш взгляд весьма полезна сама по себе. Ниже мы приводим результаты тестирования для трех разных принципов разделения. В первых двух мы использовали принцип хэширования для КР, в последнем нет. Это означает, что в КР мы вставляли значение номера квартала, так как мы делали поквартальное разделение . Результаты тестирования приведены в таблице 9.

Таблица 9

Как видно из результатов теста, использование нехешированного КР намного хуже по сравнению с использованием хэширования КР для SQL_2. Это очевидно, - чтобы получить, скажем, информацию о количестве рядов, мы должны сделать запросы:

1. Хешированный КР (поквартальное разделение)

SELECT count(*) FROM TEST_BIG_PART_OPT1
WHERE KEY_Q_M= floor(4/3+0.8)*100000 + floor(5/3+0.8)*10000 + 4*100 + 5*1;

2. Нехэшированный КР (поквартальное разделение)

SELECT count(*) FROM TEST_BIG_PART_OPT1
WHERE     KEY_Q_M= 220
      AND TO_CHAR(saledate,'mm') = `04'
      AND TO_CHAR(shipdate,'mm') = `05'

Использование хэш-функции проводит к четырехкратному улучшению ВО.

Copyright © 1994-2016 ООО "К-Пресс"