[Komunikacija] SQL Server Povzetek particioniranih tabel

Uradna dokumentacija

Visokonivojski koraki za ustvarjanje tabele particij

1. Ustvarite skupino datotek (podobno kot Oracle tablespace), seveda je ne morete ustvariti, lahko vse particije združite v eno skupino datotek

2. Vzpostaviti particijsko funkcijo in razdeljevati podatke glede na razpon

3. Vzpostavite shemo razdeljevanja, povežite funkcijo particije in povežite tudi skupino datotek, funkcija razdeli podatke na več razponov, morate povezati več skupin datotek, seveda pa lahko te razpone razdelkov vključite v isto skupino datotek

4. Ustvarite tabelo in povežite shemo prostorskega načrtovanja

Napaka, na katero smo naleteli

Ko neposredno z desnim klikom izvezete tabelo za izvoz strukture tabele, informacij o particijah ne morete izvoziti, podatke o particijah tabele pa lahko izvozite le z desnim klikom na skripto za generiranje naloge baze podatkov

Nekaj zaključkov iz tabele delitve:

1. Razdeljena polja ni nujno indeksirana

2. Razdeljena polja je mogoče ustvariti kot gručene ali negručene indekse

3. Razdeljena polja, ne glede na to, ali gre za gručene ali negručene indekse, se predelajo v gručene indekse brez pripadajočih shem razdeljevanja, in razdeljena tabela postane neparticionirana tabela

4. Pretvorite navadno tabelo v particirano tabelo, preprosto ustvarite gručen indeks v tabeli in uporabite shemo particioniranja na gručenem indeksu. Na primer, ko polje 1 v neparticionirani tabeli ustvari gručen indeks in je povezano s shemo particioniranja, se tabela pretvori v particirano tabelo, razdeljeno polje pa je polje 1.

5. Pretvorite particionirano tabelo v običajno tabelo; če obstaja polje particije z indeksom, ponovno zgradite particionirano polje kot gručen indeks in ga ne povezujete s shemo particij; če polje particije nima indeksa, lahko polje particije ustvari nov gručen indeks in se ne poveže s shemo particije

6. Sprememba običajne tabele v tabelo particij ali sprememba tabele particij v običajno tabelo je mogoče le z uporabo gručenih indeksov, saj so pri gručenih indeksih tabele organizacije indeksov, prerazporeditev tabel pa se izvaja z rekonstrukcijo gručenih indeksov. Če običajna tabela postane razdeljena tabela, rekonstruirajte polje particije v gručen indeks in ga povežite s shemo razdeljevanja, in če tabela particij postane običajna tabela, polje particije rekonstruirajte v gručen indeks brez povezave s shemo razdeljevanja.

7. Za ustvarjanje tabele particij mora omejitev edinstvenosti vključevati stolpec particije

8. Pri ustvarjanju sheme razdelitve je treba zagotoviti, da število skupin datotek ustreza segmentu razpona particije funkcije in da ni pomembno, če je ime skupine datotek podvojeno.

9. Funkcije razdeljevanja in sheme razdeljevanja so v podatkovni bazi, ne pa celotni instanci

10. Tabela particij je prevelika, da bi zavzela veliko prostora na disku, velikost pa se po izbrisu nekaterih polj še vedno ne spremeni

Koraki za ustvarjanje tabele particij

1.1. Primer vzpostavitve skupine datotek

spremeniti bazo podatkov test1 dodati datotečno skupino del1;

spremeniti bazo podatkov test1 dodati datotečno skupino part1000;

spremeniti bazo test1 dodati datotečno skupino part2000;

spremeni bazo test1 dodaj datotečno skupino part3000;

spremeniti bazo podatkov test1 dodati datotečno skupino part4000;

1.2. Vzpostavite primer datoteke in povežite skupino datotek

ALTER DATABASE TEST1 DODAJTE DATOTEKO(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) V DATOTEČNO SKUPINO 1;

