È possibile creare una tabella o un indice partizionato in SQL Server 2016 usando SQL Server Management Studio o Transact-SQL. I dati in una tabella partizionata e negli indici sono divisi orizzontalmente in blocchi che possono essere distribuiti in più filegroup nel database. Il partizionamento può migliorare la gestibilità e la scalabilità di tabelle e indici di grandi dimensioni.

O l'indice di solito include quattro passaggi:

Creare uno o più filegroup e i file corrispondenti che conterranno le partizioni in base allo schema di partizionamento.

Crea una funzione di partizione che mappa le righe della tabella o dell'indice alle partizioni in base ai valori degli elementi di una determinata colonna.

Creare uno schema di partizione che associ le partizioni di una tabella o indice partizionato a nuovi filegroup.

Creare o modificare una tabella o un indice e specificare uno schema di partizione come posizione di archiviazione.

In questa sezione

Prima di iniziare, completare i seguenti passaggi.

Restrizioni

Sicurezza

Creare una tabella o un indice partizionato utilizzando i seguenti strumenti:

Studio di gestione di SQL Server

Restrizioni

L'ambito di una funzione e di uno schema di partizione è limitato al database in cui è stato creato. Le funzioni di partizionamento risiedono in uno spazio dei nomi separato dalle altre funzioni all'interno del database.

Se qualsiasi riga nella funzione di partizione ha colonne di partizione nulle, tali righe vengono posizionate nella partizione più a sinistra. Tuttavia, se viene specificato null come valore limite e viene specificata l'opzione RIGHT, la partizione più a sinistra viene lasciata vuota e i valori null vengono inseriti nella seconda partizione.

Sicurezza

Autorizzazioni

La creazione di una tabella partizionata richiede l'autorizzazione CREATE TABLE sul database e l'autorizzazione ALTER sullo schema in cui viene creata la tabella. La creazione di un indice partizionato richiede l'autorizzazione ALTER sulla tabella o sulla vista in cui viene creato l'indice. La creazione di una tabella o di un indice partizionato richiede una delle seguenti autorizzazioni aggiuntive:

ALTER ANY DATASPACE permesso. Questa autorizzazione viene assegnata per impostazione predefinita ai membri del ruolo predefinito del server amministratore di sistema e ruoli di database predefiniti db_proprietario E db_ddladmin.

Autorizzazione CONTROL o ALTER sul database in cui vengono creati la funzione e lo schema di partizione.

Autorizzazione CONTROL SERVER o ALTER ANY DATABASE sul server di database su cui vengono creati la funzione e lo schema di partizione.

Completare istruzioni passo passo in questa procedura per creare uno o più filegroup, i file corrispondenti e una tabella. Nell'esempio seguente, i riferimenti a questi oggetti verranno forniti durante la creazione di una tabella partizionata.

Creazione di nuovi filegroup per una tabella partizionata

Creare una tabella partizionata

Fare clic con il tasto destro sulla tabella da partizionare, selezionare Magazzinaggio e clicca Crea sezione...

IN Sezione guidata Sulla pagina Benvenuti nella procedura guidata della sezione clic Ulteriore.

Sulla pagina Selezione della colonna della partizione nella griglia selezionare la colonna in base alla quale si desidera partizionare la tabella. nella griglia Colonne di partizionamento disponibili vengono visualizzate solo le colonne con tipi di dati che possono essere partizionati. Se selezioni una colonna calcolata come colonna di partizionamento, devi renderla permanente.

La scelta della colonna di partizionamento e dell'intervallo di valori è determinata principalmente dal grado in cui i dati devono essere raggruppati logicamente. Ad esempio, puoi suddividere i dati in gruppi logici per mese o trimestre dell'anno. Le query di dati pianificate determinano se tale raggruppamento logico è adeguato per la gestione delle partizioni della tabella. Qualsiasi tipo di dati può essere utilizzato come colonne di partizionamento, tranne testo, testo, Immagine, xml, timestamp, varchar(massimo), nvarchar(massimo), varbinario(massimo), alias dei tipi di dati e tipi di dati CLR definiti dall'utente.

Collocazione di questa tabella con la tabella partizionata selezionata
Consente di selezionare una tabella partizionata che contiene dati correlati da unire a questa tabella mediante partizionamento della colonna. Le tabelle partizionate unite da colonne di partizionamento sono in genere più efficienti nelle query.

Allinea l'archiviazione di file non univoci e indici univoci con colonna di partizionamento indicizzata
Allinea tutti gli indici su una tabella che sono partizionati utilizzando lo stesso schema. Allineando una tabella e i relativi indici, le partizioni possono essere spostate in modo più efficiente all'interno e all'esterno delle tabelle partizionate poiché i dati vengono partizionati utilizzando lo stesso algoritmo.

