Teoria degli indici (parte 2)

94 views
Skip to first unread message

Goldrake

unread,
Oct 5, 2005, 8:43:02 PM10/5/05
to
Faccio riferimento al mio post del 29/09/05 e successive risposte di
Marcello e di David.

Vorrei aggiungere un indice su di una tabella e vorrei impostarlo come
clustered in quanto mi e' stato detto che quel tipo di indice lavora piu'
efficacemente in presenza di select che ordinano il recordset su campi
diversi dagli id primari.

Ho tentato, quindi, di creare un nuovo indice sulla tabella impostandolo
come clustered ma sql server non me lo permette in quanto esiste gia' un
indice sulla primary key della tabella (ID) ed accetta solo un indice di
quel tipo.

Ho provato, quindi, ad aggiungere i campi che mi interessavano all'indice
principale ma, al momento della conferma, mi segnala che non puo'
modificare il vincolo PK o UNIQUE se non vengono eliminate prima le
relazioni esistenti.

Che faccio ???

Grazie


Luca Bianchi

unread,
Oct 6, 2005, 2:20:40 AM10/6/05
to
Goldrake wrote:
> Faccio riferimento al mio post del 29/09/05 e successive risposte di
> Marcello e di David.
>
> Vorrei aggiungere un indice su di una tabella e vorrei impostarlo come
> clustered in quanto mi e' stato detto che quel tipo di indice lavora
> piu' efficacemente in presenza di select che ordinano il recordset su
> campi diversi dagli id primari.

Non ho capito il senso di quest'ultima affermazione. Per sua natura un
indice clustered è altamente efficiente su quei campi che vengono richiesti
ordinati, raggruppati o che fanno parte della condizione di ricerca o di
join. A prescindere da quali siano gli "id primari" (cosa intendi per id
primari, la pk?)

> Ho tentato, quindi, di creare un nuovo indice sulla tabella
> impostandolo come clustered ma sql server non me lo permette in
> quanto esiste gia' un indice sulla primary key della tabella (ID) ed
> accetta solo un indice di quel tipo.

E' questo è naturale in considerazione della natura stessa dell'indice
clustered il quale trae la sua "forza" dal fatto di ordinare fisicamente al
suo livello foglia i dati presenti nella tabella. Dall'impossibilità di
ordinare i dati (a livello fisico) per 2 chiavi differenti scaturisce il
vincolo di poter creare un solo indice clustered per tabella...

> Ho provato, quindi, ad aggiungere i campi che mi interessavano
> all'indice principale

...non è affatto la stessa cosa, sia da un punto di vista concettuale in
quanto la definizione della primary key era già stata soddisfatta ("il campo
o i campi in grado di identificare univocamente un record" e l'aggiunta di
altri campi non poteva in alcun modo specificare ulteriormente l'univocità
della chiave) e sia in funzione del fatto che un indice composto sui campi A
e B non ha le stesse caratteristiche di un indice sui campi B e A...

> ma, al momento della conferma, mi segnala che
> non puo' modificare il vincolo PK o UNIQUE se non vengono eliminate
> prima le relazioni esistenti.
>
> Che faccio ???

...quello che ti è stato suggerito, ovvero rimuovere l'attuale PK (se a
questa tabella ne sono relazionate altre devi rimuovere preventivamente le
rispettive FK) e quindi definire di nuovo la PK questa volta di tipo non
clustered. Segui questo esempio

--Tabella con PK di tipo clustered
CREATE TABLE dbo.IC
(
ID SMALLINT NOT NULL,
Nome VARCHAR(10) NOT NULL,
CONSTRAINT PK_IC PRIMARY KEY CLUSTERED (ID)
)
GO

--Rimozione della PK
ALTER TABLE dbo.ICRef
DROP CONSTRAINT FK_IC
GO

--Creazione di un indice clustered
CREATE CLUSTERED INDEX IX_IC ON dbo.IC (Nome)

--Ridefinizione della PK questa volta di tipo non clustered
ALTER TABLE dbo.IC ADD CONSTRAINT
PK_IC PRIMARY KEY NONCLUSTERED (ID)
GO

