Newsgroup: it.comp.software.database
Titolo: Voi come fareste?

Salve a tutti,
Il problema è questo: (MS SQLServer 2000)
Ho una tabella che contiene un elevato numero di record.
Devo aggiungere un campo IDTipo per identificare il tipo di record, perchè
in certi casi un determinato record può avere un significato speciale, e
devo costruire un indice su questo campo.
Il punto è che normalmente la maggioranza di record sono standard senza
necessità di attribuirgli un tipo.
Ho 2 possibilità:
    I record standard avranno IDTipo=Null
    I record standard avranno IDTipo=0 dove 0 sarà il valore di default del
campo.
Cambia qualcosa in termini di prestazioni quando faccio una query inserendo
IDTIpo=0 al posto di IDTipo IS NULL ???
Grazie anticipate
-- 
Toglere #NOSPAM# dall'indirizzo
Cut #NOSPAM# to replay

 ha scritto nel messaggio
news:Ji3Yb.10817$Kc3.3...@twister2.libero.it...


 l'indice non verrebbe usato.
SQL Server inserisce anche i NULL negli indici e l'ottimizzatore
e' in grado di usare questa informazione per velocizzare le
richieste quando e' possibile.
Ad esempio:
USE Northwind
go
CREATE INDEX ixOrdersShipRegion ON dbo.Orders (ShipRegion)
GO
SET SHOWPLAN_TEXT ON
GO
/*
Selezionando solo la colonna ShipRegion, ed imponendo
la condizione per selezionare le righe a NULL...
*/
SELECT ShipRegion
FROM   dbo.Orders
WHERE  ShipRegion IS NULL
/*
...possiamo notare che per eseguire la richiesta e' sufficiente scorrere
(index seek) l'indice che abbiamo appena creato perche' contiene le
informazioni necessarie e sufficienti:
StmtText                                                                 
                                                      ------------------------------------------------------------------------
  |--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ixOrdersShipRegion])
     , SEEK:([Orders].[ShipRegion]=NULL) ORDERED FORWARD)
Selezionando anche la colonna OrderID, oltre alla ShipRegion, imponendo
sempre la condizione per selezionare righe a NULL...
*/
SELECT OrderID, ShipRegion
FROM   dbo.Orders
WHERE  ShipRegion IS NULL
/*
...viene utilizzato sempre quell'indice perche' l'indice clustered
e' sulla colonna OrderId e, dato che le colonne dell'indice clustered
(che sono poi la "clustering key") sono incluse in tutti gli
indici secondari (perche' sono di fatto il puntatore alle righe),
ancora una volta l'indice che abbiamo creato contiene tutte le
informazioni necessarie e sufficienti per rispondere alla richiesta.
Se iniziamo a selezionare altre colonne, ad esempio anche la
OrderDate, viene fatto un table scan perche' l'indice non basta piu'
come si dice in gergo a "coprire" la query ed, essendo la tabella
Orders del Northwind molto piccola, il costo di usare l'indice per
la ricerca a fare le operazioni di I/O extra per recuperare quella
colonna e' superiore al costo di un table scan.
Ma non c'entra nulla con NULL, infatti il table scan viene
fatto anche imponendo la condizione per selezionare gli ordini
spediti nello stato di Washington come segue:
*/
SELECT OrderID, OrderDate, ShipRegion
FROM dbo.Orders
WHERE ShipRegion = N'WA'
/*
A riprova di quanto detto se supponiamo di avere in totale circa
13000 ordini e di questi solo circa un centinaio avesse NULL
come regione di spedizione, alla richiesta...
*/
SELECT OrderID, OrderDate, ShipRegion
FROM   dbo.Orders
WHERE  ShipRegion IS NULL
/*
... SQL Server deciderebbe di utilizzare l'indice anche per la
ricerca IS NULL (facendo poi un'intersezione con con un'altro
indice su OrderDate per soddisfare la richiesta)
StmtText                                                                 
                                                           ------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([Orders].[OrderID])=([Orders].[OrderID]))
 |--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ixOrdersShipRegion])
    , SEEK:([Orders].[ShipRegion]=NULL) ORDERED FORWARD)
 |--Index Scan(OBJECT:([Northwind].[dbo].[Orders].[OrderDate]))
*/
Ciao,
--
Gianluca Hotz - SQL Server MVP
http://italy.mvps.org
 - 