Dopo aver selezionato la colonna di partizionamento e le altre colonne, fare clic su Ulteriore.

Sulla pagina Selezione della funzione di partizionamento Nel cap Selezionare la funzione di sezionamento fare clic su o . Quando si sceglie Creare una funzione di sezionamento inserire un nome di funzione. Se l'opzione è selezionata Funzione esistente sezionando, quindi selezionare dall'elenco il nome della funzione che verrà utilizzata per il partizionamento. Si noti che se non ci sono altre funzioni di partizionamento nel database, il file Funzione di partizionamento esistente non sarà disponibile.

Ulteriore.

Sulla pagina Selezione di uno schema di partizionamento Nel cap Scegli uno schema di partizionamento fare clic su o . Quando si sceglie Crea schema di partizione inserire un nome schema. Se l'opzione è selezionata Schema di partizionamento esistente, quindi selezionare dall'elenco il nome dello schema da utilizzare. Se non sono presenti altri schemi di partizione nel database, il parametro Schema di partizionamento esistente non sarà disponibile.

Dopo aver completato questa pagina, fare clic sul pulsante Ulteriore.

Sulla pagina Mappatura delle partizioni Nel cap Allineare Selezionare Bordo sinistro O Bordo destro per selezionare il valore limite massimo o minimo da includere in tutti i filegroup creati. Oltre al numero di filegroup specificati come valori limite durante la creazione delle partizioni, è necessario inserire sempre un filegroup aggiuntivo.

nella griglia Selezione di filegroup e specifica dei valori limite in campo Filegroup selezionare il filegroup in cui verranno partizionati i dati. Nel cap Confine immettere un valore limite per ogni filegroup. Se non viene specificato alcun valore limite, la funzione di partizione associa l'intera tabella o l'indice a una singola partizione usando il nome della funzione di partizione.

In questa pagina sono disponibili le seguenti opzioni aggiuntive:

Imposta i confini...
Apertura di una finestra di dialogo Impostazione dei valori limite, in cui è possibile selezionare punti di interruzione e intervalli di date per le partizioni. Questa opzione è disponibile solo se si seleziona una colonna di partizionamento che contiene uno dei seguenti tipi di dati: data, appuntamento, smalldatetime, dataora2 O datetimeoffset.

Valutazione di archiviazione
Stima del numero di righe necessarie e spazio disponibile per archiviare ogni filegroup specificato per le partizioni. Questi valori sono di sola lettura nella griglia.

Nella finestra di dialogo Impostazione dei valori limiteÈ possibile impostare le seguenti opzioni aggiuntive:

data d'inizio
Selezionare una data di inizio per i valori dell'intervallo di partizioni.

data di scadenza
Selezionare una data di fine per i valori dell'intervallo di partizioni. Quando si sceglie Bordo sinistro Sulla pagina Mappatura delle partizioni questa data sarà l'ultimo valore per ciascuno dei filegroup e delle partizioni. Quando si sceglie Bordo destro Sulla pagina Mappatura delle partizioni questa data sarà il primo valore nel penultimo filegroup.

intervallo di date
Seleziona la granularità della data o il passaggio del valore dell'intervallo per ogni sezione.

Dopo aver completato questa pagina, fare clic sul pulsante Ulteriore.

Sulla pagina Selezione di un parametro di uscita specificare come popolare la tabella partizionata. Selezionare Crea copione per creare uno script SQL basato sui dati nelle pagine precedenti della procedura guidata. Selezionare Corri subito per creare una nuova tabella partizionata dopo aver completato tutte le restanti pagine della procedura guidata. Selezionare Programma per creare in anticipo una nuova tabella partizionata tempo a disposizione in futuro.

Quando si sceglie Crea copione v Parametri dello script saranno disponibili le seguenti opzioni:

Script di output su file
Creazione di uno script come file SQL. Immettere il nome e la posizione del file nel campo Nome del file o clicca Revisione per aprire la finestra di dialogo Posizione del file di script. Nel cap Salva come Selezionare Testo in Unicode O Testo dell'ANSI.

Invia lo script negli appunti
Salvataggio dello script negli appunti.

Visualizza lo script nella nuova finestra della query
Lo script viene creato in una nuova finestra dell'editor di query. Questa opzione è selezionata per impostazione predefinita.

Quando si sceglie Programma clic Cambia orario.

1. Nella finestra di dialogo Crea un programma di lavoro in campo Nome immettere un nome per la pianificazione del lavoro.

   Elencato Tipo di pianificazione seleziona il tipo di programma:

   • Avvia automaticamente all'avvio di SQL Server Agent

     Esegui quando i processori sono inattivi

     ricorrente. Selezionare questa opzione se la nuova tabella partizionata viene regolarmente aggiornata con nuovi dati.

     una volta. Questa opzione è selezionata per impostazione predefinita.

