Administrarea indecsilor Oracle9I

Clasificarea indecsilor

Un index este o structura arborescenta ce permite accesul direct la o linie de date dintr-un tabel. Indecsii pot fi clasificati pe baza structurii lor logice sau pe baza implementarii lor fizice. Clasificarea din punct de vedere logic grupeaza indecsii din perspectiva aplicatiilor, in timp ce clasificarea fizica este derivata din modul in care indecsii au fost stocati.

Clasificarea logica:

Cu o singura coloana sau cu coloane concatenate.

Unici sau non-unici.

Ce au la baza o functie

Domeniu

Clasificare fizica:

Partitionati sau nepartitionati

B-tree: normali sau cu cheie inversa

Bitmap

Un index pe o singura coloana are cheia indexului formata dintr-o singura coloana. Un index concatenat, cunoscut si ca index compus, are cheia indexului formata din mai multe coloane. Coloanele dintr-un index concatenat nu trebuie sa fie in aceeasi ordine ca in tabel, nici adiacente. Numarul maxim de coloane dintr-un index compus este de 32, dar dimensiunea combinata a tuturor coloanelor din index nu trebuie sa depaseasca jumatate din spatiul disponibil intr-un bloc de date.

Indecsi pot fi unici si non-unici. Indecsii unici garanteaza ca doua linii de date dintr-un tabel nu vor avea valori duplicate in cheia indexului. Indecsii non-unici nu impun aceasta restrictii.

Indecsi bazati pe functii sunt creati atunci cand sunt utilizate functii sau expresii ce implica una sau mai multe coloane din tabelul ce poate fi indexat. Un index bazat pe functie calculeaza anterior valorile functiilor sau expresiilor si le stocheaza in index. Indecsii bazati pe functii pot fi creati ca indecsi.

Clasificare fizica:

Indecsii partitionati sunt folositi la tabelele de mari dimensiuni pentru a stoca intrarile corespunzatoare unui index intr-un anumit segment de valori. Partitionarea permite unui index distribuirea pe mai multe spatii-tabel, micsoreaza accesul concurential la discuri, permitand si o administrare mai facila. Indecsii partitionati sunt adese utilizati impreuna cu tabele partitionate pentru a imbunatatii scalabilitatea si a usura administrarea. Un index partitionat poate fi creat pentru fiecare tabel partitionat.

Indecsi B-tree

Desi toate tipurile de indecsi folosesc structura B-tree termenul este folosit uzual pentru indecsii ce stocheaza o lista de ROWID-uri pentru fiecare cheie.

Structura unui index B-tree : cel mai inalt nivel al indexului este radacina, care contine intrari ce pointeaza catre urmatorul nivel al indexului. Nivelul urmator este format din blocuri branch care, la randul sau, contine intrari catre un nivel urmator. Cel mai coborat nivel este format din blocuri leaf, care contin intrarile index ce pointeaza catre liniile corespunzatoare din tabel. Blocurile leaf au legaturi duble intre ele pentru a facilita atat accesul in sensul crescator al valorilor cheilor, cat si accesul in sensul descrescator.

O intrare index este alcatuita din urmatoarele componente:

O intrare header ce contine numarul de coloane si informatiile referitoare la blocare.

O pereche de valori care definesc dimensiunea coloanei din cheie urmata de valoarea pentru valoarea respectiva. Numarul acestor perechi este maximul numarului de coloane din index.

ROWID-ul liniei care contine valoarea cheii.

Caracteristicile intrarilor leaf din index:

Valorile cheilor sunt repetate daca exista linii multiple care au aceeasi valoare a cheii, mai putin an cazul in care indexul este compresat.

Nu exista nici o intrare index corespunzatoare unei linii ale carei coloane sunt toate NULL. Dealtfel, daca in clauza WHERE este specificat o valoare NULL, atunci regasirea pe segmentul respectiv se va face prin scanare completa a acestuia.

Daca toate liniile apartin unui singur tabel se va folosi ROWID-ul restrictionat pentru a pointa catre liniile acestuia.

Efectele unei operatii DML asupra unui index:

Operatiile INSERT dintr-o tabela au ca rezultat adaugarea de intrari in indexul asociat acestuia.

Stergerea unei linii dintr-un tabel are ca rezultat stergerea logica a intrarii index din indexul asociat. Spatiul utilizat de catre intrarea index stearsa nu este disponibil unei noi intrari pana cand toate intrarile din blocul corespunzator nu vor fi sterse in totalitate.