Le ultime 2 attività (creazione dell'ic e ridefinizione della pk) potrebbero
essere invertite, ma dal momento che ogni indice non clustered assume come
puntatore ai dati la chiave clustered (se esistente), definire prima la PK
risparmia alcune attività al sistema. Per ulteriori approfondimenti ti
rimando a questo mio articolo sull'indicizzazione dei dati

http://italy.mvps.org/MVPs/lbianchi/art_indici.htm

con il suggerimento di approfondire ogni singolo aspetto trattato sul Book
On Line di SQL Server.

> Grazie

Bye

--
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com


Goldrake

unread,
Oct 6, 2005, 1:37:05 PM10/6/05
to
"Luca Bianchi" <rightjoinR...@hotmail.com> ha scritto nel messaggio
news:3qju1nF...@individual.net...

> Goldrake wrote:
>> Faccio riferimento al mio post del 29/09/05 e successive risposte di
>> Marcello e di David.
>>
>> Vorrei aggiungere un indice su di una tabella e vorrei impostarlo come
>> clustered in quanto mi e' stato detto che quel tipo di indice lavora
>> piu' efficacemente in presenza di select che ordinano il recordset su
>> campi diversi dagli id primari.
>
> Non ho capito il senso di quest'ultima affermazione. Per sua natura un
> indice clustered è altamente efficiente su quei campi che vengono
> richiesti ordinati, raggruppati o che fanno parte della condizione di
> ricerca o di join. A prescindere da quali siano gli "id primari" (cosa
> intendi per id primari, la pk?)

Si intendevo quello ma, a questo punto, credo di aver interpretato male il
senso del discorso che mi e' stato fatto. Io avevo capito che si potevano
impostare piu' indici clustered diversi.

A questo punto, pero', vorrei capire una cosa: visto che nelle select che mi
interessano ci sono sia delle join fatte sulle pk delle tabelle e sia degli
ordinamenti fatti su altri campi, come mi devo comportare per avere la
massima performance ?

>> Ho tentato, quindi, di creare un nuovo indice sulla tabella
>> impostandolo come clustered ma sql server non me lo permette in
>> quanto esiste gia' un indice sulla primary key della tabella (ID) ed
>> accetta solo un indice di quel tipo.
>
> E' questo è naturale in considerazione della natura stessa dell'indice
> clustered il quale trae la sua "forza" dal fatto di ordinare fisicamente
> al suo livello foglia i dati presenti nella tabella. Dall'impossibilità di
> ordinare i dati (a livello fisico) per 2 chiavi differenti scaturisce il
> vincolo di poter creare un solo indice clustered per tabella...

Quindi, se non ho capito male, un indice di tipo clustered puo' essere solo
impostato solo su di un campo ?

>> Ho provato, quindi, ad aggiungere i campi che mi interessavano
>> all'indice principale
>
> ...non è affatto la stessa cosa, sia da un punto di vista concettuale in
> quanto la definizione della primary key era già stata soddisfatta ("il
> campo o i campi in grado di identificare univocamente un record" e
> l'aggiunta di altri campi non poteva in alcun modo specificare
> ulteriormente l'univocità della chiave) e sia in funzione del fatto che un
> indice composto sui campi A e B non ha le stesse caratteristiche di un
> indice sui campi B e A...

Mmmhh..... inizio ad avere le idee un po' confuse.
Perdonami, ma vorrei portarti un esempio pratico.

Mettiamo di avere due tabelle (Clienti e ProdottiClienti) dove la tabella
Clienti e' relazionata Uno a Molti con quella ProdottiClienti e che in una
select vengano messe in join tra loro con la PK (ID) della tabella clienti e
la FK (idcliente) della tabella ProdottiClienti e che i campi selezionati
siano Clienti.Cognome, Clienti.Nome, ProdottiClienti.Marca,
ProdottiClienti.Modello e che si richieda un ordinamento per il campo
Cognome.

Tu come imposteresti gli indici sulle tabelle ?

---- cut ----

> Per ulteriori approfondimenti ti rimando a questo mio articolo
> sull'indicizzazione dei dati
>
> http://italy.mvps.org/MVPs/lbianchi/art_indici.htm
>
> con il suggerimento di approfondire ogni singolo aspetto trattato sul Book
> On Line di SQL Server.

Sicuramente andro' a documentarmi meglio.
Spesso, pero', le guide non sempre sono comprensibili al 100%.
A volte alcuni esempi pratici valgono piu' di mille spiegazioni teoriche.

Grazie mille per la pazienza


Marcello

unread,
Oct 6, 2005, 2:15:35 PM10/6/05
to
Ciao,

> Si intendevo quello ma, a questo punto, credo di aver interpretato
male il
> senso del discorso che mi e' stato fatto. Io avevo capito che si
potevano
> impostare piu' indici clustered diversi.

L'indice cluster rappresenta l'ordinamento fisico della tabella. Ignora
per un momento SQL Server e immagina di emulare un piccolo database
engine. Supponiamo di dover archiviare i dati

Frutto Colore Quantità
pesca Giallo 4
banana Giallo 8
mela Rosso 6

Naturalmente puoi scrivere in un file, man mano vengono passati, i tre
"records" e ottieni esattamente i dati come riportati sopra. Evolvendo
le tue features potresti permettere di impostare un "clustered index",
cioè un ordinamento con cui archivi i dati. Quindi ti si potrebbe
chiedere di archiviare i dati ordinati [cluster] per frutto. A questo
punto i tuoi dati assumerebbero l'aspetto

banana Giallo 8
mela Rosso 6
pesca Giallo 4

va da se che non ci possono essere due cluster poichè i dati possono
essere ordinati in unico modo, in compenso nulla vieta di avere l'unico
cluster ordinato rispetto a più campi, dove "ordina secondo A e B"
significa "Ordina secondo A e, a parità di A, secondo B".

Ora vengono gli indici non cluster. Si hanno due casi: o esiste un
indice cluster o non esiste [indice heap]. Sulla nostra minitabella
vogliamo creare un undice sul campo quantità. Un modo ragionevole
potrebbe essere [faccio riferimento ai dati senza cluster]:

4 --> Vedi riga 1
6 --> Vedi riga 3
8 --> Vedi Riga 2

Come vedi un indice è del tutto simile a un vero indice di un libro, in
cui accanto a una voce appare la pagina a cui cercare.
Se però esiste un cluster allora l'indice potrebbe essere costruito
altrettanto bene così:

4 --> Vedi pesca
6 --> vedi mela
8 --> vedi banana

Di questi indici non cluster [che sono a tutti gli effetti nuove
tabelle] se ne possono naturalmente creare a piacere. In pratica, sempre
nel caso libro:

1)Il libro è uno e lì le lettere sono ordinato in modo unico
2)Gli indici alla fine del libro possono essere molteplici [indice degli
autori, degli argomenti, dei capitoli...] e possono fare riferimento o
alla pagina [alla posizione fisica - indice heap] o all'ordinamento
intrinseco del libro.

Il funzionamento delgi indici è tuttosommato semplice, se riesci a
focalizzare la loro struttura puoi rispondere agevolmente dassolo a
tutte le domande che hai posto.
Sui BOL trovi moltissimo materiale discorsivo e non tecnico che
approfondisce gli aspetti che ho accennato in modo elementare.
Anche tra i post di questo ng si trova moltissimo materiale interessante.

marc.

Goldrake ha scritto:

Luca Bianchi

