CAPITOLUL 18
                                       
   
   
             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

   
       
   Unit 18 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;

Inapoi la cuprins.

BogSoft 1999