Рецепт самогона

Рецепт самогона

14. февраля 2012 22:20

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

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

При создании секционированной функции, мы указываем, к какой области интервала значений принадлежит аргумент boundary_value [ ,...n ] (к левой или правой). Сегодня я расcкажу, как выбор RANGE [ LEFT | RIGHT ] влияет на операции SPLIT/MERGE RANGE.

ALTER PARTITION FUNCTION

Эта инструкция изменяет функцию секционирования путем разбиения или слияния ее пограничных значений. При выполнении инструкции ALTER PARTITION FUNCTION одна секция какой-либо таблицы или индекса, которые используют функцию секционирования, может быть разбита на две секции или две секции могут быть объединены в одну.

Синтаксис:

ALTER PARTITION FUNCTION partition_function_name()
{ 
    SPLIT RANGE (boundary_value )
  | MERGE RANGE (boundary_value ) 
} [ ; ]

SPLIT (разбиение)

Для демонстрации создадим 2 функции секционирования left и right:

--Секционированная left-функция
create partition function pf_range_left ( int )
as range left for values ( 1, 10, 100 );
go
--Секционированная схема для left-функции
create partition scheme ps_range_left
as partition pf_range_left
all to ( [PRIMARY] );
go

--Секционированная right-функция
create partition function pf_range_right ( int )
as range right for values ( 1, 10, 100 );
go
--Секционированная схема для right-функции
create partition scheme ps_range_right
as partition pf_range_right
all to ( [PRIMARY] );
go

Создадим две одинаковых таблицы, но одну секционируем с помощью схемы left, а другую с помощью схемы right:

create table dbo.tbl_test_01 ( id int ) on ps_range_left ( id );
create table dbo.tbl_test_02 ( id int ) on ps_range_right ( id );

Наполним одинаковыми данными:

insert into dbo.tbl_test_01 
  output inserted. into dbo.tbl_test_02
  values (3),(5),(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),(111);
go

Информация по таблицам из sys.partitions:

select partition_id, partition_number, rows
  from sys.partitions
  where object_id = object_id ( N'dbo.tbl_test_01', N'U' );
go
select partition_id, partition_number, rows
  from sys.partitions
  where object_id = object_id ( N'dbo.tbl_test_02', N'U' );
go


В таблицах по четыре секции, больше всего записей (девять) в секции номер три, она содержит значения в интервале от 10 до 100, в нашем случаи это значения - 11, 21, 31, 41, 51, 61, 71, 81, 91. Теперь посмотрим, что произойдёт при разбиении (SPLIT) этой секции на две для разных схем секционирования (left и right): 10-50 и 50-100.

alter partition scheme ps_range_left
next used [PRIMARY];
alter partition function pf_range_left()
split range (50);
go
alter partition scheme ps_range_right
next used [PRIMARY];
alter partition function pf_range_right()
split range (50);
go

Результат перераспределения значений ниже:

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

В первом случаи (для левой функции) секция №3 с идентификатором 72057594042122240 превратилась в секцию №4 и теперь содержит 5 записей, при этом в таблице появилась новая секция с идентификатором 72057594042515456 и теперь она является секцией №3 и содержит в себе 4 записи (суммарно 9 строк, которые до разделения все находились в секции №3).

А теперь посмотрим, как обстоят дела у второй таблицы (правая функция секционирования). Секция №3 с идентификатором 72057594042384384 осталась на своём месте. В таблице появилась новая секция 72057594042580992, которая является №4 и в неё переехали 5 записей.

В зависимости от функции секционирования перемещение записей происходит по разному. Ответ на то, почему так происходит, можно найти в BOL (SQL Server Books Online):

На основе аргумента boundary_value компонент Database Engine разбивает один существующий диапазон на два. Один из них, содержащий новый аргумент boundary_value, является новой секцией.


Именно в этой фразе скрыт ответ. Но каким образом определяется, какой секции принадлежит аргумент boundary_value (в нашем случаи значение 50). Именно значение left и right определяют к какой секции принадлежит новая точка.

Для left-функции точка 50 принадлежит новой секции. До разделения секция №3 включала в себя значения в интервале:

10 < id <= 100 - partition_id = 72057594042122240

После разделения мы получили новый интервал (секция №3), куда были перемещены 4 записи:

10 < id <= 50 - partition_id = 72057594042515456

А секция №3 превратилась в секцию №4 и содержит интервал:

50 < id <= 100 - partition_id = 72057594042122240

Для right-функции точка 50 остаётся в старой секции. До разделения секция №3 включала в себя значения в интервале:

10 <= id < 100 - partition_id = 72057594042384384

После разделения мы получили интервал (секция №3):

10 <= id < 50 - partition_id = 72057594042384384

И новую секцию №4, которая содержит интервал (и в которую переместили 5 строк):

50 <= id < 100 - partition_id = 72057594042580992

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

