В СУБД Oracle есть возможность секционировать представления . Основная идея проста. Пусть физическая таблица разбита на несколько таблиц (необязательно с помощью методов секционирования таблиц) в соответствии с критерием разбиения, который делает обработку запроса более производительной. Критерий разбиения будем называть предикатом секционирования . Тогда можно создать и настроить представления таким образом, чтобы с их помощью обращение к данным этих таблиц было проще для пользователя. Секция представления определяется в соответствии с диапазоном значений ключа секционирования . Запросы, которые используют диапазон значений для выборки данных из секций представления, будут получать доступ только к тем секциям, которые соответствуют диапазонам значений ключа секционирования .
Секции представления могут быть определены предикатами секционирования , заданными либо при помощи ограничения CHECK , либо с использованием предложения WHERE . Покажем, как могут быть применены оба приема, на примере несколько модифицированной таблицы "Продажи" (Sales), которую мы рассматривали в предыдущем разделе. Допустим, что данные о продажах для календарного года размещаются в четырех отдельных таблицах, каждая из которых соответствует кварталу года - Q1_Sales, Q2_Sales, Q3_Sales и Q4_Sales.
Пример 20.14 .
С помощью ограничения CHECK . С помощью команды ALTER TABLE можно добавить ограничения на колонку "Дата продажи" (s_date) каждой таблицы, чтобы ее строки соответствовали одному из кварталов года. Созданное затем представление sales дает возможность обращаться к этим таблицам, как к одной, так и ко всем вместе.
ALTER TABLE Q1_Sales ADD CONSTRAINT C0 CHECK (s_date BETWEEN "jan-1-2002" AND "mar-31-2002"); ALTER TABLE Q2_Sales ADD CONSTRAINT C1 CHECK (s_date BETWEEN "apr 1-2002" AND "jun-30-2002"); ALTER TABLE Q3_Sales ADD CONSTRAINT C2 check (s_date BETWEEN "jul-1-2002" AND "sep-30-2002"); ALTER TABLE Q4_Sales ADD CONSTRAINT C3 check (s_date BETWEEN "oct-1-2002" AND "dec-31-2002"); CREATE VIEW sales_v AS SELECT * FROM Q1_Sales UNION ALL SELECT * FROM Q2_Sales UNION ALL SELECT * FROM Q3_Sales UNION ALL SELECT * FROM Q4_Sales;
Преимуществом такого секционирования представлений является то, что предикат ограничения CHECK не оценивается для каждой строки запроса. Такие предикаты исключают вставку в таблицы строк, не соответствующих критерию предиката. Строки, соответствующие предикату секционирования , извлекаются из базы данных быстрее.
Пример 20.15 .
Секционирование представлений с помощью предложения WHERE . Создадим представление для тех же таблиц, что и в примере выше.
CREATE VIEW sales_v AS SELECT * FROM Q1_Sales WHERE s_date BETWEEN "jan-1-2002" AND "mar-31-2002" UNION ALL SELECT * FROM Q2_Sales WHERE s_date BETWEEN "apr-1-2002" AND "jun-30-2002" UNION ALL SELECT * FROM Q3_Sales WHERE s_date BETWEEN "jul-1-2002" AND "sep-30-2002" UNION ALL SELECT * FROM Q4_Sales WHERE s_date BETWEEN "oct-1-2002" AND "dec-31-2002";
Метод секционирования представлений с помощью предложения WHERE имеет некоторые недостатки. Во-первых, критерий секционирования проверяется во время выполнения для всех строк во всех секциях, которые охватываются запросом. Во-вторых, пользователи могут ошибочно вставить строку не в ту секцию, т.е. вставить строку, относящуюся к первому кварталу, в третий квартал, что приведет к неправильной выборке данных по этим кварталам.
У этого приема есть и достоинство по сравнению с использованием ограничения CHECK . Можно разместить секцию, соответствующую предикату WHERE , на удаленной базе данных. Фрагмент определения преставления приведен ниже.
Принимая решение о создании , необходимо помнить о следующих факторах.
- и удаление данных, работать на уровне секции, а не целой базовой таблицы.
- Доступ к одной из секций не оказывает никакого действия на данные в других секциях.
- СУБД Oracle обладает необходимыми встроенными возможностями для распознавания секционированных представлений .
- Секционирование представлений очень полезно при работе с таблицами, содержащими большое количество исторических данных.
Секционирование таблиц в СУБД семейства MS SQL Server
Создание секционированных таблиц
В СУБД семейства MS SQL Server также поддерживается секционирование таблиц, индексов и представлений. Однако, в отличие от СУБД семейства Oracle, секционирование в СУБД семейства MS SQL Server выполняется по унифицированной схеме.
В MS SQL Server все таблицы и индексы в БД считаются секционированными, даже если они состоят всего лишь из одной секции. Фактически, секции представляют собой базовую организационную единицу в физической архитектуре таблиц и индексов . Это означает, что логическая и физическая архитектура таблиц и индексов , включающая несколько секций, полностью отражает архитектуру таблиц и индексов , состоящих из одной секции.
Секционирование таблиц и индексов задается жестко на уровне строк (секционирование по столбцам не допускается) и позволяет осуществлять доступ через единую точку входа (имя таблицы или имя индекса ) таким образом, что в коде приложения не требуется знать число секций. Секционирование может осуществляться на базовой таблице, а также на связанных с ней индексах .
Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES . Если дата продажи была 23 июня 2006 года, то строка будет храниться в секции 2 (P2).
Теперь создадим схему секционирования . Схема секционирования отображает секции на различные файловые группы (с именами MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4 ) , как показано в следующей команде:
CREATE PARTITION SCHEME MyPartitionScheme AS MyPartitionFunction TO (MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4)
MyPartitionScheme – это имя схемы секционирования , а имя MyPartitionFunction определяет функцию секционирования . Эта команда отображает данные в секции, которые связаны с одной или несколькими файловыми группами. Строки с данными со значениями колонки "Дата продажи" (Date_of_Event date) до 1/01/05 связаны с MyFilegroup1 . Строки этой колонки со значениями, большими или равными 1/01/05 и до 1/01/07, назначены MyFilegroup2 . Строки со значениями, большими или равными 1/01/07 и до момента 1/01/09, связаны с MyFilegroup3 . Все остальные строки со значениями, большими или равными 1/01/09, связаны с MyFilegroup4 .
Для каждого набора граничных значений (которые задаются условием FOR VALUES функции секционирования ) количество секций будет равно "Количество граничных значений" + 1 секция. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT , количество секций всегда будет равно "Количество граничных значений" + 1, вплоть до 1000 секций на таблицу.
Теперь мы можем создать секционированную таблицу фактов "Продажи" (SALES). Создание секционированной таблицы мало чем отличается от создания обычной таблицы, нужно только сослаться на имя схемы секционирования в условии ON , как показано в команде ниже.
CREATE TABLE SALES (Sales_ШВ bigint identity (1, 1) primary not clustered NOT NULL, Cust_ID bigint null, Prod_ID bigint null, Store_ID bigint null, REG_ID char(10) null, Time_of_Event time null, Quantity integer not null, Amount dec(8,2) not null, Date_of_Event date NOT NULL) ON MyPartitionScheme (Date_of_Event)
Определяя имя схемы секционирования , проектировщик указывает, что эта таблица является индексы . Это позволяет проектировщику проектировать структуру индекса на основе разделенных данных, а не на основе всех данных таблицы. Создание секционированных индексов влечет за собой создание отдельных сбалансированных деревьев на секционированных индексах . В результате разделения индексов создаются индексы меньшего размера, и администратору БД или ХД становится проще их обслуживать во время изменения, добавления и удаления данных.
При создании секционированных индексов можно создавать выровненные или невыровненные индексы . Выровненные индексы подразумевают прямую связь с секционированными данными таблицы. В случае с невыровненными индексами выбирается другая схема секционирования .
Из этих двух методов предпочтителен выровненный индекс , который выбирается по умолчанию, если после создания секционированной таблицы индексы создаются без указания другой схемы секционирования . Использование выровненных индексов предоставляет необходимую гибкость для создания дополнительных секций в таблице, а также позволяет переводить принадлежность той или иной секции на другую таблицу. Для решения большинства задач, связанных с секционированием , достаточно применить для индексов схему секционирования таблицы.
Пример. 20.19 .
Создадим секционированный некластеризованный индекс на секционированной таблице "Продажи" (SALES) из предыдущего примера 20.18.
CREATE PARTITION SCHEME Index_primary_Left_Scheme AS PARTITION Index_Left_Partition ALL TO ()
Теперь выполним команду создания индекса , как показано ниже.
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_partition(Cust_ID) ON Index_primary_Left_Scheme (Cust_ID)
В этом некластеризованном индексе в качестве ключа индекса используется колонка "Идентификатор покупателя" (Cust_ID), которая не является ключом секционирования таблицы "Продажи" (SALES).
Решения о секционировании индексов принимаются проектировщиком ХД на стадии проектирования или администратором ХД на стадии эксплуатации ХД. Целью секционирования индексов является либо обеспечение производительности запросов, либо упрощение процедур сопровождения индекса .
Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).
Вступление
Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.Горизонтальное секционирование
Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.Вертикальное секционирование
Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.Поддержка секционирования в современных СУБД
Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.- Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
- Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
- MySQL - поддерживает начиная с версии 5.1.
- И так далее…
Секционирование в Postgresql
Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.Постановка задачи
База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.1. Создаем основную таблицу:
CREATE TABLE analytics.events
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);* This source code was highlighted with Source Code Highlighter .
При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.
3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);* This source code was highlighted with Source Code Highlighter .
4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт - создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;* This source code was highlighted with Source Code Highlighter .
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();* This source code was highlighted with Source Code Highlighter .
5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* This source code was highlighted with Source Code Highlighter .
Конец первой части
Итак, что мы имеем? Давайте по пунктам:1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.
О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.
UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации , но разве ж это меня остановит?).
Вступление
Вообще под секционированием в общем случае понимают не какую-то технологию, а скорее подход к проектированию БД, появившийся задолго до того, как СУБД начали поддерживать т.н. секционированные таблицы. Мысль очень простая - разделить таблицу на несколько частей меньшего размера. Различают два подвида - горизонтальное и вертикальное секционирование.Горизонтальное секционирование
Части таблицы содержат разные ее строки. Положим у нас есть таблица логов некоего абстрактного приложения - LOGS. Мы можем разбить ее на части - одна для логов за январь 2009, другая - за февраль 2009, и т.д.Вертикальное секционирование
Части таблицы содержат разные ее столбцы. Найти применение для вертикального секционирования (когда оно действительно оправдано) несколько сложнее, чем для горизонтального. В качестве сферического коня предлагаю рассмотреть такой вариант: таблица NEWS имеет столбцы ID, SHORTTEXT, LONGTEXT, и пусть поле LONGTEXT используется намного реже первых двух. В таком случае имеет смысл разбить таблицу NEWS по столбцам (создать две таблицы для SHORTTEXT и LONGTEXT соответственно, связанных первичными ключами + создать view NEWS, содержащую оба столбца). Таким образом, когда нам нужно только описание новости, СУБД не придется читать с диска еще и весь текст новости.Поддержка секционирования в современных СУБД
Большинство современных СУБД поддерживают секционирование таблиц в том или ином виде.- Oracle - поддерживает секционирование начиная с 8й версии. Работа с секциями с одной стороны очень простая (вообще можно о них не думать, работаешь как с обычной таблицей*), а с другой - все очень гибко. Секции можно разбивать на «subpartitions», удалять, делить, переносить. Поддерживаются разные варианты индексирования секционированной таблицы (глобальный индекс, секционированный индекс). Ссылочка на объемное описание.
- Microsoft SQL Server - поддержка секционирования появилась недавно (в 2005). Первое впечатление от использования - «Ну наконец-то!!:)», второе - «Работает, вроде все ок». Документация на msdn
- MySQL - поддерживает начиная с версии 5.1. Очень хорошее описание на хабре
- И так далее…
Секционирование в Postgresql
Секционирование таблиц в postgresql несколько отличается в реализации от остальных БД. Основой для секционирования служит наследование таблиц (вещь присущая исключительно postgresql). То есть, у нас должна быть основная таблица (master table), а ее секциями будут таблицы-наследники. Будем рассматривать секционирование на примере задачи, приближенной к реальности.Постановка задачи
База данных используется для сбора и анализа данных о посетителях сайта/сайтов. Объемы данных достаточно велики для того, чтобы задуматься о секционировании. При анализе в большинстве случаев используются данные за последний день.1. Создаем основную таблицу:
CREATE TABLE analytics.events
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);
2. Секционировать будем по дням по полю event_time. На каждый день будем создавать новую секцию. Именовать секции будем по правилу: analytics.events_DDMMYYYY. Вот например секция для 1го января 2010 года.
CREATE TABLE analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK (event_time >= TIMESTAMP "2010-01-01 00:00:00" AND event_time < TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS (analytics.events);* This source code was highlighted with Source Code Highlighter .
При создании секции явно задаем поле event_id (PRIMARY KEY не наследуется) и создаем CHECK CONSTRAINT на поле event_time, дабы не вставить лишнего.
3. Создаем индекс на поле event_time. При разбиении таблицы на секции, мы подразумеваем, что большинство запросов к таблице events будут использовать условие на поле event_time, так что индекс на этом поле нам очень поможет.
CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 USING btree(event_time);* This source code was highlighted with Source Code Highlighter .
4. Мы хотим добиться того, чтобы при вставке в основную таблицу, данные оказывались в предназначенной им секции. Для этого делаем следующий финт - создаем триггер, который будет управлять потоками данных.
CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NEW .event_time < TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ELSE
RAISE EXCEPTION "Date % is out of range. Fix analytics.events_insert_trigger" , NEW .event_time;
END IF ;
RETURN NULL ;
END ;
$$
LANGUAGE plpgsql;* This source code was highlighted with Source Code Highlighter .
CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();* This source code was highlighted with Source Code Highlighter .
5. Все готово, у нас теперь есть секционированная таблица analytics.events. Можем начинать яростно анализировать ее данные. Кстати, CHECK constraints мы создавали не только для того, чтобы защитить секции от некорректных данных. Postgresql может их использовать при составлении плана запроса (правда при живом индексе на event_time выигрыш это даст минимальный), достаточно воспользоваться директивой constraint_exclusion:
SET constraint_exclusion = on ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;* This source code was highlighted with Source Code Highlighter .
Конец первой части
Итак, что мы имеем? Давайте по пунктам:1. Таблицу events, разбитую на секции, анализ имеющихся данных за последние сутки становится проще и быстрее.
2. Ужас от осознания того, что все это нужно как-то поддерживать, создавать вовремя секции, не забывая менять триггер соответствующим образом.
О том как просто и беззаботно работать с секционированными таблицами расскажу во второй части.
UPD1: Заменил партиционирование на секционирование
UPD2:
По мотивам замечания одного из читателей, не имеющего, к сожалению, аккаунта на хабре:
С наследованием связано несколько моментов, которые стоит учитывать при проектировании. Секции не наследуют первичный ключ и внешние ключи на их столбцы. То есть, при создании секции, нужно явно создавать PRIMARY KEY и FOREIGN KEYs на столбцы секции. От себя замечу, что создавать FOREIGN KEY на столбцы секционированной таблицы не лучший путь. В большинстве случаев секционированная таблица является «таблицей фактов» и сама ссылается на «dimension» таблицы.
В ходе работ над большими таблицами мы постоянно сталкиваемся с проблемами с производительностью их обслуживания и обновления данных. Одним из наиболее продуктивных и удобных решений возникающих проблем является секционирование.
Если в общих словах, то секционирование – это разбиение таблицы или индекса на блоки. В зависимости от настройки секционирования блоки могут быть различных размеров, могут храниться в разных файловых группах и файлах.
У секционирования есть как преимущества, так и недостатки.
Преимущества хорошо расписаны на сайте компании Microsoft, приведу выдержку:
«Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.
- Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, такая операция, как загрузка данных из OLTP в систему OLAP, выполняется за секунды, а не за минуты и часы, как в случае несекционированных данных.
- Операции обслуживания можно выполнять быстрее с одной или несколькими секциями. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей. Например, можно сжать данные в одну или несколько секций или перестроить одну или несколько секций индекса.
- Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.
В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. SQL Server может одновременно обращаться только к одному диску, что может снизить производительность. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы
».
К недостаткам же можно отнести сложность в администрировании и поддержке работы секционированных таблиц.
Мы не будем останавливаться на вопросах реализации секционирования, так как данный вопрос очень хорошо описан на сайте компании Microsoft .
Вместо этого мы постараемся показать способ оптимизации работы секционированных таблиц, а точнее покажем оптимальный (на наш взгляд) способ обновления данных за любой промежуток времени.
Большим плюсом секционированной таблицы является физическое разграничение данных секций. Это свойство позволяет нам менять секции местами между собой или же с любой другой таблицей.
При обычном обновлении данных с использованием скользящего окна (к примеру, за месяц), нам потребуется пройти следующие этапы:
1. Найти нужные строки в большой таблице;
2. Удалить найденные строки из таблицы и индекса;
3. Вставить новые строки в таблицу, обновить индекс.
При хранении в таблице миллиардов строк эти операции займут довольно продолжительное время, мы же можем ограничиться практически одним действием: просто заменить нужную секцию на подготовленную заранее таблицу (или секцию). При этом нам не потребуется удалять или вставлять строки, а так же нужно обновлять индекс на всей большой таблице.
Перейдем от слов к делу и покажем, как же это реализовать.
1. Для начала настраиваем секционированную таблицу так, как написано в статье, указанной выше.
2. Создаем таблицы, необходимые для обмена.
Для обновления данных нам потребуется мини-копия целевой таблицы. Мини-копией она является потому, что в ней будут храниться данные, которые должны добавиться в целевую таблицу, т.е. данные всего за 1 месяц. Так же потребуется третья пустая таблица для реализации обмена данных. Зачем она нужна – объясню позже.
К мини-копии и таблице для обмена ставятся жесткие условия:
- До использования оператора SWITCH должны существовать обе таблицы. Перед выполнением операции переключения в базе данных должны существовать и таблица, откуда перемещается секция (исходная таблица), и таблица, получающая секцию (целевая таблица).
- Секция-получатель должна существовать и должна быть пустой. Если таблица добавляется как секция в уже существующую секционированную таблицу или секция перемещается из одной секционированной таблицы в другую, то секция-получатель должна существовать и быть пустой.
- Несекционированная таблица-получатель должна существовать и должна быть пустой. Если секция предназначена для формирования единой несекционированной таблицы, то необходимо, чтобы таблица, получающая новую секцию, существовала и являлась пустой несекционированной таблицей.
- Секции должны быть из одного и того же столбца. Если секция переключена из одной секционированной таблицы в другую, то обе таблицы должны быть секционированы по одному и тому же столбцу.
- Исходная и целевая таблицы должны находиться в одной и той же файловой группе. Исходная и целевая таблицы в инструкции ALTER TABLE...SWITCH должны храниться в одной и той же файловой группе, так же как и их столбцы с большими значениями. Любые соответствующие индексы, секции индексов или индексированные представления секций также должны храниться в той же файловой группе. Однако она может отличаться от файловой группы для соответствующих таблиц или других соответствующих индексов.
Объясню ограничения на нашем примере:
1. Мини-копия таблицы должна быть секционирована по тому же столбцу, что и целевая. Если мини-копия является не секционированной таблицей, то она должна хранится в той же файловой группе, что и заменяемая секция.
2. Таблица для обмена должна быть пустой и так же должна быть секционированна по тому же столбцу или же должна храниться в той же файловой группе.
3. Реализуем обмен.
Сейчас мы имеем следующее:
Таблица с данными за все времена (далее Table_A)
Таблица с данными за 1 месяц (далее Table_B)
Пустая таблица (далее Table_C)
Первым делом нам нужно узнать в какой секции у нас хранятся данные.
Узнать это можно запросом:
SELECT
count(*) as
, $PARTITION.(dt) as
, rank() over (order by $PARTITION.(dt))
FROM dbo. (nolock)
group by $PARTITION.(dt)
В этом запросе мы получаем секции, в которых есть строки с информацией. Количество можно не подсчитывать – мы это делали для проверки необходимости обмена данных. Rank же используем, чтобы можно было идти в цикле и обновлять несколько секций в одной процедуре.
Как только узнали в каких секциях у нас хранятся данные – их можно менять местами. Допустим, что данные хранятся в секции 1.
Тогда нужно выполнить следующие операции:
Поменять секции из целевой таблицы с таблицей для обмена.
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице не осталось данных в нужной нам секции, т.е. секция пуста
Поменять местами секции из целевой таблицы и мини-копии
ALTER TABLE . SWITCH PARTITION 1 TO . PARTITION 1
Теперь мы имеем следующее:
В целевой таблице появились данные за месяц, а в мини-копии теперь пустота
Очистить или удалить таблицу для обмена.
Если у вас на таблице имеется кластерный индекс, то так же не проблема. Его необходимо создать на всех 3 таблицах с секционированием по одному и тому же столбцу. При смене секций индекс автоматически обновится, не перестраиваясь.
