[Связь] Обзор разделённых таблиц на SQL Server

Официальная документация

Шаги высокого уровня для создания таблицы разделов

1. Создать группу файлов (похожую на oracle tablespace), конечно, её создать невозможно, можно поместить все разделы в одну группу файлов

2. Создать функцию разбиения и распределить данные по дальности

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

4. Создайте таблицу и объедините схему зонирования

Обнаружен баг

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

Некоторые выводы из таблицы разбиения:

1. Разбитые поля не обязательно должны быть индексированы

2. Разбитые поля могут быть созданы как кластеризованные или нокластеризованные индексы

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

4. Преобразуйте обычную таблицу в разбившую, просто создайте кластерный индекс в таблице и используйте схему разбиения на кластерном индексе. Например, когда поле 1 неразбитой таблицы создаёт кластерный индекс и связано со схемой разбиения, таблица преобразуется в разбиваемую таблицу, а разбиенное поле — поле 1.

5. Преобразовать разбиенную таблицу в нормальную, если есть поле с индексом, перестроить разбиенное поле в кластерный индекс и не ассоциироваться со схемой разбивания; если поле не имеет индекса, поле может создать новый кластеризованный индекс и не ассоциироваться со схемой разбиений

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

7. Для создания таблицы разделов ограничение уникальности должно включать столбец разбиения

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

9. Функции разбиения и схемы разбиения находятся в базе данных, а не обращены ко всему экземпляру

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

Шаги создания таблицы разделов

1.1. Пример создания группы файлов

alter database test1 add filegroup part1;

alter database test1 add filegroup part1000;

alter database test1 add filegroup part2000;

alter database test1 add filegroup part3000;

alter database test1 add filegroup part4000;

1.2. Создать пример файла и ассоциировать группу файлов

ALTER DATABASE test1 ДОБАВИТЬ FILE(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) В FILEGROUP PART1;

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) В FILEGROUP PART1000;

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) В FILEGROUP PART2000;

ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) В FILEGROUP PART3000;

ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) В FILEGROUP PART4000;

2. Пример установления функции разбиения, функция разбиения называется partfun1

CREATE PARTITION FUNCTION partfun1 (int)

КАК ДИАПАЗОН ЛЕВОГО ЗНАЧЕНИЯ ('1000', '2000', '3000', '4000')

--ЗНАЧЕНИЯ ('1000', '2000', '3000', '4000') указывает, что таблица будет разделена на 5 зон, которые делятся по размеру значений в полях таблицы, пять зон являются самыми маленькими — 1000, 1000-2000, 2000-3000, 3000-4000, 4000-крупнейшие

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

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (часть 1, часть 1000, часть 2000, часть 3000, часть 4000);

--Построен на нескольких группах файлов: part1, part1000, part2000, part3000, part4000

СОЗДАТЬ СХЕМУ РАЗДЕЛОВ partschema2

AS PARTITION partfun1

К (ЧАСТЬ1,[ПЕРВИЧНАЯ],[ПЕРВИЧНАЯ],[ПЕРВИЧНАЯ],[ОСНОВНАЯ]);

--Построенный на группе файлов part1, [PRIMARY], нет проблем заменить часть 1 на [PRIMARY], чтобы это было похоже на создание на группе файлов [PRIMARY]

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (часть 1);

--всё построено на файловой группе part1

СОЗДАТЬ СХЕМУ РАЗДЕЛОВ partschema4

AS PARTITION partfun1

ВСЕ В ([ПЕРВИЧНЫЕ]);

--всё построено на файловой группе [PRIMARY]

4. Пример создания таблицы разбиений

СОЗДАТЬ ТАБЛИЦУ parttable1(

[ID] [int] НЕ NULL,

[IDText] [нварчар] (макс) НЕДЕЙСТВИТЕЛЬНЫЙ

[Дата] [время свидания] NULL)

ON [partschema1](ID);

вставить значения Parttable1 (1,'1',getdate()-4);

вставить значения в parttable1 (1001,'1001',getdate()-3);

вставить значения Parttable1 (2001,'2001',getdate()-2);

вставить значения Parttable1 (3001,'3001',getdate()-1);

вставить значения Parttable1 (4001,'4001',getdate());

5. Проверьте данные таблицы разделов

ВЫБЕРИТЕ * ИЗ PARTTABLE1;