Seleziona o deseleziona Incluso per abilitare o disabilitare la pianificazione.

Quando si sceglie ricorrente:

1. Nel cap Frequenza sulla lista Eseguita specificare la frequenza di esecuzione:

   • Quando si sceglie Quotidiano in campo Funziona ogni Specifica la frequenza con cui è pianificata l'esecuzione dell'attività in giorni.

     Quando si sceglie settimanalmente in campo Funziona ogni Specifica la frequenza con cui il lavoro viene ripianificato in settimane. Selezionare il giorno o i giorni della settimana in cui viene eseguita la pianificazione del processo.

     Quando si sceglie Mensile clic Giorno O Definito.

     • Quando si sceglie Giorno immettere la data del mese in cui deve essere eseguita la pianificazione del lavoro e specificare la frequenza con cui la pianificazione del lavoro deve essere eseguita di nuovo in mesi. Ad esempio, se si desidera che la pianificazione del processo venga eseguita il 15 di ogni secondo mese, selezionare Giorno e inserisci "15" nel primo campo e "2" nel secondo campo. Si prega di notare che il numero inserito nel secondo campo non deve superare "99".

       Quando si sceglie Definito selezionare un giorno specifico della settimana nel mese in cui si desidera eseguire la pianificazione del processo e specificare la frequenza con cui la pianificazione del processo deve essere eseguita di nuovo nei mesi. Ad esempio, se si desidera che la pianificazione del processo venga eseguita l'ultimo giorno della settimana di ogni due mesi, selezionare Giorno, Selezionare scorso nel primo elenco e giorno lavorativo nel secondo elenco, quindi inserire "2" nel secondo campo. Puoi anche scegliere Primo, secondo, terzo O il quarto, nonché giorni specifici della settimana (ad esempio, domenica o mercoledì) nei primi due elenchi. Si prega di notare che il numero inserito nell'ultimo campo non deve superare "99".

In campo Quante volte al giorno specificare la frequenza con cui la pianificazione del lavoro deve essere rieseguita il giorno in cui viene avviata:

• Quando si sceglie Esegui una volta per specificare un'ora del giorno specifica per eseguire la pianificazione del lavoro nel campo Esegui una volta per. Specificare l'ora del giorno: ore, minuti e secondi.

  Quando si sceglie Funziona ogni specificare la frequenza dell'esecuzione dell'attività nel giorno selezionato nel campo Frequenza. Ad esempio, se si desidera che la pianificazione del processo venga eseguita ogni 2 ore nel giorno di inizio della pianificazione del processo, selezionare Funziona ogni., inserisci "2" nel primo campo, quindi seleziona dall'elenco orologio. Puoi anche selezionare da questo elenco minuti E secondi. Si prega di notare che il numero inserito nel primo campo non deve superare "100".

  In campo Iniziare a immettere l'ora di inizio dell'esecuzione della pianificazione del lavoro. In campo finire a inserire l'ora per terminare la riprogrammazione del lavoro. Specificare l'ora del giorno: ore, minuti e secondi.

Nel cap Durata, in zona data d'inizio immettere la data di inizio dell'esecuzione della pianificazione del processo. Selezionare data di scadenza O Nessuna data di fine per specificare la data di fine per l'esecuzione della pianificazione del lavoro. Quando si sceglie data di scadenza immettere la data di fine per l'esecuzione della pianificazione del lavoro.

Quando si sceglie un valore una volta v Esecuzione unica in campo data inserire la data di inizio per la pianificazione del lavoro. In campo Tempo inserire l'ora di inizio per la pianificazione del lavoro. Specificare l'ora del giorno: ore, minuti e secondi.

Nel cap Riepilogo v Descrizione Verificare che tutte le impostazioni di pianificazione dei processi siano corrette.

Fare clic sul pulsante OK.

Dopo aver completato questa pagina, fare clic sul pulsante Ulteriore.

Sulla pagina Visualizzazione di un riepilogo Nel cap Visualizza le opzioni selezionate Espandi tutte le opzioni disponibili per assicurarti che tutte le impostazioni della sezione siano corrette. Se tutte le impostazioni sono corrette, premere il pulsante Pronto.

Pagina Prestazione La Creazione guidata partizione viene utilizzata per tenere traccia delle informazioni sullo stato relative alle azioni della Creazione guidata partizione. A seconda delle azioni selezionate nella procedura guidata, la pagina di avanzamento può contenere una o più azioni. La casella in alto mostra lo stato generale della procedura guidata e il numero di messaggi di stato, di avviso e di errore che ha ricevuto.

Sulla pagina Prestazione La procedura guidata per la creazione della sezione ha le seguenti opzioni:

