Changes between Version 4 and Version 5 of DataStoringOrganization

Timestamp:

02/08/26 16:30:23 (3 weeks ago)

Author:

185022

Comment:

--

DataStoringOrganization

@@ -4 +4 @@
 
 Целта овде е оптимизација на складирањето и перформансите преку:
-* распределба на податоците во `filegroups` по временски период
-* партиционирање по датум (година/деценија)
-* clustered индекси поставени на `partition scheme` за да се активира `partitioning`
-
-== Filegroups по временски период
-
-Креирани се `filegroups` за логичка/физичка сегментација на податоците:
-
-* `FG_Old` (пред 2000)
-* `FG_2000s` (2000-2009)
-* `FG_2010s` (2010-2019)
-* `FG_2020s` (2020-2029)
-* `FG_Future` (2030+)
-
-Причина: 
-* подобро управување со големи податочни сетови (VLDB пристап)
-* можност за backup/restore по filegroup
-* подобра I/O локалност и одржување (архивирање на стари партиции)
-
-{{{#!sql
-alter database HospitalSyntheaDB add filegroup FG_Old;
-go
-alter database HospitalSyntheaDB add file (name='FG_Old', filename='C:\Users\User\Documents\DBA\FG_Old.ndf') to filegroup FG_Old;
-go
-
-alter database HospitalSyntheaDB add filegroup FG_2000s;
-go
-alter database HospitalSyntheaDB add file (name='FG_2000s', filename='C:\Users\User\Documents\DBA\FG_2000s.ndf') to filegroup FG_2000s;
-go
-
-alter database HospitalSyntheaDB add filegroup FG_2010s;
-go
-alter database HospitalSyntheaDB add file (name='FG_2010s', filename='C:\Users\User\Documents\DBA\FG_2010s.ndf') to filegroup FG_2010s;
-go
-
-alter database HospitalSyntheaDB add filegroup FG_2020s;
-go
-alter database HospitalSyntheaDB add file (name='FG_2020s', filename='C:\Users\User\Documents\DBA\FG_2020s.ndf') to filegroup FG_2020s;
-go
-
-alter database HospitalSyntheaDB add filegroup FG_Future;
-go
-alter database HospitalSyntheaDB add file (name='FG_Future', filename='C:\Users\User\Documents\DBA\FG_Future.ndf') to filegroup FG_Future;
-go
-
-}}}
-
-== Партиционирање
-
-=== Партициски функции и шема
-
-Поради тоа што `encounters`, `medications`, `observations` и `procedures` користат ист тип за колоната за датум - `datetime2`, тие може да користат иста функција - `pf_Encounters_Year` и иста партициска шема - `ps_Encounters_Year`.
-
-Табелата `conditions` користи различен тип за датум - `date`, па за неа ќе се направи посебна партициска функција и шема.
-
-==== `pf_Encounters_Year` и `ps_Encounters_Year`
-
-Партициската функција - `pf_Encounters_Year (datetime2)` е креирана со `boundaries: 2000, 2010, 2020, 2030`.
-
-Партициската шема `ps_Encounters_Year` ги мапира партициите на горните `filegroups`.
-
-{{{#!sql
-create partition function pf_Encounters_Year (datetime2)
-as range right for values
-(
-    '2000-01-01',
-    '2010-01-01',
-    '2020-01-01',
-    '2030-01-01'
-);
-go
-
-create partition scheme ps_Encounters_Year
-as partition pf_Encounters_Year to
-(
-    FG_Old,
-    FG_2000s,
-    FG_2010s,
-    FG_2020s,
-    FG_Future
-);
-go
-}}}
-
-* `range right` значи дека граничната вредност припаѓа на десната (повисока) партиција.
-
-==== `pf_Conditions_Year` и `ps_Conditions_Year`
-
-{{{#!sql
-create partition function pf_Conditions_Year (date)
-as range right for values 
-(
-    '2000-01-01',
-    '2010-01-01',
-    '2020-01-01',
-    '2030-01-01'
-);
-go
-
-create partition scheme ps_Conditions_Year
-as partition pf_Conditions_Year to
-(
-    FG_Old,
-    FG_2000s,
-    FG_2010s,
-    FG_2020s,
-    FG_Future
-);
-go
-}}}
-
-=== Clustered индекси со партиционирање
-
-Партиционирањето реално се применува кога clustered индексот е креиран врз партициската шема.
-
-Помалите табели како `allergies` и `immunizations` не се партиционирани затоа што се помали и не зависат од временски период.
-
-==== `encounters`
-
-{{{#!sql
-create clustered index CI_Encounters_StartDate_Id
-on encounters(start, Id)
-with (drop_existing = off)
-on ps_Encounters_Year(start);
-go
-}}}
-
-==== `observations`
-
-{{{#!sql
-create clustered index CI_Observations_Date_Patient
-on observations([date], patient)
-with (drop_existing = off)
-on ps_Encounters_Year([date]);
-go
-}}}
-
-==== `medications`
-
-{{{#!sql
-create clustered index CI_Medications_Start
-on medications([start], patient)
-with (drop_existing = off)
-on ps_Encounters_Year([start]);
-go
-}}}
-
-==== `procedures`
-
-{{{#!sql
-create clustered index CI_Procedures_Start
-on procedures([start], patient)
-with(drop_existing = off)
-on ps_Encounters_Year([start]);
-go
-}}}
-
-==== `conditions`
-
-{{{#!sql
-create clustered index CI_Conditions_Start
-on conditions([start], patient)
-with (drop_existing = off)
-on ps_Conditions_Year([start]);
-go
-}}}
-
-=== Верификација
-
-Проверката е слична за сите табели. 
-
-Подолу е прикажан пример само за `encounters` табелата.
-
-==== Проверка дека датотечните групи се креирани исправно
-
-{{{#!sql
-select 
-    fg.name as FilegroupName,
-    df.name as LogicalFileName,
-    df.type_desc as FileType,
-    df.physical_name as PhysicalPath,
-    df.size / 128.0 as SizeMB
-from sys.filegroups fg
-left join sys.database_files df ON fg.data_space_id = df.data_space_id
-order by fg.name;
-go
-}}}
-
-==== Проверка дека партициската шема и функцијата работат исправно
-
-{{{#!sql
-select 
-    ps.name as PartitionScheme,
-    pf.name as PartitionFunction,
-    dds.destination_id as PartitionNumber,
-    fg.name as FilegroupName,
-    prv.value as UpperBoundaryValue
-from sys.partition_schemes ps
-join sys.partition_functions pf on ps.function_id = pf.function_id
-join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id
-join sys.filegroups fg on dds.data_space_id = fg.data_space_id
-left join sys.partition_range_values prv 
-    on pf.function_id = prv.function_id 
-    and dds.destination_id = case when pf.boundary_value_on_right = 1 then prv.boundary_id + 1 else prv.boundary_id end
-where ps.name = 'ps_Encounters_Year'
-order by dds.destination_id;
-go
------------------------------
-select
-    fg.name as FilegroupName,
-    df.name as LogicalFileName,
-    df.physical_name,
-    df.size / 128.0 as SizeMB
-from sys.filegroups fg
-    left join sys.database_files df on fg.data_space_id = df.data_space_id;
-go
-}}}
-
-==== Дистрибуција по партиции
-
-{{{#!sql
-select
-    $partition.pf_Encounters_Year([start]) as PartitionNumber,
-    count(*) as 'RowCount',
-    min([start]) as MinDate,
-    max([start]) as MaxDate
-from dbo.encounters
-group by $partition.pf_Encounters_Year([start])
-order by PartitionNumber;
-go
-}}}
-
-==== Filegroup usage
-
-{{{#!sql
-select 
-    fg.name as FilegroupName,
-    count(*) as PartitionCount
-from sys.partitions p
-join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
-join sys.data_spaces ds on i.data_space_id = ds.data_space_id
-join sys.filegroups fg on ds.data_space_id = fg.data_space_id
-where object_name(p.object_id) = 'Encounters'
-group by fg.name;
-go
-}}}
-
-==== Partition elimination
-
-{{{#!sql
-set statistics io on
-select count(*) from encounters where [start] >= '2022-01-01';
-set statistics io off;
-}}}
+* [wiki:FileGroups распределба на податоците во `filegroups` по временски период]
+* [wiki:Partitioning партиционирање по датум (година/деценија) и clustered индекси поставени на партициска шема за да се активира партиционирањето]
+* [wiki:PartitioningVerification верификација на партиционирањето]