[Comunicazione] Riepilogo delle tabelle partizionate su SQL Server

Documentazione ufficiale

Passaggi di alto livello per creare una tabella di partizione

1. Creare un file group (simile a Oracle Table Space), ovviamente non puoi crearlo, puoi mettere tutte le partizioni in un unico file group

2. Stabilire una funzione di partizione e distribuire i dati in base all'intervallo

3. Stabilisci uno schema di partizioni, associa la funzione di partizione e associa anche il gruppo di file, la funzione di partizione divide i dati in diversi intervalli, devi associare diversi gruppi di file, ovviamente puoi anche inserire questi intervalli di partizioni nello stesso gruppo di file

4. Creare una tabella e associare lo schema di zonizzazione

Incontro di un bug

Quando fai clic destro sulla tabella per esportare la struttura della tabella, non puoi esportare le informazioni della partizione, e puoi esportare le informazioni della tabella solo cliccando con il tasto destro sullo script database-task-generate

Alcune conclusioni dalla tabella delle partizioni:

1. I campi partizionati non devono necessariamente essere indicizzati

2. I campi partizionati possono essere creati come indici raggruppati o indici noclusterizzati

3. I campi partizionati, siano essi indici clusterizzati o noclustered, vengono ricostruiti in indici clusterizzati senza schemi di partizionamento associati, e la tabella partizionata diventa una tabella non partizionata

4. Converti una tabella ordinaria in una tabella partizionata, basta creare un indice clusterizzato nella tabella e usare lo schema di partizionamento sull'indice clusterizzato. Ad esempio, quando il campo 1 di una tabella non partizionata crea un indice clusterizzato ed è associato a uno schema di partizionamento, la tabella viene convertita in una tabella partizionata, e il campo partizionato è campo 1.

5. Convertire la tabella partizionata in una tabella normale, se c'è un campo di partizione con indice, ricostruire il campo partizionato come un indice clusterizzato e non associarlo allo schema di partizione; se il campo di partizione non ha un indice, il campo di partizione può creare un nuovo indice raggruppato e non associarsi allo schema di partizione

6. Cambiare una tabella normale in una tabella di partizione o cambiare una tabella di partizione in una tabella ordinaria può essere ottenuto solo usando indici clusterizzati, perché con indici clusterizzati sono tabelle di organizzazione degli indici, e la ridistribuzione delle tabelle avviene tramite la ricostruzione degli indici clusterizzati. Se la tabella ordinaria diventa una tabella partizionata, ricostruisci il campo di partizione in un indice clusterizzato e lo associa allo schema di partizione, e se la tabella di partizione diventa una tabella normale, ricostruisci il campo di partizione in un indice clusterizzato senza associare lo schema di partizione.

7. Per creare una tabella di partizione, il vincolo di unicità deve includere una colonna di partizione

8. Quando si crea uno schema di partizioni, è necessario assicurarsi che il numero di gruppi di file corrisponda al segmento dell'intervallo di partizione della funzione di partizione, e non importa se il nome del gruppo file è duplicato.

9. Le funzioni di partizionamento e gli schemi di partizionamento sono in un database, non rivolgono l'intera istanza

10. La tabella delle partizioni è troppo grande per occupare molto spazio su disco, e la dimensione non cambia comunque dopo aver eliminato alcuni campi

Passaggi per creare una tabella di partizione

1.1. Esempio di creazione di un gruppo di file

alter database test1 aggiungi file group parte 1;

alter database test1 aggiungi file group part1000;

alter database test1 add filegroup part2000;

alter database test1 aggiungi file group part3000;

alter database test1 aggiungi filegroup part4000;

1.2. Stabilire un esempio di file e associare un gruppo di file

ALTER DATABASE test1 AGGIUNGI FILE(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) AL FILEGROUP part1;

ALTER DATABASE test1 AGGIUNGI FILE(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) al file group part1000;