unread,
Oct 6, 2005, 2:17:15 PM10/6/05
to
Goldrake wrote:
> Si intendevo quello ma, a questo punto, credo di aver interpretato
> male il senso del discorso che mi e' stato fatto. Io avevo capito che
> si potevano impostare piu' indici clustered diversi.

Assolutamente impossibile e se fai mente locale sull'architettura di un
indice clustered ti renderai conto che non può esistere più di un indice
clustered per tabella.

> A questo punto, pero', vorrei capire una cosa: visto che nelle select
> che mi interessano ci sono sia delle join fatte sulle pk delle
> tabelle e sia degli ordinamenti fatti su altri campi, come mi devo
> comportare per avere la massima performance ?

Impossibile dare una risposta. Fai una prova con entrambe le situazioni e
analizza i piani di esecuzione ed i costi delle query in entrambi gli
scenari. Valuta anche il peso ponderato di ciascuna delle 2 query prendendo
in esame diversi fattori. Il peso sale per la query che viene eseguita più
volte durante il giorno, diminuisce se una delle query venisse eseguita
"offline" (ad esempio dei report generati durante la notte) e aumenta di
nuovo se la esegue l'amministratore delegato. In base a tutti questi fattori
decidi poi quale è la miglior soluzione...

>> E' questo è naturale in considerazione della natura stessa
>> dell'indice clustered il quale trae la sua "forza" dal fatto di
>> ordinare fisicamente al suo livello foglia i dati presenti nella
>> tabella. Dall'impossibilità di ordinare i dati (a livello fisico)
>> per 2 chiavi differenti scaturisce il vincolo di poter creare un
>> solo indice clustered per tabella...
>
> Quindi, se non ho capito male, un indice di tipo clustered puo'
> essere solo impostato solo su di un campo ?

No, hai capito male. Io ho parlato di 2 chiavi e non di 2 campi. Per chiave
si intende il o i campi (quindi uno o più di uno e fino ad un massimo di 16
in SQL2000 e fino a 32 in SQL2005) che formano la definizione dell'indice...

> Perdonami, ma vorrei portarti un esempio pratico.
>
> Mettiamo di avere due tabelle (Clienti e ProdottiClienti) dove la
> tabella Clienti e' relazionata Uno a Molti con quella ProdottiClienti
> e che in una select vengano messe in join tra loro con la PK (ID)
> della tabella clienti e la FK (idcliente) della tabella
> ProdottiClienti e che i campi selezionati siano Clienti.Cognome,
> Clienti.Nome, ProdottiClienti.Marca, ProdottiClienti.Modello e che si
> richieda un ordinamento per il campo Cognome.
>
> Tu come imposteresti gli indici sulle tabelle ?

Se il join tra le 2 tabelle non produce molte righe il costo
dell'ordinamento sarà basso ed in questo caso sarebbe opportuno NON
sacrificare l'indice clustered sul Cognome e destinarlo altrove (condizione
di join). Come detto, però, la scelta deve scaturire da dati oggettivi
forniti dai piani di esecuzione e i costi delle query con scenari
differenti. Inoltre la scelta di un indice deve essere fatta sulla base
della globalità di tutte le attività che intervengono sulla tabella e non
sulla base di una sola query a meno che questa non sia quella "più
significativa" e le altre possano considerarsi trascurabili perchè eseguite
di rado, in orari notturni o da colleghi antipatici...

> Grazie mille per la pazienza

Di niente, figurati...

Bye

--
Luca Bianchi
Microsoft MVP - SQL Server

http://blogs.aspitalia.com/lucabianchi/
http://mvp.support.microsoft.com


Goldrake

unread,
Oct 6, 2005, 5:05:26 PM10/6/05
to
"Marcello" <marcello...@epomops.it> ha scritto nel messaggio
news:eNzkZIqy...@tk2msftngp13.phx.gbl...

> L'indice cluster rappresenta l'ordinamento fisico della tabella. Ignora
> per un momento SQL Server e immagina di emulare un piccolo database
> engine. Supponiamo di dover archiviare i dati
>
> Frutto Colore Quantità
> pesca Giallo 4
> banana Giallo 8
> mela Rosso 6
>
> Naturalmente puoi scrivere in un file, man mano vengono passati, i tre
> "records" e ottieni esattamente i dati come riportati sopra. Evolvendo le
> tue features potresti permettere di impostare un "clustered index", cioè
> un ordinamento con cui archivi i dati. Quindi ti si potrebbe chiedere di
> archiviare i dati ordinati [cluster] per frutto. A questo punto i tuoi
> dati assumerebbero l'aspetto
>
> banana Giallo 8
> mela Rosso 6
> pesca Giallo 4

---- quit ----

In altre parole, quindi, sarebbe corretto affermare che un indice cluster
non e' altro che una tabella che contiene alcuni dei valori duplicati dalla
tabella dati originale ed ordinati in base ad un criterio preimpostato ?

Oppure e' la stessa tabella che viene riorganizzata ogni volta che viene
effettuato un inserimento/aggiornamento ??

> Ora vengono gli indici non cluster. Si hanno due casi: o esiste un indice
> cluster o non esiste [indice heap]. Sulla nostra minitabella vogliamo
> creare un undice sul campo quantità. Un modo ragionevole potrebbe essere
> [faccio riferimento ai dati senza cluster]:
>
> 4 --> Vedi riga 1
> 6 --> Vedi riga 3
> 8 --> Vedi Riga 2

Ti prego di scusarmi in anticipo se ti faccio domande sceme ma c'e' qualcosa
che mi sfugge.

Se l'indice "non clustered" e' un riferimento alle righe di una tabella
sulla base di alcuni campi contenuti in esso, sarebbe corretto affermare che
se all'interno di un indice non clustered (heap) mettiamo, per esempio,
tutti e tre i campi della tabella dei frutti che hai fatto come esempio,
alla fine otterremo lo stesso risultato di avere un indice clustered con la
sola differenza che successivamente l'engine del db dovrà interrogare
ugualmente la tabella dati ?

