[Communicatie] SQL Server Samenvatting van gepartitioneerde tabellen

Officiële documentatie

Hoog-niveau stappen om een partitietabel te maken

1. Maak een bestandsgroep aan (vergelijkbaar met Oracle tablespace), natuurlijk kun je die niet aanmaken, je kunt alle partities in één bestandsgroep zetten

2. Stel een partitiefunctie op en verdeel gegevens volgens welk bereik

3. Stel een partitieschema op, koppel de partitiefunctie en koppel ook de bestandsgroep, de partitiefunctie verdeelt de data in verschillende bereiken, je moet meerdere bestandsgroepen koppelen, natuurlijk kun je deze partitiebereiken ook in dezelfde bestandsgroep plaatsen

4. Maak een tabel aan en koppel het bestemmingsplan

Een bug tegengekomen

Wanneer je direct met de rechtermuisknop op de tabel klikt om de tabelstructuur te exporteren, kun je de partitie-informatie niet exporteren, en kun je alleen de partitie-informatie van de tabel exporteren door met de rechtermuisknop op het database-task-generate script te klikken

Enkele conclusies uit de partitietabel:

1. Gepartitioneerde velden hoeven niet per se geïndexeerd te worden

2. Gepartitioneerde velden kunnen worden aangemaakt als geclusterde indexen of no-clustered indexen

3. Gepartitioneerde velden, of het nu geclusterde indexen zijn of niet-geclusterde indexen, worden herbouwd tot geclusterde indexen zonder bijbehorende partitioneringsschema's, en de gepartitioneerde tabel wordt een niet-gepartitioneerde tabel

4. Converteer een gewone tabel naar een gepartitioneerde tabel, maak gewoon een geclusterde index in de tabel en gebruik het partitioneringsschema op de geclusterde index. Bijvoorbeeld, wanneer veld 1 van een niet-gepartitioneerde tabel een geclusterde index aanmaakt en gekoppeld is aan een partitioneringsschema, wordt de tabel omgezet in een partitioneerde tabel en is het gepartitioneerde veld veld 1.

5. Converteer de gepartitioneerde tabel naar een normale tabel; als er een partitieveld met een index is, bouw het gepartitioneerde veld dan opnieuw op als een geclusterde index en koppel het niet aan het partitieschema; als het partitieveld geen index heeft, kan het partitieveld een nieuwe geclusterde index aanmaken en niet met het partitieschema associëren

6. Het veranderen van een normale tabel in een partitietabel of het veranderen van een partitietabel in een gewone tabel kan alleen worden bereikt door gebruik te maken van geclusterde indexen, omdat bij geclusterde indexen indexorganisatietabellen worden gerealiseerd, en de herverdeling van tabellen wordt gerealiseerd door de reconstructie van geclusterde indexen. Als de gewone tabel een gepartitioneerde tabel wordt, reconstrueer dan het partitieveld tot een geclusterde index en koppel deze aan het partitieschema, en als de partitietabel een normale tabel wordt, reconstrueer dan het partitieveld tot een geclusterde index zonder het partitieschema te associëren.

7. Om een partitietabel te maken, moet de uniciteitsbeperking een partitiekolom bevatten

8. Bij het maken van een partitieschema is het noodzakelijk ervoor te zorgen dat het aantal bestandsgroepen overeenkomt met het partitiebereiksegment van de partitiefunctie, en het maakt niet uit of de bestandsgroepsnaam wordt gedupliceerd.

9. Partitioneringsfuncties en partitioneringsschema's bevinden zich in een database, niet gericht op de hele instantie

10. De partitietabel is te groot om veel schijfruimte in te nemen, en de grootte verandert nog steeds niet na het verwijderen van enkele velden

Stappen om een partitietabel te maken

1.1. Voorbeeld van het opzetten van een bestandsgroep

Database test1 toevoegen bestandgroep deel1;

Database Test1 toevoegen filegroup part1000;

alter database test1 add filegroup part2000;

alter database test1 add filegroup part3000;

Database Test1 toevoegen filegroup part4000;

1.2. Een voorbeeld van een bestand opstellen en een bestandsgroep koppelen

ALTER DATABASE test1 VOEG BESTANDSNAAM = test1part1, BESTANDSNAAM = 'G:\test1part1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) aan bestandsgroep deel 1;

