[Komunikacja] Podsumowanie tabel partycjonowanych w SQL Server

Oficjalna dokumentacja

Kroki wysokiego poziomu do stworzenia tabeli partycji

1. Stwórz grupę plików (podobną do Oracle Tablespace), oczywiście nie możesz jej stworzyć, możesz umieścić wszystkie partycje w jednej grupie plików

2. Ustalić funkcję partycjonowania i rozdzielić dane według zakresu

3. Ustal schemat partycji, powiązaj funkcję partycji, a także powiązaj grupę plików, funkcja partycji dzieli dane na kilka zakresów, musisz powiązać kilka grup plików, oczywiście możesz też umieścić te zakresy w tej samej grupie plików

4. Stwórz tabelę i powiązaj schemat zagospodarowania przestrzennego

Napotknięty błąd

Gdy bezpośrednio klikniesz prawym przyciskiem myszy na tabelę, aby wyeksportować strukturę tabeli, nie możesz wyeksportować informacji o partycji, a informacje o partycjach tabeli możesz wyeksportować tylko po kliknięciu prawym przyciskiem na skrypt task-generate bazy danych

Kilka wniosków z tabeli podziału:

1. Pola podzielone nie muszą być koniecznie indeksowane

2. Pola partycjonowane mogą być tworzone jako indeksy klastrowane lub nieklastrowane

3. Pola partycjonowane, niezależnie od tego, czy są to indeksy klastrowane, czy nieklastrowane, są przebudowywane na indeksy klastrowane bez powiązanych schematów partycjonowania, a tabela partycjonowana staje się tabelą niepartycjonowaną

4. Przekonwertuj zwykłą tabelę na tabelę podzieloną, po prostu stwórz w niej indeks klastrowany i użyj schematu partycjonowania na tym indeksie. Na przykład, gdy pole 1 tabeli niepartycjonowanej tworzy klasteryzowany indeks i jest powiązane ze schematem partycjonowania, tabela zostaje przekształcona w tabelę partycjonowaną, a pole partycjonowane to pole 1.

5. Przekonwertowanie tabeli partycjonowanej na tablicę normalną, jeśli jest pole partycyjne z indeksem, odbuduj pole partycjonowane jako indeks klastrowany i nie kojarz się ze schematem partycji; jeśli pole partycji nie ma indeksu, pole partycji może utworzyć nowy indeks klastrowany i nie będzie powiązane ze schematem partycji

6. Zmiana tablicy normalnej na tabelę partycji lub zmiana tabeli partycji na zwykłą tabelę można osiągnąć tylko za pomocą indeksów klastrowanych, ponieważ w przypadku klastrowanych indeksów to tabele organizacyjne indeksów, a redystrybucja tabel realizowana jest poprzez rekonstrukcję indeksów klastrowanych. Jeśli zwykła tabela stanie się tabelą partycjonowaną, przetworz pole partycji na indeks klastrowany i połącz je ze schematem partycji, a jeśli tabela partycji stanie się tablicą normalną, zrekonstruuj pole partycji w indeks klastrowany bez powiązania schematu partycji.

7. Aby utworzyć tabelę partycji, ograniczenie jednoznaczności musi zawierać kolumnę partycji

8. Podczas tworzenia schematu partycji konieczne jest zapewnienie, że liczba grup plików odpowiada segmentowi zakresu partycji funkcji partycji i nie ma znaczenia, czy nazwa grupy pliku jest zdublowana.

9. Funkcje partycjonowania i schematy partycjonowania znajdują się w bazie danych, a nie dotyczą całej instancji

10. Tabela partycji jest zbyt duża, by zajmować dużo miejsca na dysku, a jej rozmiar nadal się nie zmienia po usunięciu niektórych pól

Kroki tworzenia tabeli partycji

1.1. Przykład tworzenia grupy plików

Alter Database test1 Add Filegroup Part1;

Alter Database test1 Add Filegroup part1000;

Alter Database test1 Add Filegroup part2000;

Alter Database test1 Add Filegroup part3000;

Alter Database Test1 Add Filegroup part4000;

1.2. Ustalenie przykładu pliku i powiązanie grupy plików

