|
Секционирование со скользящим окном и архивной секцией.
#38409410
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Novosibirsk
Сообщения: 1 942
|
|
Друг попрсил скинуть макетик по секционированию.
Может еще кому будет полезен.Поправки и предложения приветствуются
Модель секционирования1. Создаём 5 секций с открытой 1-ой секцией (RIGHT-функция секционирования)
1.[архивная секция c открытой левой границей и смещающейся правой границей] (компрессированная)
2.[квартал]
3.[квартал]
4.[квартал]
5.[текущий квартал с открытой правой границей]
2. под каждую секцию создаём файловую группу
в 1-ой файловой группе [TEST_FG1] всегда лежит 1 секция ( объём данных растёт по мере сдвига окна и переноса в неё данных)
4 остальных группы [TEST_FG2],[TEST_FG3],[TEST_FG4],[TEST_FG5] ходят по кругу по алгоритму скользящего окна
3. скользящее окно
1. при завершении текущего квартала делаем слияние 1 и 2 секции удаляя 1-ую граничнуюю точку
и происходит автоматическое переливание данных из 2 секции в 1 секцию
(первоначально из фаловой группы [TEST_FG2] -> [TEST_FG1]) и сеций становиться 4
2. Назначаем освободившуюся файловую группу [TEST_FG2] следующей используемой (NEXT USED)
3. Создаём новую граничную точку начала следующего квартала.
При этом 4-ая секция ограничивается новой точкой и создаётся новая 5 -ая секция открытая справа
которая размещается в файловой группе [TEST_FG2].
Таким образом данные из самого старого квартала переносятся в архивную секцию
а окно из 4 -х кварталов смещается на 1 квартал вперёд. Скрипт создания,тестирования,удаления макета
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
/****************************************************************************************************************/
-- Создание файловых групп и файлов
-- (размер и рассположение на дисках по вкусу)
--!!!!!! ПОПРАВИТЬ ПУТЬ N'D:\TEMP\TEST_F1.ndf
/****************************************************************************************************************/
CREATE DATABASE TestP
GO
USE [TestP]
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG1]
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG2]
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG3]
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG4]
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG5]
ALTER DATABASE TestP ADD FILE
(NAME = N'TEST_F1', FILENAME = N'D:\TEMP\TEST_F1.ndf', SIZE = 1000MB, FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG1]
ALTER DATABASE TestP ADD FILE
(NAME = N'TEST_F2', FILENAME = N'D:\TEMP\TEST_F2.ndf', SIZE = 1000MB, FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG2]
ALTER DATABASE TestP ADD FILE
(NAME = N'TEST_F3', FILENAME = N'D:\TEMP\TEST_F3.ndf', SIZE = 1000MB, FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG3]
ALTER DATABASE TestP ADD FILE
(NAME = N'TEST_F4', FILENAME = N'D:\TEMP\TEST_F4.ndf', SIZE = 1000MB, FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG4]
ALTER DATABASE TestP ADD FILE
(NAME = N'TEST_F5', FILENAME = N'D:\TEMP\TEST_F5.ndf', SIZE = 1000MB, FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG5]
/****************************************************************************************************************/
-- Создание функции секционирования
/****************************************************************************************************************/
CREATE PARTITION FUNCTION PFN_YearByQuarter(datetime)
AS
RANGE RIGHT FOR VALUES ('20121001',
'20130101',
'20130401',
'20130701' -- начало текущего квартала
)
GO
/****************************************************************************************************************/
-- Создание схемы секционирования
/****************************************************************************************************************/
CREATE PARTITION SCHEME [PScheme_YearByQuarter]
AS
PARTITION PFN_YearByQuarter TO
( [TEST_FG1],[TEST_FG2],[TEST_FG3],[TEST_FG4],[TEST_FG5])
GO
/****************************************************************************************************************/
-- Создание секционированой таблицы
/****************************************************************************************************************/
CREATE TABLE [dbo].[TestPartitionTable](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL,
--..........any columns
[Value] [nvarchar](128) NULL,
CONSTRAINT [PK_TestPartitionTable] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[RecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PScheme_YearByQuarter]([Date])
) ON [PScheme_YearByQuarter]([Date])
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS (1) -- КОПРЕССИРУЕМ ПЕРВУЮ СЕКЦИЮ (архивные данные)
)
GO
CREATE NONCLUSTERED INDEX [XIE1_TestPartitionTable] ON [dbo].[TestPartitionTable]
([Value] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PScheme_YearByQuarter]([Date]) ---!!!! ИНДЕКСЫ ВЫРАВНИВАЕМ ПО СХЕМЕ СЕКЦИОНИРОВАНИЯ
GO
ALTER INDEX [XIE1_TestPartitionTable] ON [dbo].[TestPartitionTable]
REBUILD PARTITION = ALL
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ; ---!!! И КОПРЕССИРУЕМ ДЛЯ ПЕРВОЙ СЕКЦИИ
GO
/****************************************************************************************************************/
-- заполним тестовыми данными
/****************************************************************************************************************/
TRUNCATE TABLE [dbo].[TestPartitionTable]
DECLARE @d datetime
SET @d = '20120101'
WHILE @d < '20131101'
BEGIN
SET @d = DATEADD(hh,1,@d)
INSERT INTO [dbo].[TestPartitionTable]([Date],Value)
SELECT @d,convert(varchar(8),@d,112)
END
/****************************************************************************************************************/
-- Проверяем распределение данных по секциям
/****************************************************************************************************************/
SELECT
fg.name as FileGroupName
,p.partition_number
,p.data_compression_desc
,case when f.type = 'R' THEN '>= ' ELSE '<= ' END+CAST(r.value as varchar(24)) AS [range]
,p.rows
--,a.DataUsed/128 as [DataUsed_MB]
,a.DataUsed as [DataUsed_MB]
FROM sys.indexes i
JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
JOIN sys.partition_functions f ON f.function_id = ps.function_id
JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id and p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
OUTER APPLY(SELECT SUM(u.used_pages) AS DataUsed
FROM sys.allocation_units u
LEFT OUTER JOIN sys.data_spaces AS ds ON ds.data_space_id = u.data_space_id and ds.type = 'FG'
WHERE u.container_id =
CASE u.[type]
WHEN 2 THEN p.partition_id
ELSE p.hobt_id
END) a
WHERE 1=1
AND i.object_id = Object_ID('[dbo].[TestPartitionTable]')
AND (i.index_id IN (0,1))
ORDER BY p.partition_number
/****************************************************************************************************************/
-- моделируем сдвиг окна (в рабочей версии оформить процедурой и запускать job-ом в конце квартала)
/****************************************************************************************************************/
-- Проверка необходимости сдвига окна
-- если период от начала 5-ой открытой секции( 4-ая граница ) до текущей даты не превысил квартал
-- выходим
-- иначе делаем сдвиг
/* при тестировании макета проверку отключаем
DECLARE @NOW datetime
SET @NOW =getdate()
IF EXISTS(SELECT 1
FROM sys.partition_functions f
JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id=4
WHERE f.name = 'PFN_YearByQuarter'
AND DATEADD(qq,1,CAST(r.value as datetime)) >= @NOW)
RETURN
*/
-- находим файловую группу 2-ой секции (1-ый квартал после архивной секции)
-- находим первое и последнеe значение границ секций (partition_range_value)
DECLARE @FG_P2 varchar(100)
DECLARE @FirstRange DATETIME
DECLARE @LastRange DATETIME
SELECT
@FG_P2 =MAX(CASE WHEN p.partition_number =2 THEN fg.name ELSE '' END)
, @FirstRange =MAX(CASE WHEN r.boundary_id =1 THEN cast([r].value as datetime) ELSE '19000101' END)
, @LastRange =MAX(CASE WHEN r.boundary_id =4 THEN cast([r].value as datetime) ELSE '19000101' END)
FROM sys.indexes i
JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
JOIN sys.partition_functions f ON f.function_id = ps.function_id
JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id and p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
WHERE 1=1
AND i.object_id = Object_ID('[dbo].[TestPartitionTable]')
AND (i.index_id IN (0,1))
--SELECT @FG_P2,@FirstRange,@LastRange
-------------------------------------------------------------------------------------------------------
-- сливаем данные из второй секции в первую удаляя первую границу
-------------------------------------------------------------------------------------------------------
ALTER PARTITION FUNCTION PFN_YearByQuarter()
MERGE RANGE (@FirstRange)
-------------------------------------------------------------------------------------------------------
-- инициируем NEXT USED
-------------------------------------------------------------------------------------------------------
DECLARE @SQL2 NVARCHAR(500)
SET @SQL2 = 'ALTER PARTITION SCHEME [PScheme_YearByQuarter] NEXT USED '+@FG_P2
EXEC (@SQL2)
-------------------------------------------------------------------------------------------------------
-- добавляем новую граничную точку (+ квартал к последней существующей граничной точке)
-------------------------------------------------------------------------------------------------------
SET @LastRange = DATEADD(qq,1,@LastRange)
ALTER PARTITION FUNCTION PFN_YearByQuarter()
SPLIT RANGE (@LastRange)
/****************************************************************************************************************/
-- Проверяем распределение данных по секциям после сдвига
/****************************************************************************************************************/
SELECT
fg.name as FileGroupName
,p.partition_number
,p.data_compression_desc
,case when f.type = 'R' THEN '>= ' ELSE '<= ' END+CAST(r.value as varchar(24)) AS [range]
,p.rows
--,a.DataUsed/128 as [DataUsed_MB]
,a.DataUsed as [DataUsed]
FROM sys.indexes i
JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
JOIN sys.partition_functions f ON f.function_id = ps.function_id
JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id and p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
OUTER APPLY(SELECT SUM(u.used_pages) AS DataUsed
FROM sys.allocation_units u
LEFT OUTER JOIN sys.data_spaces AS ds ON ds.data_space_id = u.data_space_id and ds.type = 'FG'
WHERE u.container_id =
CASE u.[type]
WHEN 2 THEN p.partition_id
ELSE p.hobt_id
END) a
WHERE 1=1
AND i.object_id = Object_ID('[dbo].[TestPartitionTable]')
AND (i.index_id IN (0,1))
ORDER BY p.partition_number
--данные из 2 секции перенеслись в сжатый архив
--границы секций сдвинулись на квартал
--!!! можно заметить что данные которые входили в последнюю секцию но имели дату из следующего квартала
-- перенеслись в новую секцию в соответствии с новой границей
/****************************************************************************************************************/
-- очистка тестовых данных
/****************************************************************************************************************/
IF OBJECT_ID('[dbo].[TestPartitionTable]') IS NOT NULL DROP TABLE [dbo].[TestPartitionTable]
DROP PARTITION SCHEME [PScheme_YearByQuarter]
DROP PARTITION FUNCTION PFN_YearByQuarter
ALTER DATABASE TestP REMOVE FILE TEST_F1
ALTER DATABASE TestP REMOVE FILE TEST_F2
ALTER DATABASE TestP REMOVE FILE TEST_F3
ALTER DATABASE TestP REMOVE FILE TEST_F4
ALTER DATABASE TestP REMOVE FILE TEST_F5
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG1]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG2]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG3]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG4]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG5]
USE master
GO
DROP DATABASE TestP
|
|
|
