Автор: Вячеслав
Чтобы создать секционированную таблицу или индекс, необходимо выполнить следующие шаги:
- Создайте функцию секционирования, чтобы указать, каким образом таблица или индекс, где используется эта функция, могут быть секционированы, т.е создать функцию разбиения данных, по каким условиям.
- Создайте схему секционирования, чтобы указать размещение секций функцией секционирования для файловых групп.
- Создайте таблицу или индекс с использованием схемы секционирования.
-
Создание функции секционирования:
|
1 2 3 |
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
-
Далее создадим схему секционирования:
|
1 2 3 4 |
CREATE PARTITION SCHEME part_sch_test AS PARTITION part_func_test TO (f1, f2, f3, f_actual); |
Перед созданием схемы убедитесь, что нужные файловые группы созданы и привязаны к файлам базы данных.
-
Затем создаем таблицу с указанием нашей функции секционирования:
|
1 2 3 4 5 6 7 8 9 |
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) |
Все, таблица секционирована.
Чтобы проверить, в какую секцию попадет значение , можно выполнить функцию
|
1 |
select $PARTITION.part_func_test(1000) |
результат это номер секции, куда попадает данное значение.
Так же представления, для получения информации по секционированию:
|
1 2 3 4 5 6 7 8 9 |
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 |
По запросу
|
1 |
select * from sys.partitions where object_id=object_id('dbo.tbl_part1') |
можно определить, сколько примерно записей находится в секциях.
По данному запросу можно определить, сколько данных в каждой секции(более красиво оформлено):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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
Для этого необходимо сначала добавить новую файловую группу в функцию секционирования:
|
1 2 3 |
ALTER PARTITION SCHEME [part_sch_test] NEXT USED f4; |
После этого изменяем функцию секционирования:
|
1 2 3 |
ALTER PARTITION FUNCTION [part_func_test]() SPLIT range(20) |
Т.е добавляем новую границу и сопоставляется с файловой группой f4.
Если посмотреть код создания функции и схемы секционирования:
|
1 2 3 4 5 6 7 8 9 |
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, то получим сливание периодов секционирования, на нашем примере вернемся к первоначальной схеме секционирования
|
1 2 |
ALTER PARTITION FUNCTION [part_func_test]() merge range(20) |
2) С ростом данных, нам необходимо периодически обновлять секционирование, старые данные переносить в архив, оставлять актуальные
Для этого делаем:
Удаляем левую границу:
|
1 2 3 |
ALTER PARTITION FUNCTION [part_func_test]() merge range(5) |
Удаленную файловую группу, которая принадлежала левой границе , назначаем, как следующая :
|
1 2 3 |
alter PARTITION SCHEME [part_sch_test] next used f1 |
Отделяем актуальные данные, у нас это значения id больше 20:
|
1 2 3 |
ALTER PARTITION FUNCTION [part_func_test]() split range(20) |
Вот и все окончательное секционирование выглядит так:
|
1 2 3 4 5 6 7 8 |
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) |
И еще, немного в дополнение, в нашем примере мы создавали секционированную таблицу, а что делать, если таблица уже есть — ответ нужно удалить кластеризованный индекс и его заново создать с использованием схемы секционирования, после этого наша таблица будет секционирована.
Вот вроде и все, по секционированию.