http://www.ghotz.com
http://www.ugiss.org
  - 
http://www.ugidotnet.org

EV> Salve a tutti,
EV> Il problema è questo: (MS SQLServer 2000)
EV> Ho una tabella che contiene un elevato numero di record.
EV> Devo aggiungere un campo IDTipo per identificare il tipo di record,
EV> perchè in certi casi un determinato record può avere un significato
EV> speciale, e devo costruire un indice su questo campo.
EV> Il punto è che normalmente la maggioranza di record sono standard
EV> senza necessità di attribuirgli un tipo.
EV> Ho 2 possibilità:
EV>     I record standard avranno IDTipo=Null
EV>     I record standard avranno IDTipo=0 dove 0 sarà il valore di
EV> default del campo.
EV> Cambia qualcosa in termini di prestazioni quando faccio una query
EV> inserendo
EV> IDTIpo=0 al posto di IDTipo IS NULL ???
Ciao,
come hai potuto constatare dall'altro post il problema nell'utilizzare
NULL al posto di un'altro valore non e' legato al fatto che poi
SQL Server non usi l'indice.
Io userei un'altro valore perche' NULL ha una semantica assolutamente
differente: significa che il valore e' mancante perche' non applicabile,
oppure perche' non disponibile.
In ogni caso, se come dici c'e' un forte sbilanciamento in cui la
maggior parte delle righe e' di un tipo, l'indice sara' utile solo per
identificare il sottoinsieme piu' piccolo (perche' la selettivita'
dell'indice e' alta).
Dato che stiamo parlando di NULL e di tipo record, vorrei fare due
riflessioni in merito ad alcuni aspetti della struttura che potresti
implementare.
Quando ci si ritrova ad avere un campo come questo, spesso significa che
nel modello abbiamo a che fare con una generalizzazione dove c'e'
un'entita' padre ed una o piu' entita' figlie.
Per fare un esempio pensiamo ad una base di dati per uno studio medico,
i dati che vogliamo catturare per i pazienti sono: nome, cognome, data
di nascita, sesso, stato della prostata, numero di gravidanze.
Per semplicita' supponiamo che ogni paziente sia identificato solo da
nome e cognome.
La prima cosa da rilevare e' che in base al sesso alcuni attributi non
sono applicabili: lo stato della prostata non e' applicabile alle
pazienti di sesso femminile, il numero di gravidanze non e' invece
applicabile ai pazienti di sesso maschile.
A livello logico abbiamo le seguenti tre entita':
           -------------------
           |     Paziente    |
           -------------------
           | nome            |
           | cognome         |
           | sesso           |
           | data di nascita |
           -------------------
                    /\
                    ||
         |---------------------|
         |                     |
-------------------    ---------------------
|     Maschio     |    |      Femmina      |
|------------------    ---------------------
| stato prostata  |    | numero gravidanze |
-------------------    ---------------------
Si tratta appunto di una generalizzazione con l'entita' padre
Paziente e le due entita' figlie PazienteMaschio e PazienteFemmina.
Nel modello logico possiamo decidere se:
a) accorpare le figle della generalizzazione del padre
           ---------------------
           |     Paziente      |
           ---------------------
           | nome              |
           | cognome           |
           | sesso             |
           | data di nascita   |
           | stato prostata    |
           | numero gravidanze |
           ---------------------
b) accorpare il padre della generalizzazione nelle figlie
-------------------    ---------------------
|     Maschio     |    |      Femmina      |
|------------------    ---------------------
| nome            |    | nome              |
| cognome         |    | cognome           |
| sesso           |    | sesso             |
| data di nascita |    | data di nascita   |
| stato prostata  |    | numero gravidanze |
-------------------    ---------------------
c) sostituire la generalizzazione con associazioni
           -------------------
           |     Paziente    |
           -------------------
           | nome            |
           | cognome         |
           | sesso           |
           | data di nascita |
           -------------------
               1|        |1
                |        |
         |------|        |-----|
        1|                     |1