ALTER DATABASE test1 VOEG FILE(NAAM = test1part1000,BESTANDSNAAM = 'G:\test1part1000.ndf', SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TOE AAN FILEGROUP part1000;

ALTER DATABASE test1 VOEG BESTANDSNAAM = test1part2000, BESTANDSNAAM = 'G:\test1part2000.ndf', GROOTTE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) AAN FILEGROUP part2000;

ALTER DATABASE test1 VOEG BESTANDSNAAM = test1part3000, BESTANDSNAAM = 'G:\test1part3000.ndf', GROOTTE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) AAN FILEGROUP part3000;

ALTER DATABASE test1 VOEG BESTANDSNAAM = test1part4000, BESTANDSNAAM = 'G:\test1part4000.ndf', GROOTTE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) AAN FILEGROUP part4000;

2. Een voorbeeld van het opzetten van een partitiefunctie, de partitiefunctie wordt partfun1 genoemd

CREATE PARTITION FUNCTION partfun1 (int)

ALS OVERLIGGENDE BEREIK VOOR WAARDEN ('1000', '2000', '3000', '4000')

--VALUES ('1000', '2000', '3000', '4000') geeft aan dat de tabel wordt verdeeld in 5 zones, die zijn verdeeld op basis van de grootte van de waarden in de tabelvelden; de vijf zones zijn het kleinst - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-grootste

3. Een voorbeeld van het opzetten van een schema, koppel de partitiefunctie partfun1 en koppel de bestandsgroep

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (deel1, deel1000, deel2000, deel3000, deel4000);

--Gebouwd op verschillende bestandsgroepen van part1, part1000, part2000, part3000, part4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

AAN (DEEL 1,[PRIMAIR],[PRIMAIR],[PRIMAIR],[PRIMAIR];);

--Gebouwd op deel 1, [PRIMARY] bestandsgroep, is het geen probleem om deel 1 te vervangen door [PRIMARY], zodat het vergelijkbaar is met het bouwen op de [PRIMARY] bestandsgroep

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (deel 1);

--allemaal gebouwd op de part1-bestandsgroep

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALLE NAAR ([PRIMAIR]);

--allemaal gebouwd op de [PRIMARY] bestandsgroep

4. Voorbeeld van het opzetten van een partitietabel

MAAK TABEL PARTTABLE1(

[ID] [int] NIET NULL,

[IDText] [nvarchar] (max) NUL

[Datum] [datum] NULL)

OP [partschema1](ID);

Invoegen in parttable1 waarden (1,'1', getdate()-4);

Invoegen in parttable1 waarden (1001,'1001', getdate()-3);

Invoegen in parttable1-waarden (2001,'2001', getdate()-2);

Invoegen in parttable1-waarden (3001,'3001', getdate()-1);

Invoegen in parttable1-waarden (4001,'4001',getdate());

5. Controleer de gegevens van de partitietabel

SELECTEER * UIT deeltafel1;

--Retourneert alle rijen van de partitietabel

KIES EEN $PARTITION. [partfun1] (4) UIT deeltafel1;

--Retouren die de rij met de ID-veldwaarde 4 partitioneren behoort tot

SELECTEER * UIT parttable1 waar $PARTITION. [partfun1] (ID)=2

--Geeft alle rijen in de tweede partitie terug, de ID is de partitieveld-ID

Let op: Je kunt SELECT * FROM parttable1 niet gebruiken waar $PARTITION. [partfun1] (ID)=2 heeft een resultaat, wat betekent dat het een gepartitioneerde tabel is, en de laatste test 7 van dit artikel is een niet-gepartitioneerde tabel, maar SELECT * UIT parttable1 waarbij $PARTITION. [partfun1] (ID)=2 heeft nog steeds een resultaat

Nieuwe partities

1. Specificeer een bestandsgroep die gebruikt kan worden voor het partitieschema (voeg een bestandsgroep toe voor het partitieschema).

2. Wijzig de partitiefunctie (voeg het databereik van de partitiefunctie toe)

ALTER PARTITION SCHEME partschema1 VOLGENDE GEBRUIK [PRIMAIR]

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

Selecteer p.partition_number,p.rows uit sys.indexes i inner join sys.partitions p op p.object_id = i.object_id en i.object_id = object_id('parttable2') volgorde met 1

