Секционирование. Очистка больших таблиц.

Источник: dbasimple

Вячеслав

Одно из возможных применений секционирование - это обслуживание больших таблиц, такое как удаление старых данных, оставлять только актуальные.

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

Имелась таблица логов системы, количество строк переваливало за 65 млн. строк. Необходимо было оставить только актуальные данные, при том , что таблица должна быть доступна для вставки.. Самих актуальных данных было уже более 10 млн. строк. Конечно можно было настроить задание по очистке таблицы порциями, но это и долго и "просто").Решил использовать секционирование .

Итак, план был таков:

1) Переименовать текущую таблицу логов

2) Создать новую таблицу с именем исходной таблицы логов

3) В переименованной таблице создать секции со старыми данными и актуальными

4) Секцию с актуальными данными присоединить к таблице логов

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

Здесь я немного схитрил, я переименовывал таблицу логово два раза, второй раз после завершения создания секций , переименовывается таблица логов, создается новая таблица логов и тут идет присоединение секции к таблице. А затем просто из второй переименованной таблицы скопированы данные в новую таблицу логов( за время создания секций это количество было более 5000 строк).

Меньше слов, больше кода:

1) Переименование таблицы

USE [DB]

GO
EXEC sp_rename 'dbo.logs', 'logs1';
GO

2) Создание аналогичной таблицы

USE [DB]

GO
CREATE TABLE [dbo].[logs](
      [novell] [nvarchar](50) NULL,
      [ip] [nvarchar](50) NULL,
      [eventtime] [datetime] NULL,
      [num] [nvarchar](100) NULL,
      [id] [nvarchar](100) NULL,
      [ppa] [nvarchar](100) NULL
)
GO

3) Создание функции секционировании, схемы и создание секций.

USE [DB]

GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [partFuncLogs](datetime)
AS RANGE RIGHT FOR VALUES (N'2014-01-01T00:00:00.001')

CREATE PARTITION SCHEME [partSchemeLogs]
AS PARTITION [partFuncLogs] TO ([PRIMARY], [PRIMARY])

CREATE CLUSTERED INDEX [ClusteredIndex_on_partSchemeLogs_635265953843153227]ON [dbo].[logs1]
(
[eventtime]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE= OFF) ON [partSchemeLogs]([eventtime])

DROP INDEX [ClusteredIndex_on_partSchemeLogs_635265953843153227] ON [dbo].[logs1] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

4)Присоединений секции к новой таблице логово

ALTER TABLE dbo.logs1 SWITCH PARTITION 2 TO dbo.logs

В текущем коде пропущены пункты второго переименования и пересоздания таблицы логов, но они повторяют пункты 1 и 2. Так же хочу отметить, что в этот момент были транзакции ожидавшие вставки в таблицу логов. После создания таблиц, новых данных было порядка 100 строк. Само создание и присоединение секции занимает доли секунды.

А это так, для информации , узнать в какой секции данные )

SELECT $PARTITION.partFuncLogs ('2014-01-01T09:31:08.657') (Написал для себя , чтобы запомнить);

Благодаря такому решению, мы имеем:

- Таблица была доступна.
- Данный разделены
- Потрачено незначительное время.

Несколько замечание:

- функция секционирования доступна в редакции Enterprise

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

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

Хороших вам таблиц. Спасибо, если помог.