ALTER DATABASE TEST1 DODAJ PLIK(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) DO GRUPY PLIKÓW część 1;

ALTER DATABASE TEST1 DODAJ PLIK(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) DO GRUPY PLIKÓW części1000;

ALTER DATABASE TEST1 DODAJ PLIK(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) DO GRUPY PLIKÓW part2000;

ALTER DATABASE TEST1 DODAJ PLIK(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) DO GRUPY PLIKÓW part3000;

ALTER DATABASE TEST1 DODAJ PLIK(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) DO GRUPY PLIKÓW part 4000;

2. Przykładem ustanowienia funkcji partycji, funkcją partycji jest partfun1

CREATE PARTITION FUNCTION partfun1 (int)

POZOSTAWIONY ZAKRES WARTOŚCI ('1000', '2000', '3000', '4000')

--WARTOŚCI ('1000', '2000', '3000', '4000') oznacza, że tabela będzie podzielona na 5 stref, które są podzielone według wielkości wartości w polach tabeli, pięć stref jest najmniejszych - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-największych

3. Przykład ustanowienia schematu, powiązania funkcji partycji partfun1 i powiązania grupy plików

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (części1, część 1000, część 2000, część 3000, część 4000);

--Zbudowane na kilku grupach plików części1, części1000, częściowo2000, częściowo3000, częściowo4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

DO (CZĘŚCI 1,[PRIMARNY],[GŁÓWNY],[GŁÓWNY],[GŁÓWNY]);

--Zbudowany na części 1, grupie plików [PRIMARY], nie ma problemu z zastąpieniem części 1 na [PRIMARY], tak aby był podobny do budowania na grupie plików [PRIMARY]

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (część 1);

--wszystko zbudowane na grupie plików part1

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALL TO ([PRIMARY]);

--wszystko zbudowane na grupie plików [PRIMARY]

4. Przykład tworzenia tabeli partycji

UTWÓRZ TABELĘPARTTABLE1(

[ID] [int] NIE NULL,

[IDText] [nvarchar] (Max) ZERO

[Date] [datetime] NULL)

ON [partschema1](ID);

wstaw do Parttable1 wartości (1,'1', getdate()-4);

wstaw do parttable1 wartości (1001,'1001', getdate()-3);

wstaw do Parttable1 wartości (2001,'2001', getdate()-2);

wstaw do Parttable1 wartości (3001,'3001', getdate()-1);

wstaw do Parttable1 wartości (4001,'4001', getdate());

5. Weryfikacja danych tabeli partycji

WYBIERZ * Z tabeli częściowej1;

--Zwraca wszystkie wiersze tablicy partycji

Wybierz wyraźne $PARTITION. [partfun1] (4) Z tabeli częściowej1;

--Zwraca, które dzielą wiersz wartością pola ID 4, należy do

WYBIERZ * Z tabeli części1, gdzie $PARTITION. [partfun1] (ID)=2

--Zwraca wszystkie wiersze w drugiej partycji, ID to ID pola partycji

Uwaga: Nie możesz użyć SELECT * FROM parttable1, gdzie $PARTITION. [partfun1] (ID)=2 daje wynik, oznacza to, że jest to tabela podzielona, a ostatni test 7 w tym artykule to tabela niepartycjonowana, ale WYBIERZ * Z parttable1, gdzie $PARTITION. [partfun1] (ID)=2 nadal ma wynik

Nowe podziały

1. Określ grupę plików, którą można użyć dla schematu partycji (dodaj grupę plików dla schematu partycji).

2. Zmodyfikuj funkcję partycjonowania (dodaj zakres danych funkcji partycji)

ALTER PARTITION SCHEME partschema1 NASTĘPNIE UŻYWANE [PIERWOTNE]

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

Wybierz p.partition_number,P.Rows z sys.indexes i wewnętrzny połącz sys.partitions p na p.object_id = i.object_id i i.object_id = object_id('Parttable2') uporządkuj przez 1

--Pierwsze zdanie, jeśli schemat partycji używa ALL TO ([PRIMARY]), to zdanie nie musi być wykonywane