--De eerste instructie, als het partitieschema ALL TO ([PRIMARY]) gebruikt, hoeft deze instructie niet uitgevoerd te worden

--Een nieuwe partitie wordt toegevoegd aan de tweede instructie, het bereik is 4000-4500

--De derde uitspraak verifieert of de nieuwe partitie bestaat en of er rijen zijn

Delete\Merge-partitie

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

De opdeling van 1000-2000 werd verwijderd en samengevoegd tot 1000-3000

--Kan niet uitvoeren ALTER TABLE TABLENAME DROP PARTITION PARTITION NAME zoals oracle;

Verwijder de partitietabel en de bijbehorende bestandsgroep

De volgorde van verwijdering is: verwijder de partitietabel, verwijder het partitieschema, verwijder de partitiefunctie en verwijder tenslotte de bestandsgroep, en het bijbehorende bestand wordt verwijderd nadat de bestandsgroep is verwijderd

Voorbeeld van het omzetten van een gepartitioneerde tabel naar een normale tabel en een normale tabel naar een gepartitioneerde tabel

DROP TABLE PARTTABLE1;

MAAK TABEL PARTTABLE1(

[id] [int] IDENTITEIT(1,1) NIET NULL,

[Naam] [varchar] (16) NIET NULL,

[Id2] [int] NIET NULL

) ON partschema1(Id2);

invoegen in parttable1-waarden ('1',1);

invoegen in parttable1-waarden ('1001', 1001);

invoegen in parttable1-waarden ('2001', 2001);

invoegen in parttable1-waarden ('3001', 3001);

invoegen in parttable1-waarden ('4001', 4001);

1. De unieke constraint die op de partitietabel wordt aangemaakt, moet een partitiekolom bevatten.

ALTER TABLE PARTTABLE1 VOEG CONSTRAINT TOE PK_prattable1_id PRIMAIRE SLEUTEL GECLUSTERD ([ID] ASC)

Foutkolom 'Id2' is de partitioneringskolom van de index 'PK_prattable1_id'. Partitiekolommen voor een unieke index moeten een deelverzameling zijn van de indexsleutel.

2. Maak een nieuwe geclusterde index aan in de partitiekolom id2, en parttable1 is nog steeds een gepartitioneerde tabel

Een geclusterde index CI_prattable1_id2 maken op parttable1(id2);

3. Partitiekolom id2 maakt een niet-geclusterde index aan, en parttable1 is nog steeds een gepartitioneerde tabel

dropindex CI_prattable1_id2 op parttable1;

maak niet-geclusterde index NCI_prattable1_id2 op parttable1(id2);

4. De niet-gepartitioneerde kolom-id-kolom creëert een geclusterde index, parttable1 of een gepartitioneerde tabel, wat aangeeft dat de niet-gepartitioneerde kolom een clusterindexkolom kan zijn

maak een geclusterde index CI_prattable1_id op parttable1(id);

5. Partitiekolom ID2 wordt herbouwd tot een niet-geclusterde index en gebruikt geen partitieschema, parttable1 is nog steeds een partitietabel

maak niet-geclusterde index NCI_prattable1_id2 op parttable1(id2) MET (DROP_EXISTING = AAN) OP [PRIMAIR];

6. De partitiekolom id2 wordt gereconstrueerd tot een geclusterde index zonder de ON-voorwaarde, en parttable1 is nog steeds een partitietabel

Drop index CI_prattable1_id op parttable1;

drop index NCI_prattable1_id2 op parttable1;

Een geclusterde index CI_prattable1_id2 maken op parttable1(id2);

maak een geclusterde index CI_prattable1_id2 op parttable1(id2) MET (DROP_EXISTING = AAN);

7. Gepartitioneerde kolom id2 wordt herbouwd tot een geclusterde index met ON-conditie maar zonder partitioneringsschema, en parttable1 wordt een niet-gepartitioneerde tabel

maak een geclusterde index CI_prattable1_id2 op parttable1(id2) MET (DROP_EXISTING = AAN) OP [PRIMAIR];

8. Partitiekolom id2 wordt heropgebouwd tot een geclusterde index en met behulp van het partitieschema wordt parttable1 een gepartitioneerde tabel

