[Kommunikatsioon] SQL Serveri partitsioneeritud tabelite kokkuvõte

Ametlik dokumentatsioon

Kõrgetasemelised sammud partitsioonitabeli loomiseks

1. Loo failigrupp (sarnane oracle tablespace'ile), muidugi sa ei saa seda luua, saad panna kõik partitsioonid ühte failigruppi

2. Loo partitsioonifunktsioon ja jaota andmed vastavalt vahemikule

3. Loo partitsiooniskeem, seosta partitsioonifunktsioon ja ka failigrupp, partitsioonifunktsioon jagab andmed mitmeks vahemikuks, sa pead seostama mitu failigruppi, muidugi võid need partitsioonivahemikud panna samasse failigruppi

4. Koosta tabel ja seose tsoonimisskeemiga

Viga kokku puutus

Kui sa tabelit otse paremklõpsuga tabelstruktuuri eksportimiseks, ei saa sa partitsiooniinfot eksportida ning saad tabeli partitsiooniinfot eksportida ainult paremklõpsuga andmebaasi-ülesande genereerimise skriptil

Mõned järeldused partitsioonitabelist:

1. Partitsioneeritud välju ei ole tingimata vaja indekseerida

2. Partitsioneeritud välju saab luua klasterdatud indeksitena või noklasterdatud indeksitena

3. Partitsioneeritud väljad, olgu need klasterindeksid või mitteklasterdatud indeksid, ehitatakse ümber klasterdatud indeksiteks ilma seotud partitsioneerimisskeemideta ning partitsioneeritud tabel muutub mitte-partitsioneeritud tabeliks

4. Teisenda tavaline tabel partitsioneeritud tabeliks, loo tabelisse klasterindeks ja kasuta klasterindeksi partitsioneerimisskeemi. Näiteks, kui mitte-partitsioneeritud tabeli väli 1 loob klasterdatud indeksi ja on seotud partitsioneerimisskeemiga, teisendatakse tabel partitsioneeritud tabeliks ja partitsioneeritud väli on väli 1.

5. Teisenda partitsioneeritud tabel tavaliseks tabeliks, kui on olemas partitsiooniväli indeksiga, ehita see ümber klasterdatud indeksiks ja mitte seostu partitsiooniskeemiga, kui partitsiooniväljal puudub indeks, võib partitsiooniväli luua uue klasterdatud indeksi ja mitte seostuda partitsiooniskeemiga

6. Tavalise tabeli muutmine partitsioonitabeliks või partitsioonitabeli muutmine tavaliseks saab toimuda ainult klasterdatud indeksite abil, sest klasterdatud indeksite puhul on olemas indeksorganisatsiooni tabelid ning tabelite ümberjaotamine toimub klasterdatud indeksite rekonstrueerimise kaudu. Kui tavaline tabel muutub partitsioneeritud tabeliks, rekonstrueeri partitsiooniväli klasterindeksiks ja seose see partitsiooniskeemiga, ning kui partitsioonitabel muutub tavaliseks tabeliks, rekonstrueeri partitsiooniväli klastriindeksiks ilma partitsiooniskeemi sidumata.

7. Partitsioonitabeli loomiseks peab unikaalsuspiirang sisaldama partitsiooniveeru

8. Partitsiooniskeemi loomisel tuleb veenduda, et failigruppide arv vastab partitsioonifunktsiooni partitsioonivahemiku segmendile ning pole vahet, kas failigrupi nimi dubleeritakse.

9. Partitsioneerimisfunktsioonid ja partitsioneerimisskeemid asuvad andmebaasis, mitte kogu instantsi poole

10. Partitsioonitabel on liiga suur, et võtta palju kettaruumi, ja suurus ei muutu pärast mõnede väljade kustutamist

Sammud partitsioonitabeli loomiseks

1.1. Näide failigrupi loomisest

alter database test1 lisa failigrupp osa1;

alter database test1 lisa failigrupp part1000;

alter database test1 lisa failigrupp part2000;

alter database test1 lisa failigrupp part3000;

alter database test1 lisa failigrupp part4000;

1.2. Loo faili näide ja seose failigrupp

MUUDA ANDMEBAASI TEST1 LISA FAIL(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SUURUS = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) FAILIGRUPPI OSA1;

MUUDA ANDMEBAASI TEST1 LISA FAIL(NAME = TEST1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) FAILIGRUPPI PART1000;

MUUDA ANDMEBAASI TEST1 LISA FAIL(NAME = TEST1part2000,FILENAME = 'G:\test1part2000.ndf', SUURUS = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) FAILIGRUPPI PART2000;

MUUDA ANDMEBAASI TEST1 LISA FAIL(NAME = TEST1part3000,FILENAME = 'G:\test1part3000.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) FAILIGRUPPI PART3000;

MUUDA ANDMEBAASI TEST1 LISA FAIL (NAME = TEST1part4000,FILENAME = 'G:\test1part4000.ndf', SUURUS = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) FAILIGRUPPI PART4000;

2. Näide partitsioonifunktsiooni loomisest, partitsioonifunktsiooni nimetatakse partfun1

LOO PARTITSIOONIFUNKTSIOON partfun1 (int)

VÄÄRTUSTE VASAKPOOLSE VAHEMIKUNA ('1000','2000','3000','4000')

--VALUES ('1000', '2000', '3000', '4000') näitab, et tabel jaguneb viieks tsooniks, mis on jagatud tabeliväljade väärtuste suuruse järgi, viis tsooni on väikseimad – 1000, 1000-2000, 2000-3000, 3000-4000, 4000-suurim

3. Näide skeemi loomisest: seose partitsioonifunktsioon partfun1 ja seose failigrupp

LOO PARTITSIOONISKEEM partschema1

KUI PARTITSIOON partfun1

TO (osa 1, osa 1000, osa 2000, osa 3000, osa 4000);

--Ehitatud mitmele failigrupile: part 1, part 1000, part2000, part 3000, part 4000

LOO PARTITSIOONISKEEM partschema2

KUI PARTITSIOON partfun1

KUNI (1,[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

--Ehitatud osa 1, [PRIMARY] failigrupile, pole probleemi asendada osa 1 [PRIMARY]ga, et see oleks sarnane [PRIMARY] failigrupi ehitamisega

LOO PARTITSIOONISKEEM osachema3

KUI PARTITSIOON partfun1

ALL TO (osa 1);

--kõik ehitatud Part 1 failigrupile

LOO PARTITSIOONISKEEM partschema4

KUI PARTITSIOON partfun1

KÕIK KUNI ([PRIMARY]);

--kõik ehitatud [PRIMARY] failigrupile

4. Näide partitsioonitabeli loomisest

LOO TABEL OSTABLE1(

[ID] [int] EI OLE NULL,

[IDText] [nvarchar] (max) NULL

[Kuupäev] [kuupäevaaeg] NULL)

ON [partschema1](ID);

sisesta Parttable1 väärtused (1,'1',getdate()-4);

sisesta Parttable1 väärtused (1001,'1001',getdate()-3);

sisesta osatabelisse 1 väärtused (2001,'2001',getdate()-2);

sisesta Parttable1 väärtused (3001,'3001',getdate()-1);

sisesta parttable1 väärtused (4001,'4001',getdate());

5. Kontrolli partitsioonitabeli andmeid

VALI * OSATABELIST 1;

--Tagastab kõik partitsioonitabeli read

VALI erinevad $PARTITION. [partfun1] (4) OSATABELIST 1;

--Tagastab, millele kuulub rida, mille ID-välja väärtus on 4,

VALI * OSATABELIST 1, kus $PARTITION. [partfun1] (ID)=2

--Tagastab kõik read teises partitsioonis, ID on partitsiooni välja ID

Märkus: Sa ei saa kasutada SELECT * FROM parttable1, kus $PARTITION. [partfun1] (ID)=2 on tulemus, mis tähendab, et tegemist on partitsioneeritud tabeliga, ja selle artikli viimane test 7 on mittepartitsioneeritud tabel, kuid SELECT * FROM parttable1, kus $PARTITION. [partfun1] (ID)=2 on endiselt tulemus

Uued partitsioonid

1. Määra failigrupp, mida saab kasutada partitsiooniskeemi jaoks (lisa failigrupp partitsiooniskeemile).

2. Muuda partitsioonifunktsiooni (lisa partitsioonifunktsiooni andmevahemik)

MUUDA PARTITSIOONISKEEMI osakeema1 JÄRGMINE KASUTUS [PRIMAARNE]

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

Vali p.partition_number,p.read sys.indexes i sisemine liitmine sys.partitions p peal p.object_id = i.object_id ja i.object_id = object_id('parttable2') järjestus 1 võrra

--Esimene lause, kui partitsiooniskeem kasutab ALL TO ([PRIMARY]), ei pea seda lauset täitma

--Teisele väitele lisatakse uus partitsioon, vahemik on 4000-4500

--Kolmas väide kontrollib, kas uus partitsioon eksisteerib ja kas on ridu

Delete\Merge partitsioon

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

1000–2000 jaotus kustutati ja liideti 1000–3000-ks

--Ei saa käivitada ALTER TABLE TABLENAME DROP PARTITION PARTITIONNAME nagu oracle;

Kustuta partitsioonitabel ja vastav failigrupp

Kustutamise järjekord on: kustuta partitsioonitabel, kustuta partitsiooniskeem, kustuta partitsioonifunktsioon ja lõpuks failigrupp, ning vastav fail kustutatakse pärast failigrupi kustutamist

Näide partitsioneeritud tabeli teisendamisest tavaliseks tabeliks ja tavalise tabeli teisendamisest partitsioneeritud tabeliks

DROP TABLE osatabel1;

LOO TABEL OSTABLE1(

[ID] [int] IDENTITEET(1,1) EI NULL,

[Nimi] [varchar] (16) EI OLE,

[Id2] [int] MITTE NULL

) ON partschema1(Id2);

sisestada Parttable1 väärtused ('1',1);

sisestada parttable1 väärtused ('1001',1001);

lisada osatabelisse 1 väärtused ('2001',2001);

sisesta osatabelisse 1 väärtused ('3001',3001);

sisesta osatabelisse 1 väärtused ('4001',4001);

1. Partitsioonitabelis loodud unikaalne piirang peab sisaldama partitsiooniveeru.

MUUDA TABEL, OSATABEL1 LISA PIIRANG PK_prattable1_id PRIMAARVÕTI KLASTERDATUD ([ID] ASC)

Veaveerg 'Id2' on indeksi 'PK_prattable1_id' partitsiooniveerg. Unikaalse indeksi partitsiooniveerud peavad olema indeksvõtme alamhulk.

2. Loo uus klasterindeks partitsiooniveergus id2 ja parttable1 jääb endiselt partitsioneeritud tabeliks

luua klasterdatud indeks CI_prattable1_id2 parttable1(id2)-s;

3. Partitsiooni veerg id2 loob mitteklasterdatud indeksi ning parttable1 on endiselt partitsioneeritud tabel

langusindeks CI_prattable1_id2 parttable1-s;

luua mitteklasterdatud indeks NCI_prattable1_id2 parttable1(id2)-s;

4. Mitte-partitsioneeritud veergu id-veerg loob klasterdatud indeksi, parttable1 või partitsioneeritud tabeli, mis näitab, et mitte-partitsioneeritud veerg võib olla klastriindeksi veerg

luua klasterdatud indeks CI_prattable1_id parttable1(id)-s;

5. Partitsiooni veerg ID2 on ümber ehitatud mitteklasterdatud indeksiks ega kasuta partitsioneerimisskeemi, parttable1 on endiselt partitsioonitabel

loo mitteklasterdatud indeks NCI_prattable1_id2 Parttable1(id2) PEAL, KUS (DROP_EXISTING = SEES) ON [PRIMARY];

6. Partitsiooniveerg id2 rekonstrueeritakse klastriindeksiks ilma ON tingimuseta ning parttable1 on endiselt partitsioonitabel

langusindeks CI_prattable1_id parttable1-s;

langusindeks NCI_prattable1_id2 parttable1-s;

luua klasterdatud indeks CI_prattable1_id2 parttable1(id2)-s;

luua klasterdatud indeks CI_prattable1_id2 parttable1(id2) LEHEL (DROP_EXISTING = ON);

7. Partitsioneeritud veeru id2 ehitatakse ümber klasterindeksiks ON tingimusega, kuid ilma partitsioneerimisskeemi kasutamata, ning parttable1 muutub mitte-partitsioneeritud tabeliks

Loo klasterindeks CI_prattable1_id2 Parttable1(id2)-LE, KUS (DROP_EXISTING = SEES) ON [PRIMARY]-L;

8. Partitsiooni veerg id2 ehitatakse ümber klastriindeksiks ja partitsioneerimisskeemi abil muutub parttable1 partitsioneeritud tabeliks

luua klasterindeks CI_prattable1_id2 parttable1(id2) KOOS (DROP_EXISTING = ON) partschema1(id2)-s;

9. Pärast ülaltoodud 8 klastriindeksi kustutamist on parttable1 endiselt partitsioneeritud tabel

langusindeks CI_prattable1_id2 parttable1-s;

10. Partitsiooniveerg ID2 luuakse klasterdatud indeksina ega kasuta partitsiooniskeemi ning parttable1 muutub mittepartitsioneeritud tabeliks

luua klasterindeks CI_prattable1_id2 parttable1(id2) PEAL [PRIMARY];

11. Pärast ülaltoodud 10 klastriindeksi kustutamist on parttable1 endiselt mittejagatud tabel

langusindeks CI_prattable1_id2 parttable1-s;

12. Partitsiooniveerg id2 luuakse uuena mitteklasterdatud indeksina, kuigi kasutatakse partitsioneerimisskeemi, on see siiski mitte-partitsioneeritud tabel

luua mitteklasterdatud indeks NCI_prattable1_id2 parttable1(id2) osaschema1(id2) peal;

Kui partitsioneeritud tabel teisendatakse tavaliseks tabeliks, kustutatakse primaarvõtme piirang ning klastriindeks ehitatakse uuesti või ehitatakse algse primaarvõtme väljade primaarvõtmeks, kuid see ei ole seotud partitsioneerimisskeemiga

MUUDA TABELIT Table_name KAOTA PIIRANG PK_NAME KUI (>

LOO KLASTERDATUD INDEKS PK_NAME Table_name(VEERG) PEAL (PEAL [PRIMARY];

või

MUUDA TABELIT Table_name LISA PIIRANG PK_NAME PRIMAARVÕTI KLASTERDATUD(veerg) JUURES (PEAL [PRIMARY];

Kui tavaline tabel teisendatakse partitsioneeritud tabeliks, kui algne primaarvõti säilitatakse, kustutatakse esmase võtme piirang ning seejärel luuakse esmane võti ilma seda agregeeritud indeksina määramata, seejärel luuakse uus koondatud indeks ja partitsioneerimisskeemi kasutatakse koondatud indeksis

MUUDA TABELIT Table_name KAOTA PIIRANG PK_NAME KUI (>

MUUDA TABELIT Table_name LISA PIIRANG PK_NAME PRIMAARVÕTI MITTEKLASTERDATUD(veerg) JUURES (PEAL [PRIMARY];

--Loo primaarvõti, kuid mitte klasterdatud indeksina

LOO KLASTRITUD INDEKS index_name Table_name(veerg) PARTITSIOONISKEEMIL (partitsiooniväli)

--Loo uus klasterindeks, kus kasutatakse partitsioneerimisskeemi

Küsi partitsioneeritud tabeli ridade koguarvu ja suuruse kohta, näiteks tabel on CRM. EmailLog

Juht sp_spaceused 'CRM. EmailLog';

Küsi partitsioneeritud tabeli andmeid, mitu ridu igal partitsioonil on, näiteks on tabel CRM. EmailLog

Vali konverteerimine (varchar(50), ps.name

) kui partition_scheme,

p.partition_number,

konvert (varchar(10), ds2.name

) failigrupina,

konvert(varchar(19), isnull(v.väärtus, ''), 120) kui range_boundary,

str(p.rows, 9) kui read

sys.indexes i-st

Liitu sys.partition_schemes PS-iga i.data_space_id = ps.data_space_id

Liitu sys.destination_data_spaces DDS-iga

on ps.data_space_id = dds.partition_scheme_id

Liitu sys.data_spaces DS2-ga dds.data_space_id = ds2.data_space_id

Ühenda sys.partitions p peal dds.destination_id = p.partition_number

ja p.object_id = i.object_id ja p.index_id = i.index_id

Liitu sys.partition_functions PF ps.function_id = pf.function_id

VASAK, liitu süsteemiga. Partition_Range_values v pf.function_id = v.function_id

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

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

ja i.index_id (0, 1)

Tellimus p.partition_number

Päringu partitsioonifunktsioonid

Vali * sys.partition_functions

Vaata partitsiooni arhitektuuri

Vali * sys.partition_schemes