Indecsi

Indecsi

Un index este un obiect al schemei unei baze de date care:

creste viteza de executie a cererilor;

garanteaza ca o coloana contine valori unice.

Server-ul Oracle utilizeaza identificatorul ROWID pentru regasirea liniilor in structura fizica a bazei de date. Indexul, din punct de vedere logic, este compus dintr-o valoare cheie si din identificatorul adresa ROWID.

Cheia indexului poate fi coloana unui tabel sau concatenarea mai multor coloane (numarul maxim de coloane care pot defini cheia indexului este 32). Coloanele care apar in cheia indexului trebuie declarate NOT NULL in tabel.

Indecsii sunt utilizati si intretinuti automat de catre server-ul Oracle. Odata creat indexul, el nu necesita o actiune directa din partea utilizatorului.

Indecsii pot fi creati in doua moduri:

automat, de server-ul Oracle (PRIMARY KEY, UNIQUE KEY);

manual, de catre utilizator (CREATE INDEX, CREATE TABLE).

Server-ul Oracle creeaza automat un index unic atunci cand se defineste o constrangere PRIMARY KEY sau UNIQUE asupra unei coloane sau unui grup de coloane. Numele indexului va fi acelasi cu numele constrangerii.

Indecsii, fiind obiecte ale schemei bazei, beneficiaza de procesul de definire a unui obiect.

Crearea unui index (care nu este obligatoriu unic) pe una sau mai multe coloane ale unui tabel se face prin comanda:

CREATE [UNIQUE] INDEX <nume_index>

ON [<nume_schema>.] <nume_tabel>

           (<nume_col> [ASC | DESC], <nume_col> [ASC | DESC], .)

       | CLUSTER <nume_cluster>

Cand este creat un index, un segment de date este rezervat automat in spatiul tabel. Alocarea de memorie este controlata prin clauzele INITIAL, PCTINCREASE, PCTFREE, NEXT, care pot sa apara in comanda CREATE INDEX.

Gestiunea insertiilor si a reactualizarilor se face, ca si la tabele, utilizand parametrii PCTFREE si PCTUSED.

Exemplu

Sa se creeze un index descrescator relativ la coloana adresa din tabelul cititor.

CREATE INDEX  cititor_idx

ON cititor (adresa DESC);

Sa se afiseze informatiile referitoare la indexul cititor_idx.

SELECT  TABLE_NAME, UNIQUENESS, MIN_EXTENTS

FROM USER_INDEXES

WHERE INDEX_NAME='cititor_idx';

Sa se creeze un index explicit, referitor la cheia primara a unui tabel.

CREATE TABLE exemplu

(cod_id NUMBER(6)

PRIMARY KEY USING INDEX

(CREATE INDEX cod_id_idx ON exemplu (cod_id)),

nume VARCHAR2(20));

Mai multi indecsi asupra unui tabel nu implica intotdeuna interogari mai rapide. Fiecare operatie LMD care este permanentizata asupra unui tabel cu indecsi asociati presupune actualizarea indecsilor respectivi.

Prin urmare, este recomandata crearea de indecsi numai in anumite situatii:

coloana contine o varietate mare de valori;

coloana contine un numar mare de valori null;

una sau mai multe coloane sunt utilizate frecvent impreuna intr-o clauza WHERE sau intr-o conditie de join;

tabelul este mare si este de asteptat ca majoritatea interogarilor asupra acestuia sa regaseasca mai putin de 2-4% din linii.

Crearea unui index nu este recomandata in urmatoarele cazuri:

tabelul este mic;

coloanele nu sunt utilizate des in cadrul unei conditii dintr-o cerere;

majoritatea interogarilor regasesc mai mult de 2-4% din liniile tabelului;

tabelul este actualizat frecvent;

coloanele indexate sunt referite in expresii.

In concluzie, ce tabele sau ce coloane trebuie (sau nu) indexate?