--Возвращает все строки таблицы разбиений

ВЫБЕРИТЕ отдельные $PARTITION. [partfun1] (4) ИЗ PARTTABLE1;

--Возвращает, какое разбиение строки с ID-полем 4 принадлежит

ВЫБЕРИТЕ * ИЗ PARTTABLE1, где $PARTITION. [partfun1] (ID)=2

--Возвращает все строки во втором разделе, идентификатор — это идентификатор поля раздела

Примечание: вы не можете использовать SELECT * ИЗ parttable1, где $PARTITION. [partfun1] (ID)=2 имеет результат, это означает, что это разделённая таблица, а последний тест 7 в этой статье — неразбивённая таблица, но SELECT * FROM parttable1, где $PARTITION. [partfun1] (ID)=2 всё ещё имеет результат

Новые разделы

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

2. Модифицировать функцию разбиения (добавить диапазон данных функции разделения)

ALTER PARTITION SCHEME partschema1 СЛЕДУЮЩЕЕ ИСПОЛЬЗОВАНИЕ [ОСНОВНАЯ]

ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ('4500')

выберите p.partition_number,p.строки из sys.indexes i inner join sys.partitions p on p.object_id = i.object_id и i.object_id = object_id('parttable2') порядка по 1

--Первое утверждение: если схема разделения использует ALL TO ([PRIMARY]), этот оператор не требует выполнения

--К второму оператору добавляется новая порция, диапазон — 4000-4500

--Третий оператор проверяет, существует ли новое разбиение и есть ли строки

Удалить\Объединить раздел

ALTER PARTITION FUNCTION partfun1() ДИАПАЗОН СЛИЯНИЯ ('2000')

Раздел 1000-2000 был удалён и объединён с 1000-3000

--Невозможно выполнить ALTER TABLE TABLE DROP PARTITIONNAME PARTITIONNAME как Oracle;

Удалить таблицу разделов и соответствующую группу файлов

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

Пример преобразования разделённой таблицы в нормальную и нормальной таблицы в разбиённую таблицу

СБРОСИТЬ СТОЛ 1;

СОЗДАТЬ ТАБЛИЦУ parttable1(

[Id] [int] ИДЕНТИЧНОСТЬ(1,1) НЕ NULL,

[Имя] [варчар] (16) НЕ НУЛЕВЫЙ,

[Id2] [интеллект] НЕ НУЛЬ

) ON partschema1(Id2);

вставить значения в parttable1 ('1',1);

вставить значения в таблице 1 ('1001',1001);

вставить значения в таблице1 ('2001', 2001);

вставить значения в таблице1 ('3001',3001);

вставить значения в таблице1 ('4001',4001);

1. Уникальное ограничение, созданное на таблице разбиения, должно содержать столбец раздела.

ALTER TABLE parttable1 ДОБАВИТЬ ОГРАНИЧЕНИЕ PK_prattable1_id КЛАСТЕРИЗОВАН ПЕРВИЧНЫЙ КЛЮЧ ([ID] ASC)

Столбец ошибки 'Id2' — это столбец разбиения индекса 'PK_prattable1_id'. Столбцы разбиения для уникального индекса должны быть подмножеством ключа индекса.

2. Создайте новый кластерный индекс в столбце раздела id2, и parttable1 всё равно будет разбитой таблицей

создать кластерный индексный CI_prattable1_id2 на parttable1(id2);

3. Столбец раздела id2 создаёт некластерный индекс, и parttable1 остаётся разделённой таблицей

индекс drop index CI_prattable1_id2 на parttable1;

создать некластерный индексный NCI_prattable1_id2 на parttable1(id2);

4. Неразбитый столбец столбца с идентификатором столбца создаёт кластерный индекс, parttable1 или разделённую таблицу, что указывает на то, что неразбитый столбец может быть столбцем индекса кластера

создать кластерный индексный CI_prattable1_id на parttable1(id);

5. Столбец раздела ID2 перестраивается в некластерный индекс и не использует схему разбиения, parttable1 всё ещё является таблицей разделов

создать некластерный индекс NCI_prattable1_id2 на parttable1(id2) с (DROP_EXISTING = ON) НА [ПЕРВИЧНОЙ];

6. Столбец разделения id2 реконструируется в кластерный индекс без условия ON, и parttable1 всё ещё остаётся таблицей разбиений

Drop index CI_prattable1_id на parttable1;

индекс дропа NCI_prattable1_id2 на Parttable1;

создать кластерный индексный CI_prattable1_id2 на parttable1(id2);

создать кластерный индекс CI_prattable1_id2 на parttable1(id2) WITH (DROP_EXISTING = ON);

7. Разделённый столбец id2 перестраивается в кластерный индекс с условием ON, но без использования схемы разбиения, и parttable1 становится неразбитой таблицей

Создать кластерный индекс CI_prattable1_id2 на parttable1(id2) С (DROP_EXISTING = ON) НА [ОСНОВНОМ];

8. Столбец раздела id2 перестраивается в кластерный индекс, и с помощью схемы разбиения parttable1 становится разделённой таблицей

создать кластерный индексный CI_prattable1_id2 на parttable1(id2) WITH (DROP_EXISTING = ON) на partschema1(Id2);

9. После удаления кластеризованного индекса из 8 выше, parttable1 всё ещё остаётся разбитой таблицей

индекс drop index CI_prattable1_id2 на parttable1;

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

создать кластерный индекс CI_prattable1_id2 на parttable1(id2) ON [ПЕРВИЧНЫЙ];

11. После удаления кластерного индекса вышеуказанного 10 parttable1 всё ещё остаётся неразбитой таблицей

индекс drop index CI_prattable1_id2 на parttable1;

12. Столбец раздела id2 вновь создаётся как некластеризованный индекс, хотя используется схема разбиения, это всё равно не разбивающаяся таблица

создать некластерный индексный NCI_prattable1_id2 на parttable1(id2) на partschema1(Id2);

Если разделённая таблица преобразуется в нормальную, ограничение первичного ключа убирается, и индекс кластера перестраивается или перестраивается в первичный ключ для полей исходного первичного ключа, но он не связан со схемой разбиения

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME С (>

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС PK_NAME НА Table_name(столбца) С (НА [ПЕРВИЧНЫЙ];

или

ИЗМЕНИТЬ ТАБЛИЦУ Table_name ДОБАВИТЬ ОГРАНИЧЕНИЕ PK_NAME ПЕРВИЧНЫЙ КЛЮЧ CLUSTERED(column) С (НА [ПЕРВИЧНЫЙ];

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

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME С (>

ИЗМЕНИТЬ ТАБЛИЦУ Table_name ДОБАВИТЬ ОГРАНИЧЕНИЕ PK_NAME ПЕРВИЧНЫЙ КЛЮЧ НЕКЛАСТЕРИЗОВАН(столбца) С (НА [ПЕРВИЧНЫЙ];

--Создать первичный ключ, но не задавать его как кластерный индекс

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКСНЫЙ index_name НА Table_name(столбца) НА Схеме Разделов (Поле Раздела)

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

Запросите общее количество строк и размер разделённой таблицы, например, таблица — CRM. EmailLog

Исполнительный sp_spaceused 'CRM. EmailLog;

Запросите информацию о разделённой таблице, сколько строк в каждом разделе, например, таблица — CRM. EmailLog

Выберите Convert(Varchar(50), ps.name

) как partition_scheme,

p.partition_number,

Конвертация (Варчар(10), ds2.name

) как группу в файлах,

convert(varchar(19), isnull(v.value, ''), 120) в range_boundary,

str(p.rows, 9) в виде строк

Из sys.indexes I

Соединить sys.partition_schemes PS на i.data_space_id = ps.data_space_id

Присоединяйтесь sys.destination_data_spaces dds

на ps.data_space_id = dds.partition_scheme_id

Присоединиться к sys.data_spaces DS2 на dds.data_space_id = ds2.data_space_id

Соединить sys.partitions p на dds.destination_id = p.partition_number

и p.object_id = i.object_id и p.index_id = i.index_id

Соединить sys.partition_functions PF на ps.function_id = pf.function_id

СЛЕВА ПРИСОЕДИНИТЬСЯ к системе. Partition_Range_values v на pf.function_id = v.function_id

и v.boundary_id = p.partition_number - pf.boundary_value_on_right

ГДЕ i.object_id = object_id('crm. EmailLog')

и i.index_id в (0, 1)

Порядок по p.partition_number

Функции разбиения запросов

выбрать * из sys.partition_functions

Просмотр архитектуры разделов

выбрать * из sys.partition_schemes