In Columnstore Indexex habe ich bereits den neuen spaltenorientierten Index beschrieben. Ein wichtige Tatsache muss allerdings noch beachtet werden: Tabellen mit Columnstored Indizes sind nicht update fähig. Wer denkt, dass dies ein Mag-ich-nicht Feature ist, hat zwar Recht, aber dafür gibt es 2 nette Workarounds:
- Workaround 1: Drop table … create table
kein weitere Kommentar dazu
2. Workaround 2: Partitionswechsel
Die Idee, die hier dahinter steckt, ist die Verwendung der Partitionierung. Daten werden in eine partitionierte Tabelle (mal Tab A genannt) geschrieben, auf die der Columnstore Index gelegt wird. Der Trick ist nun eine weitere Tabelle (Tab B) anzulegen, die ebenfalls auf das selbe Partitionierungsschema gelegt wird, wie Tab A. Mittels eines Partitionswechsels (switch) kann Tab B zu Tab A hinzugefügt werden. Verwaltungsaufwand dafür so gut wie, da keine Datenverschiebung stattfindet, sondern lediglich die “Zeiger” auf die Tabellen geändert werden.
Kompliziert? Nicht wirklich…Sehen wir uns das mal an:
BEISPIEL
Zunächst wird eine Tabelle Umsatz erstellt mit Clustered Index und Columnstore Index. Ca 1. Mio Daten, die sich auf einen Datumsbereich von 1.9.2011 bis inkl. 4.9.2011 bezieht. Die Tabellen bzw der Clustered Index werden pro Tag partitioiert.
1: --Partitionierungsfunktion
2: create partition function pf_datum (date) as range left for values
3: ('20110901', '20110902', '20110903', '20110904','20110905');
4: go
5:
6: --Partitionierungsschema
7: create partition scheme ps_datumPrim as partition pf_datum all to ([PRIMARY]);
8: go
9:
10: --Demotabelle
11: create table Umsatz (
12: uid int not null identity (1,1),
13: UDatum date not null,
14: PrID smallint not null,
15: KundenID int not null,
16: Summe money)
17: on ps_datumPrim(Udatum);
18: go
19:
20: --Clustered index
21: create unique clustered index
22: IX_Udatum on Umsatz (Udatum, PrId, KundenID, summe) on ps_datumPrim(Udatum);
23:
24:
25: -- 1 mio Datensätze von 12.7.2011 bis 15.7.2011
26: set nocount on;
27: go
28: declare @i int = 0;
29: begin transaction;
30: while @i < 1000000
31: begin
32: declare @Udatum date = dateadd(day, @i /250000.00, '20110901');
33: insert into Umsatz (Udatum, PrID, KundenID, summe)
34: values (@Udatum, rand()*10000, rand()*100 + 100, rand()* 10.000+1);
35: set @i += 1;
36: if @i % 10000 = 0
37: begin
38: raiserror (N'Inserted %d', 0, 1, @i);
39: commit;
40: begin tran;
41: end
42: end
43: commit;
44: go
45:
46: --Wir sehen 250000 Datensätze pro Zeile
47: --Der Index wird natürlich pro Partition abgebildet
48: select * from sys.system_internals_partitions p
49: where p.object_id = object_id('umsatz');
50:
51: select au.* from sys.system_internals_allocation_units au
52: join sys.system_internals_partitions p
53: on p.partition_id = au.container_id
54: where p.object_id = object_id('umsatz');
55: go
Ein Blick auf die partitionierte Daten vor dem Columnstored Index
Ein Blick nach dem Anlegen des Columnstored Index
Wollte man nun Abfragen gegen ein bestimmtes Datum testen, würde man durch den Columnstore INdex einen Vorteil von ca. dem 4-fachen haben. Statt knapp 1640 Seiten werden nur noch 360 Seiten gelesen.
1: set statistics io on;
2: select count(*), sum(summe) from umsatz where Udatum = '1.09.2011'
Im Ausführungsplan läßt sich der Index beobachten:
Nun kommen neue Daten dazu. Diese legen wir zunächst in einer neuen Tabellen ab, die später nicht mehr gebraucht wird. Tabelle und Indizes sind nicht partitioniert. Eines kleines Problem.. die ID in der Tabelle dürfen natürlich nicht doppelt vorkommen, da wir anschließend die Daten aus der Staging-Tabelle in die Umsatz Tabelle switchen wollen.
1: ---UPDATE Workaround: Partitionswechsel oder löschen und neuerstellen
2: create table Umsatz_Stagingtable (
3: uid int not null identity (1000000,1),
4: UDatum date not null,
5: PrID smallint not null,
6: KundenID int not null,
7: Summe money,
8: constraint check_datum check (udatum = '20110905'))
9: on [Primary];
10: go
11:
12:
13: create unique clustered index IX_UDatum_staging
14: on Umsatz_stagingtable (Udatum, PrID, Kundenid, Summe) on [Primary];
15: go
16:
17: --250.000 Datensätze für 5.9.2011
18: set nocount on
19: go
20: declare @i int = 0;
21: begin transaction;
22: while @i < 250000
23: begin
24: insert into Umsatz_Stagingtable(Udatum, prid, Kundenid, summe)
25: values ('20110905', rand()*10000, rand()*100 + 100, rand()* 10.000+1);
26: set @i += 1;
27: if @i % 10000 = 0
28: begin
29: raiserror (N'Inserted %d', 0, 1, @i);
30: commit;
31: begin tran;
32: end
33: end
34: commit;
35: go
36:
37: --Columnstore Index
38: create columnstore index cs_Umsatz_staging
39: on Umsatz_stagingtable (Udatum, PrID, Kundenid, Summe)
40: go
In diese Stagingtabelle kommen also die neuen Werte rein. Die Tabellen liegt momentan nicht in der gleichen Partition auf dem Datenträger. Allersdings liegen sie tatsächlich auf dem gleichen Datenträger. Das wird gleich noch wichtig werden.
Das entscheidende geschieht nun hier:
1: alter partition scheme ps_datumPrim next used [PRIMARY];
2: go
3: alter partition function pf_datum() split range ('6.9.2011');
4: go
5: alter table umsatz_stagingtable switch to umsatz partition $PARTITION.PF_datum('20110905');
6: go
In 1: wird zunächst die notwendige Erweiterung der Dateigruppe bekannt gegeben. in 2: können wir den anschließenden Split in der partitionierten Tabelle Umsatz erreichen. Ab diesem Zeitpunkt haben wir eine freie Partition in der Tabelle Umsatz für Daten des 5.9.2011. Der Trick besteht nun darin, dem SQL Server zu sagen, dass die freie Partition der Tabelle Umsatz_Staging entpricht. Oder anders gesagt: Wir ändern die Information in der Datenbank so, dass die freien Partition genau der Tabelle Umsatz_Staging entpricht. (hier müßte eine gewisse andächtige Stille eintreten)
Das Ergebnis ist, dass die Stagingtabelle leer sein wird. Die Daten dieser Tabelle sind nun in der Umsatztabelle, allerdings wurden keine Daten verschoben. Wir haben schließlich nur die Ortsangaben geändert. Daher ist es wichtig die Stagingtabelle auf die gleichen Dateigruppe zu legen. Der Switch Vorgang selbst dauert daher nur Millisekunden.
Cool…
PS: Unter mehreren verborgenen Händen soll der Columnstore Index in der Final Version updatebar sein