Intelligenza
Informazioni sull'evento, sullo stato e su eventuali messaggi restituiti come risultato delle azioni della procedura guidata.

Azione
Specifica il tipo e il nome di ogni azione.

Stato
Indica se l'intera azione della procedura guidata ha restituito un valore Con successo O Errore.

Messaggio
Eventuali messaggi di errore o di avviso dal processo.

Rapporto
Creare un report contenente i risultati della Creazione guidata partizione. Opzioni disponibili: Visualizza rapporto, Salva rapporto su file, E Invia rapporto via e-mail.

Visualizzazione di un rapporto
Apertura di una finestra di dialogo Visualizzazione di un rapporto, che contiene un report testuale sulla creazione guidata delle partizioni.

Copia il rapporto negli appunti
Copia i risultati del report della procedura guidata negli Appunti.

Invia rapporto via e-mail
Copia i risultati del rapporto sullo stato della procedura guidata in un messaggio di posta elettronica.

Al termine della selezione delle opzioni, premere il pulsante vicino.

La Creazione guidata partizione creerà una funzione e uno schema di partizione, quindi applicherà la partizione alla tabella specificata. Per verificare il partizionamento di una tabella, in Esplora oggetti fare clic con il pulsante destro del mouse sulla tabella e selezionare Proprietà. Vai alla pagina magazzinaggio. La pagina visualizza informazioni, incluso il nome della funzione di partizione, lo schema e il numero di partizioni.

Creare una tabella partizionata

IN browser degli oggetti connettersi a un'istanza del Motore di database.

Nel pannello standard, selezionare Crea richiesta.

Copia il seguente esempio nella finestra della query e fai clic sul pulsante Correre. L'esempio seguente mostra la creazione di filegroup, la funzionalità e gli schemi di partizionamento. Viene creata una nuova tabella quando si specifica uno schema di partizione come posizione di archiviazione.

