OBIECTE IN BAZELE DE DATE : SECVENTIALE SI INDEXATE

OBIECTE IN BAZELE DE DATE : SECVENTIALE SI INDEXATE

Acest paragraf descrie cum se poate indexa o tabela pentru a

imbunatati timpul de raspuns in cazul unei interogari.

Crearea si utilizarea accesului secvential este de asemenea discutata

(aceasta permite atribuirea automata de numere de secventa unice in

cadrul unei aplicatii.)

* Generatorul de numere de secventa

In server-ul Oracle versiunea 6, generatorul de numere de secventa

poate fi utilizat pentru a genera automat numere de secventa

pentru rinduri in cadrul tabelelor. De exemplu se poate folosi

generatorul pentru a produce cheii primare unice.

Pentru a genera automat numere de secventa, trebuie sa definiti o

secventa folosind comanda CREATE SEQUENCE. Sintaxa este

urmatoarea:

CREATE SEQUENCE [user.] sequence_name

[ increment by n]

[ start with n]

[ maxvalue n | nomaxvalue ]

[ minvalue n | nominvalue ]

Toti parametrii comenzii sunt optionali si au urmatoarea

semnificatie:

+ user: proprietarul secventei.Implicit este user-ul care a

generat comanda CREATE SEQUENCE

+ sequence_name: numele de secventa care respecta conventiile

SQL de denumire a obiectelor

+ INCREMENT BY: determina intervalul dintre numerele de

secventa generate. Daca valoarea este pozitiva, atunci

numerele de secventa vor creste. Daca valoarea este negativa

atunci acestea vor scade. Se poate folosi orice intreg nenul.

Implicit aceasta valoare este 1

+ START WITH: specifica primul numar de secventa care va fi

generat. Implicit este 1 pentru indexi crescatori si MAXVALUE

pentru indici descrescatori

+ MINVALUE| valoarea minima a secventei ce va fi

generata.Implicit este 1

+ NOMINVALUE pentru indici crescatori si 10E27-1 pentru indici

descrescatori

+ MAXVALUE| valoarea maxima ce va poate fi generata.Implicit

este 1 pentru

+ NOMAXVALUE secvente descrescatoare, si -1 pentru secvente

crescatoare. Orice incercare de a genera numere de secventa

peste aceasta valoare va esua si se va intoarce un cod de

eroare.

Urmatoarea comanda creaza o secventa pentru coloana DEPTNO a

tabelei DEPT.

CREATE SEQUENCE dept_sep

INCREMENT BY 10

START WITH 10

MAXVALUE 10000;

Dupa ce o secventa a fost creata ea poate fi folosita pentru a

genera numere unice de secventa.

* Generarea de numere de secventa cu NEXTVAL

NEXTVAL este utilizata pentru a extrage numere de secventa

succesive dintr-o secventa specificata. Cind se acceseaza NEXTVAL

on nou numar de secventa este generat.

SELECT dept_seq.NEXTVAL

FROM SYS.DUAL ;;

NEXTVAL

10

Daca se reexecuta comanda SQL anterioara valoarea creste cu 10.

SELECT dept_seq.NEXTVAL

FROM SYS.DUAL ;;

NEXTVAL

20

Prima valoare obtinuta este 10 si aceasta se incrementeaza apoi cu

pasul 10.

Nota:

+ intodeauna se prefixeaza NEXTVAL cu numele de secventa

+ daca se refera NEXTVAL de mai multe ori pentru o singura

comanda SQL orice referire va intoarce aceasi valoare.

NEXTVAL este foarte folositoare in comenzi DML. De exemplu cind se

completeaza o tabela se poate folosi o secventa pentru a produce

valori unice pentru coloana cu cheii primare a tabelei. Exemplul

urmator foloseste secventa dept_seq pentru a adauga cheii primare

unice in tabela DEPT.

INSERT INTO DEPT

VALUES

