[Kommunikasjon] SQL Server-oppsummering av partisjonerte tabeller

Offisiell dokumentasjon

Høynivåsteg for å lage en partisjonstabell

1. Lag en filgruppe (lik Oracle tablespace), selvfølgelig kan du ikke opprette den, du kan legge alle partisjonene i én filgruppe

2. Etabler en partisjonsfunksjon og distribuer data etter hvilket område

3. Etabler et partisjonsskjema, assosier partisjonsfunksjonen, og assosier også filgruppen, partisjonsfunksjonen deler dataene inn i flere områder, du må knytte flere filgrupper, selvfølgelig kan du også legge disse partisjonsområdene i samme filgruppe

4. Lag en tabell og tilknytte reguleringsplanen

En feil oppdaget

Når du høyreklikker direkte på tabellen for å eksportere tabellstrukturen, kan du ikke eksportere partisjonsinformasjonen, og du kan bare eksportere partisjonsinformasjonen til tabellen ved å høyreklikke på database-task-generer-skriptet

Noen konklusjoner fra delingstabellen:

1. Partisjonerte felt trenger ikke nødvendigvis indekseres

2. Partisjonerte felt kan opprettes som klyngede indekser eller ikke-klyngede indekser

3. Partisjonerte felt, enten de er klyngede indekser eller ikke-klyngede indekser, bygges om til klyngede indekser uten tilhørende partisjoneringsordninger, og den partisjonerte tabellen blir en ikke-partisjonert tabell

4. Konverter en vanlig tabell til en partisjonert tabell, lag bare en klynget indeks i tabellen og bruk partisjoneringsskjemaet på den klyngede indeksen. For eksempel, når felt 1 i en ikke-partisjonert tabell oppretter en klynget indeks og er assosiert med et partisjoneringsskjema, konverteres tabellen til en partisjonert tabell, og det partisjonerte feltet er felt 1.

5. Konverter den partisjonerte tabellen til en normal tabell, hvis det finnes et partisjonsfelt med en indeks, bygg opp det partisjonerte feltet som en klynget indeks og ikke assosier det med partisjonsskjemaet, hvis partisjonsfeltet ikke har en indeks, kan partisjonsfeltet opprette en ny klynget indeks og ikke assosieres med partisjonsskjemaet

6. Å endre en normal tabell til en partisjonstabell eller å endre en partisjonstabell til en vanlig tabell kan bare oppnås ved bruk av klyngede indekser, fordi med klyngede indekser er indeksorganiseringstabeller, og omfordelingen av tabeller realiseres gjennom rekonstruksjon av klyngede indekser. Hvis den vanlige tabellen blir en partisjonstabell, rekonstruer partisjonsfeltet til en klynget indeks og assosier det med partisjonsskjemaet, og hvis partisjonstabellen blir en normal tabell, rekonstruer partisjonsfeltet til en klynget indeks uten å knytte partisjonsskjemaet.

7. For å lage en partisjonstabell må unikhetsbegrensningen inkludere en partisjonskolonne

8. Når man lager en partisjonsordning, er det nødvendig å sikre at antall filgrupper samsvarer med partisjonsområdets segment i partisjonsfunksjonen, og det spiller ingen rolle om filgruppenavnet er duplisert.

9. Partisjoneringsfunksjoner og partisjoneringsskjemaer ligger i en database, og vender ikke mot hele instansen

10. Partisjonstabellen er for stor til å ta opp mye diskplass, og størrelsen endres fortsatt ikke etter at noen felt er slettet

Trinn for å lage en partisjonstabell

1.1. Eksempel på etablering av en filgruppe

alter database test1 legg til filgruppe del1;

alter database test1 legg til filgruppe part1000;

alter database test1 legg til filgruppe part2000;

alter database test1 legg til filgruppe part3000;

alter database test1 legg til filgruppe part4000;

1.2. Etabler et eksempel på en fil og assosier en filgruppe