Se ho sparato troppe cazzate, ti autorizzo a trattarmi male !
:-)

--- cut ----

> Il funzionamento delgi indici è tuttosommato semplice, se riesci a
> focalizzare la loro struttura puoi rispondere agevolmente dassolo a tutte
> le domande che hai posto.

Gia'.. penso proprio che il propria sia comprendere bene le logiche che sono
alla base.

Mi sa che dovro' leggere ancora un po' di documentazione.

Grazie , comunque, per la chiarezza e semplicita' dei tuoi esempi.

Ciao

Lorenzo Benaglia

unread,
Oct 6, 2005, 5:10:25 PM10/6/05
to
Goldrake wrote:
> In altre parole, quindi, sarebbe corretto affermare che un indice
> cluster non e' altro che una tabella che contiene alcuni dei valori
> duplicati dalla tabella dati originale ed ordinati in base ad un
> criterio preimpostato ?

No, per niente.

Leggi questo post:
http://groups.google.it/group/microsoft.public.it.sql/msg/a8693e050bb3bc09

Ciao!

--
Lorenzo Benaglia


Microsoft MVP - SQL Server

http://blogs.dotnethell.it/lorenzo
http://italy.mvps.org


Goldrake

unread,
Oct 6, 2005, 5:13:39 PM10/6/05
to

"Luca Bianchi" <rightjoinR...@hotmail.com> ha scritto nel messaggio
news:3ql81sF...@individual.net...
> Goldrake wrote:

> Assolutamente impossibile e se fai mente locale sull'architettura di un
> indice clustered ti renderai conto che non può esistere più di un indice
> clustered per tabella.

Ok.. ora e' chiaro

>> A questo punto, pero', vorrei capire una cosa: visto che nelle select
>> che mi interessano ci sono sia delle join fatte sulle pk delle
>> tabelle e sia degli ordinamenti fatti su altri campi, come mi devo
>> comportare per avere la massima performance ?
>
> Impossibile dare una risposta. Fai una prova con entrambe le situazioni e
> analizza i piani di esecuzione ed i costi delle query in entrambi gli
> scenari.

Quindi, vado di Sql Profiler, giusto ?

Grazie
Ciao


Lorenzo Benaglia

unread,
Oct 6, 2005, 5:23:26 PM10/6/05
to
Goldrake wrote:
>> Impossibile dare una risposta. Fai una prova con entrambe le
>> situazioni e analizza i piani di esecuzione ed i costi delle query
>> in entrambi gli scenari.
>
> Quindi, vado di Sql Profiler, giusto ?

Analizza i piani di esecuzione con Query Analyzer, premento CTRL+K oppure
selezionando il menu Query, Show Execution Plan prima di eseguire la query.
Per capire come leggere il piano di esecuzione, fai riferimento a questi
capitoli sui Books Online:

"Query Window Execution Plan Pane"
http://msdn.microsoft.com/library/en-us/qryanlzr/qryanlzr_1bad.asp

"Graphically Displaying the Execution Plan Using SQL Query Analyzer"
http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_5pde.asp

> Grazie
Prego.

Ciao!

--
Lorenzo Benaglia


Microsoft MVP - SQL Server

http://blogs.dotnethell.it/lorenzo
http://italy.mvps.org


Luca Bianchi

unread,
Oct 7, 2005, 2:46:56 AM10/7/05
to
Goldrake wrote:
> Quindi, vado di Sql Profiler, giusto ?

SQLProfiler è un tool che si pone davanti a SQL Server per catturarne tutte
le attività che gli vengono inviate. Al di la del fatto che tra le info può
fornire anche i costi in termini di CPU, il numero di letture eseguite, ecc,
il suo scopo primario è quello di individuare le query che vengono eseguite.
Nel tuo caso le query le conosci già (forse non conosci o vuoi verificare la
frequenza di esecuzione) ed è probabilmente più agevole utilizzare allo
scopo il query plan e abilitare la visualizzazione delle statistiche
(attenzione, non si tratta delle statistiche degli indici propriamente
dette) con l'istruzione

SET STATISTICS IO ON

Fino a che non switchi ad OFF questa impostazione, al termine di tutte le
query eseguite nella connessione corrente visualizzerai una riga simile a

Table 'nometabella'. Scan count 1, logical reads 51, physical reads 0,
read-ahead reads 47.

Il numero di letture logiche ti indica quante pagine sono state lette per
soddisfare la query. L'obiettivo da raggiungere è quello di far leggere a
SQL Server il minor numero di pagine possibile.
Una cosa che finora non è emersa (oppure me la sono persa io) è che la
presenza di un indice non significa che lo stesso venga utilizzato. In
alcuni casi (tutt'altro che rari) anche in presenza di un indice SQL Server
può decidere di non utilizzarlo in quanto la scansione della tabella è
considerata più vantaggiosa ed è il caso di quegli indici che non hanno una
adeguata selettività. Immagina ad esempio un elenco di valori, ad esempio
una tabella Ordini, in cui siano presenti qualche milione di record. Il
campo codcliente è indicizzato ed ha una distribuzione di circa il 5%
(ovvero ogni cliente ha mediamente il 5% degli ordini). Facendo una query
del tipo

SELECT campi
FROM Ordini
WHERE codcliente = 9

prima di eseguire la query SQL Server esamina le possibilità che ha a
disposizione (ovvero si costruisce il query plan) per soddisfare la query.
In questa fase analizza il costo stimato per eseguire la query utilizzando
l'indice (sulla base delle statistiche dell'indice stesso) oppure se è il
caso di ignorare l'indice e procedere ad un table scan. Quale strada sarà
più vantaggiosa dipende dal numero di pagine che sarà necessario leggere in
un caso e nell'altro...

