12. Alte obiecte din baza de date

O bază de date conţine şi alte obiecte decât cele cu care v-aţi familiarizat în celelalte capitole. Scopul acestui capitol este de a va explica rolul acestor obiecte care nu au fost încă descrise pe parcursul lecţiilor anterioare.

Obiectele care pot exista într-o bază de date sunt: tabele, vederi, secvenţe, indecşi şi sinonime. Pentru că tabelele şi vederile au fost pe larg descrise în capitolele anterioare ne vom ocupa acum de secvenţe, indecşi şi sinonime.

Secventele

De ce folosim secventele?

Aplicaţiile au în general nevoie în cadrul tabelelor de a conţine unele numere de identificare unice per tabelă cum este şi cazul cheilor primare. Acestea pot fi calculate de către aplicaţie direct folosind anumiţi algoritmi în funcţie de înregistrările deja existente şi apoi pot fi introduse în baza de date odată cu celelalte date. În acest caz însă apar mari probleme de sincronizare astfel încât dacă mai multe instanţe a aceleaşi aplicaţii accesează în acelaşi timp aproximativ datele deja existente în baza de date rezultatul cheilor va fi acelaşi. De asemenea sunt necesare de la una la mai multe cereri la baza pentru a determina care sunt valorile deja existente astfel încât aplicaţia consuma mai mult timp procesor pentru fiecare inserare şi devine mult mai lentă.

O soluţie la aceste probleme sunt secvenţele. Secvenţele sunt nişte obiecte care pot fi împărţite între mai mulţi utilizatori şi care pot să genereze numere întregi unice.

Cea mai răspândita utilizare a lor este pentru cheile unice ale tabelelor. Secvenţele sunt incrementate sau decrementate cu ajutorul unei rutine interne a programului de bază de date (Oracle).

Secvenţele nu sunt o proprietate a tabelelor astfel încât ele pot si folosite de către mai multe tabele în acelaşi timp. În practica uzuală pentru fiecare tabelă în parte se atribuie o secvenţă separată dar ele mai pot fi utilizate astfel încât mai multe tabele folosesc aceeaşi secvenţă sau o tabelă poate avea mai multe secvenţe.

Crearea secvenţelor.

O secvenţă poate fi creată utilizând următoarea sintaxa SQL:

CREATE SEQUENCE nume_secventa
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE  n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE |NOCACHE}];

Vom incerca sa facem o trecere in revista a parametrilor care sunt utilizati in aceasta cerere SQL speciala:

Exemplul 1 - Crearea unei secvente:

Vom continua cu un scurt exemplu de creare de secventa in cadrul tabelei de departamente pentru a fi folosita de cheia primara.

Sa ne asiguram ca aceasta secventa nu exista deja:

Acum sa o cream avand optiunile urmatoare: incepe la 120, creste cu 10 unitati, valoare maxima este 9999, fara ciclu si fara cache.

Pseudocoloanele NEXTVAL si CURRVAL.

Acestea se folosesc pentru a putea accesa valorile unei secvente. Denumirile lor sunt sugestive.

NEXTVAL este o pseudocoloana care este utilizata pentru a putea extrage valoarea urmatoare dintr-o secventa. Aceasta se foloseste intr-un select impreuna cu numele secventei din care se doreste extragerea valorii. Daca sintaxa cererii SQL contine formatul nume_secventa.NEXTVAL atunci secventa este incrementata, valoarea curenta din NEXTVAL este plasata in CURRVAL si NEXTVAL va contine urmatorul numar din secventa.

CURRVAL este folosita pentur a putea extrage din cadrul secventei ultima valoare care a fost generata. Precum am precizat anterior trebuie utilizata pseudocoloana NEXTVAL pentru geerarea valorii care va fi continuta in CURRVAL. CURRVAL este extras in acelasi mod in care se extrage si valoarea NEXTVAL printr-o cerere SQL SELECT impreuna cu numele secventei pentru care se doreste aflarea valorii. Cand cererea se face cu ajutorul sintaxei nume_secventa.CURRVAL valoarea returnata va fi valoarea care a fost folosita ultima data in cadrul aceluiasi proces.

Exista niste constrangeri la folosirea pseudoloanelor CURRVAL si NEXTVAL:

• Cand cererea SELECT se refera la obiecte de tip vedere
• Cand in cererea SELECT se utilizeaza cuvantul cheie DISTINCT
• Cand cererea SELECT contine GROUP BY, HAVING sau ORDER BY
• Cand instructiunea SELECT este folosita intr-o sub-cerere.
• Cand este utilizata expresia DEFAULT impreuna cu CREATE TABLE sau cu ALTER TABLE

Exemplu 2 - Utilizarea unei secvente:

Pentru inserararea in baza de date in tabela de departamente vom folosi in cererea noastra SQL secventa pe care am creat-o mai inainte. Pentru inserare se va folosi cererea urmatoare:

Pentru a putea sa vedem valoarea inserata se poate folosi urmatoarea cerere:

