Gestiunea indecsilor

Gestiunea indecsilor

Introducerea inregistrarilor in tabele se face de regula fara a tine seama de anumite criterii de ordonare. Consultarea lor insa, are in vedere anumite cerinte informationale, scop in care, inregistrarile din tabele sunt ordonate dupa diferite criterii impuse de natura prelucrarilor. Pornind de la afirmatia ca limbajul SQL opereaza pe 'ansambluri de date', reiese din cele prezentate ca ansamblurile de date nu sunt ordonate, iar elementele nu sunt toate de acelasi tip. Cand se efectueaza o operatie pe un ansamblu, actiunea se aplica asupra tuturor elementelor dintr-o data.

Schema unei baze de date defineste structurile obiectelor si regulile carora li se conformeaza. In SQL, singura structura de date este tabela.

Tabelele pot fi de baza ( permanente) sau virtuale (view-uri). SQL-92 permite definirea tabelelor temporare globale si partajate. Avantajul de a avea o structura unica de date este faptul ca rezultatul oricarei operatii este tot o tabela si, ca urmare, nu este necesara convertirea structurilor.

Tabelele si coloanele pot avea asociate restrictii. O restrictie este o regula care defineste conditiile care trebuie indeplinite pentru validarea datelor dupa efectuarea unei tranzactii asupra bazei de date.

Restrictiile pe coloane (de domeniu) sunt reguli asociate tabelelor conform carora toate liniile tabelei sunt validate. Restrictiile de domeniu pot fi definite prin:

• NOT NULL;
• restrictii de verificare;
• restrictii de unicitate;
• clauza de referinta.

Aceste restrictii definesc regulile de mentinere a coerentei bazei de date. Ele sunt verificate de SGBD la fiecare executie a instructiunii SQL.

Instructiunea prin care sunt definite restrictiile de unicitate si de referinta este instructiunea CONSTRAINT.

Daca acceptam notiunea de index in precizarea restrictiilor, atunci vom considera ca exista indecsi care apartin unei singure tabele si care permit accesul direct (instructiunea CREATE INDEX) si indecsi care apartin unei relatii intre tabele (clauza CONSTRAINT).

Indecsi care apartin unei singure tabele

Indecsii au rolul de a optimiza codul de interogare. Interogarile pe o tabela sunt favorizate de existenta indecsilor. Daca nu exista un index pe o tabela, atunci nu poate fi utilizat accesul direct (indexat). Acest mod de accesare returneaza doar o inregistrare la un moment dat. Crearea unui index se face cu instructiunea CREATE INDEX.

Pentru a optimiza timpul de cautare a datelor trebuie creati indecsi, dar nu mai mult decat este necesar. Un index cere sa fie actualizat dupa efectuarea oricarei instructiuni INSERT, DELETE sau UPDATE.

Prea multi indecsi definiti pot produce consum de timp pentru a-i intretine. De retinut faptul ca indecsii trebuie utilizati cu precautie.

Crearea unui index

Instructiunea CREATE INDEX

Comanda permite crearea unui index simplu sau multiplu pe o tabela. Indexul poate fi cluster sau noncluster. Pe o tabela poate exista un singur index cluster care mentine tabela ordonata ca in cazul unei sortari. Un index cluster are tendinta de a pune valorile duplicate ale coloanei indexate in aceeasi pagina de memorie fizica, ceea ce poate accelera accesul la date legate.

Din motive fizice evidente, o tabela nu poate dispune decat de un singur index cluster. Alegerea coloanelor pentru index poate influenta performantele in exploatarea bazei de date.

Sintaxa generala este:

CREATE [CLUSTERED] [UNIQUE] INDEX nume_index

ON nume_tabela (nume_camp1 [, nume_camp2, .]);

Exemple:

l. CREATE INDEX X ON Salariati (Nune, Pren);

2. CREATE INDEX Y ON Salariati (Pren, Nume}; -

3. CREATE CLUSTERED INDEX X1 ON Salariati (Nume, Pren) ;

4. CREATE CLUSTERED INDEX Yl ON Salariati (Pren, Nume);

5. CREATE UNIQUE INDEX Z ON Depozit, (codprodus) ;

6. CREATE INDEX I1 ON Depozit (cantitate);

7. CREATE INDEX I2 ON Depozit (culoare) ;

Ordinea coloanelor care formeaza indexul este foarte importanta pentru optimizarea cautarilor. Nu este acelasi lucru daca ordinea precizata este Nume, Pren sau Pren, Nume. Ordinea trebuie stabilita avand la baza urmatoarea regula: va fi precizata ca prima coloana aceea care are valorile distincte cele mai numeroase, dupa care urmeaza cea cu valorile distincte mai putin numeroase.