> Grazie
> Ciao

Bye

--
Luca Bianchi
Microsoft MVP - SQL Server

http://mvp.support.microsoft.com


Marcello

unread,
Oct 7, 2005, 3:42:37 AM10/7/05
to
Ciao,

> In altre parole, quindi, sarebbe corretto affermare che un indice
cluster
> non e' altro che una tabella che contiene alcuni dei valori duplicati
dalla
> tabella dati originale ed ordinati in base ad un criterio preimpostato ?
>
> Oppure e' la stessa tabella che viene riorganizzata ogni volta che viene
> effettuato un inserimento/aggiornamento ??

La seconda. L'indice cluster E' LA TABELLA DEI DATI.

> Se l'indice "non clustered" e' un riferimento alle righe di una tabella
> sulla base di alcuni campi contenuti in esso, sarebbe corretto
affermare che
> se all'interno di un indice non clustered (heap) mettiamo, per esempio,
> tutti e tre i campi della tabella dei frutti che hai fatto come esempio,
> alla fine otterremo lo stesso risultato di avere un indice clustered
con la
> sola differenza che successivamente l'engine del db dovrà interrogare
> ugualmente la tabella dati ?

Si, un indice completo di tutti i campi è una copia della tabella
ordinata in altro modo... ovviamente normalmente questo non succede e un
indice contiene solo alcuni campi.

> Se ho sparato troppe cazzate, ti autorizzo a trattarmi male !

Non troppe :-)
Capire questa parte di SQL è fondamentale e permette poi di scrivere
buone query, continua nell'approfondimento, ci sono moltissimi aspetti
da chiarire e usa più che puoi i BOL.

marc.

Goldrake ha scritto:

Goldrake

unread,
Oct 7, 2005, 4:09:38 AM10/7/05
to
"Marcello" <marcello...@epomops.it> ha scritto nel messaggio
news:u1CsWLxy...@TK2MSFTNGP14.phx.gbl...

> Ciao,
>
> > In altre parole, quindi, sarebbe corretto affermare che un indice
> cluster
> > non e' altro che una tabella che contiene alcuni dei valori duplicati
> dalla
> > tabella dati originale ed ordinati in base ad un criterio preimpostato ?
> >
> > Oppure e' la stessa tabella che viene riorganizzata ogni volta che viene
> > effettuato un inserimento/aggiornamento ??
>
> La seconda. L'indice cluster E' LA TABELLA DEI DATI.

Ma, allora, non sarebbe stato meglio chiamarla : Tabella clustered ???
Sono gia' abbastanza confuso di mio, se ci si mettono anche i db
developer... !! :-)

Grazie mille Marcello.
Sei veramente un mito !!!

A presto


Marcello

unread,
Oct 7, 2005, 4:15:32 AM10/7/05
to
Ciao,

> Ma, allora, non sarebbe stato meglio chiamarla : Tabella clustered ???

No, il nome è scelto piuttosto bene. La tabella è l'insieme dei dati,
l'indice cluster è il "primo che creo" e che applico direttamente alla
tabella, ma resta un ordinamento e, di conseguenza, un indice.

>se ci si mettono anche i db developer... !! :-)

Alcuni concetti sono effettivamente ambigui talvolta [dai un'occhiata
sui BOL al concetto di "vincolo univoco" e "indice univoco"]. Queste
ambiguità più che dai developers nascono dalla sovrapposizione della
teoria dei db relazionali con la pratica.

marc.

Goldrake ha scritto:

Lorenzo Benaglia

unread,
Oct 7, 2005, 4:45:07 AM10/7/05
to
Goldrake wrote:
> Ma, allora, non sarebbe stato meglio chiamarla : Tabella clustered ???

No, un indice è un albero binario, non è una tabella!
In un indice clustered il livello foglia coincide con le data pages della
tabella, ma si tratta di due concetti completamente differenti.
Approfondisci il discorso sui Books Online a partire dal seguente paragrafo:

"Table and Index Architecture"
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_8sit.asp

--
Lorenzo Benaglia


Microsoft MVP - SQL Server

http://blogs.dotnethell.it/lorenzo
http://italy.mvps.org


Gianluca Hotz

unread,
Oct 8, 2005, 12:40:58 AM10/8/05
to
G> Se l'indice "non clustered" e' un riferimento alle righe di una tabella
G> sulla base di alcuni campi contenuti in esso, sarebbe corretto affermare
G> che se all'interno di un indice non clustered (heap) mettiamo, per
G> esempio, tutti e tre i campi della tabella dei frutti che hai fatto come
G> esempio, alla fine otterremo lo stesso risultato di avere un indice
G> clustered con la sola differenza che successivamente l'engine del db
G> dovrà interrogare ugualmente la tabella dati ?

No, se l'indice contiene _tutti_ i campi della tabella non
e' necessario interrogare ulteriormente la tabella dati.

Cerco di riassumerti un po' di concetti.

Se non hai familiarita' con le strutture ad albero, o con
l'algoritimica per navigarle, ti consiglio una lettura
preventiva di questo articolo:

http://en.wikipedia.org/wiki/Btree

e di questo (che e' l'implementazione degli indici che
adotta SQL Server):

http://en.wikipedia.org/wiki/B_plus_tree

Da qui in avanti il post e' Query Analyzer "friendly",
nel senso che puoi farne il cut&paste e leggere ed
eseguire dal Query Analyzer se il verdone dei commenti
non ti da fastidio :-)

--<CUT HERE>--
/*
Una tabella fisicamente puo' esistere solo in due modi:
un Heap o un indice Clustered.

Supponiamo che la tabella si chiami Test e che abbia le
seguenti colonne (A, B, C, D, E).
*/