USA AdventureWorks2012; ANDARE -- Aggiunge quattro nuovi filegroup al database AdventureWorks2012 ALTER DATABASE AdventureWorks2012 AGGIUNGI FILEGROUP test1fg; ANDARE ALTER DATABASE AdventureWorks2012 AGGIUNGI FILEGROUP test2fg; ANDARE ALTER DATABASE AdventureWorks2012 AGGIUNGI FILEGROUP test3fg; ANDARE ALTER DATABASE AdventureWorks2012 AGGIUNGI FILEGROUP test4fg; -- Aggiunge un file per ogni filegroup. ALTER DATABASE AdventureWorks2012 AGGIUNGI FILE(NOME=test1dat1, NOMEFILE= "C:\Programmi\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf", SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test1fg; ALTER DATABASE AdventureWorks2012 AGGIUNGI FILE(NOME=test2dat2, NOMEFILE= "C:\Programmi\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf", SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test2fg; ANDARE ALTER DATABASE AdventureWorks2012 AGGIUNGI FILE(NOME=test3dat3, NOMEFILE= "C:\Programmi\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf", SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test3fg; ANDARE ALTER DATABASE AdventureWorks2012 AGGIUNGI FILE(NOME=test4dat4, NOMEFILE= "C:\Programmi\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf", SIZE = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) TO FILEGROUP test4fg; ANDARE -- Crea una funzione di partizione chiamata myRangePF1 che suddividerà una tabella in quattro partizioni CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); ANDARE -- Crea uno schema di partizione denominato myRangePS1 che applica myRangePF1 ai quattro filegroup creati in precedenza CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; ANDARE -- Crea una tabella partizionata denominata PartitionTable che utilizza myRangePS1 per partizionare col1 CREATE TABLE PartitionTable (col1 int PRIMARY KEY , col2 char (10 )) ON myRangePS1 (col1) ; ANDARE

Definizione del partizionamento della tabella

La query seguente restituisce una o più righe se PartitionTable è partizionato. Se la tabella non è partizionata, non viene restituita alcuna riga.

Definizione dei valori limite per una tabella partizionata

La query seguente restituisce i valori limite per ogni partizione in PartitionTable .

SELECT t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id AND i .index_id = p .index_id JOIN sys .partition_schemes AS s ON i .data_space_id = s.data_space_id JOIN sys .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partition_range_values ​​AS r ON f .function_id = r.function_id e r.boundary_id = p .partition_number WHERE t .name = "PartitionTable" AND i .type<= 1 ORDER BY p .partition_number;

Definizione della colonna di partizionamento di una tabella partizionata

La query seguente restituisce il nome della colonna di partizionamento di una tabella. Tabella delle partizioni.

SELEZIONA t. AS ObjectID , t .name AS TableName , ic.column_id AS PartitioningColumnID , c .name AS PartitioningColumnName FROM sys .tables AS t JOIN sys .indexes AS i ON t . = io. E io .<= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- perché 0 = colonna senza partizionamento JOIN sys .columns AS c ON t . = C. AND ic.column_id = c .column_id WHERE t .name = "PartitionTable" ; ANDARE

Per ulteriori informazioni, vedere.

Buonasera/pomeriggio/mattina caro habralyudi! Continuiamo a sviluppare e integrare il blog sul mio rdbms open source preferito Postgresql. Miracolosamente, è successo che l'argomento dell'argomento di oggi non sia mai stato sollevato qui. Devo dire che il partizionamento in postgresql è descritto molto bene nella documentazione, ma questo mi fermerà?).

introduzione

In generale, il partizionamento è generalmente inteso non come una sorta di tecnologia, ma piuttosto come un approccio alla progettazione di database apparso molto prima che DBMS iniziasse a supportare il cosiddetto. tabelle partizionate. L'idea è molto semplice: dividere il tavolo in più parti di dimensioni inferiori. Esistono due sottospecie: sezionamento orizzontale e verticale.

Sezionamento orizzontale

Parti della tabella contengono le sue diverse righe. Supponiamo di avere una tabella di log di un'applicazione astratta - LOGS. Possiamo suddividerlo in parti: una per i log di gennaio 2009, una per i log di febbraio 2009 e così via.

Sezionamento verticale

Parti della tabella contengono le sue diverse colonne. Trovare un utilizzo per il partizionamento verticale (quando è veramente giustificato) è un po' più difficile che per il partizionamento orizzontale. Come cavallo sferico, propongo di considerare questa opzione: la tabella NEWS ha le colonne ID, SHORTTEXT, LONGTEXT e lascia che il campo LONGTEXT sia usato molto meno spesso dei primi due. In questo caso, ha senso dividere la tabella NEWS per colonne (creare due tabelle rispettivamente per SHORTTEXT e LONGTEXT, collegate da chiavi primarie + creare una vista NEWS contenente entrambe le colonne). Pertanto, quando abbiamo solo bisogno di una descrizione della notizia, il DBMS non deve leggere l'intero testo della notizia dal disco.

Supporto per il partizionamento nei moderni DBMS

La maggior parte dei DBMS moderni supporta il partizionamento delle tabelle in una forma o nell'altra.

• Oracolo- supporta il partizionamento a partire dalla versione 8. Da un lato, lavorare con le sezioni è molto semplice (non devi pensarci affatto, lavori come con una normale tabella*), ma dall'altro tutto è molto flessibile. Le sezioni possono essere divise in "sottopartizioni", cancellate, divise, trasferite. Sono supportate varie opzioni per l'indicizzazione di una tabella partizionata (indice globale, indice partizionato). Link alla descrizione completa.
• Server SQL Microsoft- il supporto per il partizionamento è apparso di recente (nel 2005). La prima impressione dall'uso è "Bene, finalmente !! :)", la seconda è "Funziona, sembra tutto a posto". Documentazione su msdn
• MySQL- supporta dalla versione 5.1.
• E così via…

*- Sto mentendo, ovviamente, c'è una serie standard di difficoltà: creare una nuova sezione in tempo, buttare via quella vecchia, ecc., Ma comunque in qualche modo tutto è semplice e chiaro.

Partizionamento in Postgresql

Il partizionamento delle tabelle in postgresql è in qualche modo diverso nell'implementazione rispetto ad altri database. Il partizionamento si basa sull'ereditarietà della tabella (una cosa unica di postgresql). Cioè, dobbiamo avere una tabella principale (tabella principale) e le sue sezioni saranno tabelle discendenti. Prenderemo in considerazione la sezione sull'esempio di un compito vicino alla realtà.

Formulazione del problema

Il database viene utilizzato per raccogliere e analizzare i dati sui visitatori del sito/i. I volumi di dati sono sufficientemente grandi da giustificare il partizionamento. Nell'analisi, nella maggior parte dei casi, vengono utilizzati i dati dell'ultimo giorno.
1. Crea la tabella principale:

CREATE TABLE analytics.events

user_id UUID NON NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR(1024) NOT NULL ,
riferimento VARCHAR(1024),
ip INET NON NULL
);

2. Partizioniamo per giorno in base al campo event_time. Creeremo una nuova sezione per ogni giorno. Nomineremo le sezioni secondo la regola: analytics.events_GGMMAAAA. Ecco una sezione di esempio per il 1° gennaio 2010.

CREA TABELLA analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) PRIMARY KEY ,
CHECK(ora_evento >= TIMESTAMP "2010-01-01 00:00:00" AND ora_evento< TIMESTAMP "2010-01-02 00:00:00" )
) INHERITS(analytics.events);

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

Quando creiamo una sezione, impostiamo esplicitamente il campo event_id (PRIMARY KEY non è ereditato) e creiamo un CHECK CONSTRAINT sul campo event_time per non inserire troppo.