ALTER DATABASE test1 AGGIUNGI FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) al filegroup part2000;

ALTER DATABASE test1 AGGIUNGI FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) AL FILEGROUP part3000;

ALTER DATABASE test1 AGGIUNGI FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) AL FILEGROUP part4000;

2. Un esempio di stabilimento di una funzione di partizione, la funzione di partizione è chiamata partfun1

CREA FUNZIONE PARTITION partfun1 (int)

COME INTERVALLO RIMANENTE PER I VALORI ('1000', '2000', '3000', '4000')

--VALORI ('1000', '2000', '3000', '4000') indicano che la tabella sarà divisa in 5 zone, che sono divise in base alla dimensione dei valori nei campi della tabella, le cinque zone sono le più piccole - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-più grandi

3. Un esempio di come stabilire uno schema, associare la funzione di partizione partfun1 e associare il gruppo file

CREATE PARTITION SCHEME partschema1

COME PARTIZIONE PARTE 1

TO (part1, part1000, part2000, part3000, part4000);

--Costruito su diversi file group di part1, part1000, part2000, part3000, part4000

CREA SCHEMA DI PARTIZIONE partschema2

COME PARTIZIONE PARTE 1

TO (PARTE1,[PRIMARIA], [PRIMARIA], [PRIMARIA]);

--Costruito sulla parte 1, [PRIMARY] file group, non è un problema sostituire la part1 con [PRIMARY], in modo che sia simile alla costruzione del file group [PRIMARY]

CREA SCHEMA DI PARTIZIONE partschema3

COME PARTIZIONE PARTE 1

ALL TO (parte 1);

--tutti costruiti sul file group parte 1

CREATE PARTITION SCHEME partschema4

COME PARTIZIONE PARTE 1

TUTTI A ([PRIMARIO]);

--tutti costruiti sul file group [PRIMARY]

4. Esempio di creazione di una tabella di partizione

CREATE TABLE PARTTABLE1(

[ID] [int] NON NULLO,

[IDText] [nvarchar] (max) NULLO

[Data] [datatime] NULL)

ON [partschema1](ID);

inserire in parttable1 valori (1,'1', getdate()-4);

inserire in parttable1 valori (1001,'1001', getdate()-3);

inserire in parttable1 valori (2001, '2001', getdate()-2);

inserire in parttable1 i valori (3001,'3001', getdate()-1);

inserire in parttable1 valori (4001,'4001', getdate());

5. Verifica i dati della tabella delle partizioni

SELEZIONA * DA PARTTABLE1;

--Restituisce tutte le righe della tabella delle partizioni

SELEZIONA $PARTITION distinti. [partfun1] (4) DALLA parte tabella1;

--Restituisce che partiziona la riga con il valore del campo ID 4 a cui appartiene

SELEZIONA * DA parttable1 dove $PARTITION. [partfun1] (ID)=2

--Restituisce tutte le righe nella seconda partizione, l'ID è l'ID del campo partizione

Nota: Non puoi usare SELECT * FROM parttable1 dove $PARTITION. [partfun1] (ID)=2 ha un risultato, significa che è una tabella partizionata, e l'ultimo test 7 di questo articolo è una tabella non partizionata, ma SELECT * FROM parttable1 dove $PARTITION. [partfun1] (ID)=2 ha ancora un risultato

Nuove partizioni

1. Specificare un gruppo di file che possa essere utilizzato per lo schema di partizione (aggiungere un gruppo di file per lo schema di partizioni).

