Tabelele reprezinta structurile fundamentale de stocare a bazelor de date relationale Oracle. Tabelele sunt compuse din linii si coloane.
Un tabel poate fi privit ca fiind o matrice bi-dimensionala având o serie de linii si coloane. O coloana contine date de un singur tip despre o anumita entitate. Un exemplu de coloana ar putea fi nume sau oras. O linie este o colectie de date pentru fiecare coloana a unui tabel. De exemplu, o linie a unui tabel care contine coloanele nume_oras si nume_stat ar putea fi "Lexington KY" sau "Denver CO".
Un tabel are un numar fixat de coloane predefinite si de obicei contine mai multe linii. Numarul de linii poate varia de la O la un numar practic nelimitat. Adevarata limitare a numarului de linii este data de dimensiunea bazei de date.
Tabelul Oracle poate fi folosit pentru pastrarea informatiilor despre o singura entitate. Un asemenea tabel ar putea sa 828h76i contina o lista a numelor salariatilor sau o lista a numerelor departamentelor din compania dumneavoastra. De asemenea, un tabel poate ilustra relatia dintre un angajat si numarul departamentului de care apartine.
O baza de date relationala stocheaza datele în unitati logice de stocare numite tabele. Tabelele sunt cele care dau forta si flexibilitatea modelului relational. Utilizarea tabelelor prezinta urmatoarele avantaje:
Baza de date devine mai usor de înteles datorita structurii foarte simple a tabelelor (linie/coloana)
Relatiile dintre datele situate în diverse tabele sunt usor de stabilit
Regulile de integritate se pot construi rapid
Tabelele reduc necesarul de spatiu de stocare si redundanta datelor
Oracle va permite sa creati nume sugestive pentru tabele si coloane, dându-va astfel posibilitatea sa îmbunatatiti claritatea bazei de date
Dupa ce ati creat un tabel, puteti sa inserati linii de date în el folosind instructiuni SQL. In continuare, informatiile stocate în tabel sunt disponibile în vederea interogarii, actualizarii si eliminarii.
Paragraful care urmeaza va explica modul în care creati, modificati si distrugeti tabele.
De obicei, stabilirea parametrilor de stocare si definirea clusterelor pentru tabele cad în sarcina administratorului bazei de date. Acesta trebuie sa utilizeze informatiile provenind de la creatorul aplicatiei, privind modul de utilizare a aplicatiei si tipurile de date necesare efectuarii diverselor sarcini.
În continuare sunt enumerate aspectele generale care trebuie avute în vedere atunci când intentionati sa creati un nou tabel.
Tabelele trebuie sa fie normalizate.
Coloanele care permit valori nule vor fi definite ultimele pentru a conserva spatiul de stocare.
Grupati tabelele în clustere ori de câte ori este posibil.
Specificati valori adecvate pentru parametrii pctfree si pctused în momentul crearii tabelului.
Specificati parametrii initrans si maxtrans în momentul crearii tabelului
Specificati spatiul-tabel în care vor fi pastrate tabelele.
Estimati dimensiunea tabelului si specificati parametrii de stocare. Folositi aceste estimari pentru a stabili resursele necesare de procesare si de spatiu de stocare pe disc.
În cazul în care constatati ca veti avea tabele foarte mari, de genul celor cu milioane de linii, trebuie sa luati anumite masuri de precautie. Daca tabelul urmeaza sa creasca în timp ajungând la dimensiuni foarte mari, asigurati-va ca extinderile acestuia sunt suficient de mari astfel încât segmentul sa nu aiba prea multe extinderi.
În cazul tabelelor foarte mari trebuie avute în vedere înca doua aspecte: pe de o parte, specificati parametrii de stocare astfel încât tabelul si indecsii lui sa se gaseasca în spatii-tabel diferite; pe de alta parte, alocati suficient spatiu temporar pentru actiuni cum ar fi sortarea.
Parcurgeti urmatoarele sase etape pentru a calcula dimensiunea proiectata a unui tabel. Aceste etape furnizeaza o valoare estimativa rezonabila si nu un numar precis.
Determinati spatiul necesar pentru antetul blocului de date folosind urmatoarea formula:
antet bloc = (antet fix + antet de tranzactie variabil) + (catalog de tabele + catalog de linii)
antet fix = 57. octeti
antet de tranzactie variabil = 23* valoarea parametrului initrans
catalog de tabele = 4
catalog de linii = 2* numarul de linii dintr-un bloc
Determinati spatiul disponibil pentru date per bloc de date, folosind urmatoarea formula:
Spatiu disponibil pentru date = (dimensiune bloc - total antet bloc) -((dimensiune bloc - (antet fix + antet de tranzactie variabil)) * (pctfree/100))
Puteti obtine dimensiunea blocului curent prin lansarea comenzii show parameters db_block_size.
Calculati spatiul de date combinat pe baza valorilor unei linii tipice. Acest lucru se realizeaza prin evaluarea numarului de linii din tabel, a tipului coloanelor si a mediei dimensiunilor coloanelor de lungime variabila. Utilizati functia avg pentru fiecare linie pentru a-i afla dimensiunea medie.
Calculati dimensiunea medie globala a unei linii folosind urmatoarea formula:
dimensiunea medie globala liniei = antet linie +A + B + C
A = totalul lungimii tuturor coloanelor care stocheaza 250 de octeti sau mai putin.
B = totalul lungimii tuturor coloanelor care stocheaza 250 de octeti sau mai mult.
C = spatiul de date combinat al tuturor coloanelor (calculat în etapa 3).
Determinati numarul mediu de linii per bloc cu urmatoarea formula:
numar mediu de linii/bloc = spatiu disponibil/dimensiunea medie a liniei
Spatiul disponibil este cel calculat în etapa 2, iar dimensiunea medie a liniei este cea calculata în etapa 4.
Calculati numarul de blocuri cu urmatoarea formula:
numar de blocuri = numar de linii/numarul mediu de linii per bloc Numarul mediu de linii per bloc a fost determinat în etapa 5.
Prima etapa pentru crearea unui nou tabel Oracle o reprezinta proiectarea tabelului. Iata câteva aspecte care trebuie avute în vedere la proiectarea unui tabel:
Folositi nume sugestive pentru tabel si pentru coloane
Selectati tipul de date adecvat pentru fiecare coloana
Normalizati tabelele atunci când este posibil
Definiti la sfârsit coloanele care accepta valori nule pentru a conserva spatiul de stocare
Nu uitati sa documentati numele tabelului si scopul acestuia folosind comanda comment
Creati un tabel pentru relatii si un tabel distinct pentru date
Înainte de a crea un tabel, trebuie sa stabiliti daca sunt necesare restrictii de integritate. Puteti defini restrictiile de integritate pentru coloanele unui tabel pentru a impune automat anumite reguli de utilizare pentru baza de date. De exemplu, daca aveti un tabel care contine informatii referitoare la angajati, puteti folosi restrictia de integritate not nuli pentru coloana nume_ang. Prezenta acestei restrictii va obliga utilizatorul sa insereze o valoarea în aceasta coloana pentru fiecare linie a tabelului.
Pentru a crea un tabel, creati un script SQL folosind comanda create table. Sintaxa generala a comenzii pentru a crea un tabel este urmatoarea:
create table tablename
(nume_coloana tip_data restrictie,
(nume_coloana tip_data restrictie)
Numele tabelelor si ale coloanelor pot fi formate din 1 pâna la 30 de caractere. Primul caracter trebuie sa fie o litera. Numele tabelului nu poate fi numele unui alt obiect detinut de acelasi utilizator al serverului Oracle. O restrictie este p regula care limiteaza valorile datelor pentru una sau mai multe coloane ale unui tabel.
Exemplul care urmeaza prezinta un script SQL care va crea un tabel de angajati cu cinci coloane. Tabelul va contine numarul de marca al angajatului, numele si prenumele angajatului, numarul departamentului de care apartine angajatul si data angajarii.
create table ang(
marca_ang number(15) primary key,
prenume varchar2(20) not nuli,
nume varchar2(40) not nuli,
nr_dept varchar2(12),
data ang, date not nuli)
pctfree 20
pctused 40
tablespace ang_01
storage(initiaT 50k
next 50k
maxextents 15
pctincrease 25);
marca_ang este difinita drept cheie primara a tabelului. Scopul definirii unei chei primare este identificarea univoca a liniilor tabelului (coloana pentru care s-a definit o cheie primara nu poate contine valori duplicate). Restrictiile not nuli impuse unora dintre coloane indica faptul ca aceste coloane trebuie sa contina o valoare pentru fiecare linie.
pctfree reprezinta procentajul din fiecare bloc de date care este rezervat cu spatiu liber în vederea expansiunii viitoare. In acest exemplu, 20% din fiecare bloc este lasat deoparte în vederea utilizarii viitoare.
pctused defineste procentul de umplere a blocurilor unei extinderi pâna la realizarea caruia Oracle continua sa insereze noile înregistrari în extinderea curenta; în momentul în care continuarea inserarii de noi linii ar duce la depasirea acestui procentaj, programul opreste inserarea, aloca o noua extindere si continua inserarea noilor linii în noua extindere, în exemplul de fata, aceste procentaj este de 40%.
Oracle utilizeaza spatiul liber din extindere ca spatiu de lucru în timpul operatiunilor de inserare si de actualizare. Desi o valoare scazuta a parametrului pctused duce la cresterea spatiului de stocare neutilizat, ea îmbunatateste eficienta procesarii anumitor comenzi SQL. O valoare mare a parametrului pctused reduce spatiul de stocare neutilizat însa reduce viteza de executie a instructiunilor insert si update.
Acest tabel va fi creat în spatiul-tabel ang_01. Stocarea este definita prin alocarea initiala a unui spatiu de 50KB, urmatoarea extindere fiind de 50KB. Daca tabelul continua sa creasca, îi vor fi alocate extinderi suplimentare. Aceste extinderi vor fi alocate pe rând pâna când este atins totalul de 15 extinderi. Daca în continuare tabelul are nevoie de spatiu suplimentar, va fi generata o eroare Oracle care va indica faptul ca a fost atins numarul maxim de extinderi alocate. Fiecare dintre aceste extinderi va creste cu o rata de 25% fata de cea precedenta. In cazul de fata, prima extindere va avea 50KB. A doua va avea tot 50KB. A treia extindere va avea 62.5KB (50*1.25), iar a patra va avea 78.1KB (62.5*1.25) si asa mai departe.
Pentru a adauga comentarii referitoare la un tabel si la coloanele acestuia în dictionarul de date, folositi comanda comment. Exemplul urmator creeaza un comentariu în dictionarul de date pentru coloana data_ang din tabel ANG:
comment on column ang.data_ang
IS Aceasta data reprezinta data la care a fost angajata persoana. Coloana Data_inc reprezinta data la care angajatul a inceput sa lucreze efectiv;
Pentru a elimina comentariul din exemplul anterior, folositi exemplul urmator:
comment on column ang.data_ang is ''
Urmatorul exemplu creeaza un comentariu în dictionarul de date pentru tabelul DEPT:
comment on table dept is 'In aceasta lista sunt enumerate si descrise departamentele companiei' încarcarea unui tabel în timpul crearii
Atunci când creati un tabel, aveti posibilitatea ca în acelasi timp sa-1 si populati. Pentru a face acest lucru, veti adauga în instructiunea SQL create table clauza as. Exemplul urmator creeaza tabelul ANG si încarca în el continutul tabelului ANG_UK:
create table ang(
marca_ang,
nume,
prenume,
nr_dept,
data_ang)
pctfree 20
pctused 40
tablespace ang_01
storage(initial 50k
next 50k
maxextents 15
pctincrease 25)
as select marca_ang, prenume, nume, nr_dept, data_ang from ang_uk;
Atunci când folositi clauza as, nu este premisa specificarea tipurilor de date; definitiile coloanelor pot specifica numai numele, valorile prestabilite si restrictiile de integritate.
Oracle preia tipurile de date si lungimile coloanelor din subinterogare. Programul defineste de asemenea restrictii not nuli pentru coloanele din noul tabel daca acestea existau în coloanele corespunzatoare ale tabelului selectat.
Comanda create table nu poate sa contina în acelasi timp clauza as si o definitie a unei restrictii de integritate prin referire.
Grupurile (clustere) reprezinta o metoda optionala de stocare a datelor tabelelor. Tabelele care sunt accesate frecvent împreuna pot fi stocate fizic împreuna în aceleasi blocuri de date. Tabelele dintr-un grup sunt stocate împreuna pentru a reduce numarul de operatii de I/O cu discul.
Cheia unui grup este definita ca reprezentând coloanele pe care tabelele grupate le au în comun. Valorile cheii grupului sunt stocate o singura data în schema, reducându-se în acest fel necesarul de spatiu de stocare si procesarile suplimentare.
Pentru coloanele cheie ale grupului trebuie creat în mod explicit un index de grup. Indexul este utilizat pentru localizarea univoca a liniilor de date.
Exemplul urmator executa comanda create cluster pentru a crea un grup numit ang_dept. Acest grup va cuprinde tabelele care sunt grupate pe baza coloanei nr_dept:
create cluster ang_dept (nr_dept number(5))
pctused 80
pctfree 5;
Adaugarea tabelelor la grup se realizeaza prin adaugarea clauzei cluster în comanda create table, în exemplul urmator sunt create doua tabele, ANG si DEPT, în grupul ang_dept:
create table ang(
marca_ang number(15), primary key,
nume varchar2(20) not null,
prenume varchar2(40) not null,
nr_dept varchar2(12),
data_ang date, not null
sal number (6))
pctfree 20
pctused 40
tablespace ang_01
storage(initial 50k
next 50k
maxextents 15
pctincrease 25);
Urmatoarea instructiune SQL creeaza indexul grupului pentru grupul ang_dept:
create index i_ang_dept
on cluster ang_dept;
Pentru a crea un grup în schema dumneavoastra, trebuie sa posedati privilegiul de sistem create cluster. Pentru a crea un grup în schema altui utilizator, trebuie sa posedati privilegiul de sistem create any cluster.
Pentru a distruge un grup dintr-o baza de date, lansati comanda drop cluster: drop cluster ang_dept
Un grup nu poate fi distrus daca exista tabele în el, deci va trebui sa includeti în comanda drop cluster clauza optionala including tables. Mai mult, va trebui sa includeti si clauza optionala cascade constraints pentru a distruge toate restrictiile de 'integritate prin referire din afara grupului care se refera la chei din tabelele grupate.
Urmatorul script va distruge grupul ang_dept, tabelele grupate ANG si DEPT si orice restrictie de integritate gasita în afara grupului:
drop cluster ang_dept
including tables cascade constraints;
N.T. Comanda create table trebuie sa contina o clauza de genul "on cluster emp_dept".
Puteti distruge orice grup din schema dumneavoastra. Pentru a distruge un grup care nu se gaseste în schema dumneavoastra, trebuie sa posedati privilegiul de sistem drop any cluster. O schema este colectia tabelelor, vederilor si privilegiilor care formeaza baza dumneavoastra de date.
Sistemul Oracle va permite sa modificati orice tabel existent. Motivele pentru care ati putea dori sa modificati un tabel sunt urmatoarele:
Pentru a adauga una sau mai multe coloane unui tabel
Pentru a adauga sau a distruge restrictii asociate unui tabel
Pentru a modifica parametrii pctused si pctfree
Pentru a modifica definitia unei coloane existente (tipul de date si lungimea)
Atunci când modificati definitiile coloanelor unui tabel, acesta trebuie sa fie gol pentru a putea face urmatoarele operatii:
Micsorarea lungimii unei coloane
Modificarea tipului de date ale unei coloane
Marirea lungimii unei coloane de tip char poate dura destul de mult. Aceasta deoarece tipul de date char face ca programul Oracle sa completeze cu blancuri fiecare linie pentru coloana modificata.
Pentru a modifica un tabel existent, folositi comanda alter table. Codul care urmeaza adauga o noua coloana tabelului DEPT:
alter table dept
add (profil_dept varchar2(35));
Urmatorul exemplu modifica tabelul ANG, indicând faptul ca trebuie completat câmpul salariu:
alter table emp modify (sal not nuli);
Acest exemplu modifica tabelul ANG prin adaugarea unei restrictii de tip cheie externa:
alter table ang
add constraint s_dept_man_id_fk foreign key (nr_dept)
references dept;
Acest exemplu elimina o restrictie din tabelul DEPT:
change dept to ang
alter table dept
drop constraint s_dept_man_id_fk;
alter table emp
Pentru a modifica un tabel, acesta trebuie sa se gaseasca în schema dumneavoastra; în caz contrar, trebuie sa posedati fie privilegiul alter pentru tabelul respectiv, fie privilegiul de sistem alter any table.
Pentru a elimina din baza de date un tabel împreuna cu toate datele acestuia, puteti folosi comanda drop table. De exemplu, urmatoarea comanda distruge tabelul DEPT:
drop table dept;
Pentru a distruge un tabel, acesta trebuie sa se gaseasca în schema dumneavoastra; în caz contrar, trebuie sa posedati privilegiul de sistem drop any table.
Distrugerea unui tabel are urmatoarele efecte:
Definitia tabelului este eliminata din dictionarul de date, tabelul si datele devenind inaccesibile. Acest lucru este echivalent cu eliminarea efectiva a liniilor tabelului.
Toti indecsii si declansatorii asociati cu tabelul sunt distrusi, indiferent daca sunt creati de dumneavoastra sau de alt utilizator.
Vederile si sinonimele care fac referire la tabel nu sunt distruse însa devin invalide.
Toate blocurile care au fost alocate tabelului sunt returnate spatiului-tabel ca spatiu liber si pot fi utilizate de orice alt obiect care necesita noi extinderi.
O metoda eficienta de a elimina toate liniile dintr-un tabel este sa utilizati comanda truncate table. Aceasta comanda elimina toate înregistrarile din tabel si elibereaza spatiul alocat care era folosit pentru pastrarea înregistrarilor eliminate.
Comanda truncate table este mai eficienta si mai rapida decât comanda delete f rom. De obicei, utilizatorii întâmpina doua probleme atunci când încearca sa elimine volume mari de înregistrari cu comanda delete from. In primul rând, ei risca sa primeasca un mesaj de eroare prin care sunt avertizati ca segmentul lor de revenire este prea mic. Aceasta este o falsa eroare deoarece de obicei, segmentul lor de revenire este corect dimensionat, însa tranzactia este prea mare. în al doilea rând, obiectul tabel pastreaza întregul spatiu alocat atunci când comanda delete from este finalizata. Programul Oracle nu elibereaza acest spatiu dupa executarea acestei comenzi, deci câstigul de spatiu este nul.
Comanda truncate table rezolva ambele probleme. Ea nu foloseste segmentul de revenire în timp ce este executata. Prin urmare, este mai rapida si nu sunteti limitat de spatiul segmentului. Folosirea comenzii truncate table duce la eliberarea spatiului-tabel imediat dupa executia acesteia.
Comanda truncate table nu poate fi derulata înapoi; prin urmare, trebuie sa o folositi cu precautie în aplicatiile dumneavoastra. Pentru a folosi comenzile truncate table sau truncate cluster, tabelul, respectiv grupul trebuie sa se gaseasca în schema dumneavoastra sau trebuie sa posedati privilegiul de sistem delete any table.
În exemplul urmator, comanda truncate table este folosita pentru a elimina toate liniile din tabelul DEPT:
truncate table dept;
în acest capitol, ati facut cunostinta cu tabelele, care sunt structurile logice de stocare fundamentale ale bazei de date relationale Oracle. Tabelele pot fi privite ca matrice bi-dimensionale care contin un numar predeterminat de coloane si linii multiple.
Folosirea tabelelor este componenta esentiala a utilizarii bazei de date relationale Oracle. Tabelele furnizeaza o structura logica simplificata usor de înteles si suficient de flexibila pentru a satisface cerintele bazei dumneavoastra de date.
în acest capitol, ati parcurs etapele necesare crearii si modificarii tabelelor, Cu comanda create' table, puteti sa optimizati spatiul de stocare, precum si sa stabiliti diverse restrictii
|