Folosirea secventelor cu incarcare in prealabil in memorie are avantajul ca viteza de raspuns creste din partea bazei de date. Din pacate apar cu ocazia aceasta si unele probleme. In momentul unei erori fizice a masinii pe care ruleaza baza de date informatiile care sunt pastrate in memorie sunt pierdute si apar asa numitele gauri in secventa. Astfel de gauri in secventa mai pot aparea in cazul in care se sterg inregistrari din baza de date, in cazul in care o cerere a esuat si sistemul trebuie sa se intoarca la valorile precedente (actiune de Rollback) sau in momentul in care o secventa este utilizata pentru mai multe tabele.

Modificarea secventelor

O secventa poate fii modificata folosing urmatorul query generic:

ALTER SEQUENCE nume_secventa
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE  n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE |NOCACHE}];

unde nume_secventa este numele secventei care se doreste a fi modificata. Aceasta metoda este utila de exemplu pentru cazul in care secventa atinge valoarea maxima si in cazul dorintei de a o folosi in continuare ea intoarce o eroare.

Exista unele restrictii in cazul alterarii unei secvente deja existe. Acestea sunt:

• Userul care executa actiunea de alterare trebuie sa aiba drepturile necesare sau sa fie proprietarul secventei.
• Numai numerele care vor fi generate in viitor vor fi afectate.
• Secventa va trebui distrusa si recreata pentru ca valoarea de inceput sa poata fi modificata

Anumite validari logice de genul ca valoarea maxima nou setata trebuie sa nu fie mai mica decat valoarea exista deja trebuie indeplinite.

Stergerea secventelor

Stergerea unei secvente se poate efectua cu ajutorul urmatoarei cereri SQL:

DROP SEQUENCE nume_secventa

unde nume_secventa este numele secventei care se doreste distrusa.

Indecsii

Indecsii sunt niste obiecte de tip schema care imbunatatesc timpul de cautare si access la inregistrarile din baza de date. Indecsii sunt creati in mod explicit sau automat in anumite conditii.

De ce folosim indecsii?

Indecsii ofera o metode de access directa catre inregistrarile care se doresc extrase. Scopul lor este de a diminua operatile de intrare/iesire catre disc prin folosirea unei metode de indexe a cailor catre acele date. Indecsii sunt utilizati si mentinuti de catre serverul Oracle in mod transparent pentru utilizator care nu implica nici o alta actiune ulterioara.

Modul in care indecsii sunt conceputi ii fac independent fizic si logic de baza de date ceea ce se exprima prin faptul ca ei pot sa fie creati sau distrusi in orice moment fara a afecta structura bazei de date sau asupra celorlalti indecsi. De asemenea din partea de management transparent face parte si faptul ca daca o baza de date este distrusa atunci si indecsii care au fost creati pentru ea sunt de asemenea distrusi.

Cum se creaza indecsii?

Indecsii sunt creati in doua feluri:

- Automat: In momentul in care se creaza o cheie primara asupra tabelelor, in momentul in care se creaza o restrictie ca inregistrarile din baza de date sa fie unice si in alte situatii asemanatoare.

- Manual: Se pot defini indecsi manuali care nu trebuie sa contina o restrictie cum este cazul indecsilor unici.

Indecsii sunt creati folosind urmatoarea cerere SQL la baza de date:

CREATE INDEX numele_index
ON numele_tabelei (camp1[, camp2].... )

unde numele_index este numele pe care il are indexul, numele_tabelei este numele tabelei din baza de date pentru a carei campuri indexul va fi creat si camp1, camp2... sunt numele campurilor din tabela pentru care se creaza acest index. Un index poate fi creat pentru una sau mai multe coloane din aceeasi tabela.

Exemplu 1 - crearea unui index:

O sa cream un index pentru departamente care ne va ajuta sa extragem foarte repede o inregistrare in functie de numele departamentului.

Cand se creaza indecsii?

Exista mai multe situatii care necesita crearea indecsilor in baza de date. Un index este creat daca:

• o singura coloana contine o varietate foarte mare de valori si un numar mare de valori.
• campul din baza de date contine multe inregistrari de tip null
• campurile sunt folosite foarte frecvent in conditile de selectare a inregistrarilor sau in cazuri de JOIN
• tabela pe care se creaza indexul este foarte mare si cererile in mod frecvent nu extrag o cantitate mare de date (2-4%)
• in momentul in care anumite chei sunt create in tabela ca si cheile unice si atunci Oracle genereaza automat un index unic.

Cand nu se creaza indecsii si de ce?

In general nu tot timpul este necesar si bine sa fie creati indexi in baza de date.O creare excesiva de indecsi in baza de date are si multe neajunsuri. In momentul in care sunt adaugati indecsi baza de date face o repunere la zi a lor pentru fiecare operatie care este executata. In momentul in care sunt creati prea multi indecsi operatile de extragere din baza de date isi amelioreaza viteza doar cu un raport mic dar pentru fiecare operatiune de inserare sau stergere timpul necesar creste simtitor precum si resursele folosite. Deci se pune problema cand nu sunt necesari incsii:

• Cand tabela pe care se doreste adaugarea este mica.
• Cand coloanele pe care se adauga indecsii nu sunt folosite frecvent pentru executarea de extrageri de inregistrari
• Cand cererile de tip select in general extratc un numar mare de inregistrari (mai mare de 2-4%)
• Cand operatiuni de UPDATE sunt foarte des utilizate.
• Cand coloanele indexate sunt referentiate ca parte integranta a unor expresii.

Confirmarea indecsilor

O lista completa de indecsi poate fi extrasa din vederea USER_INDEXES care contine numele indexului si daca acesta are o restrictie de unicitate. Urmatorul query poate fi folosit pentru tabela 'departments'

Indecsii bazati pe expresii

Indecsi mai pot fi adaugati nu numai pe campuri simple din baza de date. Ei pot sa fie folositi de asemenea pe anumite expresii. Aceste expresii pot fi construite din campuri din tabela, constante, funtii SQL sau functii definite de catre utilizator. In felul acesta se pot optimiza cererile de extragere care contin conditii complexe bazate pe calcule.

Exemplu 2: - Index bazat pe o functie SQL

Indexul urmator este folosit pentru o tabela de angajati care este foarte mare si in general in aplicatii se folosesc extragerile de o singura inregistrare din baza de date. Daca presupunem ca cea mai frecventa metoda de filtrare a cererii SQL este dupa nume si prenume putem crea un index pe cele doua campuri reunite:

Distrugerea indecsilor

Distrugerea incsilor este realizata utilizand urmatoarea cerere SQL:

DROP INDEX nume_index;

unde nume_index reprezinta numele indexului.

Indecsii nu pot modificati. Din aceasta cauza in momentul in care este necesara modificarea unui index este necesara distrugerea lui prealabila.

Exemplu - Distrugerea indexului creat

Pentru distrugerea indexului pe care l-am creat intr-un exemplu anterior va trebui sa folosim urmatoarea cerere:

Sinonimele

Sinonimele sunt niste aliasuri pe care le putem adauga oricarui obiect din baza de date pentru a simplifica accesul la aceasta resursa. Sinonimele sunt folosite pentru cazul in care exista in diferite scheme din baza de date tabele cu mai multe denumiri, cand denumirile obiectelor sunt foarte lungi si/sau greu de tinut minte de catre programatorul care trebuie sa le foloseasca si in alte cazuri. Principalul avantaj al utilizarii lor este pentru micsorarea timpul necesar din partea utilizatorilor pentru a-si crea cererile SQL. In felul acesta tabelele, vederile, secventele, procedurile sau alte obiecte au un nume alternativ pentru access.

Cum se creaza sinonimele?

Pentru crearea sinonimelor este necesara o cerere SQL care are urmatorul format:

CREATE [PUBLIC] SYNONYM nume_sinonim
FOR nume_obiect;

In acest caz putem distinge urmatorii parametrii:

Daca dorim sa accesam o tabela pe care un alt utilizator a creat-o in prealabil si careia ii dorim asignarea unui sinonim suntem obligati sa introducem inaintea numelui tabelei si numele utilizatorului care a creat tabela, in fata numelui tabelei, despartite de un punct.

Exemplu 1: Crearea unui sinonim

In acest exemplu vom incerca sa cream un sinonimpentru tabela de departamente care are un nume destul de lung.

In felul acesta nu vom mai fi oblicati sa accesam in cererile noastre SQL tabela de departamente prin scrierea numelui tabelei 'department' si este suficienta doar utilizarea denumirii noi create: 'dep'

Exista unele restrictii in folosirea sinonimelor:

• obiectele respective nu pot sa fie continute intr-un pachet.
• numele sinonimului privat trebuie sa fie unic in cadrul grupului de obiecte pentru care acel user este proprietar.

Un sinonim nu poate fi schimbat odata creat. El poate sa fie doar distrus. Distrugerea unui sinonim nu implica si distrugerea obiectului de care aliasul este atasat. Distrugerea unui sinonim este facuta folosind o cerere SQL de tipul urmator:

DROP [PUBLIC] SYNONYM nume_sinonim

unde nume_sinonim designeaza numele sinonimului care sa fie distrus.

Un sinonim de tip public poate sa fie sa fie distrus doar de catre un utilizator administrativ. Sinonimele private trebuie distruse de utilizatorul care le-a creat acestea nefiind disponibile in cadrul listei numelor de obiecte a celorlalti utilizatori.

Concluzile capitolului:

Scopul acestui capitol a fost de a va invata urmatoarele:

• Cum se pot genera numere secventiale utilizand un motor de generare de secvente.
• Acesarea detalilor secventelor, si detailarea informatilor disponibile pentru o secventa din tabela USER_SEQUENCES
• Crearea indecsilor pentru a optimiza viteza de extragere a informatilor din baza de date.
• Cum se pot vedea informatile suplimentare pentru indecsi prin folosirea tabelei USER_INDEXES
• Utilizarea sinonimelor pentru a avea nume alternative pentru diferite obiecte din baza de date.