ALTER DATABASE test1 LEGG TIL FIL(NAVN = test1part1,FILNAVN = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) I FILGRUPPE del 1;

ALTER DATABASE test1 LEGG TIL FIL(NAVN = test1part1000,FILNAVN = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) I FILEGROUP part1000;

ALTER DATABASE test1 LEGG TIL FIL(NAVN = test1part2000,FILNAVN = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TIL FILEGROUP part2000;

ALTER DATABASE test1 LEGG TIL FIL(NAVN = test1part3000,FILNAVN = 'G:\test1part3000.ndf', STØRRELSE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TIL FILEGROUP part3000;

ALTER DATABASE test1 LEGG TIL FIL(NAVN = test1part4000,FILNAVN = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) I FILEGROUP part4000;

2. Et eksempel på å etablere en partisjonsfunksjon, partisjonsfunksjonen kalles partfun1

CREATE PARTITION FUNCTION partfun1 (int)

SOM VENSTRE SPENN FOR VERDIER ('1000', '2000', '3000', '4000')

--VERDIER ('1000', '2000', '3000', '4000') indikerer at tabellen vil bli delt inn i 5 soner, som deles inn etter størrelsen på verdiene i tabellfeltene, de fem sonene er de minste - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-største

3. Et eksempel på etablering av et skjema, assosier partisjonsfunksjonen partfun1, og assosier filgruppen

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (del1,del1000,del2000,del3000,del4000);

--Bygget på flere filgrupper av del1, del1000, del2000, del3000, del4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

TO (DEL 1,[PRIMÆR],[PRIMÆR],[PRIMÆR],[PRIMÆR],[PRIMÆR]);

--Bygget på del 1, [PRIMARY] filgruppe, er det ikke noe problem å erstatte del 1 med [PRIMARY], slik at det ligner på å bygge på [PRIMARY] filgruppen

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (del 1);

--alle bygget på part1-filgruppen

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALLE TIL ([PRIMÆR]);

--alle bygget på [PRIMARY] filgruppen

4. Eksempel på etablering av en partisjonstabell

OPPRETT TABELL parttable1(

[ID] [int] IKKE NULL,

[IDText] [nvarchar] (max) NULL

[Dato] [datotid] NULL)

PÅ [partschema1](ID);

sett inn verdier i parttable1 (1,'1', getdate()-4);

sett inn verdier i parttable1 (1001,'1001', getdate()-3);

sett inn verdier i parttable1 (2001,'2001', getdate()-2);

sett inn verdier i parttable1 (3001,'3001', getdate()-1);

sett inn verdier i parttable1 (4001,'4001',getdate());

5. Verifiser dataene i partisjonstabellen

VELG * FRA deltabell1;

--Returnerer alle rader i partisjonstabellen

VELG DISTINKTE $PARTITION. [partfun1] (4) FRA deltabell1;

--Returer som deler opp raden med ID-feltverdien 4 tilhører

VELG * FRA parttable1 hvor $PARTITION. [partfun1] (ID)=2

--Returnerer alle rader i den andre partisjonen, ID-en er partisjonsfelt-ID-en

Merk: Du kan ikke bruke SELECT * FROM parttable1 der $PARTITION. [partfun1] (ID)=2 har et resultat, det betyr at det er en partisjonert tabell, og siste test 7 i denne artikkelen er en ikke-partisjonert tabell, men VELG * FRA parttable1 hvor $PARTITION. [partfun1] (ID)=2 har fortsatt et resultat

Nye partisjoner

1. Spesifiser en filgruppe som kan brukes for partisjonsskjemaet (legg til en filgruppe for partisjonsskjemaet).

2. Endre partisjonsfunksjonen (legg til dataområdet til partisjonsfunksjonen)

ALTER PARTITION SCHEME partschema1 NESTE BRUK [PRIMÆR]

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

Velg p.partition_number,p.rader fra sys.indexes i inner join sys.partitions p på p.object_id = i.object_id og i.object_id = object_id('parttable2') rekkefølge med 1

--Den første setningen, hvis partisjonsskjemaet bruker ALL TO ([PRIMARY]), trenger ikke denne setningen å utføres

--En ny partisjon legges til den andre setningen, intervallet er 4000-4500

--Den tredje setningen verifiserer om den nye partisjonen eksisterer og om det finnes rader

Delete\Merge-partisjonen

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

Partisjonen 1000-2000 ble slettet og slått sammen til 1000-3000

--Kan ikke utføre ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME som oracle;

Slett partisjonstabellen og den tilhørende filgruppen

Rekkefølgen på slettingen er: slett partisjonstabellen, slett partisjonsskjemaet, slett partisjonsfunksjonen, og til slutt slett filgruppen, og den tilsvarende filen slettes etter at filgruppen er slettet

Eksempel på å konvertere en partisjonert tabell til en normal tabell og en normal tabell til en partisjonert tabell

DROP TABLE parttable1;

OPPRETT TABELL parttable1(

[id] [int] IDENTITET(1,1) IKKE NULL,

[Navn] [varchar] (16) IKKE NULL,

[Id2] [int] IKKE NULL

) PÅ partschema1(Id2);

sette inn verdier i parttable1 ('1',1);

sett inn verdier i parttable1 ('1001', 1001);

sett inn i deltabell1-verdier ('2001', 2001);

sett inn i parttable1-verdier ('3001', 3001);

sett inn verdier i parttable1 ('4001', 4001);

1. Den unike begrensningen som opprettes i partisjonstabellen må inneholde en partisjonskolonne.

ALTER TABLE parttable1 LEGG TIL BEGRENSNING PK_prattable1_id PRIMÆRNØKKEL KLYNGET ([ID] ASC)

Feilkolonne 'Id2' er en oppdelingskolonne i indeksen 'PK_prattable1_id'. Partisjonskolonner for en unik indeks må være en delmengde av indeksnøkkelen.

2. Opprett en ny klynget indeks i partisjonskolonnen id2, og parttable1 er fortsatt en partisjonert tabell

opprette klynget indeks CI_prattable1_id2 på parttable1(id2);

3. Partisjonskolonne id2 lager en ikke-klynget indeks, og parttable1 er fortsatt en partisjonert tabell

droppindeks CI_prattable1_id2 på deltabell1;

opprette ikke-klynget indeks NCI_prattable1_id2 på parttable1(id2);

4. Den ikke-partisjonerte kolonne-id-kolonnen oppretter en klynget indeks, parttable1 eller en partisjonert tabell, som indikerer at den ikke-partisjonerte kolonnen kan være en klyngeindekskolonne

Lag klynget indeks CI_prattable1_id på parttable1(id);

5. Partisjonskolonne ID2 bygges om til en ikke-klynget indeks og bruker ikke en partisjoneringsmetode, parttable1 er fortsatt en partisjonstabell

opprett ikke-klynget indeks NCI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ) PÅ [PRIMÆR];

6. Partisjonskolonnen id2 rekonstrueres til en klynget indeks uten ON-betingelsen, og parttable1 er fortsatt en partisjonstabell

droppindeks CI_prattable1_id på deltabell1;

droppindeks NCI_prattable1_id2 på parttable1;

opprette klynget indeks CI_prattable1_id2 på parttable1(id2);

lage klynget indeks CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ);