Indecsii trebuie utilizati cu precautie. Cea mai mare parte a lor sunt structuri de arbori. Ei constau in pagini sau noduri (care isi iau valorile din coloanele din tabela pe care se construieste indexul) si din pointeri. Acestia puncteaza catre alte noduri ale arborelui, eventual catre liniile tabelei care a fost indexata. Ideea este ca o cautare in index este mai rapida decat cautarea in tabela in mod secvential. Indexul este totodata ordonat pe coloanele utilizate pentru a-l construi; liniile din tabela pot fi in aceasta ordine sau nu. Cand indexul si tabela sunt ordonate pe aceleasi coloane, indexul se numeste cluster.

De exemplu: Fie tabela Personal a carei cheie este marca angajatului. Actualizarea tabelei va fi facuta desigur cu identificarea angajatului (deci, prin acces indexat), dar interogarile care utilizeaza acest index sunt prea putine, viteza de actualizare a liniilor individuale in tabela va fi aceeasi, indiferent daca indexul este cluster sau nu.

Este mult mai interesant sa se construiasca un index cluster pe codul departamentului (NON UNIQUE) care sa sorteze tabela in ordinea departamentelor, caz in care toti angajatii unui departament ar fi pusi in aceeasi pagina fizica de memorie si, ca urmare, pentru a raspunde unei interogari, ar fi necesar sa fie citite mai putine pagini. De exemplu:

SELECT *FROM Depozit

WHERE cantitate = 50 and culoare = 'albastru';

In cazul in care exista un index pe culoare si nu pe cantitate, vor fi cautate intai liniile ce contin culoarea 'albastru' si abia dupa aceea va fi testata cantitatea.

Trebuie mentionat si faptul ca un index nu va putea fi utilizat in cazul in care coloana corespunzatoare este utilizata in cadrul interogarii in componenta unei expresii. De exemplu:

Fie tabela Depozit indexata dupa cantitate. Interogarea

SELECT *FROM Depozit

WHERE cantitate - 50 = 0 and culoare = 'albastru';

nu va tine seama de index.

Exista cazuri in care se doreste chiar evitarea utilizarii unui index, situatie in care se recurge la un artificiu de acest tip si anume se pune coloana index intr-o expresie:

SELECT * FROM Depozit

WHERE cantitate = 50 -O and culoare = 'albastru';

Stergerea unui index

Instructiunea DROP INDEX

Aceasta comanda sterge indexul dintr-o tabela specificata prin nume_tabela.

Sintaxa generala este:

DROP INDEX nume_index ON nume_tabela ;

Exemplu:

DROP INDEX I1 ON Depozit;

Indecsi care apartin unei legaturi dintre tabele

Clauza CONSTRAINT

Permite crearea unui index dupa numele unui camp; indexul poate fi declarat drept cheie primara (PRIMARY KEY) sau ca UNIQUE sau stabileste o relatie intre campul nume de index si campul unei tabele externe (cu optiunea REFERENCES foreign_table [foreign_field]).

Sintaxa generala a instructiunii este:

CONSTRAINT nume_index

Clauzele UNIQUE si PRIMARY KEY

Exista o diferenta intre UNIQUE si PRIMARY KEY si anume: pe o tabela poate exista o singura cheie primara, dar UNIQUE specifica existenta unor valori unice la nivelul unei coloane. In plus, PRIMARY KEY contine automat o restrictie NOT NULL pe cand o coloana UNIQUE poate avea valori NULL daca nu este specificata in mod expres clauza NOT NULL.

Mentionam faptul ca indexul poate fi simplu (creat pe o singura coloana) sau multiplu (creat pe mai multe coloane).

REFERENCES

Este varianta cea mai simpla de definire a unei restrictii relationale si care s-a dovedit deosebit de practica. Aceasta clauza pune in legatura doua tabele. Ea specifica faptul ca valorile unei coloane apartinand tabelei (care serveste la stabilirea de legaturi) trebuie sa apara intr-o coloana din tabela referita al carui nume este precizat in restrictie (CONSTRAINT nume_index).

Observatie. Restrictiile UNIQUE nu sunt acelasi lucru cu INDEX UNIQUE. Un INDEX UNIQUE nu poate fi referit pentru ca el apartine unei singure tabele si nu unei legaturi intre tabele, in plus, intr-o restrictie nu exista ordonare, in timp ce un index unique este ordonat; de aceea, ei poate fi util pentru sortari.

Exemple:

CONSTRAINT idcod PRIMARY KEY (codp);

CONSTRAINT rcod REFERENCES vanzari (codp);

Comanda DROP CONSTRAINT

Permite stergerea unei restrictii de unicitate sau de referinta utilizand sintaxa:

DROP CONSTRAINT nume_index;