SQL ja pisut teistmoodi Partitioned View

Selle loo kirjutan nii eesti kui inglise keeles.

Sel nädalal pidin mängima uue vana vahendiga: Partitioned Views. Ja jäin hätta. Õnneks ainult natukeseks.

Hakatuseks meenutus, mis on partitioned view. See on tehnika, mis võimaldab mitu ühesugust tabelit liita näiliselt üheks. Igal võivad olla omad tugiindeksid, nad võivad paikneda erinevates (ka lugemiseks vaid) failigruppides.

Partitioned view hea omadus on, et kui ta on päringu sihmärk ja päringus on sobiv where-fraas, siis päringu täitmisplaani lülitatakse ainult vajalikud tabelid. Mõnede tingimuste täitmisel on partitioned view ka uuendatav, sel juhul ta “paistab” nagu “päris tabel”, aga on palju paindlikum ja kiirem.

Väike näide:

create table T1 (kood int primary key (check kood between 1 and 100, .... )
create table T2 (kood int primary key (check kood between 101 and 200, .... )
create table T3 (kood int primary key (check kood between 201 and 300, .... )
GO
create view TT as
select * from T1
union all
select * from T2
union all
select * from T3
GO

Saadud view TT ongi partitioned view. Päring:

select ... from TT where kood = 107

puudutab vaid tabelit T2 ja päring

select ... from TT where kood between 170 and 210

puudutab vaid tabeleid T2 ja T3.

Tavaliselt tükeldatakse tabel mingi kuupäeva järgi, siis saab lausa vanemad kirjed paigutada lugemiseks ainult (read only) failigruppi ja päringud lähevad kordi kiiremaks.

Seda vigurit õpetan ja kasutan ma juba ammu. Eelduseks on, et mul on osalistel tabelitel veerg, mille kaupa tükeldada ja sellel veerul on korrektne check-constraint, mis jaotab suure pildi mittekattuvateks ja kontrollitud osadeks.

Ja see eeldab, et andmemudel on minu käes ja minu kontrolli all. Aga kui ei ole?

Sattusin lahendama ülesannet, kus osalisi tabeleid on sadu, nende andmemudelit ma muuta ei saa ning nad on jaotatud ettevõtete ja aastate kaupa. Tabeli nimi määrab, millise ettevõtte ja millise aasta andmed seal on. Aruandluse jaoks on aga vaja teha vaade, mis haarab kokku andmed mõnedest või kõigist sellistest tabelitest.

Lihtsuse mõttes teen näited väikese arvu tabelitega: TabXX2012, TabXX2013, Tabxx2014, TabYY2012, TabYY2013, TabYY2014.

Kui ma panen kokku union view kujul

create view TabAll
as
select 'XX' as Ettevote, 2012 as Aasta, * from TabXX2012
union all
select 'XX' as Ettevote, 2013 as Aasta, * from TabXX2013
union all
select 'XX' as Ettevote, 2014 as Aasta, * from TabXX2014
union all
select 'YY' as Ettevote, 2012 as Aasta, * from TabYY2012
union all
select 'YY' as Ettevote, 2013 as Aasta, * from TabYY2013
union all
select 'YY' as Ettevote, 2014 as Aasta, * from TabYY2014

siis sellest pole palju kasu. Päring

select * from TabAll where Aasta = 2013

päringuplaan näitab, et läbi käiakse kõik tabelid. Päringu optimeeria “ei näe”, et aasta 2013 sialdub ainult kahes tabelis. Leidsin aga vahva lahenduse, kõnealune view tuleb teha pisut keerukam (neid viewsid teeb mulle 1 script – ise ma neid ei tee)

create view TabAll
as
select * from (select 'XX' as Ettevote, 2012 as Aasta, * from TabXX2012) T where Ettevote = 'XX' and Aasta = 2012
union all
select * from (select 'XX' as Ettevote, 2013 as Aasta, * from TabXX2013) T where Ettevote = 'XX' and Aasta = 2013
union all
select * from (select 'XX' as Ettevote, 2014 as Aasta, * from TabXX2014) T where Ettevote = 'XX' and Aasta = 2014
union all
select * from (select 'YY' as Ettevote, 2012 as Aasta, * from TabYY2012) T where Ettevote = 'YY' and Aasta = 2012
union all
select * from (select 'YY' as Ettevote, 2013 as Aasta, * from TabYY2013) T where Ettevote = 'YY' and Aasta = 2013
union all
select * from (select 'YY' as Ettevote, 2014 as Aasta, * from TabYY2014) T where Ettevote = 'YY' and Aasta = 2014

Selline view töötab samamoodi nagu “klassikaline” partitioned view. Päring

select * from TabAll where Aasta = 2012

puudutab vaid tabeleid TabXX2012 ja TabYY2012. Saladus peitub selles where lauses. Päringu optimeerija näeb where lauses olevat predikaati ja oskab optimeerida nii, et pöördutakse vaid nende osade poole, kus where predikaadid “kattuvad”.

Lisan peagi ka jätkupostituse, kus kirjeldan, kuidas sellist viewd ja tehnikat veel ära kasutada saab.