ALTER DATABASE TEST1 DODAJ DATOTEKO(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) V DATOTEČNO SKUPINO part1000;

ALTER DATABASE TEST1 DODAJ DATOTEKO(IME = test1part2000,DATOTEKA = 'G:\test1part2000.ndf',VELIKOST = 5MB,NAJVEČJA velikost = 100MB,RAST datotek = 5MB) V DATOTEČNO SKUPINO part 2000;

ALTER DATABASE TEST1 DODAJ DATOTEKO(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) V DATOTEČNO SKUPINO part3000;

ALTER DATABASE TEST1 DODAJ DATOTEKO(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) V SKUPINO DATOTEK part4000;

2. Primer vzpostavitve funkcije particije, funkcija particije se imenuje partfun1

CREATE PARTITION FUNCTION partfun1 (int)

KOT JE RAZPON ZA VREDNOSTI ('1000', '2000', '3000', '4000')

--VALUES ('1000', '2000', '3000', '4000') pomeni, da bo tabela razdeljena na 5 con, ki so razdeljene glede na velikost vrednosti v poljih tabele, pet con je najmanjših - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-največje

3. Primer vzpostavitve sheme, pridružite particijsko funkcijo partfun1 in povežite datotečno skupino

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (del1,1000,2000,3000,4000);

--Zgrajeno na več skupinah datotek part1, part1000, part2000, part3000, part4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

ZA (1.,[PRIMARNO],[PRIMARNO],[PRIMARNO],[PRIMARNO]);

--Zgrajeno na part1, skupini datotek [PRIMARY], ni težav zamenjati del1 z [PRIMARY], tako da je podobno kot gradnja na skupini datotek [PRIMARY]

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (1. del);

--vse zgrajeno na part1 datotečni skupini

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALL TO ([PRIMARNI]);

--vse zgrajeno na datotečni skupini [PRIMARY]

4. Primer vzpostavitve particijske tabele

USTVARI TABELO parttable1(

[ID] [int] NI NULL,

[IDText] [nvarchar] (Max) NULL

[Datum] [čas za zmenek] NULL)

ON [partschema1](ID);

vstavi v parttable1 vrednosti (1,'1', getdate()-4);

vstavi vrednosti v parttable1 (1001,'1001',getdate()-3);

vstavi vrednosti v parttable1 (2001,'2001', getdate()-2);

vstavi vrednosti v parttable1 (3001,'3001', getdate()-1);

vstavi v parttable1 vrednosti (4001,'4001', getdate());

5. Preverite podatke tabele particij

IZBERI * IZ parttable1;

--Vrne vse vrstice tabele particij

IZBERITE različne $PARTITION. [partfun1] (4) IZ parttable1;

--Vrni, ki razdelijo vrstico z vrednostjo ID polja 4, pripada

IZBERI * IZ parttable1, kjer $PARTITION. [partfun1] (ID)=2

--Vrne vse vrstice v drugi particiji, ID je ID polja particije

Opomba: Ne morete uporabiti SELECT * FROM parttable1, kjer $PARTITION. [partfun1] (ID)=2 ima rezultat, kar pomeni, da gre za particirano tabelo, zadnji test 7 tega članka pa je neparticionirana tabela, vendar SELECT * FROM parttable1, kjer $PARTITION. [partfun1] (ID)=2 ima še vedno rezultat

Nove particije

1. Določite skupino datotek, ki jo je mogoče uporabiti za shemo razdelitve (dodajte skupino datotek za shemo razdelitev).

2. Spremenite funkcijo razdelitve (dodajte podatkovni razpon funkcije particije)

ALTER PARTITION SCHEME PARTSCHEMA1 NASLEDNJIČ UPORABLJEN [PRIMARNI]

ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ('4500')

Izberite p.partition_number,P.Rows iz sys.indexes in inner join sys.partitions p na p.object_id = i.object_id in i.object_id = object_id('parttable2') po 1