-------------------    ---------------------
|     Maschio     |    |      Femmina      |
|------------------    ---------------------
| stato prostata  |    | numero gravidanze |
-------------------    ---------------------
A meno di come vogliamo fare il modello logico, vediamo alcune
possibili implementazioni a livello fisico.
soluzione 1)
CREATE TABLE dbo.Paziente (
     Nome            varchar(30)  NOT NULL
,    Cognome         varchar(30)  NOT NULL
,    Sesso           char(1)      NOT NULL
,    DataNascita     datetime     NOT NULL
,    StatoProstata   varchar(30)  NULL
,    Gravidanze      int          NULL
,    CONSTRAINT pkPaziente
     PRIMARY KEY (Nome, Cognome)
,    CONSTRAINT ckPazienteSessoValido
     CHECK  (Sesso IN ('M', 'F'))
)
Questa soluzione e' quasi sempre la piu' intuitiva e la
piu' semplice da realizzare, per contro ci sono una serie
di considerazioni da fare.
Prima di tutto non dobbiamo scordarci di aggiungere gli opportuni
vincoli:
v1) un paziente di sesso femminile non puo' uno stato prostata
v2) un paziente di sesso maschile non puo' avere gravidanze
ALTER TABLE dbo.Paziente
ADD CONSTRAINT ckPazienteAttributiValidi
CHECK ((Sesso = 'M' AND Gravidanze IS NULL)
   OR (Sesso = 'F' AND StatoProstata IS NULL))
Se ci dimentichiamo di mettere i corretti vincoli, rischiamo di
inserire dati che non hanno senso e di avere risultati non veritieri.
Inoltre al crescere del numero di attributi non comuni, cresce
la complessita dei vincoli da creare.
Un'altro problema e' dato dal fatto che con uno schema di questo
tipo ti ritrovi ad avere un alto numero di NULL e a dover conoscere
la semantica del modello per interpretare quali di questi NULL sono
da imputarsi all'assenza del dato e quali invece sono da imputarsi
all'inapplicabilita' del dato.
Possiamo arricchire lo schema creando le seguenti viste:
CREATE VIEW dbo.PazienteFemmina (
     Nome
,    Cognome
,    Sesso
,    DataNascita
,    Gravidanze
)
AS
SELECT
      Nome
,     Cognome
,     Sesso
,     DataNascita
,     Gravidanze
FROM  dbo.Paziente
WHERE Sesso = 'F'
WITH  CHECK OPTION
GO
CREATE VIEW dbo.PazienteMaschio (
      Nome
,     Cognome
,     Sesso
,     DataNascita
,     StatoProstata
)
AS
SELECT
      Nome
,     Cognome
,     Sesso
,     DataNascita
,     StatoProstata
FROM  dbo.Paziente
WHERE Sesso = 'M'
WITH  CHECK OPTION
GO
Nota bene che le due viste sono aggiornabili direttamente, la clausola
WITH CHECK OPTION garantisce inoltre che l'inserimento che stiamo
effettuando sia congruente con la clausola di selezione (puoi inserire
solo pazienti di sesso maschile nella vista PazienteMaschio e solo
pazienti di sesso femminile nella vista Paziente).
soluzione 2)
CREATE TABLE dbo.PazienteMaschio (
        Nome            varchar(30)     NOT NULL
,       Cognome         varchar(30)     NOT NULL
,       Sesso           char(1)         NOT NULL
,       DataNascita     datetime        NOT NULL
,       StatoProstata   varchar(30)     NULL
,       CONSTRAINT      pkPazienteMaschio
        PRIMARY KEY     (Nome, Cognome)
,       CONSTRAINT      ckPazienteMaschioSessoValido
        CHECK           (Sesso = 'M')
)
GO
CREATE TABLE dbo.PazienteFemmina (
        Nome            varchar(30)     NOT NULL
,       Cognome         varchar(30)     NOT NULL
,       Sesso           char(1)         NOT NULL
,       DataNascita     datetime        NOT NULL
,       Gravidanze      int             NULL
,       CONSTRAINT      pkPazienteFemmina
        PRIMARY KEY     (Nome, Cognome)
,       CONSTRAINT      ckPazienteFemminaSessoValido
        CHECK           (Sesso = 'F')
)
GO
Il principale problema di questa soluzione e' che non possiamo
accedere direttamente a tutti i pazienti, anche se possiamo
risolvere il problema agevolmente creando una vista come segue:
CREATE VIEW dbo.Paziente (
        Nome
,       Cognome
,       Sesso
,       DataNascita
)
AS
SELECT
        Nome