Actualizarile dintr-o coloana cheie se materializeaza antr-o stergere logica si o operatie de insert la nivel de index. Parametrul PCTFREE nu are efect asupra indexului decat in momentul crearii acestuia. O noua intrare va fi adaugata intr-un bloc index, fiind ignorat parametru PCTFREE.

Indecsi bitmap

Sunt mult mai eficienti decat indecsii B-tree in urmatoarele situatii:

Cand un tabel are milioane de inregistrari si coloana cheie are un raport de cardinalitate mic, ceea ce inseamna un numar mic de inregistrari distincte raportate la numarul total de inregistrari (mai putin de 1%).

Cand regasirile folosesc adesea conditii WHERE multiple invocand operatorul OR.

Coloana cheie are o activitate de actualizare redusa, sau nu este actualizata deloc (read-only).

Structura unui index bitmap: este structurat logic tot ca un arbore B-tree, dar nodurile leaf stocheaza un bitmap pentru fiecare cheie in loc de ROWID-ul respectiv. Fiecare bit din bitmap corespunde unui posibil ROWID, iar daca bitul este setat (1), inseamna ca linia cu ROWID corespunzator contine o valoare a cheii.

Un nod leaf dintr-un index bitmap contine urmatoarele:

O intrare header ce contine numarul de coloane si informatiile referitoare la blocare.

Valoari ale cheii constand in lungimea si valoarea acesteia.

ROWID-ul de start, format din numarul fisierului de date, numarul blocului, numarul primei linii din tabel referita de catre bitmap.

ROWID-ul de sfarsit, format din numarul fisierului de date, numarul blocului, numarul ultimei linii din tabel referita de catre bitmap.

Un segment bitmap alcatuit dintr-un sir de biti.

Obs. Indecsii bitmap folosesc tipul ROWID restrictionat.

Utilizarea indecsilor bitmap:

Structura B-tree este folosita pentru a localiza nodurile frunza ce contin segmentele bitmap pentru o anumita valoare a cheii. ROWID-ul de start si segmentele bitmap sunt folosite pentru a localiza o linie ce contine valoarea cheii. Cand se efectueaza modificari asupra coloanei cheie din tabel, trebuie modificate si inregistrarile din bitmap. Aceasta implica blocarea segmentului bitmap asociat valorii respective. Deoarece blocarea implica intreg segmentul bitmap, linia ce este acoperita de catre segmentul respectiv nu poate fi actualizata de catre alte tranzactii decat dupa finalizarea tranzactiei.

Comparatii intre indecsi B-Tree si Bitmap

Indecsii bitmap sunt mai compacti decat indecsii B-tree.

Actualizarile coloanelor cheie din indecsii bitmap sunt mult mai costisitoare, deoarece indecsii bitmap blocheaza la nivel de segment bitmap, in timp ce indecsii B-tree blocheaza la nivel de intrare.

Indecsii bitmap sunt foarte eficienti in regasirile ce folosesc predicate booleene.

Indecsii bitmap sunt mai eficienti in mediile OLAP, pe cand ce de tip B-tree sunt mai eficienti in mediile OLTP

Crearea indecsilor B-tree

CREATE [UNIQUE] INDEX [schema.] nume_index

ON [schema.] nume_tabel (

Nume_coloana [ASC].[DESC] [,.] )

[NO[LOGGING]]

[TABLESPACE] nume_spatiu_tabel

[NOSORT]

[clauza stocare]

[clauza utilizare spatiu]

Clauza stocare:

STORAGE (

[INITIAL integer [K | M]]

[NEXT integer [K | M]]

[PCTINCREASE integer ]

[MINEXTENTS integer ]

[MAXEXTENTS integer | UNLIMITED]

[BUFFER POOL [KEEP | RECYCLE | DEFAULT]] )

Clauza utilizare spatiu:

[PCTFREE integer]

[INITRANS integer]

[MAXTRANS integer]

Consideratii privind crearea indecsilor :

Indecsii maresc performanta interogarilor si incetinesc operatiunile DML. Este de dorit crearea unui numar cat mai mic de indecsi pe tabelele volatile.

Este recomandata crearea indecsilor in spatii-tabele separate de ce necesare segmentelor de revenire, temporare sau de date, pentru a preveni accesul concurent la discuri.

Se pot obtine imbunatatiri semnificative de performanta pentru crearea indecsilor de dimensiuni mari prin evitarea inregistrarilor redolog pentru segmentele index. Pentru aceasta se recomanda folosirea clauzei NOLOGGING.

