Indecsii si secventele sunt doua obiecte ale schemei unei baze de date care faciliteaza accesarea rapida si univoca a datelor specificate.
Serverul Oracle utilizeaza identificatorul rowid pentru regasirea liniilor în structura fizica a bazei de date. Un index este o structura a bazei de date care este utilizata de server pentru regasirea rapida a unei linii în-tr-un tabel. Indexul este compus dintr-o valoare cheie (coloana unei linii) si din identificatorul rowid.
O secventa este o lista de numere secventiale care este generata de serverul Oracle. Sistemul Oracle poseda un generator de numere secventiale care este utilizat automat pentru generarea cheilor primare pentru datele dumneavoastra.
În programul Oracle, indecsii sunt folositi pentru îmbunatatirea performantelor interogarilor dumneavoastra. Indecsii furnizeaza o cale de acces mai rapida la datele tabelului. Folositi corespunzator, indecsii reprezinta instrumentul princi 16116o144q pal de reducere a operatiilor de I/O cu discul.
Pentru a avea o imagine privind utilizarea practica a indecsilor, gânditi-va la domeniile curente de utilizare a lor, cum ar fi aceasta carte. Ea poseda un index care va ajuta sa gasiti rapid informatiile pe care le cautati. Indexul va furnizeaza locatia exacta a informatiilor prin intermediul unui numar de pagina, în lipsa unui asemenea index, ati fi probabil nevoit sa parcurgeti cartea pagina cu pagina pentru a gasi ceea ce cautati, în mod similar, indexarea unei coloane importante va accelera raspunsul serverului Oracle la interogari.
Serverul Oracle genereaza numere secventiale unice care pot fi folosite drept chei primare. Acest lucru este benefic deoarece numerele secventiale sunt generate automat si pot fi folosite pentru coordonarea cheilor din mai multe tabele.
În lipsa secventelor Oracle, numerele secventiale ar trebui sa fie generate în aplicatia dumneavoastra. O cheie primara poate fi creata prin selectarea celei mai recent generate valori secventiale, în plus, crearea cheilor primare secventiale conduce automat la blocarea tabelului care contine numerele secventiale, ceea ce face ca ceilalti utilizatori ai bazei de date sa fie nevoiti sa-si astepte rândul pentru a obtine urmatoarea valoare a cheii primare. Secventele Oracle elimina aceasta blocare si îmbunatatesc concurenta aplicatiei dumneavoastra.
Pot fi creati indecsi pentru una sau mai multe coloane ale unui tabel. Odata creat, un index este actualizat în permanenta de serverul Oracle. Modificarile datelor din tabel sunt automat încorporate în toti indecsii relevanti, procesul fiind complet transparent utilizatorului.
Indecsii sunt independenti din punct de vedere fizic si logic de datele tabelului. Ei pot fi distrusi si creati oricând fara ca tabelele sau ceilalti indecsi sa fie afectate. Daca este distrus uri index, toate aplicatiile continua sa functioneze; cu toate acestea, accesul la datele anterior indexate poate deveni mai lent.
Puteti crea un numar nelimitat de indecsi pentru un tabel. Pentru a stabili care coloane trebuie indexate, trebuie sa aveti in vedere cerintele aplicatiei si criteriile de performanta. Cu cât exista mai multi indecsi pentru un tabel, cu atât mai mare este volumul datelor procesate la fiecare modificare a tabelului. In mod special, atunci când sunt inserate sau eliminate linii, trebuie actualizati toti indecsii tabelului, în plus, atunci când este actualizata o coloana, trebuie actualizati toti indecsii asociati coloanei respective.
Este recomandabil sa creati un index pentru o coloana a unui tabel dupa ce tabelul a fost populat cu date. încarcarea datelor într-un tabel este mai eficienta în lipsa indecsilor. Singura exceptie de la aceasta regula o constituie încarcarea datelor într-un grup - în acest caz, indexul trebuie creat înaintea încarcarii datelor.
Atunci când creati un index pentru un tabel, trebuie sa aveti în vedere urmatoarele aspecte:
Indexati o coloana numai daca cititi frecvent mai putin de 15% din liniile unui tabel de dimensiuni mari. Acest procent variaza în functie de viteza de scanare a tabelului si de gradul de grupare a datele în raport cu cheia de indexare. In general, cu cât este mai mare viteza de scanare, cu atât procentul poate fi mai mic. Cu cât datele sunt mai grupate, cu atât procentul va fi mai mare.
Coloanele indexate sunt utilizate în reuniuni pentru a îmbunatati performantele reuniunilor de tabele multiple.
Nu indexati tabele mici cu putine linii.
Cheile primare si cele unice au automat indecsi, însa este recomandabil sa creati indecsi si pentru cheile externe.
În general, analizând datele din coloanele tabelului, puteti stabili daca o anumita coloana trebuie indexata sau nu. în cazul în care datele dintr-o coloana contin multe nuluri pe care le cautati frecvent, nu creati un index pentru aceasta coloana. Similar, atunci când coloana contine date foarte asemanatoare, nu indexati coloana. In schimb, în cazul în care coloana contine în majoritate valori unice sau un domeniu larg de valori, ea se preteaza la crearea unui index.
Un tabel poate avea un numar aproape nelimitat de indecsi. Exista însa dezavantajul ca, pe masura ce se mareste numarul indecsilor unui tabel, creste si numarul de operatiuni necesare în momentul modificarii tabelului. De exemplu, atunci când sunt inserate linii într-un asemenea tabel, toti indecsii trebuie actualizati, ceea ce duce la scaderea performantelor serverului.
Trebuie deci gasita o solutie de compromis între viteza de acces a interogarilor la datele unui tabel si viteza operatiunilor de inserare sau de actualizare relative la tabelul respectiv. Daca predomina net operatiunile de citire asupra unui tabel, este utila existenta mai multor indecsi, însa daca tabelul este actualizat masiv, este de preferat un numar mai mic de indecsi.
Exemplul urmator creeaza un index unic pentru coloana telefon din tabelul ANG:
create unique index i_ang_tel_uk on ang(telefon);
Puteti de asemenea sa creati un index compus pentru mai multe coloane. Exemplul urmator creeaza un index compus pentru coloanele oras si cod din tabelul ANG:
create index i_ang_oras_cod on ang (oras, cod);
Într-un index compus, veti plasa coloana cea mai selectiva pe prima pozitie (oras în exemplul anterior). Puteti avea maxim 16 coloane într-un index compus; aceste coloane pot sa apara în orice ordine si nu este necesar sa fie adiacente în tabel.
Pentru a crea un index, trebuie sa fiti proprietarul tabelului respectiv sau sa posedati privilegiul de sistem index object pentru el. In plus, schema care contine rndexul trebuie sa aiba alocata o cota pentru spatiul-tabel care urmeaza sa contina indexul sau sa posede privilegiul de sistem unlimited tablespace.
Indecsii pot sa fie unici sau ne-unici. Indecsii unici garanteaza faptul ca nu va exista'în tabel nici o pereche de linii care sa aiba valori identice în coloanele care definesc indexul. Indecsii jie-unici nu impun restrictii în legatura cu valorile din coloanele care îi definesc, întrucât unicitatea este un concept pur logic, trebuie sa faca parte din definitia coloanei. Programul Oracle impune restrictii de unicitate prin crearea automata a unui index unic pentru o cheie unica. De asemenea, programul Oracle creeaza un index unic pentru cheia (coloana) primara.
Atunci când creati un index, aveti posibilitatea sa specificati valori pentru parametrii care urmeaza. Parametrilor pentru care nu se specifica valori în instructiunea create index li se vor atribui valorile prestabilite.
tablespace - Indecsii pot fi creati în orice spatiu-tabel; nu este necesar sa fie creati în acelasi spatiu-tabel ca si tabelul pe care îl indexeaza. Utilizarea unor spatii-tabel diferite pentru tabel si indecsii acestuia îmbunatateste performantele datorita reducerii timpilor de monopolizare a discului. Utilizarea aceluiasi spatiu-tabel este mai convenabila în ceea ce priveste întretinerea celor doua obiecte. De exemplu, copiile de siguranta sunt mai usor de efectuat atunci când cele doua obiecte se gasesc în acelasi spatiu-tabel.
Cluster - Specifica numele cheii grupului care este indexata (în cazul indexarii unui grup). Cheile grupurilor trebuie indexate pentru ca tabelele asociate sa poata fi accesate.
Pctfree - Atunci când este creat un index pentru un tabel, blocurile de date ale indexului sunt completate cu valorile existente în tabel pâna la limita pctfree. Zona rezervata de parametrul pctfree nu va fi folosita pentru indecsi. Daca în blocul index curent nu mai exista spatiu disponibil, valoarea indexata va fi plasata într-un nou bloc index, în consecinta, daca aveti intentia sa inserati multe Unii într-un tabel indexat, valoarea parametrului pctfree trebuie sa fie mare pentru a putea stoca noile valori ale indexului. Daca dimensiunea tabelului indexat este relativ constanta, este recomandabila stabilirea unei valori mai mici pentru parametrul pctfree astfel încât sa fie necesare mai putine blocuri pentru stocarea valorilor indexului.
Initrans - Acest parametru defineste spatiul ce va fi alocat initial pentru o tranzactie în blocurile de date ale unui segment indexat.
Maxtrans - Acest parametru defineste spatiul maxim care poate fi alocat pentru o tranzactie în blocurile de date ale unui segment indexat.
Storage - Stabileste explicit parametrii de stocare. Este preferabil sa aveti mai putine extinderi mari decât mai multe extinderi mici.
Printre motivele pentru care ar putea fi necesar sa distrugeti un index se numara urmatoarele:
Indexul nu mai este necesar
Aplicatiile nu utilizeaza interogari care sa foloseasca indexul respectiv
Indexul nu îmbunatateste performantele, deci puteti elimina procesarile
suplimentare prin distrugerea indexului
Indexul a devenit prea fragmentat, deci trebuie distrus înainte de a-l reconstrui
Exemplul urmator distruge indexul i_ang_oras_cod:
drop index i_ang_oras_cod;
Atunci când un index este distrus, toate extinderile segmentulului index sunt returnate spatiului-tabel care contine indexul si devin disponibile pentru alte obiecte din spatiul-tabel. Indecsii sunt distrusi automat atunci când este distrus tabelul asociat.
Pentru a distruge un index, acesta trebuie sa se gaseasca în schema dumneavoastra sau trebuie sa posedati privilegiul de sistem drop any index.
Atunci când în baza dumneavoastra de date sunt create tabele grupate, înainte de a trece la inserarea datelor, trebuie sa creati un index de grup. Gruparea tabelelor nu afecteaza crearea de indecsi suplimentari pentru tabelele individuale ale grupului; acestia pot fi creati si distrusi ca de obicei.
Instructiunea urmatoare creeaza un index de grup pentru grupul dept_divizie
create index dept divizie_index
on cluster dept_dTvizie
initrans 2
maxtrans 255
tablespace user_01
storage(
initial 50k
next 50k
minextents 5
maxextents 25
pctincrease 20)
pctfree 20;
Pentru a crea un index de grup, trebuie sa fie adevarata cel putin una dintre urmatoarele afirmatii:
Grupul trebuie sa se gaseasca în schema dumneavoastra si sa posedati privilegiul de sistem create index
Posedati privilegiul de sistem create any index în ambele cazuri, trebuie sa posedati suficient spatiu în propriul spatiu-tabel sau sa posedati privilegiul de sistem unlimited tablespace.
Pentru a modifica un index de grup, folositi comanda alter index. Instructiunea urmatoare mareste valoarea parametrilor de stocare pentru indexul de grup dept_divizie_index:
alter index dept_divizie_index storage (pctincrease 25)
Distrugerea unui index de grup nu afecteaza grupul sau tabelele grupului, în schimb, în lipsa indexului de grup, tabelele grupate nu mai pot fi utilizate. Prin urmare, nu veti distruge un index de grup decât daca intentionati sa îl reconstruiti sau sa distrugeti întregul grup.
Secventele sunt create pentru a simplifica munca de programare prin crearea unei liste secventiale de numere pe care programatorii le pot utiliza pentru a genera valorile cheilor primare. Numerele secventiale sunt întregi de pâna la 38 de cifre.
Pentru a crea o secventa în schema dumneavoastra, folositi comanda create sequence
create sequence s_marca_ang
increment by l
start with l
nomaxvalue
nocycle
cache 15;
Exemplul precedent creeaza secventa numita s_marca_ang. Aceasta lista de numere secventiale începe cu valoarea l si este incrementata cu l pentru fiecare numar care urmeaza. Nu exista valoare maxima pentru aceasta secventa, deci secventa nu va reîncepe ciclic de la valoarea initiala, în sfârsit, serverul Oracle genereaza în avans în memorie urmatoarele 15 valori din secventa pentru îmbunatatirea performantelor.
Atunci când este folosit parametrul cache, secventele specificate de aceasta comanda sunt create direct în memoria cache. Pe masura ce sunt utilizate numerele din secventa stocate în memoria cache, va fi generat în memorie urmatorul set de numere din secventa. Specificarea unei valori mari pentru parametrul cache va permite sa accesati mai multe numere din secventa cu mai putine operatii de I/O cu discul.
Puteti verifica existenta secventelor prin selectarea tabelului user_sequences din dictionarul de date. Urmatorul exemplu afiseaza numele si descrierea tuturor secventelor existente în schema utilizatorului:
select sequence_name, min_value, max_value
increment_by, last_number
from user_sequences:
Vor fi afisate urmatoarele informatii:
Sequence_name min_value max_value increment by last_number
S_MARCA_ANG 1 999999 1 389
S_NR_DEPT 1 999999 1 24
Puteti modifica oricare dintre parametrii specificati în comanda create sequence prin lansarea comenzii alter sequence:
alter sequence s_marca_ang maxvalue 50000 cache 20 cycle;
Acest exemplu modifica parametrul cache la valoarea 20 si permite generatorului de secvente sa o ia de la început atunci când este atinsa valoarea maxvalue. Ori de câte ori modificati o secventa, modificarile intra în vigoare numai pentru viitoarele numere din secventa.
Dupa definirea unei secvente, aceasta poate fi utilizata si incrementata de mai multi utilizatori. Programul' Oracle nu asteapta încheierea unei tranzactii care acceseaza secventa de numere pentru a permite utilizarea ei de catre un alt utilizator.
O secventa poate fi referita prin intermediul unei comenzi SQL cu ajutorul pseudo-coloanelor nextval si currval. Urmatoarea valoare din secventa este generata cu pseudo-coloana nextval, în timp ce valoarea curenta a secventei este repetata cu pseudo-coloana currval.
În exemplul urmator, secventa s_marca_ang este utilizata pentru a insera o noua linie în tabelul ANG:
insert into ang (marca_ang, prenume, nume)
values (s_marca_ang.nextval, 'thomas','smith');
Exemplul urmator ilustreaza utilizarea pseudo-coloanei nextval într-o comanda
update:
update dept
set nr_dept = s_nr_dept.nextval where nr_dept =19;
La generarea unui nou numar din secventa, acesta este disponibil numai sesiunii care 1-a creat. Toti ceilalti utilizatori care utilizeaza aceeasi secventa vor obtine o noua si unica vafoare din secventa pentru propria sesiune.
Pentru a utiliza valoarea curenta a secventei în sesiunea dumneavoastra curenta, folositi pseudo_coloana currval. Currval este valida numai daca nextval a fost utilizata în sesiunea curenta. Urmatorul exemplu foloseste aceeasi valoare din secventa ca si exemplul anterior:
insert into table dept (nr_dept, nr_ang)
values (s_nr_dept.nextval,s_marca_ang.currval);
Pseudo-coloanele nextval si currval pot fi utilizate în urmatoarele situatii:
Împreuna cu clauza value într-o instructiune insert
În lista de selectare a instructiunii select
Împreuna cu clauza set într-o comanda update
Pseudo-coloanele nu pot fi utilizate în urmatoarele situatii:
O instructiune select care are clauza distinct
O instructiune select care are una dintre clauzele order by sau group by
O instructiune select utilizata într-un operator union, minus sau intersect
O subinterogare, o interogare a unei vederi sau o interogare a unui instantaneu
Pentru a utiliza o secventa, ea trebuie sa existe în schema dumneavoastra sau trebuie sa posedati privilegiul de sistem select ob j ect pentru o alta secventa.
Pentru a distruge o secventa, folositi comanda SQL drop sequence. De exemplu, rândul urmator distruge secventa s_marca_ang:
drop sequence s_marca_ang
Puteti distruge orice secventa din schema dumneavoastra; pentru secventele din alte scheme trebuie sa posedati privilegiul de sistem drop any sequences.
In acest capitol, ati facut cunostinta cu doua obiecte ale bazei de date: indexul si secventa. Un index foloseste' identificatorul rowid pentru a gasi linia fizica solicitata de o interogare. O secventa este o lista secventiala de numere unice care sunt folosite pentru generarea automata a valorilor cheilor primare.
Structurile index si secventa sunt folosite pentru simplificarea muncii de programare si pentru îmbunatatirea performantelor bazei de date. Un index furnizeaza calea cea mai rapida la locatia fizica a datelor. O secventa este generata pentru a furniza o valoare a cheii primare pentru fiecare linie.
În acest capitol, au fost ilustrate etapele necesare pentru crearea unui index pentru un tabel în vederea îmbunatatirii performantelor serverului si au fost prezentate criteriile pentru stabilirea oportunitatii crearii indecsilor pentru un tabel.
De asemenea, au fost ilustrate etapele necesare pentru generarea si utilizarea secventelor. Au fost prezentate exemple în legatura cu utilizarea secventelor pentru pastrarea concordantei între mai multe tabele.
|