,       Cognome
,       Sesso
,       DataNascita
FROM    dbo.PazienteMaschio
UNION ALL
SELECT
        Nome
,       Cognome
,       Sesso
,       DataNascita
FROM    dbo.PazienteFemmina
GO
Un beneficio di questo approccio e' che selezionando da questa
vista, specificando il sesso come criterio di ricerca, sara'
acceduta solo la tabella relativa al sesso specificato.
Ad esempio eseguendo SELECT * FROM dbo.Paziente WHERE sesso = 'F'
l'ottimizzatore, in virtu' del CHECK constraint, capisce che puo'
accedere solo alla tabella dbo.PazienteFemmina.
Un secondo beneficio e' quello dato dal fatto che NULL significa
unicamente valore assente.
Rimane comunque il problema che questa vista non e' aggiornabile
direttamente: e' necessario creare un trigger di tipo INSTEAD OF.
soluzione 3)
CREATE TABLE dbo.Paziente (
        Nome            varchar(30)     NOT NULL
,       Cognome         varchar(30)     NOT NULL
,       Sesso           char(1)         NOT NULL
,       DataNascita     datetime        NOT NULL
,       CONSTRAINT      pkPaziente
        PRIMARY KEY     (Nome, Cognome)
,       CONSTRAINT      ckPazienteSessoValido
        CHECK           (Sesso IN ('M', 'F'))
)
GO
CREATE TABLE dbo.PazienteMaschio (
        Nome            varchar(30)     NOT NULL
,       Cognome         varchar(30)     NOT NULL
,       StatoProstata   varchar(30)     NULL
,       CONSTRAINT      pkPazienteMaschio
        PRIMARY KEY     (Nome, Cognome)
,       CONSTRAINT      fkPazienteMaschio
        FOREIGN KEY     (Nome, Cognome)
        REFERENCES      dbo.Paziente(Nome, Cognome)
)
GO
CREATE TABLE dbo.PazienteFemmina (
        Nome            varchar(30)     NOT NULL
,       Cognome         varchar(30)     NOT NULL
,       StatoProstata   varchar(30)     NULL
,       CONSTRAINT      pkPazienteFemmina
        PRIMARY KEY     (Nome, Cognome)
,       CONSTRAINT      fkPazienteFemmina
        FOREIGN KEY     (Nome, Cognome)
        REFERENCES      dbo.Paziente(Nome, Cognome)
)
GO
Con questa soluzione, oltre ad evitare i NULL di tipo "inapplicabile",
possiamo anche evitare di avere quelli che indicano l'assenza del dato.
Comunque per garantire i vincoli v1 e v2 elencati precedentemente
dobbiamo creare dei trigger perche' in SQL Server non e' possibile
utilizzare una subquery in un vincolo di tipo CHECK e non e' possible
neppure creare una ASSERT.
Ovviamente il discorso su come trattare le generalizzazione e' molto
piu' ampio, e ci sono molte sfumature, era solo per dare un'idea di
come si puo' affrontare il problema in vari modi diversi e per
ricordare di non dimenticarsi che anche scegliendo la prima soluzione,
per evitare inconstistenze dobbiamo sempre applicare gli opportuni
vincoli