Deaorece intrarile index sunt mai mici decat liniile din tabel corespunzatoare, blocurile index au tendinta de a avea mai multe intrari in cadrul unui bloc. Din acest motiv parametru INITRANS trebuie sa fie, in general, mai mare la index decat la tabelul corespunzator.

Parametru PCTFREE pentru un index lucreaza diferit decat la tabele. Acest parametru este folosit doar la crearea indexului pentru a rezerva spatiu pentru intrari index ce vor fi inserate in acelasi bloc index. Intrarile index nu pot fi actualizate. Atunci cand o coloana este actualizata, aceasta implica o stergere logica a intrarii index, urmata de un insert a valorii cheii actualizate.

Crearea indecsilor bitmap

CREATE BITMAP INDEX [schema.] nume_index

ON [schema.] nume_tabel (

Nume_coloana [ASC].[DESC] [,.] )

[NO[LOGGING]]

[TABLESPACE] nume_spatiu_tabel

[NOSORT]

[clauza stocare]

[clauza utilizare spatiu]

Clauza stocare:

STORAGE (

[INITIAL integer [K | M]]

[NEXT integer [K | M]]

[PCTINCREASE integer ]

[MINEXTENTS integer ]

[MAXEXTENTS integer | UNLIMITED]

[BUFFER POOL [KEEP | RECYCLE | DEFAULT]] )

Clauza utilizare spatiu:

[PCTFREE integer]

[INITRANS integer]

[MAXTRANS integer]

Parametrul CREATE_BITMAP_AREA_SIZE determina cantitatea de memorie ce va fi folosita pentru stocarea segmentelor bitmap in memorie. Valoarea implicita este 8MB. O valoare mare poate duce la crearea rapida a indexului. Daca raportul de cardinalitate este foarte mic, acest parametru poate fi setat la o valoare mai mica. Regula este de a aloca mai multa memorie pentru rapoarte de cardinalitate mari.

Schimbarea parametrilor de stocare pentru indecsi

Unii dintre parametrii de stocare si de utilizare a blocului pot fi modificati utilizand comanda ALTER INDEX.

Sintaxa:

ALTER INDEX [schema.]index

[ clauza stocare ]

[ INITRANS integer ]

[ MAXTRANS integer ]

Implicatiile schimbarii parametrilor de stocare pentru un index sunt aceleasi ca si pentru tabele. Cel mai des sunt folosite aceste schimbari pentru a mari parametrul MAXEXTENTS sau a parametrilor INITRANS si MAXTRANS, pentru a asigura un inalt nivel de acces concurent la disc (blocuri).

Alocarea si dealocare indecsilor

Sintaxa:

ALTER INDEX [schema.]index

Alocarea manuala si dealocarea spatiului pentru un index se face dupa aceleasi reguli ca si la tabele. Spatiul indexului este dealocat atunci cand tabelul corespunzator este trunchiat, deaorece trunchierea la nivel de tabel se regaseste automat si la nivel de index.

Reconstruirea indecsilor

Sintaxa:

ALTER INDEX [schema.]index

REBUILD

[TABLESPACE nume_spatiu_tabel ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ clauza stocare ]

[ LOGGING | NOLOGGING ]

[ REVERSE | NOREVERSE ]

Reconstruirea indecsilor prezinta urmatoarele caracteristici:

Un index nou este reconstruit utilizand indexul existent ca sursa de date.

Nu sunt necesare operatii de sortare, consumatoare de resurse, atunci cand un index este reconstruit pe structura unui index existent.

Vechii indecsi sunt stersi dupa ce noul index este construit. In timpul reconstruirii noului index este necesara o cantitate de spatiu care sa sustina ambii indecsi in spatiile tabele corespunzatoare.

Indexul rezultat nu va contine intrari sterse. Astfel, acest nou index va utiliza spatiul mai eficient.

Interogarile vor continua sa utilizeze vechiul index atata timp cat noul index este in proces de reconstruire.

Reconstruirea indecsilor este poate fi necesara in urmatoarele situatii:

Un index existent necesita mutarea in alt spatiu-tabel. Aceasta poate fi necesara daca indexul pupoleaza acelasi spatiu-tabel ca si tabelul referit sau daca obiectele trebuiesc redistribuite peste discuri.

Un index poate contine un numar mare de intrari sterse. Aceasta este o problema comuna indecsilor corespunzatori unor tabele asupra carora se efectueaza operatii DML intensive.

Tabelul corespunzator indexului este mutat intr-un alt spatiu-tabel utilizand comanda:

ALTER TABLE nume_tabel MOVE TABLESPACE nume_spatiu_tabel;

Reconstruirea indecsilor online