2. Modificare la funzione di partizione (aggiungere l'intervallo di dati della funzione di partizione)

ALTER PARTITION SCHEME parteschema1 SUCCESSIVAMENTE USATO [PRIMARY]

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

seleziona p.partition_number,p.righe da sys.indexes i join internamente sys.partitions p on p.object_id = i.object_id e i.object_id = object_id('parttable2') ordinano di 1

--La prima istruzione, se lo schema di partizione usa ALL TO ([PRIMARY]), questa istruzione non deve essere eseguita

--Una nuova partizione viene aggiunta alla seconda istruzione, l'intervallo è 4000-4500

--La terza affermazione verifica se la nuova partizione esiste e se ci sono righe

Elimina\Unisci partizione

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

La partizione 1000-2000 fu eliminata e unita in 1000-3000

--Impossibile eseguire ALTER TABLE TABLENAME DROP PARTITIONNAME PARTITIONNAME come oracle;

Elimina la tabella delle partizioni e il corrispondente gruppo di file

L'ordine di cancellazione è: elimina la tabella delle partizioni, elimina lo schema delle partizioni, elimina la funzione partizionale e infine elimina il gruppo file, e il file corrispondente viene eliminato dopo che il gruppo file è stato eliminato

Esempio di conversione di una tabella partizionata in una tabella normale e di una tabella normale in una tabella partizionata

TABELLA DI RACCOLTA 1;

CREATE TABLE PARTTABLE1(

[Id] [int] IDENTITÀ(1,1) NON NULLA,

[Nome] [varchar] (16) NON NULLA,

[Id2] [int] NON NULLO

) ON partschema1(Id2);

inserire nei valori della parttable1 ('1',1);

inserire nei valori della parttable1 ('1001', 1001);

inserire nei valori della parttable1 ('2001', 2001);

inserire in parttable1 valori ('3001', 3001);

inserire in parttable1 i valori ('4001', 4001);

1. Il vincolo unico creato sulla tabella di partizione deve contenere una colonna di partizione.

ALTER TABLE PARTTABLE1 AGGIUNGE VINCOLO PK_prattable1_id CLUSTER CHIAVE PRIMARIA ([ID] ASC)

La colonna di errore 'Id2' è la colonna di partizionamento dell'indice 'PK_prattable1_id'. Le colonne di partizione per un indice unico devono essere un sottoinsieme della chiave indice.

2. Crea un nuovo indice clusterizzato nella colonna di partizione id2, e parttable1 resta comunque una tabella partizionata

creare indice clusterizzato CI_prattable1_id2 su parttable1(id2);

3. La colonna di partizione id2 crea un indice non clusterizzato, e parttable1 è ancora una tabella partizionata

indice di caduta CI_prattable1_id2 su parttable1;

creare indice non clusterizzato NCI_prattable1_id2 su parttable1(id2);

4. La colonna ID della colonna non partizionata crea un indice clusterizzato, un parttable1 o una tabella partizionata, indicando che la colonna non partizionata può essere una colonna indice cluster

creare indice clusterizzato CI_prattable1_id su parttable1(id);

5. La colonna di partizione ID2 viene ricostruita in un indice non clusterizzato e non utilizza uno schema di partizionamento, parttable1 è ancora una tabella di partizione

creare indice non clusterizzato NCI_prattable1_id2 su parttable1(id2) CON (DROP_EXISTING = ON) SU [PRIMARY];

6. La colonna di partizione id2 viene ricostruita in un indice clusterizzato senza la condizione ON, e parttable1 è ancora una tabella di partizione

indice di caduta CI_prattable1_id su parttable1;

indice di caduta NCI_prattable1_id2 su parttable1;

creare indice clusterizzato CI_prattable1_id2 su parttable1(id2);

creare indice clusterizzato CI_prattable1_id2 su parttable1(id2) CON (DROP_EXISTING = ON);

7. La colonna partizionata id2 viene ricostruita in un indice clusterizzato con condizione ON ma senza usare lo schema di partizionamento, e parttable1 diventa una tabella non partizionata

creare indice clusterizzato CI_prattable1_id2 su parttable1(id2) CON (DROP_EXISTING = ON) SU [PRIMARIO];

8. La colonna di partizione id2 viene ricostruita in un indice clusterizzato e, utilizzando lo schema di partizionamento, parttable1 diventa una tabella partizionata

creare indice clusterizzato CI_prattable1_id2 su parttable1(id2) CON (DROP_EXISTING = ON) su partschema1(Id2);

9. Dopo aver eliminato l'indice clusterizzato in 8 sopra, parttable1 resta una tabella partizionata

indice di caduta CI_prattable1_id2 su parttable1;

10. La colonna di partizione ID2 viene creata come un indice clusterizzato e non utilizza lo schema di partizionamento, e parttable1 diventa una tabella non partizionata

creare indice clusterizzato CI_prattable1_id2 su parttable1(id2) SU [PRIMARY];

11. Dopo aver eliminato l'indice clusterizzato dei 10 sopra, parttable1 resta una tabella non partizionata

indice di caduta CI_prattable1_id2 su parttable1;

12. La colonna di partizione id2 è stata creata come indice non clusterizzato, anche se viene utilizzato lo schema di partizionamento, è comunque una tabella non partizionata

creare indice non clusterizzato NCI_prattable1_id2 su parttable1(ID2) su partschema1(Id2);

Se la tabella partizionata viene convertita in una tabella normale, il vincolo della chiave primaria viene eliminato e l'indice del cluster viene ricostruito o ricostruito nella chiave primaria per i campi della chiave primaria originale, ma non è correlato allo schema di partizionamento

ALTER TABLE Table_name DROP VINCTRIT PK_NAME CON (>

CREARE PK_NAME INDICE CLUSTERIZZATO SU Table_name(COLONNA) CON (SU [PRIMARIO];

o

ALTER TABLE Table_name AGGIUNGERE VINCOLO PK_NAME PRIMARY KEY CLUSTERED(column) CON (SU [PRIMARY];

Se la tabella normale viene convertita in una tabella partizionata, se la chiave primaria originale deve essere mantenuta, il vincolo della chiave primaria viene eliminato, quindi la chiave primaria viene creata senza impostarla come indice aggregato, e poi viene creato un nuovo indice aggregato, e lo schema di partizionamento viene utilizzato nell'indice aggregato

ALTER TABLE Table_name DROP VINCTRIT PK_NAME CON (>

ALTER TABLE Table_name AGGIUNGERE VINCOLO PK_NAME CHIAVE PRIMARIA NONCLUSTERED(column) CON (SU [PRIMARIA];

--Creare una chiave primaria, ma non impostata come indice clusterizzato

CREA index_name DI INDICE CLUSTERIZZATO SU Table_name(colonna) SULLO schema di partizione (campo partizione)

--Creare un nuovo indice clusterizzato in cui venga utilizzato uno schema di partizionamento

Interroga il numero totale di righe e la dimensione di una tabella partizionata, ad esempio, la tabella è CRM. EmailLog

Esecutivo sp_spaceused 'CRM. EmailLog';

Interroga le informazioni di una tabella partizionata, quante righe ha ciascuna partizione, ad esempio, la tabella è CRM. EmailLog

seleziona convertir(varchar(50), ps.name

) come partition_scheme,

p.partition_number,

convert(varchar(10), ds2.name

) come file group,

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

str(p.rows, 9) come righe

Da sys.Indexes I

unirsi sys.partition_schemes ps su i.data_space_id = ps.data_space_id

Unisciti sys.destination_data_spaces DDS

su ps.data_space_id = dds.partition_scheme_id

unirsi sys.data_spaces DS2 su dds.data_space_id = ds2.data_space_id

Unisci sys.partizioni p su dds.destination_id = p.partition_number

e p.object_id = i.object_id e p.index_id = i.index_id

unirsi sys.partition_functions pf su ps.function_id = pf.function_id

JOIN SYS. Partition_Range_values v su pf.function_id = v.function_id

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

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

e i.index_id in (0, 1)

Ordine di p.partition_number

Funzioni di partizione di query

seleziona * da sys.partition_functions

Visualizza l'architettura della partizione

seleziona * da sys.partition_schemes