Condividi tramite

  • Articolo

Utilizzo dei tipi di dati hierarchyid (Motore di database)

Il tipo di dati hierarchyid è fornito dal sistema. Utilizzare hierarchyid come tipo di dati per creare tabelle con una struttura gerarchica o per fare riferimento alla struttura gerarchica dei dati di un altro percorso. Utilizzare le funzioni hierarchyid per eseguire query ed attività con i dati gerarchici, utilizzando Transact-SQL.

I dati gerarchici vengono definiti come un set di dati correlati tra loro tramite relazioni gerarchiche. Si parla di relazioni gerarchiche quando un elemento di dati è l'elemento padre di un altro elemento. I dati gerarchici sono comuni nei database. Negli esempi vengono illustrati gli aspetti seguenti:

  • Una struttura organizzativa

  • Un file system

  • Un set di attività di un progetto

  • Una tassonomia di termini del linguaggio

  • Un grafico di collegamenti tra pagine Web

Nuovo in SQL Server 2008, il tipo hierarchyid facilita l'archiviazione e l'esecuzione di una query sui dati gerarchici. hierarchyid è ottimizzato per la rappresentazione di alberi, che sono il tipo più comune di dati gerarchici.

Proprietà chiave di hierarchyid

Un valore del tipo di dati hierarchyid rappresenta una posizione in un albero gerarchico. I valori per hierarchyid hanno le proprietà descritte di seguito:

  • Estremamente compresso

    Il numero medio di bit richiesto per rappresentare un nodo in un albero con n nodi dipende dal fanout medio (il numero medio di elementi figlio di un nodo). Per i piccoli fanout (0-7), la dimensione è approssimativamente 6*logAn bit, dove A è il fanout medio. Un nodo in una gerarchia organizzativa di 100.000 persone con un fanout medio di 6 livelli richiede circa 38 bit. Viene arrotondato a 40 bit, o 5 byte, per l'archiviazione.

  • Il confronto avviene in ordine di scorrimento in profondità

    Dati due valori hierarchyida e b, a<b indica che a precede b nell'attraversamento del primo livello di profondità dell'albero. Gli indici sui tipi di dati hierarchyid sono in ordine di scorrimento della profondità e i nodi l'uno vicino all'altro nell'attraversamento del primo livello di profondità della struttura sono archiviati l'uno vicino all'altro. Ad esempio, gli elementi figlio di un record sono archiviati adiacenti al record specifico.

  • Supporto per eliminazioni e inserimenti arbitrari

    Utilizzando il metodo GetDescendant, è sempre possibile generare un elemento di pari livello a destra o a sinistra di un determinato nodo oppure tra due elementi di pari livello. La proprietà del confronto è gestita quando un numero arbitrario di nodi viene inserito o eliminato dalla gerarchia. La maggior parte degli inserimenti e delle eliminazioni mantiene la proprietà di compattezza. Tuttavia, gli inserimenti tra due nodi produrranno i valori hierarchyid con una rappresentazione leggermente meno compatta.

Limitazioni di hierarchyid

Le limitazioni seguenti si applicano al tipo di dati hierarchyid:

  • Una colonna di tipo hierarchyid non rappresenta automaticamente un albero. È compito dell'applicazione generare e assegnare i valori hierarchyid in maniera tale che la relazione desiderata tra le righe sia riflessa nei valori. Per alcune applicazioni potrebbe essere addirittura inutile avere una colonna di tipo hierarchyid per rappresentare un albero. Forse i valori sono riferimenti a percorsi in una gerarchia definita in un'altra tabella.

  • La concorrenza nella generazione e nell'assegnazione dei valori hierarchyid viene gestita dall'applicazione. Non c'è garanzia che i valori hierarchyid di una colonna siano univoci a meno che l'applicazione utilizzi un vincolo della chiave univoca o applichi univocità stessa tramite la logica.

  • Le relazioni gerarchiche rappresentate dai valori hierarchyid non sono applicate come una relazione della chiave esterna. È possibile e qualche volta appropriato avere una relazione gerarchica dove A ha un figlio B, A viene eliminato e lascia a B una relazione con un record inesistente. Se questo comportamento è inaccettabile, l'applicazione deve eseguire una query per i discendenti prima di eliminare gli elementi padre.