-- attenzione: non eseguire con la visualizzaizone
-- del piano di esecuzione attiva
USE tempdb
GO

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test

CREATE TABLE dbo.Test (
A char(150)
, B char(150)
, C char(150)
, D char(150)
, E char(150)
)

SET NOCOUNT ON

DECLARE @i int

SET @i = 1

WHILE @i < 30000
BEGIN
INSERT dbo.Test
VALUES (
CAST(@i AS char(150))
, CAST(@i AS char(150))
, CAST(@i AS char(150))
, CAST(@i AS char(150))
, CAST(@i AS char(150))
)

SET @i = @i + 1
END

/*
Un Heap e' una collezione di pagine dati all'interno
delle quali le righe possono trovarsi in ordine sparso.

Per sapere quali pagine appartengono ad una determinata
tabella, ci sono delle pagine specializzate (IAM) che
tengono traccia dell'allocazione.

Se, ad esempio, voglio selezionare i dati della colonna D
per tutte le righe che hanno un determinato valore per
la colonna A, devo:

- recuperare le pagine IAM che mi dicono dove sono
allocate le pagine della tabella

- per ogni riga, di ogni pagina, verificare che
il valore della colonna A sia quello cercato

- ritornare il valore della colonna D delle righe che
soddisfano la condizione precedente

Questo tipo di accesso e' detto "Table Scan".

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT D
FROM dbo.Test
WHERE A = '200'

/*
Supponiamo ora di creare un indice non-clustered, o secondario,
sulla colonna A.
*/

CREATE UNIQUE
NONCLUSTERED
INDEX ixTest ON dbo.Test(A)

/*
L'indice e' una struttura detta B+Tree le cui pagine contengono
i valori ordinati della colonna indicizzata e nelle pagine foglia,
oltre ai valori, sono contenuti anche dei puntatori (RID) alle
righe delle pagine della tabella (Heap) a cui l'indice si riferisce.

Riprendendo l'esempio delle ricerca precedente, in questo
caso potro':

- recuperare la pagina radice dell'albero

- navigare l'albero fino alle pagine foglia confrontando
il valore ricercato per la colonna A con i valori in esso
contenuto

- recuperare i puntatori (RID) alle righe dalle pagine
foglia dell'albero per le righe il cui confronto ha avuto
esito positivo

- recuperare le righe tramite i RID dalla tabella a cui
l'indice si riferisce e ritornare il valore della colonna D

Questo tipo di accesso e' detto "Index Seek" (la ricerca dei
RID per la chiave di ricerca) seguito da un "bookmark lookup"
(il recupero dei valori della colonna dalla tabella tramite
i RID).

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT D
FROM dbo.Test
WHERE A = '200'

/*
Supponiamo, invece, di creare un indice composto da entrambe
le colonne A e D (ed eliminiamo l'indice precedente).
*/

DROP INDEX Test.ixTest

CREATE UNIQUE
NONCLUSTERED
INDEX ixTest ON dbo.Test(A, D)

/*
Riprendendo il solito esempio potro':

- recuperare la pagina radice dell'albero

- navigare l'albero fino alle pagine foglia confrontando
il valore ricercato per la colonna A con i valori in esso
contenuto

A questo punto per risolvere la query non ho piu' bisogno
di recuperare i puntatori ed andare a leggere i valori della
colonna D dalla tabella perche' li ho gia' nell'indice, quindi:

- ritornare il valore della colonna D dalle pagine foglia
dell'indice

Questo tipo di accesso e' analogo al primo passo della
precedente Query, un "Index Seek" ma abbiamo risparmiato
l'operazione di "Bookmark Lookup" perche' i valori erano
delle pagine foglia dell'indice.

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT D
FROM dbo.Test
WHERE A = '200'

/*
Un indice di questo tipo e' detto "covering index" per quella
query perche' contiene dati sufficienti a risolverla.

Se cambiassi la query, ad esempio cercando sempre per un
determinato valore di A ma selezionando la colonna E, questo
indice mi sarebbe ancora utile per la ricerca ("Index Seek"),
ma dovrei poi accedere alla tabella per recuperare i valori
della colonna E che non sono contenuti nell'indice ("Bookmark
Lookup").

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT E
FROM dbo.Test
WHERE A = '200'

/*
Passiamo ora alla strutturazione fisica di tipo Clustered
Index.

In questo caso la tabella e' un vero e proprio B+Tree, non
una serie di pagine dati riconducibili ad una tabella tramite
pagine specifiche di allocazione.

Supponiamo di creare l'indice clustered sulla colonna A.
*/

DROP INDEX Test.ixTest

CREATE UNIQUE
CLUSTERED
INDEX clTest ON dbo.Test(A)

/*
In questo caso le pagine foglia dell'indice conterranno
tutti i valori di tutte le colonne della tabella ordinati
per i valori della colonna A, mentre le pagine dei livelli
superiori conterranno solo i valori della colonna A.

Riprendendo l'esempio di query precedente, potro':

- recuperare la pagina radice dell'albero

- navigare l'albero fino alle pagine foglia confrontando
il valore ricercato per la colonna A con i valori in esso
contenuto

- ritornare semplicemente i valori della colonna E
per le righe il cui confronto ha dato esito positivo

(perche' nell'indice clustered le pagine foglia
contengono tutte le colonne)

Questo tipo di accesso e' detto "Clustered Index Seek".

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT E
FROM dbo.Test
WHERE A = '200'

/*
Supponiamo ora di cambiare la query e di voler sempre
selezionare i valori della colonna E, ma questa volta
per tutte le righe che hanno un determinato valore per
la colonna B.

In questo caso non potro' navigare l'albero confrontando
il valore ricercato con i valori della colonna A, ma dovro':

- comunque recuperare la pagina radice dell'albero

- recuperare tutte le pagine foglia dell'albero e per ogni
riga, di ogni pagina, verificare che il valore della colonna
B sia quello cercato

- ritornare il valore della colonna E delle righe che
soddisfano la condizione precedente

Questo tipo di accesso e' detto "Clustered Index Scan"
e possiamo dire che assomiglia al "Table Scan" visto in
precedenza con la tabella Heap (i livelli superiori
dell'indice ci servono solo per recuperare tutte le pagine
foglia, un po' come nel "Table Scan" le pagine IAM ci servono
per recuperare tutte le pagine della tabella).

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT E
FROM dbo.Test
WHERE B = '200'

/*
Per cercare di migliorare l'efficenza di questa nuova
query supponiamo di creare un indice non clustered (o
secondario) sulla colonna B.
*/

