Tabulu horizontālā dalīšana (Partitioned Tables)

Raksts par dalītām tabulām- kad to būtu vērts darīt (vismaz- apsvērt darīt), daži trūkumi vai nianses pirms vispār domāt par tabulu dalīšanu, arī mazliet par attīstības vēsturi. Galvenokārt visa informācija rakstā ir ņemta no šejienes.

Kad par to domāt?
Tabulas dala (veido Partitioned Tables) vairāku iemelsu dēļ. Viens no variantiem ir tad, kad tabulas dati tiek dažādi izmantoti-  iepriekšējā gada dati tiek reti lietoti savukārt šī gada dati tiek regulāri lasīti un mainīti. Vēl tabulu dalīšanu izmanto gadījumos, kad ir nepieciešams sabalansēt tabulas noslodzi, piemēram, visi dati tiek bieži lietoti, tomēr tos var sadalīt vairākās daļās balstoties uz kādu loģisku kritēriju- teiksim- klientu tabulu sadalīt pēc klienta nosaukuma sākuma (nu varbūt ne pats veiksmīgākais piemērs).

Vizuāli tas varētu izskatīties apmēram tā:

Abas tabulas ir loģiska datu struktūra. Fiziski savukārt tās ir domāts sadalīt daļās- pirmajā gadījumā tāpēc, ka nesenie dati tiek izmantoti daudz biežāk nekā vēsturiskie dati. Otrajā gadījumā dati ir sadalīti tāpēc, ka ir milzīga klientu bāze (kā jau attēlā redzams) un lai pēc iespējas optimizētu piekļuvi un darbību ar to, tabula tiek sadalīta vairākās daļās.

Varbūt trūkumi?
Domājot par tabulu dalīšanu vairākās fiziskās daļās, tomēr jāņem vērā, ka līdz ar būtiskiem ātrdarbības uzlabojumiem (un daudzām papildus optimizēšanas iespējām) nāk līdzi papildus administratīvais slogs. Arī nepieciešams rakstīt garākus skritpus. Un jo vairāk jādara, jo lielāka iespēja kļūdīties..

Arī datu bāzes tabulu izmērs ir jāņem vērā. Dalīt tabulas vairākās daļās ir nozīme tad, ja tabula ir gana liela (atkal- ļoti subjektīvs lielums), bet lai būtu konkrētāk- ja neapmierina ātrdarbība darbojoties ar tabulu datiem. Piemēram, lēna piekļuve dēļ resursu slēgšanas (locking).

SQL Server tabulu dalīšana (partitioning) nav pieejama Express versijās (šeit var apskatīties ko SQL Express nedara)

Nedaudz vēstures
Tabulu dalīšanu pavisam senos laikos (līdz SQL Server 6.5) varēja realizēt tikai veidojot vairākas tabulas, ar procedūru un skatu palīdzību regulējot piekļuvi pareizajai tabulai. Piemēram- gribam sadalīt klientu tabulu daļās atkarībā no sākuma burta? Veidojam n tabulas, veidojam procedūru, kas atkarībā no klienta nosaukuma ievieto datus pareizajā tabulā, veidojam procedūras kas meklē klientus pareizajā tabulā.. utt utt.. Nemaz neizskatās labi..
SQL Server 7.0 jau bija gudrāks- bija iespējams veidot dalītos skatus. Ideja tāda, ka skats sastāv no vairākām tabulām, kas savienotas ar 'Union All'. Izmantojos ierobežojumus (constraints) uz kolonnām, vaicājuma izpildes optimizators varēja pats "izdomāt", no kuras tabulas ņemt datus, pārējās tabulas neaiztiekot. Tātad- nolasot klientu, kas sākas ar burtu "A", SQL Server pats izdomā, ka jālasa dati tikai no tās tabulas, kurā klienta nosaukums sākas ar "A" burtu. Iepriekšējās versijās tika pārskatītas visas tabulas šāda skata gadījumā. Tomēr tas neatrisināja datu ievietošanas, dzēšanas un mainīšanas problēmas- joprojām jāmaina dati pareizajā tabulā.
SQL Server 2000 savukārt atrisināja arī šo problēmu. Šajā versijā dalītās tabulas, kas apvienotas skatā (ar ierobežojumu palīdzību) zināja ne vien no kuras tabulas datus nolasīt, bet arī kurā tabulā dati ir jāmaina izpildot Insert, Update un Delete komandas. Izveidoju arī nelielu piemēru.
Tātad, risinājumam ir nepieciešamas divas (vai vairākas) tabulas. Šajā gadījumā izveidoju divas tabulas, kur vienā glabājas dati- senāki par 2005 gadu un otrā- viss pārējais:

Create Table dbo.TestT1
(
    TestID int,
    gads int CHECK (gads>=2005),
    teksts nvarchar(50),
    constraint PKpartA primary key(TestID, Gads)
)
go
Create Table dbo.TestT2
(
    TestID int,
    gads int CHECK (gads<2005),
    teksts nvarchar(50),
    constraint PKpartB primary key(TestID, Gads)
)   

Kad ir izveidotas tabulas, tās ir jāapvieno vienā skatā. Pateicoties tam, ka kolonnai Gads ir veidoti ierobežojumi (check constraints) un gads ir arī primārās atslēgas daļa, skats "zinās" kurā vietā dati ir jāievieto un no kuras tabulas jāizgūst:

Create view dbo.TestT
As
select * from dbo.testt1
union all
select * from dbo.testt2

Tālāk jau var izpildīt kādu eksperimentu, lai pārliecinātos, ka tas tiešām strādā:

insert into dbo.TestT(testid, gads, Teksts) Values (2, 2006, 'lalala')
select * from dbo.TestT
select gads, Teksts from dbo.testt1
select gads, Teksts from dbo.testt2

Tāpat interesanti ir apskatīt izpildes plānu- piemēram šādam vaicājumam:

select * from dbo.TestT where gads < 2005

Risinājums ir visai glīts, tomēr realizācija joprojām ir salīdzinoši grūti veidojama. Lai arī šādā gadījumā ir iespējams iespējams veidot datu bāzes lietojumus, kas pilnīgi neko nenojauš par to, kā dati tiek fiziski glabāti, datu bāzes uzturēšana joprojām ir salīdzinoši sareģīts process, dēļ daudzajiem objektiem (piemēram- ko darīt situācijā, ja tabulai vajag jaunu kolonnu?). Vēl ir nianse, ka katra tabula, kas paslēpta zem skata, var saturēt savus indeksus, līdz ar to veidojot izpildes plānus, katrā gadījumā tas var būt pilnīgi savādāks.

SQL Server 2005 dalītās tabulas (Partitioned Tables)
Par dalītajām tabulām droši vien šis nebūs pēdējais rakstiņš, tāpēc šeit tikai tāds kā ievads, pārāk neiedziļinoties niansēs.
Tāda vispārīgā ideja ir tāda, ka iepriekš mums katrai daļai vajadzēja veidot savu tabulu, ko vēlāk ar skata palīdzību varēja apvienot veidojot vienu loģisku tabulu (ar skata palīdzību). To var darīt joprojām, bet SQL Server 2005 ideju turpina pilnveidot. Tagad var tikt veidota viena tabula, kura savukārt tiek fiziski sadalīta vairākās daļās.
Idejas realizācija notiek apmēram tā- no sākuma tiek izveidota dalīšanas funkcija, kas nosaka, pēc kāda kritērija loģiski dalīt datus. Vēlāk tiek veidota dalīšanas shēma, kuras uzdevums ir katrai daļai norādīt tās fizisko atrašanās vietu (filegroup). Fiziskā atrašanās (labākajā gadījumā) ir- katrai  loģiskajai daļai sava fiziskā, bet ne obligāti. Kad ir izveidota funkcija un shēma, tiek veidota dalītā tabula (partitioned table).

Turpinājumā neliels piemērs kā to var realizēt. Kā jau tika minēts- pirmais uzdevums ir izveidot funkciju, kas datus loģiski sadalīs daļās:

CREATE PARTITION FUNCTION myTestRange (int)
AS RANGE Right FOR VALUES (2005);

Šajā gadījumā funkcija saucas 'myTestRange', ieeja būs int datu tips. funkcija dalīs datus atkarībā no ieejas parametra vērtības. ja skaitlis būs lielāks vai vienāds par 2005, tad dati tiks novietoti vienā daļā, pārējos gadījumos- otrā. Kad funkcija ir izveidota, tad veido dalīšanas shēmu, kas nosaka kurā failu grupā nonāks dati:

CREATE PARTITION SCHEME MyTestRangeScheme
AS PARTITION myTestRange
ALL TO ([PRIMARY]);

Shēmas nosaukums ir 'MyTestRangeScheme'. Šī shēma ir izveidota tā, lai visi dati nonāktu vienā un tajā pašā failu grupā (noklusētajā). Fiziski gan joprojām tiek veidotas vairākas datu kopas. Jāpiebilst, ka vienai funkcijai var būt vairākas shēmas, kur novietot datus. Kad ir funkcija un shēma, tad veido dalīto tabulu, norādot shēmu pēc kā dalīt datus:

Create Table dbo.Test
(
    TestID int identity,
    gads int,
    teksts nvarchar(50),
    constraint PK_Test primary key(TestID, gads)
)
On MyTestRangeScheme(gads)

Būtiskākā atšķirība no parastas tabulas ir tāda, ka beigās ir nevis noklusētais 'On Primary', bet norādīta shēma, kā dalīt datus. Shēmai tiek padots tabulas lauks, pēc kuras tiek noteikts kurā fiziskā daļā dati tiek novietoti.
Tad nelieli testi, kā pārbaudīt, vai tas strādā. ievietosim tabulā trīs rindas:

Insert into dbo.Test(gads, teksts)
values (2004, 'viens')
Insert into dbo.Test(gads, teksts)
values (2006, 'divi')
Insert into dbo.Test(gads, teksts)
values (2007, 'trīs')

Izpildot vaicājumu no tabulas, joprojām viss izskatās kā parasti. Bet mēs varam paskatīties, kā dati ir sadalīti pa daļām (tiek parādītas daļas, rindu skaits tajā):

SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='Test';

Dalīšanas shēmu un funkciju joprojām var mainīt. Tas notiek apmēram šādi (nosaka kur fiziski nolikt nākamo tabulas fizisko daļu):

ALTER PARTITION SCHEME MyTestRangeScheme
NEXT USED [Primary]

Pēc tam sadalam intervālu >= 2005 divās daļās- pirmā, kur dati >= 2005 un < 2007 un otrā- kur dati >= 2007. tas notiek šādi:

ALTER PARTITION FUNCTION myTestRange ()
SPLIT RANGE (2007);

Tālāk apskatot daļas var redzēt, ka dati ir sadalīti trijās daļās, katrā daļā ir viena rindiņa:

SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='Test';

Lai pēc sevis sakoptu jāizpilda šāds skripts:

drop table dbo.test
drop PARTITION SCHEME MyTestRangeScheme
drop partition function myTestRange

Tā īsumā izskatās tabulu dalīšana. Doma šo tēmu turpināt kādā no nākamajiem rakstiem.