Strategie di indicizzazione

Esistono due strategie per indicizzare i dati gerarchici:

  • Depth-first

    In un indice depth-first, le righe di un sottoalbero vengono archiviate le une accanto alle altre. Ad esempio, tutti i dipendenti che riportano a un responsabile vengono archiviati accanto al record dei responsabili.

I nodi vengono archiviati insieme.

  • Breadth-first

    Il breadth-first archivia le righe di ogni livello della gerarchia insieme. Ad esempio, i record dei dipendenti che riportano direttamente allo stesso responsabile vengono archiviati gli uni accanto agli altri.

I livelli della gerarchia vengono archiviati insieme.

Esempi

Il metodo GetLevel() può essere utilizzato per creare un ordine breadth-first. Nell'esempio seguente, vengono creati sia gli indici breadth-first sia gli indici depth-first:

USE AdventureWorks2008R2 ; 
GO

CREATE TABLE Organization
   (
    BusinessEntityID hierarchyid,
    OrgLevel as BusinessEntityID.GetLevel(), 
    EmployeeName nvarchar(50) NOT NULL
   ) ;
GO

In un indice depth-first tutti i nodi del sottoalbero di un nodo vengono posizionati insieme. Gli indici depth-first sono pertanto utili per rispondere alle query sui sottoalberi, ad esempio "Trova tutti i file in questa cartella e nelle relative sottocartelle".

CREATE CLUSTERED INDEX Org_Breadth_First 
ON Organization(OrgLevel,BusinessEntityID) ;
GO

CREATE UNIQUE INDEX Org_Depth_First 
ON Organization(BusinessEntityID) ;
GO

In un indice breadth-first tutti gli elementi figlio diretti di un nodo vengono posizionati insieme. Pertanto, gli indici breadth-first sono in grado di fornire risposte alle query sugli elementi figlio immediati, ad esempio "Trova tutti i dipendenti che riportano direttamente a questo responsabile".

La scelta tra depth-first e/o breadth-first e la selezione di uno di questi nonché della chiave di clustering (se disponibile) dipende dall'importanza relativa dei tipi di query di cui sopra e dall'importanza relativa delle operazione SELECT e DML. Per un esempio dettagliato delle strategie di indicizzazione, vedere Esercitazione: Utilizzo del tipo di dati hierarchyid.

Quando utilizzare le alternative a hierarchyid

Due alternative a hierarchyid per la rappresentazione di dati gerarchici sono:

  • Elemento padre/figlio

  • XML

hierarchyid è generalmente superiore a queste alternative. Tuttavia, esistono determinate situazioni illustrate in dettaglio di seguito in cui le alternative sono superiori.

Elemento padre/figlio

Quando si utilizza l'approccio dell'elemento padre/figlio, ogni riga contiene un riferimento all'elemento padre. La tabella seguente definisce una tabella tipica utilizzata per contenere le righe padre e figlio in una relazione padre/figlio:

USE AdventureWorks2008R2 ;
GO

CREATE TABLE ParentChildOrg
   (
    BusinessEntityID int PRIMARY KEY,
    ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName nvarchar(50) 
   ) ;
GO

Confronto padre/figlio e hierarchyid per operazioni comuni

  • Le query del sottoalbero sono notevolmente più veloci con hierarchyid.

  • Le query discendenti dirette sono leggermente più lente con hierarchyid.

  • Lo spostamento di nodi non foglia è notevolmente più lento con hierarchyid. L'inserimento di nodi non foglia e l'inserimento o lo spostamento di nodi foglia sono caratterizzati dalla stessa complessità con hierarchyid.