CREATE UNIQUE
NONCLUSTERED
INDEX ixTest ON dbo.Test(B)

/*
Quando un indice non clustered viene creato per una tabella
strutturata tramite un indice clustered, la sua struttura
e' simile a quella vista precedentemente per una tabella
Heap.

Con la differenza che le pagine foglia contengono
anche i valori della colonna dell'indice clustered (se
l'indice clustered e' composto da piu' colonne, le pagine
foglia di quello non clustered contengono i valori di
tutte le colonne che compongono quello clustered).

Questi valori che si riferiscono alla colonna (o alle
colonne) dell'indice clustered si chiamano in gergo
"clustering keys".

Dunque, nel nostro caso, l'indice non clustered conterra'
i valori ordinati della colonna B per le pagine non foglia
ed i valori di entrambe le colonne B ed A per le pagine
foglia.

Tornando alla query di esempio potro':

- recuperare la pagina radice dell'albero dell'indice
non clustered

- navigare l'albero fino alle pagine foglia confrontando
il valore ricercato per la colonna B con i valori in esso
contenuto

- recuperare i valori della colonna A (clustering keys)
per le righe il cui confronto ha dato esito positivo

- recuperare la pagina radice dell'albero dell'indice
clustered

- navigare l'albero fino alle pagine foglia confrontando
i valori della colonna A trovati precedentemente con i
valori in esso contenuto

- ritornare il valore della colonna E (dalle pagine foglia)
delle righe che soddisfano la condizione precedente

Questo tipo di accesso e' detto "Index Seek"
seguito da un "bookmark lookup", simile all'esempio
visto per l'indice non clustered con la tabella Heap.

La differenza sostanziale e' che nel primo caso (indice
non clustered per tabella Heap) reperisco dei puntatori
fisici (RID) alle pagine della tabella mentre nel secondo
caso (indice non clustered per tabella con indice clustered)
reperisco dei puntatori logici (clustering keys) con i quali
posso navigare l'indice clustered per arrivare infine alle
pagine della tabella.

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT E
FROM dbo.Test
WHERE B = '200'

/*
Il fatto che l'indice non clustered contenga per definizione
anche la clustering key puo' essere un vantaggio per alcune
query perche' permette di risolverle senza dover recuperare
ulteriori valori.

Ad esempio se volessimo selezionare i valori della colonna A,
ma questa volta per tutte le righe che hanno un determinato
valore per la colonna B potro':

- navigare l'albero fino alle pagine foglia confrontando
il valore ricercato per la colonna B con i valori in esso
contenuto

- recuperare i valori della colonna A (clustering keys)
per le righe il cui confronto ha dato esito positivo

Si tratta ancora una volta di accesso di tipo "Index Seek",
come quello visto per il "covering index", ma in questo caso
non abbiamo dovuto specificare anche la colonna A per
l'indice perche', essendo la clustering key, e' implicitmente
contenuta nelle pagine foglia.

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

SELECT A
FROM dbo.Test
WHERE B = '200'

/*
Se, per assurdo, volessi creare un indice non clustered su
tutte le colonne di una tabella con un indice clustered,
questo indice sarebbe per definizione sufficiente per
risolvere qualunque query (contenendo tutte le colonne
della tabella) senza dover accedere alla tabella.

Si tratterebbe di un "Index Seek" solo sull'indice
non clustered.

Pui verificare il piano di esecuzione premendo CTRL-K nel
Query Analyzer ed eseguendo la seguente query:
*/

DROP INDEX dbo.Test.ixTest

CREATE UNIQUE
NONCLUSTERED
INDEX ixTest ON dbo.Test (A, B, C, D, E)

SELECT A, B, C, D, E
FROM dbo.Test
WHERE A = '200'

/*
Inoltre: a meno che la tabella originale non abbia a sua
volta l'indice clustered definito da tutte le colonne (ma
sarebbe un'assurdo nell'assurdo), tale indice occuperebbe piu'
spazio della tabella originale, e quindi sarebbe anche meno
efficiente.

Occuperebbe piu' spazio perche' se supponiamo, come nel
nostro caso, di creare l'indice clustered sulla colonna A
e di creare l'indice non clustered su tutte le colonne, sia
le pagine foglia, che quelle dei livelli superiori, di
quest'ultimo, conterrebbero i valori di tutte le colonne.

Puoi fare un breve test eseguendo i seguenti comandi nel
Query Analyzer:
*/

DBCC SHOWTABLEAFFINITY('dbo.test')

DBCC SHOW_STATISTICS('dbo.test','clTest')
DBCC SHOW_STATISTICS('dbo.test','ixTest')

/*
Come puoi vedere tramite showtableaffinity, il numero di
extent in uso per l'indice clustered (indid=1) e' 384
mentre quello dell'indice non clustered (indid=2) e'423.

Inoltre osservando i risultati di show_statistics puoi
vedere come per l'indice clustered la dimensione media
della chiave sia di 150 byte (sono i livelli superiori
che contengono solo i valori della colonna A), mentre
quella della chiave dell'indice non clustered sia di
750 byte (lo somma della dimensione di tutte le colonne).
*/
--<CUT HERE>--