(dept_seq.NEXTVAL, 'ACCOUNTING', `NEW YORK');

1 record created

SQL> SELECT * FROM DEPT;

DEPTNO DNAME    LOC

----- ----- ---- ----- ----- --------

10 ACCOUNTING NEW YORK

Inaintea versiunii 6, numere unice de secventa erau generate la

nivelul aplicatiilor. De exemplu, o modalitate obisnuita de

implementare este de a forta fiecare tranzactie sa blocheze tabela

cu numarul de secventa sa incrementeze secventa si apoi sa

elibereze resursa. In aceasta implementare, numai un singur numar

de secventa poate fi generat la un moment dat. Generatorul de

numere de secventa ORACLE permite generarea simultana a multiple

numere de secventa de catre useri diferiti.

Cind un astfel de numar este generat, secventa este incrementata

indiferent de tranzactiile in desfasurare. Daca doi useri

acceseaza aceeasi secventa in acelasi timp, atunci fiecare user

poate sa obtina eroare deoarece numere de secventa sunt de

asemenea generate de al doilea user. Doi useri nu vor genera

niciodata acelasi numar de secventa utilizind aceiasi secventa.

Numere de secventa pot fi 'sarite' daca un user nu-si termina o

tranzactie sau daca aceasta sfirseste in mod anormal.

* Utilizarea numerelor de secventa cu CURRVAL

Pentru a accesa un numar de secventa care tocmai a fost generat

(numarul de secventa curent ) se foloseste pseudo-coloana CURRVAL.

CURRVAL reprezinta ultima valoare intoarsa unui program. Cind se

adreseaza NEXTVAL pentru o secventa data, numarul de secventa

curent este plasat in CURRVAL. NEXTVAL trebuie folosit pentru a

genera numere de secventa in sesiunea curenta a unui user, inainte

de a putea adresa CURRVAL.

INSERT INTO DEPT_HISTORY

VALUES (dept_seg.CURRVAL, 'ACCOUNTING' , `NEW YORK');

1 record created.

SQL> SELECT * FROM DEPT_HISTORY

DEPTNO DNAME LOC

------------ ----- ----- -------

10 ACCOUNTING NEW YORK

* Reguli de folosire NEXTVAL si CURRVAL

NEXTVAL si CURRVAL pot fi folosite in cadrul:

+ clauza SELECT dintr-o declaratie SELECT (exceptie la afisari)

+ lista VALUES dintr-o declaratie INSERT

+ clauza SET dintr-o declaratie UPDATE

+ in afara SELECT

* Restrictii de folosire NEXTVAL si CURRVAL

NEXTVAL si CURRVAL nu pot fi folosite:

+ intr-o lista SELECT din VIEW

+ impreuna cu cuvintul cheie DISTINCT

+ cu clauzele ORDER BY, GROUP BY, CONNECT BY, sau HAVING

dintr-o declaratie SELECT

+ cu operatorii UNION, INTERSECT, MINUS

Secventele sunt tratate in mod similar cu tabelele si pot fi

modificate sau abandonate. Proprietarul unei secvente poate de

asemenea atribui privilegii si altor useri asupra secventei.

* Modificarea unei Secvente

Se foloseste comanda ALTER SEQUENCE pentru a modifica o secventa

existenta.

AKTER SEQUENCE [user.]sequence_name

[INCREMENT BY n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

De exemplu pentru a seta o noua valoare maxima pentru secventa

dept_deq,

ALTER SEQUENCE dept_seq

maxvalue 100000

Pentru a modifica o secventa este necesar sa fii proprietarul eu

sau sa ai dreptul DBA sau sa fi primit dreptul ALTER pentru

secventa din partea proprietarului.

Nota:

+ Numai secventele viitoare vor fi influientate de catre

comanda ALTER SEQUENCE

+ Sunt facute anumite verificari. De exemplu o noua MAXVALUE nu

poate impune care este mai mica decit numarul de secventa

curent

+ Valoarea START WITH nu poate fi schimbata folosint comanda

ALTER SEQUENCE

+ Secventa trebuie abandonata si recreata pentru a reporni

secventa la un numar diferit.

* Atribuirea drepturilor asupra Secventelor

Proprietarul unei secvente poate atribui altor useri drepturi de

ALTER sau SELECT asupra secventei, si WITH GRANT OPTION ramine

valida.

Detalii in UNIT-ul 17.

* Stergerea unei Secvente

Folositi comanda DROP SEQUENCE pentru a sterge o definitie de

secventa.

Sintaxa este:

DROP SEQUENCE [user.]sequence_namel

Trebuie sa fii proprietarul secventei sau sa ai dreptul de DBA

pentru a o sterge.

Pentru a sterge secventa dept_seq, tastati:

DROP SEQUENCE sept_seq;

* Tiparirea Secventelor

Toate definitiile secventelor sunt memorate intr-o tabela de

secvente. Pentru a vedea secventele la care ai access,

interogheaza baza de date urmarind USER_SEQUENCES sau

ALL_SEQUENCES.

Indexarea

Indexarea in sistemul ORACLE are doua principale scopuri:

1. Pentru a accelera afisarea liniilor cu o cheie particulara.

2. Pentru a forta unicitatea valorilor din coloane, de obicei

valorile cheii principale.

Folosirea indexarii este puternic recomandata pentru a obtine

performante mai bune. Retineti ca ORACLE 7 creiaza automat INDEXI

pentru coloanele care au setate PRIMARY KEY sau UNIQUE.

Proprietarul unei tabele poate crea indexi pentru ea. Orice user

ORACLE care detine dreptul de access INDEX poate creea un index.

Odata creat, ORACLE va folosi index-ul ori de cite ori este

posibil pentru a accelera accesul datelor. Acest lucru se face in

mod automat si deobicei nu este necesara interventia userului care

poate sa nu stie de existenta acestul index.

* Structura unui INDEX ORACLE

ORACLE foloseste arbori B pentru indexare. Timpul de access este

independent de volumul de date indexat. Fiecare index construit de

ORACLE contine un numar de pagini memorate in arbore. Fiecare

pagina contine un numar de cheii. ORACLE gestioneaza aceasta

structura la fel cum sunt inserate sau sterse linii. Valorile NULL

nu sunt memorate in index, si din fericire nu ocupa spatiu.

-------- Aici se afla poza de la pagina 18-9 --------

* Tipuri de INDEX

TYPE DESCRIERE

-------- ----- ------ ----- ----- ----- ----- -----

UNIQUE    asigura ca valorile din coloanele specificate sunt unice

NON UNIQUE asigura obtinerea cit mai repede a rezultatului (implicit)

SINGLE COLUMN    exista numai o coloana in index

CONCATENATED    mai mult de 16 coloane specificate in index.

* Crearea unui Index

Index ORACLE poate fi creat in linia de comanda cu comanda CREATE

INDEX.

CREATE [UNIQUE] INDEX index_name

ON table (column [m])

* Crearea unui INDEX pentru a imbunatati timpul de raspuns

Pentru a creea un index numit I_NAME care va fi folosit pentru a

imbunatatii timpul de interogare , introduceti:

CREATE INDEX I_ENAME

ON EMP (ENAME);

* Crearea unui INDEX pentru a asigura unicitatea

Indecsi unici sunt creati automat ca un rezultat al constantelor

PRIMARY KEY sau UNIQUE in tabela. Oricum , se mai pot creea

folosind comanda CREATE UNIQUE INDEX.

Pentru a evita valori duplicate in coloana EMPNO, introduceti:

CREATE UNIQUE INDEX I_EMPNO

ON EMP (EMPNO);

Index-ul urmator asigura unicitatea valorii introduse in tabela

SHIPMENTS.

Acesta este denumit index CONCATENATED.

CREATE UNIQUE INDEX ORDER

ON SHIPMENTS (S_Num, P_Num);

* Stergerea unui INDEX

Pentru a sterge definitia unui index din tabela de date ,

introduceti:

DROP INDEX indexname;

* Cind este folosit un INDEX ?

Folosirea unui INDEX depinde in parte de optimizatorul ORACLE in

momentul executiei. ORACLE 7 permite ambele metode de optimizare

SQL, functie de un set de reguli sau functie de cost.

Reguli de folosire al unui index

Oracle decide cind este adecvat sa foloseasca indexi. Oracle tine

seama de coloanele indexate si de tipul indexului si decide

functie de urmatoarele reguli:

1. Indexii de pe coloane trebuie adresati in clauza WHERE:

Urmatoarea interogare nu foloseste un index atita timp cit

clauza WHERE nu este prezenta:

SELECT ENAME,JOB,SAL,COMM

FROM EMP;

Urmatoarea indetogare va folosi un index pe coloana ENAME:

SELECT *

FROM EMP

WHERE ENAME = 'JHONES';

2. Un index nu va fi folosit daca coloana referita in clauza

WHERE face parte dintr-o expresie sau functie.

Exemplul urmator nu foloseste index deoarece coloana este

parte a unei functii:

SELECT *

FROM EMP

WHERE UPPER(ENAME)= 'JHONES';

In mod similar daca HIREDATE a fost indexata, aceasta

interogare nu va folosi index deoarece este o expresie:

SELECT *

FROM EMP

WHERE HIREDATE+7 = '01-JAN-84';

* Utilizarea indexarii functie de cost

Modulul de optimizare bazat pe costul executiei decide un plan de

executie pentru o secventa SQL calculind costul cailor alternative

folosind informatii statistice acumulate daca este posibil. De

obicei se va decide cea mai buna cale de utilizare a unui index.

Sugestii pentru INDEXARE

+ O tabela cu mai mult de 200 linii va beneficia din indexare

pentru imbunatatirea performantelor, daca mai putin de 10%

din linii vor fi intoarse in cazul unei interogari.

+ Indexati toate coloanele acolo unde sunt cerute valori unice.

+ Daca doua sau mai multe coloane sunt utilizate in mod

frecvent impreuna in clauza WHERE sau JOIN atunci creeati un

index concatenat. (CONCATENATED INDEX)

+ Evitati mai mult de trei indexi pentru o tabela. In acest caz

va fi o supraincarcare in cazul unei operatii DML. (aceasta

regula s-ar putea sa nu fie valabila pentru tabele la care

SELECT este operatiunea cea mai frecventa, de exemplu tabele

de referinte.)

Indexare si Imbinare

Daca nu exista index in cadrul coloanelor IMBINATE atunci se poate

obtine o 'sortare-imbinata'. Asca inseamna ca fiecare este sortata

separat si apoi listele sortate sunt imbinate . De obicei

coloanele folosite pentru imbinari trebuie sa fie indexate. Daca

numai una din tabele are un index utilizabil, atunci cealalta

tabela este tabela conducatoare.

De exemplu:

SELECT ENAME, DNAME

FROM DEPT, EMP

WHERE EMP.DEPTNO = DEPT.DEPTNO

Daca numai EMP.DEPTNO este indexata , atunci DEPT este tabela

conducatoare. Acest lucru este normal deoarece DEPT intoarce mai

multe linii decit EMP. Daca ambele sunt indexate, Oracle alege

tabela conducatoare cintarind rangurile celor doua sisteme. Daca

rangurile sunt egale atunci Oracle alege tabela care este listata

ultima in clauza FROM.

Asa ca trebuie listate tabele mari cu cel mai mic numar de linii

la sfirsitul clauzei FROM.

SELECT ENAME, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

Exercitii

1. Creati un indice neunic in coloana Projid a tabelei de

atribuire.

2. Interogati tabela DDT (data dictionary table) pentru a obtine

informatii despre indexii folositi (USER_INDEXES).

Solutii:

1.

CREATE INDEX ASG_PROJID

ON ASSIGNMENTS (PROJID);

2.

SELECT TABE_NAME, INDEX_NAME

FROM USER_INDEXES;