La relazione padre/figlio è superiore quando esistono le condizioni seguenti:

  • La dimensione della chiave è molto importante. Per lo stesso numero di nodi, il valore hierarchyid è uguale a o maggiore di una famiglia di valori integer (smallint, int, bigint). Questo è solo uno dei motivi per cui utilizzare la relazione padre/figlio in casi rari, poiché hierarchyid si colloca meglio nell'I/O e nella complessità della CPU che nelle espressioni della tabella comune richieste quando si utilizza la struttura padre/figlio.

  • Le query vengono eseguite raramente sulle sezioni della gerarchia. In altre parole, le query di solito vengono eseguite solo su un solo punto della gerarchia. In questi casi il percorso unito non è importante. Ad esempio, la relazione padre/figlio è superiore se la tabella dell'organizzazione viene utilizzata solo per l'esecuzione del libro paga per i singoli dipendenti.

  • I sottoalberi non-foglia vengono spostati frequentemente e la prestazione è molto importante. In una rappresentazione padre/figlio, la modifica del percorso di una riga in una gerarchia influisce su una sola riga. La modifica del percorso di una riga in un utilizzo di hierarchyid influisce sulle righe n, dove n è il numero di nodi del sottoalbero spostato.

    Se i sottoalberi non-foglia vengono spostati frequentemente e la prestazione è molto importante, ma la maggior parte degli spostamenti sono a un livello ben definito della gerarchia, suddividere i livelli superiori e inferiori in due gerarchie. In questo modo, tutti gli spostamenti si verificano a livello di foglia nella gerarchia più elevata. Ad esempio, considerare una gerarchia di siti Web ospitata da un servizio. I siti contengono molte pagine disposte in modo gerarchico. I siti di hosting potrebbero essere spostati in altri percorsi nella gerarchia del sito, ma le pagine subordinate raramente vengono disposte nuovamente. Questo potrebbe essere rappresentato tramite:

    CREATE TABLE HostedSites 
   (
    SiteId hierarchyid, PageId hierarchyid
   ) ;
GO

XML

Un documento XML è un albero e pertanto una sola istanza del tipo di dati XML può rappresentare una gerarchia completa. In SQL Server quando viene creato un indice XML, i valori hierarchyid vengono utilizzati internamente per rappresentare la posizione nella gerarchia.

L'utilizzo del tipo di dati XML può essere superiore quando tutte le seguenti condizioni sono vere:

  • La gerarchia completa viene sempre archiviata e recuperata.

  • I dati vengono utilizzati in un formato XML dall'applicazione.

  • Le ricerche del predicato sono estremamente limitate e non sono importanti per la prestazione.

Ad esempio, se un'applicazione rileva più organizzazioni, archivia e recupera sempre la gerarchia organizzativa completa e non esegue query in una singola organizzazione, una tabella del formato seguente potrebbe avere senso:

CREATE TABLE XMLOrg 
    (
    Orgid int,
    Orgdata xml
    ) ;
GO

Migrazione dalla relazione padre/figlio a hierarchyid

La maggior parte degli alberi viene rappresentata utilizzando la relazione padre/figlio. Il modo più semplice per eseguire la migrazione da una struttura padre/figlio a una tabella utilizzando hierarchyid consiste nell'utilizzare una colonna temporanea o una tabella temporanea per monitorare il numero di nodi a ogni livello della gerarchia. Per un esempio di migrazione della tabella padre/figlio, vedere lezione 1 di Esercitazione: Utilizzo del tipo di dati hierarchyid.

Trasformazioni di query per hierarchyid

Per ottimizzare la prestazione dell'esecuzione di query sulle gerarchie, SQL Server esegue automaticamente tre trasformazioni di query che coinvolgono hierarchyid. Il risultato di queste trasformazioni può essere visualizzato nell'output di Showplan per le query trasformate.

GetAncestor viene trasformato in un'analisi di intervalli e un predicato residuo

GetAncestor(n) fornisce l'ennesimo predecessore di un nodo. Questo è utile quando la relazione precisa (padre, figlio, padre del padre ecc.) tra due nodi è necessaria, contrariamente a quanto accade con la relazione IsDescendantOf più generale.

Eseguire, ad esempio, la query riportata di seguito per cercare tutti i dipendenti il cui responsabile diretto è @value:

DECLARE @value hierarchyid ;
SELECT * FROM AdventureWorks2008R2.HumanResources.Employee 
WHERE  OrganizationNode.IsDescendantOf(@value) = 1;