7. Partisjonert kolonne id2 bygges om til en klynget indeks med ON-betingelse, men uten å bruke partisjoneringsskjema, og parttable1 blir en ikke-partisjonert tabell

lag klynget indeks CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = PÅ) PÅ [PRIMÆR];

8. Partisjonskolonne id2 bygges opp igjen til en klynget indeks, og ved bruk av partisjoneringsskjemaet blir parttable1 en partisjonert tabell

opprette klyngeindeks CI_prattable1_id2 på parttable1(id2) MED (DROP_EXISTING = ON) på partschema1(Id2);

9. Etter å ha slettet den klyngede indeksen i 8 ovenfor, er parttable1 fortsatt en partisjonert tabell

droppindeks CI_prattable1_id2 på deltabell1;

10. Partisjonskolonne ID2 opprettes som en klynget indeks og bruker ikke partisjoneringsskjemaet, og parttable1 blir en ikke-partisjonert tabell

opprette klynget indeks CI_prattable1_id2 på parttable1(id2) PÅ [PRIMÆR];

11. Etter å ha slettet den klyngede indeksen til de ovennevnte 10, er parttable1 fortsatt en ikke-partisjonert tabell

droppindeks CI_prattable1_id2 på deltabell1;

12. Partisjonskolonnen id2 er nylig opprettet som en ikke-klynget indeks, selv om partisjoneringsskjemaet brukes, er det fortsatt en ikke-partisjonert tabell

