Microsoft SQL Server. Первые шаги в секционировании (partition)

Автор: Вячеслав

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

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

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

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

1. Создание функции секционирования:

CREATE PARTITION FUNCTION part_func_test (int)

AS RANGE LEFT FOR VALUES (5, 10, 15);

В итоге наша функция разделяет данные на периоды:

1 период:  val <=5,
2 период:  5<val <=10,
3-й: 10<val <15,
4 –од:  15 <val

2. Далее создадим схему секционирования:

CREATE PARTITION SCHEME part_sch_test

AS PARTITION part_func_test
TO (f1, f2, f3, f_actual);

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

3. Затем создаем таблицу с указанием нашей функции секционирования:

CREATE TABLE [dbo].[tbl_part1](

      [id] [int] IDENTITY(1,1) NOT NULL,
      [val] [nchar](20) NULL,
 CONSTRAINT [PK_tbl_part11] PRIMARY KEY CLUSTERED 
(
      [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON  part_sch_test (id)
) ON  part_sch_test (id)

Все, таблица секционирована.

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

select $PARTITION.part_func_test(1000)

результат это номер секции, куда попадает данное значение.
Так же представления, для получения информации по секционированию:

select * from sys.partition_functions

select * from sys.partition_schemes

select * from sys.partition_parameters

select * from sys.partition_range_values

select * from sys.partitions

По запросу

select * from sys.partitions where object_id=object_id('dbo.tbl_part1')

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

SELECT

     tbl.name tbl_name,
      --idx.type_desc idx_type,
      --idx.name idx_name,
      --dts.name + ISNULL('-> ' + dts2.name, '') dts_name,
      dts.type_desc + ISNULL('-> ' + dts2.type_desc, '') dts_type,
      prt.partition_number,
      prt.rows,
      prv.value low_boundary,
      prs.name part_scheme_name,
      pfs.name part_func_name
FROM sys.tables tbl
JOIN sys.indexes idx ON idx.object_id = tbl.object_id
JOIN sys.data_spaces dts ON dts.data_space_id = idx.data_space_id
JOIN sys.partitions prt ON prt.object_id = tbl.object_id AND prt.index_id =idx.index_id
LEFT JOIN sys.partition_schemes prs ON prs.data_space_id = dts.data_space_id
LEFT JOIN sys.partition_functions pfs ON pfs.function_id = prs.function_id
LEFT JOIN sys.partition_range_values prv ON
      prv.function_id = pfs.function_id AND prv.boundary_id =prt.partition_number - 1
LEFT JOIN sys.destination_data_spaces dds ON
      dds.partition_scheme_id = prs.data_space_id    AND dds.destination_id =prt.partition_number
LEFT JOIN sys.data_spaces dts2 ON dts2.data_space_id = dds.data_space_id
where tbl.object_id=object_id('dbo.tbl_part1')

Запрос взят с sql.ru

Теперь немного поиграемся с нашей секционированной таблицей:

1)      Добавить /удалить секцию к таблице.

Пусть в выше указанном примере необходимо добавить  новый период от 20<val

Для этого необходимо сначала добавить новую файловую группу в функцию секционирования:

ALTER PARTITION SCHEME [part_sch_test]

NEXT USED f4;

После этого изменяем функцию секционирования:

ALTER PARTITION  FUNCTION [part_func_test]()

SPLIT range(20)

Т.е добавляем новую границу и сопоставляется с файловой группой f4.

Если посмотреть код создания функции и схемы  секционирования:

CREATE PARTITION FUNCTION [part_func_test](int)

AS RANGE LEFT FOR VALUES (5, 10, 15, 20)



CREATE PARTITION SCHEME [part_sch_test]

AS PARTITION [part_func_test] TO ([f1], [f2], [f3], [f4], [f_actual])

То мы увидим наши новый периоды.

Если выполним скрипт alter partition function с командой merge, то получим сливание периодов секционирования, на нашем примере вернемся к первоначальной схеме секционирования

ALTER PARTITION  FUNCTION [part_func_test]() 
merge range(20)

2) С ростом данных, нам необходимо периодически обновлять секционирование, старые данные переносить в архив, оставлять актуальные

Для этого делаем:
Удаляем левую границу:

ALTER PARTITION  FUNCTION [part_func_test]()

merge range(5)

Удаленную файловую группу, которая принадлежала левой границе , назначаем, как следующая :

alter PARTITION SCHEME [part_sch_test]

next used f1

Отделяем актуальные данные, у нас это значения id больше 20:

ALTER PARTITION  FUNCTION [part_func_test]()

split range(20)

Вот и все окончательное секционирование выглядит так:

CREATE PARTITION SCHEME [part_sch_test] AS PARTITION [part_func_test]

TO ([f2], [f3], [f1], [f_actual])


CREATE PARTITION FUNCTION [part_func_test](int)

AS RANGE LEFT FOR VALUES (10, 15, 20)

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

Вот вроде и все, по секционированию.