--Prvi stavek, če shema razdelitve uporablja ALL TO ([PRIMARY]), tega stavka ni treba izvesti

--V drugi stavek se doda nova particija, razpon je 4000-4500

--Tretja trditev preverja, ali nova delitev obstaja in ali obstajajo vrstice

Delete\Merge particija

ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000')

Delitev med 1000-2000 je bila izbrisana in združena v 1000-3000

--Ni mogoče izvesti ALTER TABLENAME, DROP PARTITIONNAME, PARTITIONNAME, podobno kot oracle;

Izbrišite tabelo particij in ustrezno skupino datotek

Vrstni red brisanja je: izbriši tabelo particij, izbriši shemo particij, izbriše funkcijo particije in nazadnje izbriše skupino datotek, pri čemer se ustrezna datoteka izbriše po izbrisu skupine datotek

Primer pretvorbe razdeljene tabele v običajno tabelo in normalne tabele v particijsko tabelo

DROP TABLE (RAZDELKA1);

USTVARI TABELO parttable1(

[Id] [int] IDENTITETA(1,1) NI NIČ,

[Ime] [varchar] (16) NI NIČLA,

[Id2] [int] NI NULL

) ON partschema1(Id2);

vstavi vrednosti v parttable1 ('1',1);

vstavi vrednosti v parttable1 ('1001', 1001);

vstavi vrednosti v parttable1 ('2001', 2001);

vstavi vrednosti v parttable1 ('3001', 3001);

vstavi vrednosti v parttable1 ('4001', 4001);

1. Edinstvena omejitev, ustvarjena v tabeli particij, mora vsebovati stolpec particije.

ALTER TABELA PARTTABLE1 DODAJ OMEJITEV PK_prattable1_id GRUČENJE PRIMARNEGA KLJUČA ([ID] ASC)

Stolpec napake 'Id2' je stolpec za razdeljevanje indeksa 'PK_prattable1_id'. Stolpci za razdeljevanje za edinstven indeks morajo biti podmnožica indeksnega ključa.

2. Ustvarite nov gručen indeks v particijskem stolpcu id2, pri čemer je parttable1 še vedno particionirana tabela

ustvarite gručen indeksni CI_prattable1_id2 na parttable1(id2);

3. Particijski stolpec id2 ustvari negručen indeks, parttable1 pa je še vedno razdeljena tabela

drop index CI_prattable1_id2 na parttable1;

ustvari negručen indeksni NCI_prattable1_id2 na parttable1(id2);

4. Stolpec id stolpca brez particij ustvari gručen indeks, parttable1 ali razdeljeno tabelo, kar kaže, da je lahko neparticioniran stolpec tudi stolpec indeksa grozdov

ustvari gručen indeksni CI_prattable1_id na parttable1(id);

5. ID2 stolpca particije je preoblikovan v negručen indeks in ne uporablja sheme razdeljevanja, parttable1 je še vedno tabela particij

ustvari negručen indeksni NCI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = ON) NA [PRIMARNO];

6. Particijski stolpec id2 je rekonstruiran v gručen indeks brez pogoja ON, in parttable1 je še vedno particijska tabela

drop index CI_prattable1_id na parttable1;

drop index NCI_prattable1_id2 na parttable1;

ustvarite gručen indeksni CI_prattable1_id2 na parttable1(id2);

ustvari gručen indeksni CI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = ON);

7. Particioniran stolpec id2 se pregradi v gručen indeks s pogojem ON, vendar brez uporabe sheme particioniranja, in parttable1 postane neparticionirana tabela

ustvari gručen indeksni CI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = VKLOPLJENO) NA [PRIMARNO];

8. Particijski stolpec id2 se pregradi v gručen indeks in z uporabo sheme particioniranja parttable1 postane particionirana tabela

ustvari gručen indeks CI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = ON) na partschema1(Id2);

9. Po izbrisu združenega indeksa v 8 zgoraj je parttable1 še vedno razdeljena tabela