maak geclusterde index CI_prattable1_id2 op parttable1(id2) MET (DROP_EXISTING = ON) op partschema1(Id2);

9. Na het verwijderen van de geclusterde index in 8 hierboven, is parttable1 nog steeds een gepartitioneerde tabel

dropindex CI_prattable1_id2 op parttable1;

10. Partitiekolom ID2 wordt aangemaakt als een geclusterde index en gebruikt het partitioneringsschema niet, en parttable1 wordt een niet-gepartitioneerde tabel

maak een geclusterde index CI_prattable1_id2 op parttable1(id2) OP [PRIMAIR];

11. Na het verwijderen van de geclusterde index van bovenstaande 10 is parttable1 nog steeds een niet-gepartitioneerde tabel

dropindex CI_prattable1_id2 op parttable1;

12. De partitiekolom id2 is nieuw aangemaakt als een niet-geclusterde index, hoewel het partitioneringsschema wordt gebruikt, blijft het een niet-gepartitioneerde tabel

niet-geclusterde index NCI_prattable1_id2 op parttable1(id2) op partschema1(Id2) aanmaken;

Als de gepartitioneerde tabel wordt omgezet naar een normale tabel, wordt de primaire sleutelbeperking verwijderd en wordt de clusterindex opnieuw opgebouwd of herbouwd tot de primaire sleutel voor de velden van de oorspronkelijke primaire sleutel, maar dit is niet gerelateerd aan het partitioneringsschema

ALTER TABLE Table_name DROPCONSTRAINT PK_NAME MET (>

MAAK EEN GECLUSTERDE INDEX PK_NAME OP Table_name(kolom) MET (OP [PRIMAIR];

of

ALTER TABLE Table_name CONSTRAINT TOEVOEGEN PK_NAME PRIMAIRE SLEUTEL GECLUSTERD (kolom) MET (OP [PRIMAIR];

Als de normale tabel wordt omgezet naar een gepartitioneerde tabel, en de oorspronkelijke primaire sleutel behouden moet blijven, wordt de primaire sleutelbeperking verwijderd, en wordt de primaire sleutel aangemaakt zonder deze als aggregatieve index in te stellen, waarna een nieuwe geaggregeerde index wordt aangemaakt, en wordt het partitioneringsschema gebruikt in de geaggregeerde index

ALTER TABLE Table_name DROPCONSTRAINT PK_NAME MET (>

ALTER TABLE Table_name CONSTRAINT TOEVOEGEN PK_NAME PRIMAIRE SLEUTEL NIET-GECLUSTERD (kolom) MET (OP [PRIMAIR];

--Maak een primaire sleutel aan, maar zet deze niet als een geclusterde index

MAAK GECLUSTERDE INDEX index_name AAN Table_name(kolom) OP Partitieschema (partitieveld)

--Maak een nieuwe geclusterde index aan waarin een partitioneringsschema wordt gebruikt

Vraag bijvoorbeeld het totale aantal rijen en de grootte van een gepartitioneerde tabel op, de tabel is CRM. EmailLog

Uitvoerend sp_spaceused 'CRM. EmailLog';

Vraag de informatie van een gepartitioneerde tabel op, hoeveel rijen elke partitie heeft, bijvoorbeeld, de tabel is CRM. EmailLog

Selecteer Convert(Varchar(50), ps.name

) als partition_scheme,

p.partition_number,

Convert(varchar(10), ds2.name

) als bestandsgroep,

convert(varchar(19), isnull(v.value, ''), 120) als range_boundary,

str(p.rows, 9) als rijen

Van sys.indexes i

Sluit je aan bij sys.partition_schemes PS op i.data_space_id = ps.data_space_id

Word lid sys.destination_data_spaces DDS

op ps.data_space_id = dds.partition_scheme_id

Word lid sys.data_spaces DS2 op dds.data_space_id = ds2.data_space_id

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

en p.object_id = i.object_id en p.index_id = i.index_id

Sluit je aan bij sys.partition_functions PF op ps.function_id = pf.function_id

LINKS SLUIT AAN bij systeem. Partition_Range_values v op pf.function_id = v.function_id

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

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

en i.index_id in (0, 1)

Bestelling per p.partition_number

Querypartitiefuncties

Selecteer * uit sys.partition_functions

Bekijk de partitiearchitectuur

Selecteer * uit sys.partition_schemes