--Do drugiego pociągu dodana jest nowa partycja, zakres wynosi 4000-4500

--Trzecie zdanie sprawdza, czy nowa podział istnieje i czy są wiersze

Usuń i połącz partycję

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

Podział z 1000-2000 został usunięty i połączony z 1000-3000

--Nie można wykonać ALTER TABLENAME, DROP PARTITIONNAME, PARTITIONNAME, podobnie jak oracle;

Usuń tabelę partycji oraz odpowiadającą jej grupę plików

Kolejność usuwania jest następująca: usunięcie tabeli partycji, usunięcie schematu partycji, usunięcie funkcji partycji, a na końcu usunięcie grupy plików, a odpowiadający jej plik jest usuwany po usunięciu grupy plików

Przykład konwersji tabeli podzielonej na tablicę normalną oraz tabeli normalnej na tabelę podzieloną

DROP TABLE częściowo1;

UTWÓRZ TABELĘPARTTABLE1(

[Id] [int] TOŻSAMOŚĆ(1,1) NIE NULL,

[Imię] [varchar] (16) NIE ZEROWY,

[Id2] [int] NIE NULL

) ON partschema1(Id2);

wstaw do Parttable1 wartości ('1',1);

wstaw do Parttable1 wartości ('1001', 1001);

wstaw do tabeli części 1 wartości ('2001', 2001);

wstaw do Parttable1 wartości ('3001', 3001);

wstaw do Parttable1 wartości ('4001', 4001);

1. Unikalne ograniczenie utworzone w tabeli partycji musi zawierać kolumnę partycji.

ALTER TABELA CZĘŚCIOWOŚCI1 DODAJ OGRANICZENIE PK_prattable1_id KLASTROWANY KLUCZ GŁÓWNY ([ID] ASC)

Kolumna błędu 'Id2' to kolumna partycjonowania indeksu 'PK_prattable1_id'. Kolumny partycyjne dla unikalnego indeksu muszą być podzbiorem klucza indeksowego.

2. Utworzenie nowego klastrowanego indeksu w kolumnie partycji id2, a parttable1 nadal będzie tabelą podzieloną

utworzenie klastrowanego indeksu CI_prattable1_id2 na parttable1(id2);

3. Kolumna partycji id2 tworzy indeks nieklastrowany, a parttable1 nadal jest tabelą podzieloną na particje

drop index CI_prattable1_id2 na parttable1;

utworzenie nieklasteryzowanego indeksu NCI_prattable1_id2 na parttable1(id2);

4. Kolumna id kolumny bez partycjonowania tworzy indeks klastrowany, parttable1 lub tabelę partycjonowaną, wskazując, że kolumna niepartycjonowana może być kolumną indeksu klastra

utworzenie klastrowanego indeksu CI_prattable1_id na parttable1(id);

5. Kolumna partycji ID2 jest przebudowana do indeksu nieklastrowanego i nie używa schematu partycjonowania, parttable1 nadal jest tablicą partycji

Utworzenie nieklastrowanego indeksu NCI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = ON) NA [PRIMARY];

6. Kolumna partycji id2 jest rekonstruowana do klastrowanego indeksu bez warunku ON, a parttable1 nadal jest tablicą partycji

drop index CI_prattable1_id na parttable1;

drop index NCI_prattable1_id2 na parttable1;

utworzenie klastrowanego indeksu CI_prattable1_id2 na parttable1(id2);

utworzenie klastrowanego indeksu CI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = ON);

7. Partycjonowany id2 kolumny jest przebudowywany do klastrowanego indeksu z warunkem ON, ale bez użycia schematu partycjonowania, a parttable1 staje się tabelą niepartycjonowaną

Utworzenie klastrowanego indeksu CI_prattable1_id2 na parttable1(id2) Z (DROP_EXISTING = ON) NA [PRIMARY];

8. Kolumna partycji id2 jest przebudowywana w klasteryzowany indeks i korzystając ze schematu partycjonowania, parttable1 staje się tabelą partycjonowaną

utworzyć klastrowany indeks CI_prattable1_id2 na parttable1(id2) WITH (DROP_EXISTING = ON) na partschema1(Id2);