indexati tabelele pentru care interogarile selecteaza un numar redus de randuri (sub 5

indexati tabelele care sunt interogate folosind clauze SQL simple;

nu indexati tabelele ce contin putine inregistrari (accesul secvential este mai simplu);

nu indexati tabelele care sunt frecvent actualizate, deoarece stergerile, inserarile si modificarile sunt ingreunate de indecsi;

indexati coloanele folosite frecvent in clauza WHERE sau in clauza ORDER BY;

nu indexati coloanele ce contin date asemanatoare (putine valori distincte);

Exemplu

CREATE INDEX upper_nume_idx ON artist (UPPER(nume));

Indexul creat prin instructiunea precedenta faciliteaza prelucrarea unor interogari precum:

SELECT  * FROM artist

WHERE UPPER(nume) = 'GRIGORESCU';

Pentru a asigura ca server-ul Oracle utilizeaza indexul si nu efectueaza o cautare asupra intregului tabel, valoarea functiei corespunzatoare expresiei indexate trebuie sa nu fie null in interogarile ulterioare crearii indexului. Urmatoarea instructiune garanteaza utilizarea indexului dar, in absenta clauzei WHERE, server-ul Oracle ar putea cerceta intreg tabelul.

SELECT  * FROM artist

WHERE  UPPER(nume) IS NOT NULL

ORDER BY UPPER(nume);

Exemplu

Sa se creeze tabelul artist, specificandu-se indexul asociat cheii primare.

CREATE TABLE artist

cod_artist NUMBER

PRIMARY KEY USING INDEX

(CREATE INDEX artist_cod_idx

ON artist(cod_artist)),

nume VARCHAR2(30) NOT NULL,

prenume VARCHAR2(30), .);

Stergerea unui index se face prin comanda:

DROP INDEX nume_index [ON [nume_schema.] nume_tabel]

Pentru a suprima indexul trebuie ca acesta sa se gaseasca in schema personala sau sa ai privilegiul de sistem DROP ANY INDEX.

Pentru a reconstrui un index se pot folosi doua metode:

se sterge indexul (DROP INDEX) si se recreeaza (CREATE INDEX);

se utilizeaza comanda ALTER INDEX cu optiunea REBUILD.

Modificarea parametrilor de stocare a indecsilor (STORAGE), alocarea (ALLOCATE EXTENT) si dealocarea (DEALLOCATE UNUSED) manuala a spatiului utilizat de un index se pot realiza cu ajutorul comenzii ALTER INDEX. Incepand cu Oracle9i, este posibila modificarea structurii indecsilor prin comanda ALTER INDEX.

Validarea unui index, adica verificarea integritatii indexului specificat pentru un tabel, se face prin comanda:

VALIDATE INDEX nume_index [ON nume_tabel] [WITH LIST]

Oracle8 foloseste urmatoarele tipuri de indecsi:

index de tip arbore B* - creat la executarea unei comenzi standard CREATE INDEX;

index partitionat - folosit in cazul tabelelor mari pentru a stoca valorile coloanei indexate in mai multe segmente;

index de cluster - bazat pe coloanele comune ale unui cluster;

index cu cheie inversa - sunt B* arbori, dar care stocheaza datele in mod invers;

index de tip bitmap - nu se stocheaza valorile efective ale coloanei indexate, ci un bitmap format pe baza acestor valori.

Versiunea Oracle8 permite construirea de tabele organizate pe baza de index. In acest caz, datele sunt stocate in indexul asociat. Un astfel de tabel poate fi manipulat de catre aplicatii la fel ca un tabel obisnuit, folosind comenzi SQL. Diferenta consta in faptul ca in cazul tabelului organizat pe baza de index, toate operatiile sunt efectuate numai asupra indexului. In loc ca fiecare intrare a indexului sa contina valoarea coloanei sau coloanelor indexate si valoarea ROWID (care identifica unic un rand) pentru randul corespunzator, ea contine intreg randul.

Coloana sau coloanele dupa care se face indexarea sunt cele care constituie cheia primara a tabelului.

Informatii referitoare la indecsi si la coloanele care ii definesc pot fi regasite in vizualizarile USER_INDEXES, respectiv USER_IND_COLUMNS din dictionarul datelor. Dintre coloanele tabelului USER_INDEXES se remarca:

Exemplu

Sa se obtina informatii referitoare la indecsii tabelului carte.

SELECT  a.index_name, a.column_name,

a.column_position poz, b.uniqueness

FROM  user_indexes b, user_ind_columns a

WHERE  a.index_name = b.index_name

AND  a.table_name = 'carte';