Universita' degli studi di Roma
"La Sapienza"
Corso di Basi di Dati - Prof.ssa Tiziana Catarci.
Anno Accademico 1999/2000 - Gennaio 2000
Tesina di confronto tra:
Db2 Universal Server 6.1 EE
Oracle 8i
Informix Dynamic Server 2000
• Implementazione SQL92: vincoli ed interrogazioni.
• I trigger: implementazione di trigger in Java e SQL.
• Indici: i tipi di indicizzazione disponibili.
• Ottimizzazione: intervenire sull'ottimizzatore delle query.
Menegoni Fabio - Moschetti Marco - Salce Gianluca
Introduzione
Questo studio riguarda il confronto di alcuni aspetti dei tre piu' importanti DBMS presenti sul
mercato attuale del software per la gestione delle basi di dati:
Oracle 8i (8.1.5) - IBM DB2 6.1 EE - Informix Dynamic Server 2000
Tutti e tre disponibili per molte piattaforme, sono stati provati nella versione per Windows NT 4
su un personal computer dotato di un processore AMD K6/200 e 64 MB di RAM.
Confrontare dei software come DBMS e' sicuramente un'impresa tutt'altro che semplice. Le loro
dimensioni sono tali che un confronto completo e approfondito da tutti i punti di vista
richiederebbe mesi di lavoro a persone con una certa esperienza. Anche limitando l'analisi solo
ad alcuni aspetti di implementazione e tuning, l'impresa e' stata ben piu' ardua di quanto stimato
inizialmente; infatti, questi programmi non sono particolarmente amichevoli, a causa ovviamente
della loro complessita'. Inoltre la quantita' di documentazione fornita e' semplicemente
disorientante anche per persone che hanno una certa familiarita' con l'uso del computer.
Gli argomenti sui quali e' posta l'attenzione, per la loro rilevanza, sono:
•
•
•
•
Implementazione SQL92: come esprimere vincoli ed interrogazioni.
I trigger: implementazione di trigger in Java.
Indici: i tipi di indicizzazione disponibili.
Ottimizzazione: come intervenire sull'ottimizzatore delle query.
Il Software a disposizione
IBM DB2 Universal Database V6.1 EE, versione per Windows NT, Free 60 days evaluation
copy richiesta gratuitamente sul sito IBM. DB2 SDK 5.1 e DB2 Client Application Enabler 5.1,
messoci a disposizione dal Dipartimento di Informatica e Sistemistica, Universita' di Roma.
Oracle 8i Enterprise Edition (8.1.5), Free 30 days evaluation copy for Windows NT, fornita
durante il seminario su Oracle tenuto durante il corso di Basi di Dati. Informix Dynamic Server
2000, V9.2, in versione Evaluation, ottenuto dal sito web di Informix. Per implementare i trigger
in Java, e' stato necessario servirsi della versione per Linux 2.2.
Note sull'installazione
L'installazione non e' semplice come puo' sembrare. E' richiesta la presenza di alcuni servizi di
rete di NT4 senza i quali l'installazione non va a buon fine.
Per quello che riguarda l'occupazione su disco, DB2 ed Informix richiedono sui 200-250 Mb per
l'installazione completa, Oracle richiede molto piu' spazio, circa 1.5 Gb.
DB2 e Oracle allocano praticamente tutta le memoria disponibile sul calcolatore, Informix
invece alloca la memoria solo quando ne ha bisogno rendendo agevole l'uso per altre
applicazioni su quella macchina. DB2 offre questa possibilita' a posteriori da un pannello di
controllo. Oracle 8i permette di specificare dei parametri per limitare l'uso della memoria
allocata a diversi tipi di buffer (redo buffer log e buffer cache principalmente) mentre
l'allocazione degli shared pool e del System Global Area non e' direttamente controllabile
dall'amministratore se non per aspetti come le politiche di pre-paging che comunque non sono
finalizzate a limitare l'uso di memoria, ma a disporne in anticipo per limitare il traffico di I/O.
Note sulla documentazione
La documentazione disponibile per tutti e tre i DBMS e' vasta ed esauriente.
Oracle la offre sotto forma di file HTML con uno strumento per la ricerca in javascript.
DB2 offre la possibilta' di accedere a documenti nazionalizzati, sia in PDF sia in HTML
mediante un tool, l'information centre, che la mostra in aree tematiche permettendo di effettuare
ricerche mediante keyword.
La documentazione di Informix e' fornita sul loro sito web in formato PDF.
Strumenti di interazione con i DBMS
Tutti mettono a disposizione uno strumento grafico per la creazione di database. Quello di Oracle
mette a disposizione una serie di opzioni proprietarie, riguardanti le estensioni multimediali, che
sconosciute al neofita e selezionate di default, allungano la creazione del database a circa un'ora.
DB2 mette a disposizione invece delle opzioni per far gestire dal sistema i file relativi al database
diminuendo le prestazioni, ma rende possibile un utilizzo meno ristretto del computer sul quale si
trova installato il DBMS.
Degna di nota e' la presenza di un tool grafico su DB2 e Informix per la gestione dei database: da
questo e' possibile creare tabelle, vincoli, trigger e vedere il contenuto delle tabelle in maniera
pratica, veloce ed intuitiva; sempre DB2 ed Informix offrono un editor SQL molto piu' evoluto di
quello presente su Oracle che sembra essere uno strumento primordiale e ostico. Infatti, questi
editor offrono la possibilita' di scrivere e modificare il codice SQL introdotto e vedere i risultati
su una pagina diversa da quella riservata al codice.
Il database
Lo schema di base di dati che e' stato utilizzato nello studio e' piuttosto semplice ed e' stato
importante per non perdere di vista i casi reali; era necessario avere a disposizione vincoli reali,
ed e' servito per darci l'idea di come si implementa uno schema concettuale all'interno di un
DBMS. Queste sono le specifiche.
L’agenzia di pubbliche relazioni
La base di dati di un’agenzia di pubbliche relazioni contiene le seguenti informazioni:
•
un catalogo di clienti, che possono essere aziende o persone fisiche;
•
un insieme di informazioni su dei banchetti organizzati nell’ambito di manifestazioni
o in occasione di singoli avvenimenti (congressi, matrimoni, cresime, ecc.) per conto
dei clienti;
•
gli elenchi degli invitati ai singoli banchetti; un elenco di ristoranti con le loro
caratteristiche. Tra i ristoranti si distinguono quelli caratteristici, che possono offrire
particolari specialita'; le specialita' sono offerte solo dai ristoranti caratteristici;
•
un insieme di menu', tra i quali si distinguono quelli contenenti le specialita' offerte
dai ristoranti caratteristici
Lo schema E/R
Le tabelle
Dal diagramma E/R e dalle specifiche e' stato possibile identificare le relazioni da implementare
nei DBMS, unitamente ad un insieme di vincoli che garantiscono l'integrita' della base di dati.
•
AZIENDA (codice fiscale, citta', via, #telefono, capitale_sociale, #dipendenti)
•
BANCHETTO (codicebanchetto, occasione, data, n_partecipanti, codice_fiscale,
nome_ristorante, localita'_ristorante, id_menu';)
•
COMPRENDE (id_menu' , nome portata )
•
INVITATO (codicebanchetto , nome )
•
MENU_STANDARD (id , costo)
•
OFFRE_S (nomeristorante , localitaristorante , nome_specialita')
•
OFFRE_M (nomeristorante , localitaristorante , id_menu')
•
PERSONA (codicefiscale , citta, via, #telefono, cognome, nome, professione)
•
PORTATA (nome , tipo) SPECIALITA(nome, tipo)
•
RISTORANTE (nome , localita' , #posti)
•
RISTORANTE_CARATTERISTICO (nome , localita')
Vincoli
•
Per la tabella BANCHETTO: il codice fiscale deve appartenere ad AZIENDA o a
PERSONA.
•
Per le tabelle AZIENDA e PERSONA: devono rispettare il vincolo imposto dalla gerarchia
ISA: ciascun codice fiscale deve essere unico nelle due tabelle.
•
Per RISTORANTE_CARATTERISTICO: deve esistere in RISTORANTE.
•
Per ogni banchetto deve esistere almeno un invitato. SPECIALITA' deve essere in
PORTATA. SPECIALITA' deve essere offerta da RISTORANTE_CARATTERISTICO.
•
Un banchetto deve avere necessariamente un menu'.
•
MENU' deve contenere almeno una portata.
•
Una portata deve essere contenuta almeno in un MENU'.
•
Un BANCHETTO deve avvenire in un RISTORANTE.
•
Se cancello un record da RISTORANTE, devo cancellare l'eventuale record in
RISTORANTE_CARATTERISTICO.
•
Se cancello un record da RISTORANTE_CARATTERISTICO, devo cancellare da
SPECIALITA' i piatti offerti da quel ristorante.
•
Il tipo di portata in SPECIALITA' deve essere congruente con il tipo in PORTATA.
•
Un cliente non puo' ordinare lo stesso menu' per piu' di due banchetti consecutivi (vincolo
imposto).
•
Il costo di un banchetto non puo' essere superiore al capitale sociale di un'azienda se il cliente
e' un'azienda.
Implementazione SQL92
Quella che segue e' l'implementazione in SQL92 del database di test; nell'eseguire questa
operazione, abbiamo verificato che alcune possibilita' offerte dallo standard non sono disponibili
nei DBMS in prova.
In particolare l'assenza della clausola ASSERT e l'impossibilita' di specificare una SELECT
all'interno di una CHECK rendono necessario l'uso di trigger. Di seguito riportiamo degli esempi
rappresentativi per ogni classe di problemi.
L'entita' CLIENTE non puo' essere rappresentata cosi' come compare nello schema E/R. La
scelta e' stata quella di avere due tabelle separate: PERSONA ed AZIENDA. Essendo codice
fiscale una chiave per CLIENTE, l'inserimento di un record in una delle due tabelle deve
verificare che nell'altra non esista un record con lo stesso codice fiscale. Questo richiede una
CHECK all'interno della CREATE TABLE, ma la CHECK di cui abbiamo bisogno ha una
SELECT annidata al suo interno. Questa opzione, possibile in SQL92, non e' permessa da
nessuno dei tre DBMS in prova. E' necessario quindi utilizzare un trigger. E' stato possibile
implementare tutti i vincoli di integrita' referenziale, come previsto. L'entita'
RISTORANTE_CARATTERISTICO e' stata modellata come una tabella la cui chiave primaria
referenzia la chiave primaria di RISTORANTE. Inoltre e' stata scelta la clausola ON DELETE
CASCADE per cancellare un record di RISTORANTE_CARATTERISTICO quando viene
cancellata la chiave in RISTORANTE. Anche questa opzione non ha dato nessun problema.
Creazione tabelle in SQL
CREATE TABLE Azienda (
cod_fiscale CHAR(16) NOT NULL,
citta CHAR(16) NOT NULL,
via CHAR(25),
telefono CHAR(20) NOT NULL,
capitale_soc INTEGER,
dipendenti INTEGER,
PRIMARY KEY (cod_fiscale));
CREATE TABLE Persona (
cod_fiscale CHAR(16) NOT NULL,
citta CHAR(16) NOT NULL,
via CHAR(25),
telefono CHAR(20) NOT NULL,
cognome CHAR(25) NOT NULL,
nome CHAR(25) NOT NULL,
professione CHAR(25),
PRIMARY KEY (cod_fiscale));
CREATE TABLE Ristorante (
nome CHAR(25) NOT NULL,
localita CHAR(25) NOT NULL,
posti INTEGER NOT NULL,
PRIMARY KEY (nome, localita));
CREATE TABLE Ristorante_carat (
nome CHAR(25) NOT NULL,
localita CHAR(25) NOT NULL,
PRIMARY KEY (nome, localita),
FOREIGN KEY(nome, localita) REFERENCES Ristorante ON DELETE CASCADE);
CREATE TABLE Portata (
nome CHAR(25) NOT NULL,
tipo CHAR(25) NOT NULL,
PRIMARY KEY (nome));
CREATE TABLE Specialita (
nome CHAR(25) NOT NULL, tipo CHAR(25) NOT NULL,
nomerist char(25) NOT NULL,
locrist char(25) NOT NULL,
PRIMARY KEY (nome),
FOREIGN KEY (nome) REFERENCES Portata ON DELETE CASCADE ,
FOREIGN KEY (nomerist, locrist) REFERENCES Ristorante_carat ON DELETE
CASCADE);
CREATE TABLE Menu (
id CHAR(20) NOT NULL,
costo INTEGER NOT NULL,
PRIMARY KEY (id));
CREATE TABLE Comprende (
id_menu CHAR(20) NOT NULL,
nome_portata CHAR(25) NOT NULL,
PRIMARY KEY (id_menu, nome_portata),
FOREIGN KEY (id_menu) REFERENCES Menu,
FOREIGN KEY (nome_portata) REFERENCES Portata);
CREATE TABLE Banchetto (
cod_banchetto CHAR(20) NOT NULL,
occasione CHAR(25),
data DATE,
num_part INTEGER,
cod_fiscale CHAR(16) NOT NULL,
nome_ristorante CHAR(25) NOT NULL,
loc_ristorante CHAR(25) NOT NULL,
id_menu CHAR(20) NOT NULL,
PRIMARY KEY (cod_banchetto),
FOREIGN KEY (nome_ristorante, loc_ristorante) REFERENCES Ristorante,
FOREIGN KEY (id_menu) REFERENCES Menu);
CREATE TABLE Invitato (
cod_banchetto CHAR(20) NOT NULL,
nome CHAR(25) NOT NULL,
PRIMARY KEY (cod_banchetto, nome),
FOREIGN KEY (cod_banchetto) REFERENCES Banchetto);
CREATE TABLE Offre_s (
nome_ristorante CHAR(25) NOT NULL,
loc_ristorante CHAR(25) NOT NULL,
nome_specialita CHAR(25) NOT NULL,
PRIMARY KEY (nome_ristorante, loc_ristorante, nome_specialita),
FOREIGN KEY (nome_ristorante, loc_ristorante) REFERENCES
Ristorante_Carat,
FOREIGN KEY (nome_specialita) REFERENCES Portata);
CREATE TABLE Offre_m (
nome_ristorante CHAR(25) NOT NULL,
loc_ristorante CHAR(25) NOT NULL,
id_menu CHAR(20) NOT NULL,
PRIMARY KEY (nome_ristorante, loc_ristorante, id_menu),
FOREIGN KEY (nome_ristorante, loc_ristorante) REFERENCES Ristorante,
FOREIGN KEY (id_menu) REFERENCES Menu);
SQL92: Le query
In questa sezione e' possibile vedere il comportamento dei 3 DBMS rispetto a dieci
interrogazioni in SQL92. Dopo aver creato le tabelle e aver popolato la base di dati analizziamo
come i vari sistemi implementano la parte relativa al linguaggio di interrogazione dello standard
SQL-92.
Query 1
Per conto di quale cliente e' stato servito il pranzo comprensivo della specialita'
‘Lepre in Salmi'’?
SELECT pe.cod_fiscale
FROM persona pe, banchetto b, comprende co, portata p
WHERE pe.cod_fiscale=b.cod_fiscale AND b.id_menu= co.id_menu
AND co.nome_portata=p.nome AND p.nome='Lepre in salmi';
Oracle
ok
DB2
ok
Informix
ok
Query 2
A quali banchetti ha preso parte Mario Rossi?
SELECT b.cod_banchetto
FROM invitato i, banchetto b
WHERE i.cod_banchetto = b.cod_banchetto AND i.nome = 'Mario Rossi';
Oracle
ok
DB2
ok
Informix
ok
Query 3
Per ogni menu' standard, quanti ristoranti lo offrono?
SELECT m.id_menu,count (m.id_menu) AS numero_ristoranti
FROM offre_m m
GROUP BY m.id_menu;
Oracle
ok
DB2
ok
Informix
ok
Query 4
Quanti ristoranti offrono un menu' standard, che comprenda 2 portate
SELECT COUNT (DISTINCT (M.nome_ristorante, M.loc_ristorante) As TotRist
FROM ( SELECT C.id_menu, Count (*) AS NumPort
FROM comprende C
GROUP BY C.id_Menu) AS PortTot, offre_m M
WHERE PortTot.id_menu = m.id_menu
AND PortTot.Numport = 2;
Oracle
NO
DB2
NO
Informix
NO
Perche' non funziona: Nessuno dei 3 DBMS accetta piu' di un argomento nella clausola
DISTINCT. Oracle non permette l'uso della parola chiave AS nella FROM per ridenominare le
tabelle, mentre Informix e DB2 funzionano correttamente sia in sua presenza che in sua assenza.
Riformulando la l’interrogazione:
SELECT COUNT (DISTINCT M.Nome_ristorante)
FROM Offre_m M, (SELECT C.id_menu, COUNT (*) AS NumPort
FROM comprende C
GROUP BY C.id_menu) PortTot
WHERE PortTot.id_menu = m.id_menu AND PortTot.Numport = 2;
Oracle
ok
DB2
ok
Informix
NO
Perche' non funziona: Informix non permette di specificare una SELECT annidata dentro una
FROM.
Query 5
Quali aziende hanno ordinato più di 2 banchetti?
SELECT a.cod_fiscale
FROM azienda a
WHERE a.cod_fiscale IN (SELECT b.cod_fiscale
FROM banchetto b
GROUP BY b.cod_fiscale
HAVING COUNT (b.cod_banchetto)2);
Oracle
ok
DB2
ok
Informix
ok
Query 6
Quanti posti hanno i ristoranti prenotati dalla azienda con il piu' alto capitale
sociale?
SELECT R.posti
FROM ristorante R, banchetto B, azienda A
WHERE A.cod_fiscale=B.cod_fiscale AND B.nome_ristorante=R.nome
AND B.loc_ristorante=R.localita
AND a.capitale_soc ALL (SELECT A1.capitale_soc
FROM Azienda A1);
Oracle
ok
DB2
ok
Query 7
Informix
ok
Quali sono i nomi delle portate che compaiono nel maggior numero di menu'?
SELECT Temp.Nome_Portata
FROM (SELECT CO.Nome_Portata, COUNT (CO.id_Menu) AS TotMenu
FROM Comprende CO
GROUP BY CO.Nome_Portata) Temp
WHERE Temp.TotMenu=(SELECT MAX(Temp.TotMenu)
FROM Temp);
Oracle
NO
DB2
NO
Informix
NO
Perche' non funziona: Nessuno permette la creazione di una tabella temporanea all'interno della
clausola FROM che venga anche referenziata in una SELECT all'interno della WHERE
Riformulando l'interrogazione diversamente tutto funziona correttamente
SELECT C1.nome_portata
FROM Comprende C1
GROUP BY C1.nome_portata
HAVING COUNT(C1.id_menu) = ALL(SELECT COUNT(C.id_menu) AS tot_menu
FROM Comprende C
GROUP By C.nome_portata);
Oracle
ok
DB2
ok
Informix
ok
Query 8
Esistono ristoranti non caratteristici che offrono specialita'?
SELECT S.Nome_Ristorante
FROM Offre_S S
WHERE S.Nome_Ristorante NOT IN (SELECT R.Nome
FROM Ristorante_carat R);
Oracle
ok
DB2
ok
Informix
ok
Query 9
Quali invitati hanno partecipato a 2 o piu' banchetti offerti da clienti diversi?
SELECT I.Nome
FROM Invitato I
GROUP BY I.Nome
HAVING 2<(SELECT COUNT (DISTINCT B.Cod_fiscale)
FROM Invitato I1,Banchetto B
WHERE I.Nome=I1.Nome AND B.Cod_Banchetto=I1.Cod_Banchetto);
Oracle
ok
DB2
ok
Informix
ok
Query 10
Quali sono gli invitati che hanno preso parte ai banchetti con i menu' piu' costosi e,
in quali ristoranti?
SELECT I.NOME
FROM Iinvitato I, Banchetto B, Menu M
WHERE I.cod_banchetto=B.cod_banchetto AND
M.id=B.id_menu AND
M.costo IN (SELECT MAX(M1.costo)
FROM MENU M1);
Oracle
ok
DB2
ok
Informix
ok
Conclusioni
Nessuno dei 3 DBMS accetta piu' di un argomento nella clausola DISTINCT (Query 4)
Informix non permette di specificare una SELECT annidata dentro una FROM. (Query 4)
Nessuno permette la creazione di una tabella temporanea all'interno della clausola FROM che
venga anche referenziata in una SELECT all'interno della WHERE.(Query 7)
Oracle non permette l'uso della parola chiave AS nella FROM per ridenominare le tabelle,
mentre Informix e DB2 funzionano correttamente sia in sua presenza che in sua assenza.
Trigger
Tutti e tre i DBMS mettono a disposizione del progettista un SQL ampliato con istruzioni
procedurali che puo' essere usato per scrivere dei trigger. Questo linguaggio non e' standard e
l'idea di scrivere trigger in Java nasce dalla speranza di poter superare questo limite. Abbiamo
scelto ed implementato come rappresentativo dei vincoli realizzabili solo attraverso trigger, il
primo relativo alle tabelle Banchetto, Azienda e Persona.
Banchetto Trigger
Il vincolo che devono soddisfare le istanze delle tabelle Banchetto, Azienda e Persona del
database implementato e' il seguente: l'aggiornamento o l’inserimento di una riga in Banchetto
deve contenere un codice fiscale presente anche nelle tabelle Persona oppure Azienda.
Per soddisfare questo vincolo e' stata scritta una procedura in Java invocata da un trigger che
genera una eccezione se il vincolo e' violato. La generazione di un'eccezione da parte di un
trigger provoca il rollback della istruzione SQL che ha attivato il trigger, cioe' INSERT o
UPDATE;
Trigger in Java
Nei tre DBMS che stiamo esaminando la implementazione del trigger e' simile, nel senso che
occorre scrivere la procedura Java, pubblicarne il prototipo nel DBMS effettuando il casting dei
tipi di dato passati come argomento ai tipi di dato disponibili in SQL, quindi creare un trigger e
collegare la trigger-action alla procedura.
Banchetto Trigger in Java su Oracle 8i
In Oracle 8i la macchina virtuale Java e' disponibile direttamente nel server, unitamente alla
possibilita' di far compilare a quest'ultimo il codice utente. Questo ci permette di scrivere il
codice Java di seguito alla CREATE PROCEDURE e di farlo compilare al DBMS. Le istruzioni
SQL che realizzano questo sono:
CREATE
import
import
import
public
OR REPLACE AND COMPILE JAVA SOURCE NAMED "BTrefAP" AS
java.sql.*;
java.io.*;
oracle.jdbc.driver.*;
class BT {
public static void refAP (String new_cf) throws SQLException {
Connection conn = new OracleDriver().defaultConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select cod_fiscale from
Persona " +
"where cod_fiscale = '" + new_cf + "'" + "UNION " +
"select cod_fiscale from Azienda " +
"where cod_fiscale = '" + new_cf + "'");
if (!rs.next()) {
System.out.println("Violato vincolo di referenza su
Azienda e Persona");
throw new SQLException("violazione vincolo di referenza
su Azienda e Persona");
}
rs.close();
stmt.close();
}
}
La pubblicazione della procedura appena creata si ottiene mediante la:
CREATE OR REPLACE PROCEDURE TRG(new_cf VARCHAR2)
AS LANGUAGE JAVA
NAME 'BT.refAP(java.lang.String)';
L'ultimo passo, necessario a collegare la procedura ad un trigger si ottiene in questo modo:
CREATE OR REPLACE TRIGGER BT
BEFORE INSERT OR UPDATE OF cod_fiscale ON Banchetto
FOR EACH ROW CALL TRG (:new.cod_fiscale)
L'implementazione del vincolo richiesto in Oracle non ha quindi rappresentato particolari
problemi.
Banchetto Trigger in Java su DB2 6.1
DB2 offre la possibilita' di scrivere funzioni definite dall'utente (udf) in vari linguaggi (c, c++,
rexx,...), tra cui anche Java, che vengono registrate nel server in maniera analoga a quanto
avviene in Oracle 8i.La differenza fondamentale tra i due DBMS consiste nel fatto che mentre in
Oracle la macchina virtuale Java e' presente nel server stesso, DB2 si serve di una macchina
virtuale esterna: e' necessario installare il JDK1.1 sulla macchina dove gira il DBMS, impostare
alcune variabili d'ambiente al quale quest'ultimo fa riferimento (CLASSPATH, ...) e far puntare
un valore di una tavola di sistema alla directory radice del JDK.
Per implementare il BanchettoTrigger come fatto in Oracle, quindi, occorre scrivere la classe
Java, la quale effettua i necessari controlli sulla validita' degli inserimenti e degli aggiornamenti,
pubblicarla nel server db2 come funzione che ritorna un intero (ad esempio: 0=inserimento
possibile, 1=inserimento scorretto) e quindi collegarla ad un trigger. Le difficolta' sorgono nel
momento in cui si scopre dalla documentazione della sintassi SQL di DB2 (IBM DB2 Universal
Database SQL Reference, Version 5.2, Document Number S10J-8165-01, comune alle versioni
5.2 e 6.1) che la CREATE TRIGGER puo' contenere, come trigger-action solo funzioni, sia
definite dall'utente che di sistema, e che una UDF non puo' contenere al suo interno istruzioni
SQL!
Ne segue l'impossibilita' di definire il trigger di cui abbiamo bisogno utilizzando la stessa
filosofia con cui lo abbiamo implementato in Oracle.
Banchetto Trigger in Java su Informix Dynamic Server 2000
Solo Informix Dynamic Server 2000 ha il supporto per funzioni definite dall'utente scritte in
Java. Inoltre, la versione dimostrativa per la piattaforma Windows NT disponibile presso il sito
di Informix non e' completa: la documentazione infatti fa riferimento ad una gerarchia di
directory inesistente. E' stato necessario servirsi della versione per Linux per poter scrivere
funzioni in Java.
A differenza di quanto avviene con Oracle, Informix si serve del JDK 1.1 di Sun per eseguire e
compilare il codice in Java, ed e' necessario effettuare una serie di modifiche in un catalogo di
sistema del DBMS per specificare i percorsi di ricerca della macchina virtuale Java, delle
eventuali librerie esterne e del codice da eseguire. La procedura necessaria a utilizzare una
funzione definita dall'utente in un trigger consiste dei seguenti passi:
•
scrittura della procedura in Java, (nel nostro caso, la procedura e' quella usata per Oracle, con
le necessarie modifiche per l'invocazione dei metodi JDBC di Informix), compilazione e
creazione di un archivio jar;
•
esecuzione di una procedura predefinita nel DBMS per installare il jar nel database
("install_jar()");
•
registrazione della funzione mediante l'istruzione CREATE PROCEDURE;
CREATE PROCEDURE BTRefAP(new_cf VARCHAR)
WITH (CLASS = "jvp")
EXTERNAL NAME 'BanchettoTRG:BT.refAP'
LANGUAGE JAVA;
L'istruzione crea la procedura BTRefAP() collegandola al metodo refAP() della classe BT che si
trova nell'archivio BanchettoTRG.jar.
Infine, la creazione del trigger e' molto simile a quella fatta per Oracle:
CREATE TRIGGER BT
INSERT ON Banchetto
REFERENCING NEW AS new
FOR EACH ROW EXECUTE PROCEDURE BTRefAP(:new.cod_fiscale)
La difficolta' nella scrittura di procedure in Java con Informix risiede tutta nel fatto che e' stato
necessario impostare un discreto numero di parametri del DBMS per permettergli di eseguire il
codice.
Trigger in SQL procedurale
Abbiamo implementato il trigger sulla tabella BANCHETTO anche in SPL/SQL in Informix,
PL/SQL in Oracle, DB2SQL in DB2.Benche' non siano identici, anche uno sguardo rapido rivela
una fortissima somiglianza sintattica fra il codice sorgente nei tre prodotti; scrivere un trigger
con questo metodo risulta piu' semplice e immediato rispetto al Java, ma non immediatamente
portabile. Esiste tuttavia una differenza fondamentale che distingue il DBMS di Informix dagli
altri due: mentre Oracle e DB2, quando specificata la clausola BEFORE INSERT nella creazione
del trigger intendono quello che il senso comune suggerisce (prima dell'esecuzione della
INSERT esegui il codice del trigger, quando la riga che stiamo per immettere soddisfa le
condizioni specificate nell'istruzione WHERE), in Informix esiste lo clausola INSERT BEFORE,
che sta a significare che, a prescindere dal contenuto della riga che stiamo inserendo (difatti nel
blocco di istruzioni che segue la BEFORE non e' possibile referenziare la nuova riga), occorre
eseguire in qualunque caso le istruzioni della BEFORE.
Banchetto Trigger in PL/SQL su Oracle 8i
Dal diagramma sintattico dell'istruzione CREATE TRIGGER, sembrerebbe possibile scrivere il
trigger nel seguente modo:
CREATE OR REPLACE TRIGGER banchetto_trg
BEFORE INSERT OR UPDATE OF cod_fiscale ON banchetto
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM azienda
a)
AND n.cod_fiscale NOT IN (SELECT p.cod_fiscale FROM persona p))
(Raise_application_error(-20000, 'Codice fiscale non valido'));
Invece, cercando di eseguire questa istruzione si viene a conoscenza del fatto che non e' possibile
effettuare una query SQL all'interno della WHERE. Occorre quindi trovare una soluzione
alternativa: seguendo il suggerimento per la soluzione di un problema analogo nella
documentazione, abbiamo dichiarato ed utilizzato una coppia di cursori, uno per la tabella
PERSONA ed uno per la tabella AZIENDA, aventi come parametro il codice fiscale che si vuole
inserire o di cui si vuole effettuare un aggiornamento. Ciascun cursore ritorna il valore
NOTFOUND se fallisce nella ricerca del codice fiscale nelle suddette tabelle e questo ci
permette di generare un'eccezione. Il codice che gestisce l'eccezione esegue una chiamata alla
routine di sistema RAISE_APPLICATION_ERROR() che ferma l'esecuzione di tutta la
procedura, stampa un messaggio di errore e un numero di errore in un intervallo riservato agli
errori definiti dall'utente. Per default, non essendo questo numero di errore inviato ad un gestore
di eccezioni, viene eseguito il rollback dell'istruzione SQL che ha provocato l'esecuzione del
trigger, cioe' INSERT o UPDATE (vedi 'Oracle 8i - Application Developer's Guide', part number
A68003-01, pag. 10.44). Il codice e' il seguente:
CREATE OR REPLACE TRIGGER banchetto_trg
BEFORE INSERT OR UPDATE OF cod_fiscale ON BANCHETTO
FOR EACH ROW
DECLARE
Dummya
CHAR(20);
Dummyp
CHAR(20);
Invalid_cf
EXCEPTION;
CURSOR Dummy_cursora (cf CHAR) IS
SELECT a.cod_fiscale FROM azienda a
WHERE a.cod_fiscale = cf;
CURSOR Dummy_cursorp (cf CHAR) IS
SELECT p.cod_fiscale FROM persona p
WHERE p.cod_fiscale = cf;
BEGIN
OPEN Dummy_cursora (:new.cod_fiscale);
OPEN Dummy_cursorp (:new.cod_fiscale);
FETCH Dummy_cursora INTO Dummya;
FETCH Dummy_cursorp INTO Dummyp;
IF Dummy_cursora%NOTFOUND THEN
CLOSE Dummy_cursora;
IF Dummy_cursorp%NOTFOUND THEN
RAISE Invalid_cf;
END IF;
CLOSE Dummy_cursorp;
END IF;
EXCEPTION
WHEN Invalid_cf THEN
Raise_application_error(-20000, 'Codice fiscale non valido!');
CLOSE Dummy_cursorp;
END;
L'esecuzione di prove di inserimento nella tabella BANCHETTO con diversi valori del codice
fiscale ci hanno permesso di verificare che il comportamento del trigger e' quello desiderato.
Banchetto Trigger in DB2SQL su DB2 6.1
La possibilita' offerta da DB2 di includere una query nella WHERE ha reso la scrittura del
trigger molto piu' semplice, rispetto ad Oracle. Questo e' il codice:
CREATE TRIGGER trg2
NO CASCADE BEFORE INSERT ON banchetto
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
WHEN(n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM azienda a
UNION SELECT p.cod_fiscale FROM persona p))
SIGNAL SQLSTATE '75000' ('Not present in Azienda or Persona')
Banchetto Trigger in SPL SQL su Informix Dynamic Server 2000
La trigger action, come nei due casi precedenti, consta della generazione di una eccezione. Data
l'impossibilita' di effettuare una chiamata diretta all'istruzione RAISE EXCEPTION all'interno
della definizione del trigger, occorre innanzi tutto definire una procedura che effettui una tale
chiamata e quindi collegarla alla trigger action. Di seguito il semplice codice:
CREATE PROCEDURE errore()
RAISE EXCEPTION -746, 0, 'errore: codice fiscale inserito non
valido!'
END PROCEDURE
Definizione del trigger:
CREATE TRIGGER banchetto
INSERT OR UPDATE OF cod_fiscale ON BANCHETTO
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM azienda
a)
AND n.cod_fiscale NOT IN (SELECT p.cod_fiscale FROM
persona p))
(EXECUTE PROCEDURE errore())
Rispetto a DB2, nella WHERE e' vietato l'uso dell'operatore UNION, cosa che ha reso necessario
l'uso degli operatori logici.
Da notare che l'eccezione esegue un rollback dell'istruzione che ha generato l'invocazione del
trigger solo se il database sul quale stiamo operando e' stato creato con il logging attivo. Questo
non avviene di default, ed e' necessario specificarlo all'atto della creazione del database. Questo
si ottiene con la seguente istruzione:
CREATE DATABASE AgenziaRelazioniPubbliche WITH LOG;
Gli indici
In questa sezione ci proponiamo di analizzare quali possibilita' vengono offerte dai software per
la gestione degli indici e in particolar modo quali tipi vengono messi a disposizione del
progettista per ottimizzare al massimo le prestazioni.
B-Tree
Tutti e tre i prodotti mettono a disposizione i B-Tree che sono il modo piu' frequente di gestire
gli indici. Le opzioni per questo tipo di indicizzazione sono molto complete e sono uguali per
tutti. E' possibile:
specificare se l'indice deve essere in ordine crescente o decrescente; indicare la percentuale di
spazio libero su ogni nodo dell'albero al momento delle sua creazione, secondo la previsione di
crescita del database; richiedere la “clusterizzazione” dell'indice. richiedere l'unicita' della chiave
creare l'indice su piu' colonne della tabella (per aumentare l'unicita' della chiave).
Hash
Oracle e' l'unico ad offrire questo potente metodo d'indicizzazione. Il manuale non parla di indici
di tipo hash ed effettivamente non e' possibile utilizzare la CREATE INDEX per ottenere questo
tipo di indice.
La procedura e' piu' complessa perche' le tabelle devono essere memorizzate su disco
differentemente dalle condizioni normali. Bisogna preparare lo spazio su disco prima: e'
necessario creare un "CLUSTER" specificando la dimensione della chiave, il numero di chiavi,
la dimensione del cluster. Al momento di creazione della tabella, questa deve essere inserita nel
"CLUSTER" creato. E' possibile utilizzare una funzione messa a disposizione dal sistema, ma e'
anche possibile crearla.
R-Tree
Per quello che riguarda metodi di indicizzazione basati su albero, Informix mette a disposizione
un altro tipo di albero chiamato R-Tree (range tree). Questa struttura e' studiata per migliorare le
prestazioni delle interrogazioni che includono nello spazio di ricerca un intervallo di valori
anziche' un valore preciso e per tutte le strutture multidimensionali a due o tre dimensioni piu'
eventualmente il tempo. La struttura di un R-Tree e' molto simile a quella di un B-Tree sebbene i
dati memorizzati al suo interno siano differenti. Il suo obiettivo e' di individuare un insieme di
dati che sia molto piu' piccolo dell'insieme di partenza con la massima velocita' possibile,
piuttosto che trovare subito l'insieme finale. L'insieme temporaneo trovato e' conservativo:
spesso contiene piu' dati di quelli richiesti, ma contiene sempre tutte le soluzioni. La visita di un
R-Tree permette di scartare una grande quantita' di dati non utili alla ricerca senza analizzarli.
Questo viene fatto eliminando dati che cadono fuori dall'area di interesse. Su questo argomento
la Informix mette a disposizione una sezione dedicata che spiega in dettaglio il funzionamento.
Bitmap
Sia Oracle che Informix mettono a disposizione un interessante metodo di indicizzazione: il tipo
bitmap.
Questo e' basato sull'associazione di una mappa di bit alla chiave di ricerca. Quindi se ci sono
pochi tipi diversi di chiave la mappa di bit si mantiene piccola e cosi' il file indice. Inoltre un file
piccolo viene caricato in poco tempo diminuendo accesso a disco e migliorando le performance.
Funzione
Sia Oracle che Informix offrono un sistema di indicizzazione basato su funzioni definite
dall'utente. Queste funzioni possono essere espressioni algebriche, codice PL/SQL, C o Java. Il
valore ritornato dalla funzione viene utilizzato come chiave per la gestione dell'indice. Un
esempio riportato nella documentazione di entrambi i software e' quello della funzione Area. Su
una tabella contenente due colonne che rappresentano altezza e larghezza di una certa superficie
a cui fa riferimento il record, e' possibile creare un indice sull'area, cosi' da poter ottimizzare
un’interrogazione nella cui WHERE sia presente l'area. La funzione ha come parametri altezza e
larghezza e restituisce il valore dell'area che viene utilizzata come chiave per l'indice. Informix
dice chiaramente che questo tipo di indicizzazione e' basato su B-Tree o R-Tree; benche' Oracle
non lo chiarisca espressamente, l'uso del B-Tree si intuisce dalla possibilita' di eseguire query su
un intervallo di dati.
Indici programmabili
Oracle ed Informix mettono a disposizione la possibilita' di programmare completamente le
funzioni di indicizzazione permettendo di scrivere il codice relativo alla loro completa gestione.
In Oracle questi vengono chiamati "Domain indexes" e le funzioni vengono caricate attraverso
l'istruzione IndexType. In Informix questo avviene attraverso un modulo chiamato "DataBlade".
Altre possibilita'
Tutti e tre i DBMS mettono a disposizione la possibilita' di dividere le tabelle su piu' dischi per
ottimizzare il loro accesso. Oracle offre alcune opzioni veramente interessanti: e' possibile
richiedere la compressione degli indici per diminuire l'accesso a disco a spese della CPU e la
possibilita' di avere tabelle “clusterizzate”, ovvero tabelle che condividono colonne in comune
vengono messe nella stessa area del disco per migliorare le prestazioni del join.
DB2 offre la possibilita' di fare una "Summary Table", ovvero una tabella che contiene al suo
interno dati di piu' tabelle ed evita quindi la necessita' di fare dei join su certe colonne delle
stesse.
Conclusioni
Appare chiaro che Oracle e' da questo punto di vista il piu' versatile in quanto mette a
disposizione del progettista tutti gli strumenti disponibili attualmente eccetto gli R-Tree. Informix
offre meno possibilita', ma e' ancora molto versatile. Il DB2 invece e' il piu' povero di tutti in
quanto mette a disposizione i pur sempre validi B-Tree.
Query optimizer
In questa sezione vogliamo esaminare come il progettista puo' vedere e migliorare le scelte del
query optmizer. Tutti i tre prodotti danno la possibilita' di vedere il piano scelto dal query
optimizer, influenzarne le scelte o escludere del tutto l'ottimizzatore.
Piano di esecuzione di una query
I comandi SQL EXPLAIN (per DB2), SET EXPLAIN (per Informix) o EXPLAIN PLAN (per
Oracle) permettono di esaminare il piano di esecuzione attuale di una query; questo consiste
nella descrizione dell'ordine e della modalita' con cui vengono eseguite le operazioni. E' cosi'
possibile conoscere le scelte fatte dall'ottimizzatore per poterle successivamente modificare.
DB2, a differenza degli altri due prodotti, mette a disposizione anche uno strumento grafico.
Riportiamo un esempio relativo alla prima query:
Strategie utilizzate dall'ottimizzatore
Le strategie offerte sono due: basata sulle regole e basata sui costi.
L'approccio basato sulle regole e' disponibile solo in Oracle per mantenere la compatibilita' con
le vecchie versioni del DBMS. L'ottimizzatore sceglie il percorso di accesso ai dati basandosi
sulla disponibilita' del percorso di accesso per l'istruzione corrente e sul punteggio assegnato a
tale percorso. Lo svantaggio di questo modello consiste nel fatto che tale punteggio e' fissato
staticamente e quindi non modificabile in funzione di necessita' specifiche.
L'approccio basato sui costi e' disponibile su tutti e tre i prodotti ed e' il piu' performante. Si basa
su una stima del costo di esecuzione di un piano (I/O, CPU, tempo...) per ciascun piano preso in
esame dall'ottimizzatore. Per fare questa stima il sistema mantiene delle tabelle contenenti dati e
statistiche relativi alla popolazione della base di dati. Vengono utilizzati degli istogrammi per
raccogliere delle informazioni statistiche. Tutti questi dati sono mantenuti in tabelle di sistema.
Ogni volta che si modifica in maniera sostanziale la base di dati (caricamento di un numero
rilevante di righe o modifica dello schema di una tabella) si dovra'; procedere all'acquisizione di
nuove informazioni. Informix e DB2 non mettono a disposizione nessuno strumento per
l'aggiornamento automatico delle statistiche a differenza di Oracle. Dato l'elevato costo
dell'operazione di aggiornamento, Informix mette a disposizione due diversi livelli di dettaglio
per l'acquisizione dei dati. Anche per Oracle e' possibile scegliere tra due diversi tipi di raccolta
dei dati: uno per righe, piu' dettagliato, e l'altro per blocchi, piu' veloce.
Metodi d'accesso
In mancanza di indici, il metodo d'accesso ai dati e' la lettura sequenziale.
I tre DBMS mettono a disposizione, inoltre, degli altri metodi di scansione relativi a certe loro
peculiarita'.
Oracle nomina la presenza di un metodo chiamato “Cluster Scan” per scandire le “Cluster
Table”.
Il metodo “Sample Table Scans” serve per fare statistiche rilevando righe campione da una
tabella. Viene utilizzata in query adibite a questo scopo.
DB2 offre un metodo chiamato “Table Scan” per scandire una “Summary Table” ed evitare un
join.
In presenza di indici, sono supportati tutti i metodi classici, ovvero l'accesso diretto al record,
l'accesso a piu' record in un intervallo e l'uso del solo indice per avere i dati richiesti quando
possibile.
DB2 parla di un “Multiple Index Access”: questo metodo implica l'uso di piu' indici disponibili
su una certa tabella. Viene applicato, per esempio, nel caso in cui la WHERE contenga
condizioni di AND e di OR su colonne sulle quali sia disponibile un indice. Ogni indice viene
utilizzato per creare una lista di Record ID che soddisfano il predicato in questione. Per avere la
lista finale di record da prelevare bisogna: nel caso OR eliminare i duplicati tra le liste, nel caso
AND la creazione di una bitmap dinamica velocizza la creazione di una lista in cui compaiono
solo i record presenti su entrambe.
Metodi di Join
Il Nested Loop Join, Hash Join sono comuni a tutti e tre i prodotti. Oracle e DB2 offrono inoltre
anche Sort-Merge Join e Star Join. Quest'ultimo serve solo quando nel join vengono usate chiavi
primarie o chiavi straniere tra una tabella di tipo “Fact tables” e una di tipo “Lookup table”.
Queste due tabelle sono generalmente usate nei datawearehouse: “Fact tables” sono tabelle molto
grandi, mentre “Lookup table” sono tabelle molto piu' piccole che contengono informazioni su
degli attributi delle prime.
Oracle nomina Cluster Join per le tabelle di tipo “Cluster Table”.
Influenzare l'ottimizzatore
Oracle ed Informix consentono di sostituire alle scelte dell'ottimizzatore le proprie e inoltre
mettono a disposizione delle istruzioni per poter influenzare le scelte dell'ottimizzatore:
permettono intervenire sulla query mediante indicazioni che possono essere aggiunte all'interno
dell'istruzione SQL di SELECT, UPDATE o DELETE per forzare alcune scelte. Oracle le
chiama “hints”, Informix le chiama "direttive di ottimizzazione".
E' possibile modificare:
•
La strategia dell'ottimizzatore (basato sulle regole, basato sui costi)
•
Scelta dell'obiettivo: miglior tempo di esecuzione, miglior tempo di risposta.
•
Il tipo di percorso di accesso
•
L'ordine di join per un'operazione di join
•
Quale tipo di join scegliere
DB2 ed Informix hanno inoltre delle classi di ottimizzazione per facilitare il compito del
progettista. DB2 offre la possibilita'; di scegliere tra 10 classi di ottimizzazione (0-9) che
impegnano in modo differente le risorse del nostro sistema, Informix da' la possibilita'; di variare
il tempo speso nella ricerca del piano piu' veloce modificando la classe di ottimizzazione.
Le classi del DB2 sono:
Classe 0: Per questa classe l'ottimizzatore utilizza il minimo numero possibile di risorse: tale
classe dovrebbe essere utilizzata solo per query molto semplici e che accedono a tabelle ben
indicizzate. Vengono considerate solo statistiche la cui distribuzione sia uniforme. Vengono
considerati solo Block Nested Loop Join e percorsi d'accesso tramite indice. La scelta del piano
viene effettuata tramite un algoritmo goloso.
Classe 1: Vengono considerate statistiche a distribuzione non uniforme. Sono considerati il
Merge Join e la scansione completa di una tabella. E' ora considerato anche lo Star Join. Utilizza
l'algoritmo goloso.
Classe 2: Per questa classe l'ottimizatore utilizza entrambi i tipi di statistiche: a distribuzione
uniforme e non. Sono inoltre disponibili tutti i metodi di riscrittura della query. Utilizza
l'algoritmo goloso.
Classe 3: Questa classe utilizza, come le successive e a differenza delle precedenti, il Dynamic
Programming Join Enumeration per la scelta del piano di esecuzione della query. Questo
algoritmo esplora piu' a fondo lo spazio delle soluzioni, ed e' raccomandato in presenza di join
multipli; a differenza della seconda classe pero' non utilizza tutti i metodi di riscrittura e solo
statistiche a distribuzione non uniforme. Utilizza anche il Multiple Index Access
Classe 5: Come detto e' la classe di default. Utilizza tutte le statistiche e la tutte le regole di
riscrittura della query. Inoltre, per interrogazioni particolarmente complesse, vengono
parzialmente utilizzate regole euristiche per limitare il tempo speso nella selezione di un piano
d'accesso.
Classe 7: Differisce dalla classe 5 per un uso completo delle regole euristiche.
Classe 9: Mette a disposizione tutti i metodi di riscrittura, tutte le statistiche, tutti i possibili
metodi di join e tutti i metodi d'accesso.
NB: le classi 4 e 6 non sono utilizzate.
E' inoltre possibile inserire nella SELECT delle clausole per influenzare l'ottimizzatore in casi
specifici per:
•
Recuperare solo quelle righe che permettono un'interrogazione read-only per evitare lock
esclusivi che possono penalizzare le prestazioni del sistema.
•
Limitare il numero di tuple recuperato. E' possibile in applicazioni tipo “browser” di voler
recuperare solo alcune righe.
•
Gestire in maniera piu' efficente il rilascio di memoria dovuto ad un cursore.
Conclusioni
Informix ed Oracle sono abbastanza simili nella filosofia d'approccio all'ottimizzazione,
permettendo d'intervenire su un gran numero di parametri a diversi livelli fino ad arrivare alla
completa esclusione dell'ottimizzatore. DB2 si differenzia dai precedenti mettendo a disposizione
dei profili predefiniti di ottimizzazione: L'impressione e' stata quella che DB2 offra
un'interazione piu' ad alto livello impedendo volontariamente l'intrusione nelle scelte del sistema.