9. Po usunięciu klastrowanego indeksu w 8 powyżej, parttable1 pozostaje tabelą podzieloną

drop index CI_prattable1_id2 na parttable1;

10. Kolumna partycji ID2 jest tworzona jako indeks klastrowany i nie korzysta ze schematu partycjonowania, a parttable1 staje się tabelą niepartycjonowaną

Utworzenie klastrowanego indeksu CI_prattable1_id2 na parttable1(id2) NA [PRIMARY];

11. Po usunięciu klastrowanego indeksu powyższych 10, parttable1 pozostaje tabelą niepodzieloną

drop index CI_prattable1_id2 na parttable1;

12. Kolumna partycji id2 jest nowo tworzona jako indeks nieklastrowany, choć stosowany jest schemat partycjonowania, to nadal jest to tabela niepartycjonowana

tworzy nieklasterizowany indeks NCI_prattable1_id2 na parttable1(id2) na partschema1(Id2);

Jeśli tabela podzielona zostanie przekształcona w tabelę normalną, ograniczenie klucza pierwotnego zostaje usunięte, a indeks klastra jest przebudowywany lub przekształcany w klucz główny dla pól oryginalnego klucza głównego, ale nie jest to powiązane ze schematem partycjonowania

TABELA ALTER Table_name OGRANICZENIE ODRZUĆ PK_NAME Z (>

UTWORZENIE PK_NAME SKUPIONEGO INDEKSU NA Table_name(KOLUMNA) Z (NA [GŁÓWNY];

lub

TABELA ALTER Table_name DODAĆ OGRANICZENIE PK_NAME KLUCZ GŁÓWNY KLASTROWANY (kolumna) Z (NA [GŁÓWNY];

Jeśli tabela normalna zostanie przekonwertowana na tabelę partycjonowaną, jeśli oryginalny klucz główny ma pozostać zachowany, ograniczenie klucza pierwotnego jest usuwane, a następnie klucz główny jest tworzony bez ustawiania go jako indeksu agregowanego, a następnie tworzony jest nowy indeks zagregowany i schemat partycjonowania jest używany w indeksie zagregowanym

TABELA ALTER Table_name OGRANICZENIE ODRZUĆ PK_NAME Z (>

ALTER TABLE Table_name DODAĆ OGRANICZENIE PK_NAME KLUCZ PODSTAWOWY NONCLUSTERED(columned) Z (NA [PRIMARY];

--Utwórz klucz główny, ale nie jako indeks skupiony

UTWÓRZ index_name KLASTROWANEGO INDEKSU NA Table_name(kolumna) NA schemacie partycji (pole partycji)

--Utwórz nowy indeks klastrowany, w którym używany jest schemat partycjonowania

Zapytaj o łączną liczbę wierszy i rozmiar tabeli podzielonej na partie, na przykład tabela jest CRM. MailLog

Dyrektor sp_spaceused 'CRM. MailLog';

Zapytaj informacje o tabeli podzielonej na partycje, na przykład ile wierszy ma każda partycja, na przykład tabela jest CRM. MailLog

Wybierz konwersję (varchar(50), ps.name

) jako partition_scheme,

p.partition_number,

Konwertyta(Varchar(10), ds2.name

) jako grupa plików,

Convert (varchar(19), isnull(v.value, ''), 120) jako range_boundary,

str(p.rows, 9) jako rows

z sys.indexes i

dołącz sys.partition_schemes PS na i.data_space_id = ps.data_space_id

Dołącz do sys.destination_data_spaces DDS

na ps.data_space_id = dds.partition_scheme_id

Dołącz do sys.data_spaces DS2 na dds.data_space_id = ds2.data_space_id

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

oraz p.object_id = i.object_id oraz p.index_id = i.index_id

dołącz sys.partition_functions pf na ps.function_id = pf.function_id

LEWO DOŁĄCZ do sys. Partition_Range_values v na pf.function_id = v.function_id

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

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

oraz i.index_id w (0, 1)

zamówienie p.partition_number

Funkcje partycji zapytań

Wybierz * z sys.partition_functions

Zobacz architekturę partycji

Wybierz * z sys.partition_schemes