Questi sono solo alcuni concetti di base sul funzionamento
degli indici clustered e non clustered, in realta' ci sono
molte altre caratteristiche, piani di accesso ed altro da
tenere in considerazione.

Alcuni esempi:

- se un indice clustered non e' UNIQUE, internamente
SQL Server aggiunge un valore numerico per rendere ogni
riga univoca e, dato che questa fa parte della chiave,
viene riportata anche negli indici non clustered aumentandone
la dimensione

- se abbiamo una tabella senza indice clustered (Heap) con
indici non clustered e decidiamo di creare un indice clustered
(magari composto da piu' campi), dovremo tenere conto del fatto
che ci sara' un impatto in termini di spazio anche sugli indici
non clustered. Questo perche' i puntatori nelle pagine foglia
degli indici non clustered passeranno dall'essere dei RID di 8
byte a delle "clustering keys" la cui dimensione e' dipendente
dalla dimensione dei campi dell'indice clustered. Per contro
tali indici potrebbero avere piu' possibilita' di essere
"covering" per alcune query.

- un indice clustered e' particolarmente efficace per le
ricerche di intervalli di valori perche' data la natura
del B+Tree (che collega le pagine foglia tramite liste
a doppia entrata) una volta localizzato un valore soglia
e' possibile sfruttare la caratteristica di ordinamento
per leggere tutte le righe fino al valore soglia successivo
(passando di pagina in pagina tramite la lista a doppia
entrata). Questa caratteristica e' ottimale anche nelle
ricerce di intervalli di valori negli indici non clustered,
ma in quelli clustered abbiamo il vantaggio di non dover
fare altri accessi se dobbiamo reperire valori di altre
colonne (perche' siamo gia' sulle pagine foglia che sono
di fatto la tabella)

- il condizionale negli esempi, parlando di come avremmo
potuto accedere ai dati sfruttando un indice, e' d'obbligo
perche' sia la selezione di una tecnica di accesso, che l'uso
di un indice, sono legate anche ad altri fattori quali, ad
esempio, le statistiche di distribuzione dei valori (un indice
potrebbe anche non essere utilizzato se il query processor
determina che una scansione completa della tabella e' meno
onerosa)

- senza un indice clustered non e' possibile fare un'efficace
manutenzione dello spazio e della frammentazione di una
tabella

- in fase di aggiornamento una tabella con indice clustered e'
soggetta al fenomeno dei page split, una tabella heap e' invece
soggetta ai forwarding pointers

- un indice clustered su una chiave monotonica crescente
(identity per esempio) determina solo un mezzo page split

- e' FONDAMENTALE non selezionare campi con non servono
nelle query (magari un bel SELECT * ...) perche' si aumenta
la possibilita' di usare un indice non clustered per
risolvere completamente una query (oltre ad altre ovvie
motivazioni)

E cosi' via...

Bisogna comprendere a fondo come sono implementanti gli indici,
come funzionano le tecniche di accesso, come opera l'ottimizzatore
delle query, e comunque bisogna sembre mediare rispetto al
carico di lavoro effettivo ed alle risorse a disposizione.

I Books Online sono, come al solito, una miniera di informazioni
ma sui siti Technet e MSDN trovi molti Whitepaper che trattano
l'argomento ancora piu' in dettaglio.

Un libro che consiglio sempre, a corredo dei Books Online,
che tratta bene l'argomento indici e query tuning e' Inside
SQL Server di MSPress (la versione per SQL Server 2005, gia'
annunciata in 3 volumi, trattera' l'argomento ancora meglio).

Insomma spero di averti scoraggiato dal prendere l'argomento
indici sottogamba ;-)

p.s. non garantisco nulla per quanto riguarda errori di
battitura, ho riletto il post 5 volte e per 5 volte l'ho
cambiato, non posso stare qui per sempre :-)

Ciao,
--
Gianluca Hotz - SQL Server MVP
http://italy.mvps.org - http://www.solidqualitylearning.com
http://www.ugiss.org - http://www.ugidotnet.org
http://www.ghotz.com - http://blogs.ugidotnet.org/ghotz


Goldrake

unread,
Oct 8, 2005, 4:24:10 PM10/8/05
to

"Gianluca Hotz" <gh...@alphasys.it> ha scritto nel messaggio
news:uw1VBK8y...@TK2MSFTNGP10.phx.gbl...

>G> Se l'indice "non clustered" e' un riferimento alle righe di una tabella
> G> sulla base di alcuni campi contenuti in esso, sarebbe corretto
> affermare
> G> che se all'interno di un indice non clustered (heap) mettiamo, per
> G> esempio, tutti e tre i campi della tabella dei frutti che hai fatto
> come
> G> esempio, alla fine otterremo lo stesso risultato di avere un indice
> G> clustered con la sola differenza che successivamente l'engine del db
> G> dovrà interrogare ugualmente la tabella dati ?
>
> No, se l'indice contiene _tutti_ i campi della tabella non
> e' necessario interrogare ulteriormente la tabella dati.
>
> Cerco di riassumerti un po' di concetti.
------- CUT -----------

Miiiiiiiiiiiiiiiiii....... sei troppo un mito Gianluca !!!
Ho da leggere per almeno una settimana !!!! :-)

Non so se e' la prima volta che fai questo post, se cosi' fosse, consiglio
di metterlo come SUPER FAQ del gruppo !!!

Veramene utilissimo, con esempi ma , sopratutto, semplice da capire per
quelli come me !!!

Grazie veramente di cuore a tutti voi che continuate a diffondere il vostro
sapere e la vostra esperienza con entusiasmo e professionalita'.

Che Dio benedica gli MVP !!! :-))

Ciao


Reply all
Reply to author
Forward
0 new messages