opprette ikke-klynget indeks NCI_prattable1_id2 på parttable1(id2) på partschema1(Id2);

Hvis den partisjonerte tabellen konverteres til en normal tabell, slettes primærnøkkelbegrensningen, og klyngeindeksen bygges opp igjen eller bygges opp til primærnøkkelen for feltene i den opprinnelige primærnøkkelen, men det er ikke relatert til partisjoneringsskjemaet

ALTER TABLE Table_name DROP-BEGRENSNING PK_NAME MED (>

LAG KLYNGET INDEKS PK_NAME PÅ Table_name(kolonne) MED (PÅ [PRIMÆR];

eller

ALTER TABELL Table_name LEGGE TIL BEGRENSNING PK_NAME PRIMÆRNØKKEL KLYNGET (kolonne) MED (PÅ [PRIMÆR];

Hvis normaltabellen konverteres til en partisjonert tabell, hvis den opprinnelige primærnøkkelen skal beholdes, slettes primærnøkkelbegrensningen, og deretter opprettes primærnøkkelen uten å sette den som en aggregert indeks, og deretter opprettes en ny aggregert indeks, og partisjoneringsmetoden brukes i den aggregerte indeksen

ALTER TABLE Table_name DROP-BEGRENSNING PK_NAME MED (>

ALTER TABLE Table_name LEGG TIL BEGRENSNING PK_NAME PRIMÆRNØKKEL IKKE-KLYNGET(kolonne) MED (PÅ [PRIMÆR];

--Opprett en primærnøkkel, men ikke satt som en klynget indeks

OPPRETT KLYNGET INDEKS index_name PÅ Table_name(kolonne) På Partisjonsskjema (Partisjonsfelt)

--Lag en ny klyngeindeks der et partisjoneringsskjema brukes

Spør i totalt antall rader og størrelsen på en partisjonert tabell, for eksempel er tabellen CRM. EmailLog

Leder sp_spaceused 'CRM. EmailLog';

Spør i informasjonen i en oppdelt tabell, hvor mange rader hver partisjon har, for eksempel er tabellen CRM. EmailLog

Velg konverter (varchar (50), ps.name

) som partition_scheme,

p.partition_number,

konvertert(varchar(10), ds2.name

) som filgruppe,

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

str(p.rows, 9) som rader

fra sys.indexes i

Bli med sys.partition_schemes PS på i.data_space_id = ps.data_space_id

Bli med sys.destination_data_spaces DDS

på ps.data_space_id = dds.partition_scheme_id

Bli med sys.data_spaces DS2 på dds.data_space_id = ds2.data_space_id

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

og p.object_id = i.object_id og p.index_id = i.index_id

Bli med sys.partition_functions PF på ps.function_id = pf.function_id

VENSTRE JOIN sys. Partition_Range_values v på pf.function_id = v.function_id

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

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

og i.index_id i (0, 1)

Bestilling etter p.partition_number

Spørringspartisjonsfunksjoner

Velg * fra sys.partition_functions

Se partisjonsarkitekturen

Velg * fra sys.partition_schemes