drop index CI_prattable1_id2 na parttable1;

10. Particijski stolpec ID2 je ustvarjen kot gručen indeks in ne uporablja sheme particioniranja, parttable1 pa postane neparticionirana tabela

ustvari gručen indeksni CI_prattable1_id2 na parttable1(id2) NA [PRIMARNEM];

11. Po izbrisu gručenega indeksa zgornjih 10 je parttable1 še vedno neparticionirana tabela

drop index CI_prattable1_id2 na parttable1;

12. Particijski stolpec id2 je na novo ustvarjen kot negručen indeks, čeprav se uporablja shema razdeljevanja, je še vedno neparticionirana tabela

ustvari negručen indeksni NCI_prattable1_id2 na parttable1(id2) na partschema1(Id2);

Če je razdeljena tabela pretvorjena v običajno tabelo, se omejitev primarnega ključa izbriše, indeks grozda pa se ponovno sestavi ali pregradi v primarni ključ za polja izvirnega primarnega ključa, vendar ni povezan s shemo razdeljevanja

ALTER TABELA Table_name OPUSTI OMEJITEV PK_NAME Z (>

USTVARITE GRUČEN INDEKSNI PK_NAME NA Table_name(stolpec) Z (NA [PRIMARNI];

ali

ALTER TABELA Table_name DODATI OMEJITEV PK_NAME PRIMARNI KLJUČ, GRUČEN (stolpec) Z (NA [PRIMARNI];

Če se običajna tabela pretvori v razdeljeno tabelo, če se ohrani izvirni primarni ključ, se omejitev primarnega ključa izbriše, nato pa se primarni ključ ustvari brez nastavitve kot agregirani indeks, nato pa se ustvari nov agregirani indeks, pri čemer se v agregiranem indeksu uporabi shema razdeljevanja

ALTER TABELA Table_name OPUSTI OMEJITEV PK_NAME Z (>

ALTER TABELA Table_name DODAJANJE OMEJITVE PK_NAME PRIMARNI KLJUČ NEGRUČEN(stolpec) Z (NA [PRIMARNEM];

--Ustvarite primarni ključ, vendar ne nastavite kot gručen indeks

USTVARITE GRUČEN INDEKS index_name NA Table_name(stolpec) NA shemi razdelitve (polje particije)

--Ustvarite nov gručen indeks, v katerem se uporablja shema razdeljevanja

Poizvedite po skupnem številu vrstic in velikosti razdeljene tabele, na primer, tabela je CRM. EmailLog

Izvršni sp_spaceused 'CRM. EmailLog';

Poizvedite informacije o razdeljeni tabeli, koliko vrstic ima vsaka particija, na primer, tabela je CRM. EmailLog

Select Convert(varchar(50), ps.name

) kot partition_scheme,

p.partition_number,

Spreobrnit(Varchar(10), ds2.name

) kot datotečna skupina,

pretvori (varchar(19), isnull(v.value, ''), 120) kot range_boundary,

str(p.rows, 9) kot vrstice

iz sys.indexes i

join sys.partition_schemes ps on i.data_space_id = ps.data_space_id

Pridruži se sys.destination_data_spaces DDS

na ps.data_space_id = dds.partition_scheme_id

Pridružite se sys.data_spaces DS2 na dds.data_space_id = ds2.data_space_id

join sys.partitions p on dds.destination_id = p.partition_number

in p.object_id = i.object_id in p.index_id = i.index_id

Pridruži se sys.partition_functions PF na ps.function_id = pf.function_id

LEVO JOIN sys. Partition_Range_values v na pf.function_id = v.function_id

in v.boundary_id = p.partition_number - pf.boundary_value_on_right

KJER i.object_id = object_id('crm. EmailLog')

in i.index_id v (0, 1)

Naročilo p.partition_number

Funkcije razdelitve poizvedb

Izberi * iz sys.partition_functions

Oglejte si arhitekturo particij

Izberi * izmed sys.partition_schemes