Indecsii pot fi reconstruiti online cu un blocaj minimal la nivel de tabel, existand insa si unele restrictii. Construirea sau reconstruirea unui index poate fi o operatie consumatoare de timp si resurse, mai ales daca tabelul este de mari dimensiuni. Inainte de aparitia Oracle 8I, crearea si reconstruirea indecsilor necesita blocarea tabelului si impiedica operatiile DML concurente. Oracle 9I ofera o metoda de creare sau recreare a unui index, permitand in acelasi timp operatii DML, dar recomandand astfel de operatii sa nu fie de amploare foarte mare.

Restrictii:

Nu se poate reconstrui un index pe un tabel temporar.

Nu se poate reconstrui un intreg index partitionat. Se poate reconstrui insa fiecare partitie sau subpartitie in parte.

Nu se poate dealoca spatiul nefolosit

Nu se poate schimba valoarea parametrului PCTFREE pentru index ca intreg.

Compactarea indecsilor

Atunci cand se intalnesc fragmentari ale indexului, acesta se poate reconstrui sau compacta. Inainte de a realiza oricare dintre aceste operatii trebuie luat in considerare care sunt costurile si beneficiile fiecarei dintre acestea, decizia trebuind luata in functie de situatia de moment. Compactarea unui index consta intr-o reconstruire a blocurilor facuta online.

Sintaxa:

ALTER INDEX [schema.]index COALESCE;

Verificarea indecsilor si a validitatii acestora

Analizarea unui index se face pentru

Verificarea tuturor blocurilor indexului pentru depistarea celor corupte. Aceasta operatie nu inseamna ca va fi verificatacorespondenta intre intrarile index si datele din tabelul corespunzator.

Popularea vederii dinamice INDEX_STATS cu informatii despre index.

Sintaxa:

ANALYZE INDEX [schema.] index VALIDATE STRUCTURE

Droparea indecsilor

Un index poate fi sters daca:

Indexul nu mai este folosit de catre aplicatie.

Se urmareste o populare intensiva a tabelului corespunzator. In acest caz se recomanda stergerea indexului, popularea tabelei, si apoi recrearea indexului, deoarece astfel incarcarea va fi mai rapida (nu mai este necesara actualizarea intrarilor indexului la fiecare actualizare a unei coloane din tabel), iar noul index creat va fi mai eficient decat cel vechi actualizat.

Indexul este folosit doar temporar si nu necesita intretinerea permanenta, in special daca este bazat pe tabele foarte dinamice. Acest comportament este specific mediilor OLTP, unde la sfarsitul unei stagiuni este necesara generarea unor rapoarte necesare unor anumite tipuri de analize.

Un index este marcat ca fiind INVALID in urma unui esec al instantei ce are loc in timpul unei operatii de incarcare.

Indexul este creat.

Indecsii necesari pentru mentinerea unor constrangeri nu pot fi dropati, deoarece constrangerea asociata trebuie mai intai dezactivata si apoi stearsa.

Identificarea indecsilor neutilizati

Sintaxa:

ALTER INDEX [schema.]index [NO]MONITORING USAGE

Incepand cu Oracle 9i statisticile despre utilizarea indecsilor pot fi colectate si vizualizate in vederea V$OBJECT_USAGE. Aceste informatii astfel colectate indica daca un index a fost folosit vreodata, si prin urmare poate fi sters. In plus, stergerea indecsilor nefolositi duce la imbunatatirea performantelor DML pe tabelele corespunzatoare, deoarece nu sunt alocate resurse suplimentare pentru actualizarea unor indecsi nefolositori. De fiecare data cand este invocata clauza MONITORING USAGE, vederea V$OBJECT_USAGE va fi resetata, informatiile anterioare fiind sterse sau resetate pentru a permite inregistrarea de noi informatii pentru indexul specificat.

Coloanele vederii dinamice V$OBJECT_USAGE:

INDEX_NAME: numele indexului.

TABLE_NAME: tabelul corespunzator.

MONITORING: indica starea: ON sau OFF.

USED: indica daca indexul a fost fo losit in intervalul monitorizat: YES sau NO

START_MONITORING: momentul inceperii monitorizarii.

END_MONITORING: momentul terminarii monitorizarii.

Obtinerea informatiilor referitoare la indecsi

Informatii importante referitoare la indecsi se pot obtine interogand vederile:

DBA_INDEXES: informatii despre indecsi.

DBA_IND_COLUMNS: informatii despre coloanele indecsilor.

V$OBJECT_USAGE: informatii despre utilizarea indecsilor.