Предположим нам необходимо создать новую секцию справа. Точкой разделения будет значение 200. При этом у нас сейчас в самой правой секции две записи (101 и 111). Интервалы для таблиц следующие:

  • Для left-функции - 100 < id <= ∞
  • Для right-функции - 100 <= id < ∞


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

select partition_id, partition_number, rows
  from sys.partitions
  where object_id = object_id ( N'dbo.tbl_test_01', N'U' )
  order by partition_number;
go
select partition_id, partition_number, rows
  from sys.partitions
  where object_id = object_id ( N'dbo.tbl_test_02', N'U' )
  order by partition_number;
go


После разбиения мы должны получить по 2 интервала:

Для left-функции 100 < id <= 200 200 < id <= ∞
Для right-функции 100 <= id < 200 200 <= id < ∞
alter partition scheme ps_range_left
next used [PRIMARY];
alter partition function pf_range_left()
split range (200);
go
alter partition scheme ps_range_right
next used [PRIMARY];
alter partition function pf_range_right()
split range (200);
go


Обратите внимание, что для left-функции опять произошло перемещение данных и наша секция 72057594042187776, которая была 5-ой и содержала две записи, заняла 6-ую позицию под данные, где id > 200, а наши две записи "уехали" в новь созданную секцию 72057594042646528, которая стала 5-ой.

Обязательно учитывайте это при проектировании, т.к. эта особенность для многих не очевидна и они рассчитывают на очень быструю и лёгкую операцию SPLIT, когда создаётся пустая секция. Особенно это ощутимо на очень больших объёмах, т.к. весь этот объём может начать перемещаться физически в новую секцию. Для right-функции в нашем примере ситуация обстоит куда лучше, мы действительно создали новую пустую секцию без какого либо перемещения самих данных. Но если мы будем создавать новую секцию слева, например произведём разбиение данных указав новую точку = -100, то ситуация будет противоположной, т.к. физическое перемещение данных будет только у right-функции.

MERGE (слияние)

RANGE RIGHT/LEFT влияет и на операцию слияния. Но не всегда! Для демонстрации будем использовать те же таблицы и для начала "удалим" точку 50, но прежде опять посмотрим на идентификаторы секций, которые должны будут слиться:

А теперь проведём операцию слияния:

alter partition function pf_range_left()
merge range (50);
go
alter partition function pf_range_right()
merge range (50);
go

И снова обратимся к sys.partitions

В обеих таблицах была удалена секция под номером три, где было 4 записи и все они были перемещены в секцию номер четыре, где было 5 записей. Для более быстрой операции слияния, SQL Server сам оценили, в какой секции меньше данных и именно ту секцию выбрал "жертвой" и данные из неё переехали в секцию, где данных было больше. Но такое поведение возможно, только если данные из этих секций лежат в одной файловой группе, как у нас.

Но если мы используем разные файловые группы или пытаемся слить две секции, в которых одинаковое количество записей, то мы опять зависим от RANGE RIGHT/LEFT.
Для демонстрации я добавлю в каждую таблицу по 7 записей в четвёртую секцию, чтобы в третей и четвёртой секциях было по 9 записей.

insert into dbo.tbl_test_01 
  output inserted. into dbo.tbl_test_02
  values (121),(122),(123),(124),(125),(126),(127);
go


А теперь объеденим эти секции, "удалив" точку = 100

alter partition function pf_range_left()
merge range (100);
go
alter partition function pf_range_right()
merge range (100);
go


Для left-функции все данные оказались в секции номер четыре с идентификатором 72057594042646528, а для right-функции в секции номер три с идентификатором 72057594042580992. Чем обусловлено такое поведение? Ответ опять кроется в BOL:

MERGE удаляет секцию и объединяет все значения, существующие в секции, в одну из оставшихся. Аргумент RANGE (boundary_value) должен быть существующим пограничным значением, в которое объединяются значения из удаленной секции. Файловая группа, изначально содержащая аргумент boundary_value, удаляется из схемы секционирования, если она не используется оставшейся секцией или не обозначена как NEXT USED. Объединенная секция находится в файловой группе, которая изначально не содержит boundary_value.


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

Функция Секция 3 Секция 4
Для left 50 < id <= 100 100 < id <= 200
Для right 50 <= id < 100 100 <= id < 200

Не забывайте о таком поведении SQL Server при операциях SPLIT/MERGE и удачи Вам!

Отпечаток детской ручки и ножки на бумаге 189
Рецепт самогона 416
Шью детишкам
Рецепт самогона 958
ЭЛЕКТРОПРИВОДИСТЕМАХ
Рецепт самогона 979
Мои гусиные скрадки. - lzip. org
Рецепт самогона 57
Киста
Рецепт самогона 809
Я был слеп, но теперь я вижу свет «Скиния»
Рецепт самогона 47
Рецепт самогона 22
Рецепт самогона 84
Рецепт самогона 83
Рецепт самогона 63
Рецепт самогона 92
Рецепт самогона 6
Рецепт самогона 82
Рецепт самогона 24
Рецепт самогона 54