3. Creare un indice nel campo event_time. Quando partizioniamo una tabella, presumiamo che la maggior parte delle query sulla tabella degli eventi utilizzerà una condizione nel campo event_time, quindi un indice su questo campo ci aiuterà molto.

CREA INDICE events_01012010_event_time_idx ON analytics.events_01012010 USANDO btree(event_time);

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

4. Vogliamo assicurarci che una volta inseriti nella tabella principale, i dati finiscano nella sezione ad essa destinata. Per fare ciò, eseguiamo la seguente finta: creiamo un trigger che controllerà i flussi di dati.

CREAZIONE O SOSTITUZIONE DELLA FUNZIONE analytics.events_insert_trigger()
RESTITUISCE IL GRILLETTO COME $$
INIZIO
IF (NEW .event_time >= TIMESTAMP "2010-01-01 00:00:00" AND
NUOVO .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 VALUES (NEW .*);
ALTRO
SOLLEVARE L'ECCEZIONE "La data % non è compresa nell'intervallo. Correggi analytics.events_insert_trigger", NUOVO .event_time;
FINISCI SE ;
RITORNO NULLO ;
FINE ;
$$
LINGUA plpgsql;

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

CREATE TRIGGER events_before_insert
PRIMA DI INSERIRE SU analytics.events
PER OGNI RIGA ESEGUIRE LA PROCEDURA analytics.events_insert_trigger();

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

5. Tutto è pronto, ora abbiamo una tabella analytics.events partizionata. Possiamo iniziare ad analizzare furiosamente i suoi dati. A proposito, abbiamo creato vincoli CHECK non solo per proteggere le sezioni da dati errati. Postgresql può usarli durante la compilazione di un piano di query (tuttavia, con un indice live su event_time, questo darà un guadagno minimo), è sufficiente utilizzare la direttiva constraint_exclusion:

SET vincolo_esclusione = attivo ;
SELECT * FROM analytics.events WHERE event_time > CURRENT_DATE ;

* Questo codice sorgente è stato evidenziato con Source Code Highlighter .

Fine della prima parte

Allora, cosa abbiamo? Andiamo punto per punto:
1. La tabella eventi, suddivisa in sezioni, l'analisi dei dati disponibili per l'ultimo giorno diventa più facile e veloce.
2. L'orrore di rendersi conto che tutto questo deve essere supportato in qualche modo, per creare sezioni in tempo, senza dimenticare di cambiare il trigger di conseguenza.

Di quanto sia facile e spensierato lavorare con le tabelle partizionate lo dirò nella seconda parte.

UPD1: Sostituito il partizionamento con il partizionamento
UPD2:
Sulla base dell'osservazione di uno dei lettori, che purtroppo non ha un account su Habré:
Esistono diverse considerazioni di progettazione associate all'ereditarietà. Le partizioni non ereditano la chiave primaria e le chiavi esterne nelle loro colonne. Cioè, quando si crea una partizione, è necessario creare esplicitamente PRIMARY KEY e FOREIGN KEY sulle colonne della partizione. Da parte mia, noto che creare una FOREIGN KEY sulle colonne di una tabella partizionata non è il modo migliore. Nella maggior parte dei casi, una tabella partizionata è una "tabella dei fatti" e si riferisce essa stessa alla "dimensione" della tabella.

Nel corso del lavoro su tabelle di grandi dimensioni, riscontriamo costantemente problemi con le prestazioni dei loro dati di manutenzione e aggiornamento. Il partizionamento è una delle soluzioni più produttive e convenienti ai problemi che si presentano.
In termini generali, il partizionamento è il partizionamento di una tabella o di un indice in blocchi. A seconda dell'impostazione di partizionamento, i blocchi possono avere dimensioni diverse e possono essere archiviati in filegroup e file diversi.
Il partizionamento presenta sia vantaggi che svantaggi.
I vantaggi sono ben descritti sul sito Microsoft, eccone un estratto:

« Il partizionamento di tabelle o indici di grandi dimensioni può fornire i seguenti vantaggi in termini di gestibilità e prestazioni.

• Ciò consente di trasferire e accedere a sottoinsiemi di dati in modo rapido ed efficiente mantenendo l'integrità del set di dati. Ad esempio, un'operazione come il caricamento dei dati da OLTP a un sistema OLAP richiede secondi, anziché minuti e ore come con i dati non partizionati.
• Le operazioni di manutenzione possono essere eseguite più velocemente con una o più sezioni. Le operazioni sono più efficienti perché vengono eseguite solo su sottoinsiemi di dati anziché sull'intera tabella. Ad esempio, è possibile comprimere i dati in una o più partizioni oppure ricostruire una o più partizioni di indice.
• È possibile migliorare le prestazioni delle query in base alle query eseguite di frequente sulla configurazione hardware. Ad esempio, Query Optimizer può eseguire query equi-join tra due o più tabelle partizionate più velocemente se tali tabelle hanno le stesse colonne di partizione, poiché le partizioni stesse possono essere unite.

Quando si ordinano i dati per le operazioni di I/O in SQL Server, i dati vengono prima ordinati per partizione. SQL Server può accedere solo a un disco alla volta, il che può ridurre le prestazioni. Per velocizzare l'ordinamento dei dati, si consiglia di distribuire i file di dati in sezioni su diversi dischi rigidi creando un RAID. Pertanto, nonostante l'ordinamento dei dati per partizione, SQL Server sarà in grado di accedere contemporaneamente a tutti i dischi rigidi in ciascuna partizione.
È inoltre possibile migliorare le prestazioni applicando blocchi a livello di partizione anziché all'intera tabella. Ciò può ridurre il numero di conflitti di blocco per la tabella».

Gli svantaggi includono la difficoltà nell'amministrazione e nel mantenimento del funzionamento delle tabelle partizionate.

Non ci soffermeremo sull'implementazione del partizionamento, poiché questo problema è descritto molto bene sul sito Web di Microsoft.

Cercheremo invece di mostrare un modo per ottimizzare il funzionamento delle tabelle partizionate, o meglio, mostreremo il modo ottimale (a nostro avviso) per aggiornare i dati per qualsiasi periodo di tempo.

Un grande vantaggio di una tabella partizionata è la separazione fisica di queste partizioni. Questa proprietà ci consente di scambiare sezioni tra loro o con qualsiasi altra tabella.
In un normale aggiornamento dei dati utilizzando una finestra scorrevole (ad esempio, per un mese), dovremo eseguire i seguenti passaggi:

1. Trova le righe richieste in una grande tabella;
2. Elimina le righe trovate dalla tabella e dall'indice;
3. Inserisci nuove righe nella tabella, aggiorna l'indice.

Quando si memorizzano miliardi di righe in una tabella, queste operazioni richiederanno molto tempo, ma possiamo limitarci a quasi un'azione: sostituire semplicemente la sezione desiderata con una tabella (o sezione) preparata in anticipo. In questo caso, non abbiamo bisogno di eliminare o inserire righe e dobbiamo anche aggiornare l'indice sull'intera tabella grande.

Passiamo dalle parole ai fatti e mostriamo come implementarlo.

1. Per prima cosa, imposta una tabella partizionata come descritto nell'articolo citato sopra.
2. Creiamo le tabelle necessarie allo scambio.

Per aggiornare i dati, abbiamo bisogno di una mini copia della tabella di destinazione. È una mini-copia perché memorizzerà i dati che dovrebbero essere aggiunti alla tabella di destinazione, ad es. dati per solo 1 mese. Avrai anche bisogno di una terza tabella vuota per implementare lo scambio di dati. Perché è necessario - spiegherò più avanti.

Sono fissate condizioni rigorose per la mini-copia e la tabella per lo scambio:

• Entrambe le tabelle devono esistere prima di utilizzare l'istruzione SWITCH. Prima di poter eseguire un'operazione di commutazione, nel database devono esistere sia la tabella da cui viene spostata la partizione (la tabella di origine) sia la tabella che riceve la partizione (la tabella di destinazione).
• La sezione di destinazione deve esistere e deve essere vuota. Se una tabella viene aggiunta come partizione a una tabella partizionata già esistente o se una partizione viene spostata da una tabella partizionata a un'altra, la partizione di destinazione deve esistere ed essere vuota.
• La tabella di destinazione non partizionata deve esistere e deve essere vuota. Se la partizione deve formare una singola tabella non partizionata, la tabella che riceve la nuova partizione deve esistere ed essere una tabella vuota non partizionata.
• Le sezioni devono provenire dalla stessa colonna. Se una partizione viene spostata da una tabella partizionata a un'altra, entrambe le tabelle devono essere partizionate sulla stessa colonna.
• Le tabelle di origine e di destinazione devono trovarsi nello stesso filegroup. Le tabelle di origine e di destinazione in un'istruzione ALTER TABLE...SWITCH devono essere archiviate nello stesso filegroup, così come le relative colonne con valori di grandi dimensioni. Nello stesso filegroup devono essere archiviati anche tutti gli indici, le partizioni di indice o le viste delle partizioni indicizzate pertinenti. Tuttavia, potrebbe differire dal filegroup per le rispettive tabelle o altri indici pertinenti.

Lasciatemi spiegare i limiti con il nostro esempio:

1. La miniatura della tabella deve essere partizionata sulla stessa colonna della destinazione. Se la miniatura non è una tabella partizionata, deve essere archiviata nello stesso filegroup della partizione da sostituire.

2. La tabella di scambio deve essere vuota e deve anche essere partizionata sulla stessa colonna o archiviata nello stesso filegroup.

3. Implementiamo lo scambio.

Ora abbiamo quanto segue:
Tabella con i dati di tutti i tempi (di seguito Tabella_A)
Tabella con i dati per 1 mese (di seguito Tabella_B)
Tabella vuota (di seguito Tabella_C)

Prima di tutto, dobbiamo scoprire in quale sezione memorizziamo i dati.
Puoi scoprirlo chiedendo:

SELEZIONARE
conta(*) come
, $PARTIZIONE.(dt) as
, rank() over (ordina per $PARTITION.(dt))
A PARTIRE DAL. (senza blocco)
raggruppa per $PARTITION.(dt)

In questa query, otteniamo sezioni che contengono righe di informazioni. Il numero non può essere contato: lo abbiamo fatto per verificare la necessità di uno scambio di dati. Usiamo Rank in modo da poter andare in loop e aggiornare diverse sezioni in un'unica procedura.

Non appena abbiamo scoperto in quali sezioni memorizziamo i dati, possono essere scambiati. Supponiamo che i dati siano archiviati nella partizione 1.

Quindi è necessario eseguire le seguenti operazioni:
Scambia le partizioni dalla tabella di destinazione con la tabella da scambiare.
ALTERARE LA TABELLA. COMMUTARE L'AREA 1 A . PARTIZIONE 1
Ora abbiamo quanto segue:
Non sono rimasti dati nella tabella di destinazione nella sezione di cui abbiamo bisogno, ad es. la sezione è vuota
Scambia le partizioni dalla tabella di destinazione e dalla miniatura
ALTERARE LA TABELLA. COMMUTARE L'AREA 1 A . PARTIZIONE 1
Ora abbiamo quanto segue:
I dati mensili sono stati visualizzati nella tabella di destinazione e la mini-copia ora è vuota
Cancella o elimina la tabella per lo scambio.

Se hai un indice cluster sulla tabella, anche questo non è un problema. Deve essere creato su tutte e 3 le tabelle partizionate sulla stessa colonna. Quando si cambiano le partizioni, l'indice si aggiornerà automaticamente senza ricostruire.

In questo articolo verranno illustrate le specifiche dei piani di esecuzione delle query durante l'accesso alle tabelle partizionate. Si noti che esiste una grande differenza tra le tabelle partizionate (che sono diventate disponibili solo con SQL Server 2005) e le viste partizionate (che erano disponibili in SQL Server 2000 e sono ancora disponibili in SQL Server 2005 e versioni successive). Dimostrerò le funzionalità dei piani di query per le viste partizionate in un altro articolo.

Vista tabella

Creiamo una semplice tabella partizionata:

crea la funzione di partizione pf(int) come intervallo per i valori (0, 10, 100)

crea lo schema di partizione ps come partizione pf all to ()

creare la tabella t (a int, b int) su ps(a)

Questo script crea una tabella con quattro partizioni. SQL Server ha assegnato valori agli ID di ciascuna delle quattro partizioni come mostrato nella tabella:

PtnId	valori
1	t.a<= 0
2	0 < t.a <= 10
3	10 < t.a <= 100
4	100 < t.a

Ora diamo un'occhiata a un piano di query che costringerebbe l'ottimizzatore a utilizzare una scansione della tabella:

……|–Scansione costante(VALORI:(((1)),((2)),((3)),((4))))
…….|–Table Scan(OGGETTO:([t]))

Nel piano precedente, SQL Server specifica in modo esplicito tutti gli ID di partizione nell'istruzione "Constant Scan" che implementa la scansione della tabella e fornisce i dati all'istruzione di join dei cicli nidificati. Ricordiamo qui che l'istruzione join dei cicli nidificati itera attraverso la tabella interna (in questo caso, una scansione completa della tabella) una volta per ogni valore dalla tabella esterna (nel nostro caso, "Scansione costante"). Pertanto, eseguiamo la scansione della tabella quattro volte; una volta per ogni ID di sezione.

Va inoltre notato che il join del ciclo annidato mostra esplicitamente che la tabella esterna è i valori della colonna in cui sono archiviati gli ID della partizione. Sebbene non sia immediatamente visibile nella vista testo del piano di esecuzione (sfortunatamente, a volte non notiamo queste informazioni), la scansione della tabella utilizza una colonna con gli ID delle partizioni selezionate per eseguire la scansione e determinare quale partizione scansionare. Queste informazioni sono sempre disponibili nel piano di esecuzione grafico (è necessario cercare nelle proprietà dell'operatore di visualizzazione della tabella), nonché nella rappresentazione XML del piano di esecuzione della query: