Создание секционированных таблиц




В учебной базе данных EducationDatabase накапливаются данные по продажам; текущим (последним обрабатываемым) годом продаж является 2011. Проведем секционирование таблицы Bill. Атрибутом секционирования выберем дату продажи товара Date. Каждая секция будет содержать сведения о продажах за определенный месяц 2011 года, за исключением последней секции, которая будет содержать сведения о продажах в последнем месяце 2011 года и позже. Дополнительно одна секция будет содержать продажи, совершенные до 2011 года.

Порядок секционирования таблиц:

1. Для таблицы Bill в контекстном меню выберите раздел Storage -> Create Partition. Откроется окно мастера секционирования.

2. На первом шаге работы мастера выберите столбец таблицы, на основе которого будет производиться секционирование. Выберем столбец Date (рисунок 1).

Рисунок 1 – Выбор столбца секционирования таблицы

3. На шаге Select a Partition Function укажите имя функции секционирования (рисунок 2).

Рисунок 2 – Создание функции секционирования

4. На шаге Select a Partition Scheme укажите имя схемы секционирования (рисунок 3).

Рисунок 3 – Создание схемы секционирования

5. На шаге Map Partitions требуется определить границы секций и связать их с файловыми группами. Сначала необходимо определиться с видом интервалов секционирования. Если выбрана опция Left Boundary, будут формироваться интервалы вида (предыдущее значение, текущее значение]. Если выбрана опция Right Boundary, будут формироваться интервалы вида [предыдущее значение, текущее значение). Для каждой секции может быть назначена своя файловая группа, либо несколько секций (возможно все) будут связаны с одной файловой группой. Выберем файловую группу PRIMARY для всех секций. Следует учитывать, что значительного выигрыша в производительности и пропускной способности возможно достичь при размещении различных секций в разных файловых группах на различных физических дисках. При этом возможно обеспечить, для определенных запросов, параллельный доступ к массивам дисков. Правые границы диапазонов секций указываются в колонке Boundary, при этом необходимо указать границы каждой секции. Возможен автоматический расчет границ диапазонов с использованием кнопки Set Boundaries… В последнем случае СУБД автоматически определит минимальное и максимальное значение в колонке секционирования и предложит размер секции (по месяцам, по кварталам и т.п.). Пользователь должен самостоятельно определить требуемые верхнюю и нижнюю границы секционирования (нас интересует 2011 год) и определиться с размером секции (выберем по месяцам). Кнопка Estimate Storage используется для расчета характеристик секций: количество записей, необходимый объем памяти, занимаемый объем памяти. В итоге карта секционировании примет вид, представленный на рисунке 4.

Рисунок 4 – Карта секционирования

6. На шаге Select an Output Option выберите опцию Create Script, открыть скрипт в новом окне редактирования запросов Script to New Query Window (рисунок 5). Другими возможными опциями являются: выполнить немедленно Run Immediately, выполнить по расписанию Schedule.

Рисунок 5 – Опции выгрузки

7. На заключительном шаге Review Summary проверьте итоговую информацию о секционировании и нажмите кнопку Finish.

Выполните сформированный скрипт:

USE [EducationDatabase]

GO

BEGIN TRANSACTION

 

CREATE PARTITION FUNCTION [billPartitionFun](datetime)

AS RANGE RIGHT FOR

VALUES (N'2011-01-01T00:00:00', N'2011-02-01T00:00:00',

N'2011-03-01T00:00:00', N'2011-04-01T00:00:00',

N'2011-05-01T00:00:00', N'2011-06-01T00:00:00',

N'2011-07-01T00:00:00', N'2011-08-01T00:00:00',

N'2011-09-01T00:00:00')

 

CREATE PARTITION SCHEME [billPartitionScheme] AS PARTITION [billPartitionFun] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

 

CREATE CLUSTERED INDEX [ClusteredIndex_on_billPartitionScheme_634648357407858521] ON [dbo].[Bill]

([Date]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [billPartitionScheme]([Date])

 

COMMIT TRANSACTION

Обратите внимание, что СУБД создает новый кластерный индекс по столбцу Date взамен кластерного индекса по первичному ключу-счетчику BillId, что предполагает реорганизацию данных в порядке сортировки по датам покупок и разделения отсортированных данных по секциям.

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

Выполним запрос:

select * from bill

where date between '05.02.2011' and '20.02.2011'

В данном случае все результирующие записи располагаются в одной секции, связанной с месяцем «февраль 2011 г.». Поиск записей осуществляется в одной секции (рисунок 6) с использованием кластерного индекса по полю date (рисунок 7).

Рисунок 6 – Описание операции поиска по кластерному индексу

Рисунок 7 – План выполнения запроса

Выполним следующий запрос:

select * from bill

where date between '05.02.2011' and '20.03.2011'

Теперь результирующие записи располагаются в двух секциях, связанных с месяцами «февраль 2011 г.» и «март 2011 г.». План запроса изменился за счет появления дополнительной операции чтения внутренней таблицы констант с номерами секций (рисунок 8). Операция поиска по кластерному индексу выполняется два раз по числу секций, участвующих в запросе (рисунок 9). Новый план выполнения запроса представлен на рисунке 10.

Рисунок 8 – Операция сканирования внутренней таблицы констант

Рисунок 9 – Операция поиска по кластерному индексу

Рисунок 10 – План выполнения запроса

Представленные два примера наглядно демонстрируют преимущества секционирования данных – интерфейс доступа к данным не меняется (используется одинаковый синтаксис SQL-запросов), но оптимизатор запросов прозрачным для разработчика и приложения способом меняется способ доступа к данным с возможностью параллельного выполнения отдельных операторов (в последнем случае – поиск по кластерному индексу).

 

Содержание работы

1. Напишите 2 запроса соединения таблиц Bill и BillItem в первом случае с фильтрацией Bill.date between '05.02.2011' and '20.02.2011', во втором случае с фильтрацией Bill.date between '05.02.2011' and '20.03.2011'. Изучите планы выполнения запросов, объясните, почему они различны. Сохраните планы выполнения запросов.

2. Проведите секционирование таблицы BillItem по сценарию, описанному в данной лабораторной работе. В качестве ключа секционирования используйте BillItem.Date. Объясните, являются ли секционированные таблицы Bill и BillItem выровненными?

3. Выполните запросы из п.1 и изучите планы их выполнения. Объясните, что изменилось в планах выполнения запросов.

 

 

Литература

1. Секционированные таблицы и индексы SQL Server 2005, По материалам статьи Kimberly L. Tripp: SQL Server 2005. Partitioned Tables and Indexes, Перевод Алексея Сафонова, https://www.sql.ru/articles/mssql/2005/073102PartitionedTablesAndIndexes.shtml

2. Microsoft MSDN, https://msdn.microsoft.com/ru-ru/

 



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2017-11-19 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: