Struktury pamięciowe |
SGA
Globalny obszar systemu jest specjalnym blokiem pamięci, w którym baza danych zapisuje najczęściej wykonywane operacje na bazie, czy tez informacje związane z funkcjonowaniem bazy danych. Obszar ten stanowi statycznie podzielony obszar pamięci współużytkowanej przez wszystkie procesy bazy Oracle. Do wersji 9i wielkość tego bloku była pobierana z pliku init.ora i nie mogła być zmieniana w czasie pracy bazy. W celu zmiany tego parametru należy zatrzymać bazę, zmienić wartość parametru i uruchomić bazę ponownie. W wersji 9i bazy wartość ta może być generowana dynamicznie w czasie pracy aplikacji. Wielkość tego buforu określamy w parametrze DB_CACHE_SIZE. Od wersji 9i bazy Oracle wprowadzono również parametr SGA_MAX_SIZE określający, do jakiej wielkości może się rozrosnąć obszar SGA.
Pamięć podręczna słownika
Pamięć podręczna słownika jest częścią SGA zawierającą informacje często pobierane z tabel słownikowych. Tabele słownikowe zawierają informacje dotyczące wszystkich obiektów bazy danych. Do takich tabel odwołujemy bardzo często i wskazane jest, aby dane w nich zawarte nie były za każdym razem czytane z dysku, tylko z pamięci, co jest zdecydowanie szybsze. Rozmiar tej pamięci określa się poprzez parametr SHARED_POOL_SIZE pliku init.ora.
Bufor dziennika powtórzeń
Bufor dziennika powtórzeń zawiera informacje potrzebne do odtwarzania transakcji, które z tego bufora co pewien czas są zapisywane do plików czynnego dziennika powtórzeń. Polepsza to wydajność bazy danych, ponieważ dane nie są zapisywane do plików, tylko są buforowane w pamięci.
Obszar dzielony
Na obszar dzielony składa się omawiana wcześniej pamięć podręczna słownika oraz biblioteczna pamięć podręczna oraz dzielony obszar SQL.
Biblioteczna pamięć podręczna (library cache) pozwala na współużytkowanie najczęściej używanych poleceń SQL.
Dzielony obszar SQL zawiera informacje dotyczące planu wykonania oraz sposób parsowania wykonanych instrukcji SQL. Obszar ten pozwala na przyspieszanie wykonywania tych instrukcji. Jeśli wykonujemy instrukcje, która znajduje się już w tym buforze system nie musi ponownie generować plan wykonania oraz sposób parsowania tej instrukcji, ponieważ dane te znajdują się w buforze. Dzielony obszar SQL, tak jak większość buforów bazy danych Oracle działa w oparciu o algorytm LRU. Bufor ma określoną wielkość, dane są zapisywane do bufora nieustannie. W przypadku, kiedy nie ma już miejsca w buforze najdłużej nie używane dane znajdujące się w buforze są z niego usuwane, aby zrobić miejsce na nowe dane.
Obszar dużych obiektów
Obszar dużych obiektów jest dodatkowym buforem danych do przechowywania dużych obiektów np. przy archiwizacji i odtwarzania danych. Wielkość tego bufora określa parametr LARGE_POOL_SIZE.
Obszar JAVA
Obszar JAVA jest wykorzystywany do buforowania poleceń języka JAVA.
Zwielokrotniony obszar buforów
Wartości dotyczące wielkości buforów danych dotyczą standardowej wielkości bufora danych. Tak samo jak możemy w bazie Oracle 9i używać segmentów o rożnych wielkościach bloków danych tak samo możemy ustawiać wielkości buforów w zależności od używanych w bazie wielkości bloków danych. Parametry te to np. DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE.
PGA
Obszar ten, to globalny obszar programu wykorzystywany przez pojedynczy proces i nie jest dzielony przez inne procesy.
Procesy drugoplanowe |
SMON
Proces SMON (System Monitor) jest procesem drugoplanowym zajmującym się kontrola, czy podczas startu bazy danych nie wymaga ona automatycznego odtwarzania za pomocą plików czynnego dziennika powtórzeń, usuwanie zbędnych obiektów transakcji. Dodatkowo proces ten zajmuje się zarządzaniem fragmentacja bazy poprzez łączenie sąsiednich wolnych bloków w jedna większą całość.
PMON
Proces PMON (Process Monitor) zajmuje się zarządzaniem procesów użytkownika poprzez usuwanie pozostałości po niepoprawnie zakończonych procesach użytkownika.
DBWR
Proces DBWR (Database Writer) zajmuje się cyklicznym zapisywaniem danych zapisanych w obszarze SGA do plików danych. Takich procesów może być więcej niż jeden. Liczbę tych procesów określamy parametrem DB_WRITER_PROCESSES. W przypadku, kiedy system operacyjny, na którym zainstalowana jest baza danych nie obsługuje asynchronicznych operacji wejścia - wyjścia, stosujemy jeden proces DBWR oraz kilka podrzędnych procesów wejścia - wyjścia. Liczbę tych procesów określamy za pomocą parametru DBWR_IO_SLAVES
LGWR
Proces LGWR (Log Writer) zajmuje się zarządzaniem zapisem zawartości bufora dziennika powtórzeń do plików czynnego dziennika powtórzeń. Tak jak i w przednich procesach proces ten zapisuje dane cyklicznie, a nie na bieżąco.
CKPT
Proces CKPT (Checkpoint) jest procesem zajmującym się rejestracjami punktów kontrolnych. Punkty te poprzez aktualizacje nagłówków plików danych oraz plików sterujących mogą spowodować przyspieszenie czasu potrzebnego do odtworzenia instancji po awarii. Z procesem tym związane są dwa parametry pliku konfiguracyjnego LOG_CHECKPOINT_INTERVAL oraz LOG_CHECKPOINT_TIMEOUT.
ARCH
Proces ARCH (Archiver) działa w przypadku, kiedy baza danych uruchomiona jest w trybie ARCHIVELOG. W trybie tym, kiedy plik dziennika powtórzeń zapełni się tworzona jest kopia tego pliku. Za to właśnie odpowiada proces ARCH.
RECO
CJQx
LMSx
Dxxx
Sxxx
Segmenty wycofania |
Segmenty wycofania są specjalnymi obiektami Oracle stosowanymi do zarządzania transakcjami. Kiedy rozpoczynamy transakcje segmenty wycofania wykorzystywane są do zapamiętania stanu modyfikowanych danych, tak, aby w przypadku wycofania transakcji łatwo można było wrócić do stanu sprzed rozpoczęcia tej transakcji.
Instalacja Oracle |
W tej części postaram sie wyjaśnic krok po kroku proces instalacji Oracle. poniższy proces był przeprowadzany w systemie Red Hat 9 z bazą danych Oracle 9i. (9.2.0).
Stworzenie użytkownika oracle po zalogowaniu się jako użytkownik root.
groupadd dba
useradd -G dba oracle
passwd oracle
Stworzenie katalogu na potrzeby bazy danych
Dla przykładu przy instalacji bazy w katalogu /opt/ora9:
mkdir -p /opt/ora9/product/9.2
mkdir /var/opt/oracle
chown oracle.dba /var/opt/oracle
chown -R oracle.dba /opt/ora9
Instalacja dodatkowych bibliotek:
compat-gcc-7.3-2.96.118.i386.rpm
compat-libgcj-7.3-2.96.118.i386.rpm
compat-libgcj-devel-7.3-2.96.118.i386.rpm
nss_db-compat-2.2-20.i386.rpm
Ustawienie parametrów jądra:
W pliku /etc/sysctl.conf należy wprowadzić linie:
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
W pliku /etc/security/limits.conf należy wprowadzić linie:
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
Jeśli jest to możliwe należy zrestartować serwer. Jeśli nie ma takiej możliwości należy zmiany te wprowadzić online:
echo 250 32000 100 128 > /proc/sys/kernel/sem
echo 536870912 > /proc/sys/kernel/shmmax
echo 4096 > /proc/sys/kernel/shmmni
echo 2097152 > /proc/sys/kernel/shmall
echo 65536 > /proc/sys/fs/file-max
echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range
Po zalogowaniu się jako użytkownik oracle należy ustawić odpowiednie zmienne środowiskowe np. poprzez umieszczenie ich w pliku ~/.bashrc:
export ORACLE_BASE=/opt/ora9
export ORACLE_HOME=/opt/ora9/product/9.2
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
export ORACLE_OWNER=oracle
export ORACLE_SID=ora9i
export ORACLE_TERM=vt100
export LD_ASSUME_KERNEL=2.4.1
export THREADS_FLAG=native
export LD_LIBRARY_PATH=/opt/ora9/product/9.2/lib:$LD_LIBRARY_PATH
export PATH=/opt/ora9/product/9.2/bin:$PATHvexport NLS_LANG='croatian_croatia.ee8iso8859p2'
Następnie jako użytkownik oracle uruchamiamy X oraz plik /mnt/cdrom/install/linux/runInstaller. Podczas instalacji zostaniemy poproszeni o podanie pewnych informacji, które ze względu na swoja prostotę nie będę tu opisywał. Ważne jest, iż podczas operacji linkowania pojawia się dwa błędy. Pierwszy błąd będzie informował o problemie z plikiem ins_oemagent.mk. Błąd ten ignorujemy, ponieważ zostanie on poprawiony później. Drugi błąd będzie dotyczył pliku $ORACLE_HOME/ctx/lib/ins_ctx.mk".
Kiedy pojawi się drugi problem należy w nowej konsoli wykonać komendę:
cd $ORACLE_HOME/install
tail make.log
Prawie na samym końcu tego pliku powinna znaleźć się linia:
gcc -o ctxhx -L/opt/ora9/product/9.2/ctx/lib/ -L/opt/ora9/product/9.2/lib/
-L/opt/ora9/product/9.2/lib/stubs/ /opt/ora9/product/9.2/ctx/lib/ctxhx.o
-L/opt/ora9/product/9.2/ctx/lib/ -lm -lsc_ca -lsc_fa -lsc_ex -lsc_da -lsc_ut
-lsc_ch -lsc_fi -lctxhx -lc -Wl,-rpath,/opt/ora9/product/9.2/ctx/lib -lnls9
-lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9 -lunls9 -lnls9
Należy skopiować ta linie, dodać na jej końcu -ldl , a następnie przejść do katalogu $ORACLE_HOME/bin oraz uruchomić ta poprawiona komendę.
Teraz po wykonaniu tej instrukcji ignorujemy ten błąd.
Po zakończeniu instalacji należy wykonać poniższe komendy:
cd $ORACLE_HOME/network/lib
make -f ins_net_client.mk install
Następnie należy wyedytować plik $ORACLE_HOME/ctx/lib/ins_ctx.mk oraz zamienić w nim tekst
ctxhx: $(CTXHXOBJ)
$(LINK) $(CTXHXOBJ) $(INSO_LINK)
na
ctxhx: $(CTXHXOBJ)
$(LINK) -ldl $(CTXHXOBJ) $(INSO_LINK)
Następnie wykonujemy komendę:
$ make -f $ORACLE_HOME/ctx/lib/ins_ctx.mk install
Teraz możemy uruchomić agentów poleceniem:
$ /opt/ora9/product/9.2/bin/agentctl start
Następnie ustawiamy dodatkowe zmienne środowiskowe
export ORACLE_BASE=/home1/orahome
export ORACLE_HOME=/home1/orahome/ora92
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
export ORACLE_OWNER=oracle
export ORACLE_SID=ora9i
export ORACLE_TERM=vt100
export LD_ASSUME_KERNEL=2.4.1
export THREADS_FLAG=native
export LD_LIBRARY_PATH=/opt/ora9/product/9.2/lib:$LD_LIBRARY_PATH
export PATH=/opt/ora9/product/9.2/bin:$PATH
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
Następnie należy przejść do katalogu $ORACLE_HOME/bin i wyedytować skrypt dbca. W nim zmienną środowiskową JRE_HOME należy ustawić na wersje JDK 1.3 lub 1.4. Po tych czynnościach należy uruchomić skrypt ./dbca.
Celem uruchomienia instancji należy zalogować się na prawach sysdba poleceniem:
sqlplus 'sys/passwd as sysdba'
a następnie wykonać polecenie:
create spfile from pfile='fullpathofthe.ora';
gdzie:
fullpathofthe.ora : pełna ścieżka do pliku .ora stworzonego narzędziem dbca.
Następnie wykonujemy:
shutdown immediate;
startup;
Typy danych |
bfile : Dane binarne przechowywane poza bazą (max 4GB)
blob : Dane binarne o zmiennej długości (max 4GB)
char : Łańcuch o stałej długości (max 2KB)
clob : Łańcuch o zmiennej długości (max 4GB)
date : Data i czas (max 7)
long : Łańcuch o zmiennej długości (max 2GB)
long raw : Dane binarne o zmiennej długości (max 2GB)
nchar : Łańcuch o stałej długości (max 2KB)
nclob : Łańcuch o zmiennej długości (max 4GB)
number : Liczba (max 38)
nvarchar2 : Łańcuch o zmiennej długości (max 4KB)
raw : Dane binarne o zmiennej długości (max 2KB)
rowid : Identyfikator wiersza (max 10)
varchar : Łańcuch o zmiennej długości (max 4KB)
varchar2 : Łańcuch o zmiennej długości (max 4KB)
Obiekty bazy danych Oracle |
Indeksy
Klastry
Kursory
Partycje
Perspektywy materializowane
Sekwencje :
Synonimy :
Tabele :
Przestrzenie nazw :
Widoki :
Indeksy |
Indeks jest specjalna struktura mającą na celu przyspieszenie operacji odczytu danych z tabel. Indeksy zakłada się na kolumnie lub kilku kolumnach tabeli. Założenie takiego indeksu oznacza, że powstanie specjalna struktura, którą możemy porównać z indeksem w książce, gdzie w szybki sposób przeglądając indeks w poszukiwania słowa mamy dostęp do informacji, gdzie w książce znajduje się poszukiwane słowo. Indeks w zdecydowany sposób przyspiesza dostęp do danych w tabeli jeśli selektywność jest niska ( w odpowiedzi na zapytanie zwracana mala ilość danych ). Zaleca się stosowanie indeksu w przypadku zwracania do ok. 5% rekordów tabeli. Kiedy selektywność jest wyższa (powyżej 5%).
Rodzaje indeksów
Indeksy można podzielić na dwa główne typy, indeksy b-drzewa oraz indeksy bitmapowe.
Indeksy b-drzewa
Indeksy b-drzewa do wyszukiwania położenia rekordów stosuje się strukturę podobna do odwróconego drzewa. Na samej górze tego drzewa znajduje się korzeń, poniżej są poziomy z gałęziami, a na samym dole znajdują się liście zawierające informacje o położeniu rekordu bądź rekordów w tabeli.
Indeksy bitmapowe
Indeks bitmapowy działa zupełnie inaczej niż indeks b-drzewa. Jest on najlepszy w przypadku niskiej selektywności. Dla przykładu kolumna z wartościami 1 lub 0 nadaje się do założenia na niej indeks bitmapowy. Informacje przechowywane w takim indeksie są w postaci mapy bitów dla każdej wartości występującej w danej kolumnie. Główną zaletą takich indeksów jest to, że bardzo łatwo takie mapy bitów można łączyć. Przykładem takiego efektywnego wykorzystania indeksów jest sytuacja, kiedy kilka wartości o malej selektywności może być połączonych w złączenie o wysokiej selektywności.
Tabele zorganizowane obiektowo
Tabele zorientowane obiektowo są bardzo podobne do indeksów b-drzewa. Zasadnicza różnica polega na tym, że w liściu nie znajduje się adres ROWID, a właściwe dane. Zaletą takiego rozwiązania jest to, że ograniczamy tu ilość operacji wejścia-wyjścia. W przypadku zwykłych indeksów najpierw przeszukujemy indeks, a potem dopiero pobieramy dane z tabeli znajdujące się pod wskazanym adresem. Tutaj, w przypadku tabel zorientowanych obiektowo przeszukujemy tylko jeden raz.
Indeksy oparte na funkcjach
Indeksy oparte na funkcjach stanowią specyficzną odmianę indeksów b-drzewa. Różnica polega na tym, ze zamiast wartości kolumn w indeksie zapisane są rezultaty funkcji podanej podczas tworzenia indeksu. Takie indeksy są efektywne, kiedy mamy zapytanie SQL, gdzie w klauzuli where występuje funkcja.
Przykład:
SELECT * from users where current_sales- last_sales>0;
Dla takiego zapytania możemy stworzyć indeks poleceniem:
Create index ind_sales on users (current_sales- last_sales);
Klastry |
Klaster, to specjalny obiekt, łączący w jeden byt kilka tabel ze sobą związanych. Oczywiście sensowne jest tworzenie indeksu klastrowego tylko wtedy, kiedy w klastrze znajdują się tabele występujące w złączeniach zapytań SQL. Utworzenie w takiej sytuacji klastra na tych tabelach zdecydowanie przyspiesza wykonywanie zapytań SQL. Przyspieszenie polega na tym, ze w przypadku złączenia tabel w klastrze dane mogą być buforowane w SGA. Dodatkowo przyspieszenie to jest spowodowane tym, że złączenie w klastrze odbywa się za pomocą jednej operacji wejścia-wyjścia. Należy tu podkreślić, że klastry mogą przyspieszyć wykonywanie zapytań, ale nie musza. Jeśli np. w klastrze z niewiadomych przyczyn umieścimy tabele, które w żadnym zapytaniu SQL nie są ze sobą złączane, to stworzenie indeksu może przynieść wręcz odwrotne skutki i spowodować spadek wydajności wykonywania zapytań. Dane w tym wypadku będą niepotrzebnie buforowane w SGA. Klastry nie należy zakładać na tabelach, na których będą często generowane pełne przeszukiwania na pojedynczych tabelach z tego klastra. Do takiej operacji wymagana jest dodatkowa ilość pamięci, co za sobą powoduje wykonywanie dodatkowych operacji wejścia-wyjścia.
Klastry haszujące
Klastry haszujące są podobne do zwykłego klastra. Różnica polega na tym, ze zamiast indeksu wskazujacego na klucz stosuje się specjalna funkcje haszujaca. Wynik tej funkcji jednoznacznie okresla miejsce, gdzie znajduja się dane. W przypadku klastra haszujacego na podstawie wyniku funkcji haszujacej od razu wiadomo, gdzie znajduja się dane ograniczajac liczbe operaci wejścia-wyjścia do jednej. W przypadku klastrow haszujacych prz identycznych danych funkcja haszujaca bedzie zwracala identyczna wartość.
Kursory |
Systemowe pseudo kolumny
Deklaracja kursora
Pobieranie danych z kursora
Użycie pętli FOR ... LOOP
Kursory FOR UPDATE
Zmienne kursorowe
Systemowe pseudo kolumny |
Systemowe pseudo kolumny nie sa typowymi kolumnami tabeli lecz kolumnami wirtualnymi, które możemy jedynie odczytywać. Do tych kolumn należą:
ROWID : Adres rekordu w tabeli. Aby móc przechowywać tą wartość należy użyć specjalnego typu UROWID
ROWNUM : Numer rekordu (jego położenie) względem początku rekordów zwracanych przez zapytanie.
Tak dla przykładu jeśli zapytanie SQL zwróci np. 10 rekordów dla pierwszego zwróconego rekordu wartość ROWNUM będzie miała wartość 1, drugiego 2, ..., a ostatniego 10.
Poniższy przykład pokazuje w jaki sposób pobrać 5 pierwszych rekordów zapytania.
select * from tab1 where rownum <=5
Deklaracja kursora |
Składnia deklaracji kursora ma postać:
CURSOR nazwa [(parametr[, parametr,...])]
[RETURN typ_zwracany] IS zapytanie;
Przykładowa deklaracji kursora:
CURSOR c1 is select * from tab1;
CURSOR c2(num number) is select * from tab1 where col1=num;
Pobieranie danych z kursora |
Pobieranie danych z kursora odbywa sie za pomocą wyrażenia FETCH, które pobiera wiersz z rezultatu zapytania i zapisuje go do odpowiednich zmiennych. Ogólny schemat wykorzystania w programach kursorów przebiega w kolejności jak poniżej:
otwarcie kursora (OPEN)
w pętli pobranie wiersza z kursora do odpowiedniej zmeinnej (FETCH)
zamknięcie kursora (CLOSE)Przykład wykorzystania kursora:
DECLARE
field1 tab1.col1%TYPE;
field2 tab1.col2%TYPE;
CURSOR c1 is select col1,clo2 from tab1;
BEGIN
OPEN c1;
LOOP
W FETCH c1 INTO field1, field2
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
END;
Użycie pętli FOR ... LOOP |
Pętla ta pozwala nam w alternatywny sposób operować na kursorach, w którym wiersz kursora zapisywany jest do zmiennej rekordowej. Schemat takiej pętli ma postać:
FOR zmienna_rekordowa IN nazwa_kursora LOOP
...
END LOOP;
Przykład:
DECLARE
Cursor cur (num NUMBER) IS
select col1,col2 from tab1 where ind=num;
BEGIN
FOR rec1 IN cur(30) LOOP
INSERT INTO othertab VALUES (rec1.col1, rec2.col2);
END LOOP;
COMMIT;
END;
Kursory FOR UPDATE |
Za pomocą kursora z opcją FOR UPDATE mamy możliwość blokowania wierszy w tabeli aby mieć pewność, że się nie zmienią przed wykonaniem poleceń update, czy też delete. W przypadku deklaracji takiego kursora wszystkie jego wiersze są blokowane kiedy otwieramy kursor (nie w przypadku pobrania danych instrukcją FETCH). W związku z czym nie możemy używać instrukcji FETCH po zatwierdzeniu transakcji lub jej wycofaniu. Najczęściej tego typu kursor jest stosowany w przypadku instrukcji UPDATE bądź DELETE z sentencją CURRENT OF. Mozliwa jest tu również opcja NOWAIT oznaczająca, że system nie będzie czekał na odblokowanie rekordów, jeśli będa one zablokowanie przez innego uzytkownika. W takiej sytuacji nastapi powrót do programu i innych zadań a następnie kolejna próba sprawdzenia blokady.
Klauzula kursora FOR UPDATE posiada jesze swoją odmianę FOR UPDATE OF. Ta forma jest stosowana w przypadku kursora definiującego zapytanie z połączonymi tabelami, gdzie po zwrocie FOR UPDATE OF podajemy nazwę kolumny z jednej z połączonych tabel, co będzie oznaczało, że nastapi blokada tylko wierszy z tej tabeli. Wiersze pozostałych tabel biorących udział w złączeniu nie będą blokowane.
Przykład:
DECLARE
Cursor c1 IS select col1 from tab1 FOR UPDATE;
...
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO ...
UPDATE tab1 SET cols1=newvalue WHERE CURRENT OF c1;
END LOOP;
Zmienne kursorowe |
Zmienne kursorowe zachowujmą sie jak zwykłe kursory, z tym że jemu dymicznie przypisujemy zapytanie. Oznacza to, że w przypadku zwykłego kursora zapytanie mu przypisane jest statyczne. W przypadku zmeinnej kursorowej zapytanei jest generowane dynamicznie, a to oznacza, że temu kursorowi możemy przypisać dowolne zapytanie zaleznie od okoliczności.
Tworzenie zmiennej kursorowej przebiega w dwóch etapach. W pierwszej fazie musimy zdefiniować specjalny typ kursorowy wg. poniższej składni:
TYPE nazwa_typu IS REF CURSOR [ RETURN zwracany_typ];
gdzie:
zwracany_typ : typ rekordowy zwracany przez kursor
Następnie deklarujemy zmienną o typie zdefiniowanym wcześniej. Skłądnia pozwalająca otworzyć kursor ma postać:OPEN nazwa_zmiennej_kursorowej FOR zapytanie_SQL;
Przykłady wykorzystania zmiennych kursorowych
DECLARE
TYPE CurTYPE IS REF CURSOR RETURN emp%ROWTYPE;
Cur CurTYPE;
...
BEGIN
OPEN Cur FOR select name from emp;
LOOP
FETCH Cur INTO rec;
EXIT WHEN Cur%NOTFOUND;
...
END LOOP;
DECLARE
TYPE CurTYPE IS REF CURSOR RETURN tab%ROWTYPE;
TYPE NameList IS TABLE OF tab%TYPE;
Cur CurTYPE;
Names NameList;
BEGIN
OPEN Cur FOR select name from tab;
FETCH Cur BULK COLLECT INTO Names;
...
END;
Partycje |
Partycjonowanie jest mechanizmem pozwalającym na podział tabel lub indeksów ma mniejsze części - "partycje". Stosuje się je najczęściej przy bardzo dużych tablicach, czy indeksach, kiedy mimo stosowania indeksów, wydajność nie jest zadowalająca. Dzieje się tak ponieważ partycje są mniejsze i dostęp do nich jest bardziej wydajny. Partycje można uznać za podzbiór tabeli w jakiś sposób ze sobą związanych ograniczając dane zapisane w tabeli do węższego grona. Przykładem może być tutaj tabela zawierająca dane o sprzedaży. Tabele taka, jeśli byłaby tak duża, ze dostęp do niej byłby mało wydajny można podzielić na partycje, gdzie każda partycja będzie zawierała dane o sprzedaży tylko i wyłącznie z jednego roku. Zbiór wszystkich partycji będzie stanowił dopiera pełną tabelę. Partycje, podobnie jak to ma miejsce w przypadku indeksów zakłada się na kluczach określających kolumnę bądź kolumny tabeli. We wcześniej podanym przypadku takim kluczem będzie rok. Tak samo jak to ma miejsce w innych przypadkach podstawa do zaprojektowania partycji powinna być charakterystyka zapytań odwołujących się do tej tabeli.
Rozróżniamy kilka sposobów partycjonowania:
partycjonowanie zakresowe
z lista wartości
z kluczem haszowym
partycje złożone
Partycjonowanie zakresowe
Kluczem w takich partycjach jest konkretny zakres danych.
Przykład:
CREATE TABLE sales (
id number,
name varchar(200),
sales_date DATE)
PARTITION BY RANGE (sales_date) (
PARTITION january2003 VALUES LESS THEN (TO_DATE('02/01/2003','MM/DD/YYYY')),
PARTITION february2003 VALUES LESS THEN (TO_DATE('03/01/2003','MM/DD/YYYY')),
...
PARTITION december2003 VALUES LESS THEN (TO_DATE('01/01/2004','MM/DD/YYYY')),
PARTITION max_value VALUES LESS THEN (MAXVALUE)
);
W powyższym przykładzie tabele dzielimy wg daty zapisanej w kolumnie sales_date. Ciekawostka jest tu ostatni przedział, który nie ma konkretnej granicy swojego przedziału. Jest to dość logiczne i zaleca się stosowanie takiego rozwiązania. Jeśli w powyższym przypadku nie wprowadzilibyśmy ostatniej partycji z zakresem kończącym się na max_value, w przypadku kiedy pojawi się rekord z kolumną sales_date poza zakresem wszystkich partycji system wygenerowałby błąd. Ostatnia partycja zabezpiecza nas przed taka sytuacja umieszczając takie rekordy w dodatkowej partycji. Przytoczony tu przykład może nie jest najbardziej trafny do ukazania konieczności wprowadzenia takiej partycji z przedziałem ograniczonym poprzez MAXVALUE. W tym przypadku znając charakterystykę działania aplikacji i np. to, że w tej kolumnie nie znajdzie się data większa niż bieżąca możemy przygotować dodatkowe partycje odpowiadające kilku przedziałom na przód, a następnie pilnować zbliżania się tych przedziałów do końca ostatniego zakresu. Jednak w wielu przypadkach nie będziemy mieli możliwości stwierdzić, kiedy wartość klucza może wyjść poza zakres. W takiej sytuacji zastosowanie partycji ograniczonej poprzez MAXVALUE wydaje się jak najbardziej wskazane.
Partycje z lista wartości
W partycjach z lista wartości zamiast na przedziałach klucz partycji zakłada się na konkretnych wartościach kolumn.
Przykład:
CREATE TABLE tab2 (
id_group NUMBER(10),
name VARCHAR(100))
PARTITION BY LIST (id_group) (
PARTITION pgroup1 VALUES (1,2,3),
PARTITION pgroup2 VALUES (4,5,6),
PARTITION pgroup3 VALUES (7,8,9)
);
Warto tu zauważyć iż w tym wypadku nie mamy możliwości użycia klauzuli MAXVALUE, dlatego należy w partycjach umieścić wszystkie możliwe wartości znajdujące się w kolumnie będącej kluczem partycji.
Partycje z kluczem haszowym
Partycje takie używają specjalnej funkcji haszujacej do określenia w której partycji znajdzie się konkretny rekord. Stosuje się ja dla bardzo dużych tabel, gdzie nie ma żadnych reguł umożliwiających wykorzystanie poprzednich dwóch sposobów, ale ze względu na swoja wielkość mamy pewność, że taka partycja poprawi wydajność. W tego rodzaju partycjach podajemy tylko ilość i nazwy partycji, a system sam zadba o odpowiednie rozłożenie rekordów pomiędzy tymi partycjami.
Przykład:
CREATE TABLE tab2 (
id_group NUMBER(10),
name VARCHAR(100))
PARTITION BY HASH (id_group)
PARTITIONS 4
STORE IN (p1, p2, p3, p4);
W powyższym przykładzie tabele dzielimy na 4 partycje p1, p2, p3 oraz p4.
Partycje złożone
Partycje złożone polegają na podziale tabeli na 2 kryteria. Najpierw tabele można podzielić zakresowo i dodatkowo te partycje można wewnętrznie podzielić wg klucza haszowego.
Przykład:
CREATE TABLE tab2 (
id_group NUMBER(10),
name VARCHAR(100))
PARTITION BY RANGE(id_group)
SUBPARTITION BY HASH(name)
SUBPARTITIONS 4 (
PARTITION january2003 VALUES LESS THEN (TO_DATE('02/01/2003','MM/DD/YYYY'))(
SUBPARTITION january2003_1 TABLESPACE tspace1,
SUBPARTITION january2003_2 TABLESPACE tspace2,
SUBPARTITION january2003_3 TABLESPACE tspace3,
SUBPARTITION january2003_4 TABLESPACE tspace4),
PARTITION february2003 VALUES LESS THEN (TO_DATE('03/01/2003','MM/DD/YYYY'))(
SUBPARTITION february2003_1 TABLESPACE tspace1,
SUBPARTITION february2003_2 TABLESPACE tspace2,
SUBPARTITION february2003_3 TABLESPACE tspace3,
SUBPARTITION february2003_4 TABLESPACE tspace4),
...
PARTITION maxvalue VALUES LESS THEN (MAXVALUE));
Perspektywy materializowane |
Perspektywy materializowane są lokalna replika odległej bazy danych. Rozróżniamy perspektywy materializowane tylko do odczytu lub modyfikowalne.
Innym podziałem takich perspektyw jest podział na proste i złożone. Perspektywy proste jak sama nazwa wskazuje opierają się na prostych zapytaniach, które nie zawierają agregacji, klauzuli connect by złączeń lub operacji ustawień. Jeśli perspektywa opiera się na zapytaniu zawierającym powyższe klauzule określamy jako złożone. Ważne jest ze w przypadku perspektyw złożonych zawsze następuje replikacja całkowita. W przypadku perspektyw prostych natomiast możemy wybrać pomiędzy replikacją całkowitą a replikacją przyrostową.
Aby móc korzystać z takiej perspektywy najpierw należy stworzyć połączenie lokalnej bazy danych ze zdalna baza. Połączenie takie tworzy się poleceniem:
Create database link db_linkconnect to user_name identyfied by user_passwordusing 'service'gdzie:
db_link : nazwa połączenia
user_name: nazwa użytkownika odległej bazy danych
user_password : hasło tego użytkownika
service : nazwa usługi lub serwisu odległej bazy danych
Przykład polecenia tworzącego taka perspektywę na lokalnym serwerze może mięć postać:
Create snapshot local_view
pctfree 5
tablespace datas
storage (initial 100K next 100K pctincrease 0)
refresh fast
start with SysDate
next SysDate +1
as select * from emp@link_name;
Polecenie to tworzy perspektywę materializowana dla prostych zapytań, co widać po zapytaniu. Klauzula refresh fast oznacza, że będzie tu wykonywana replikacja przyrostowa. Jeśli chcielibyśmy, aby replikacja była całkowita musimy użyć klauzuli refresh complete. W przypadku perspektyw złożonych nie mamy wyboru i musimy użyć klauzuli refresh complete. Dodatkowo klauzula refresh fast mówi nam, że do replikacji danych wykorzystamy dodatkowy obiekt bazy danych, dziennik perspektywy. Obiekt ten tworzy się po stronie nadrzędnej zdalnej bazy w tym samym schemacie, co tabela, do której się odwołuje.
Przykład takiego polecenia może mięć postać:
Create materialized view log on emp
tablespace big_datas
storage (initial 10K next 10K pctincrease 0)
Wywoływanie odległych procedur
Tak samo jak zapytania SQL na zdalnym serwerze możemy z naszej lokalnej bazy również wywoływać zdalne procedury. Dla zdalnej procedury calc (id IN NUMBER), aby ją wykonać na lokalnym serwerze poprzez połączenie db_link wykonujemy polecenie:
execute calc@db_link(1265);
Rodzaje złączeń |
Nested loops
Algorytm nested loops (petli zagnierzdzonej) polega na pszeszukiwaniu zewnetrznej tabeli i dla kazdego rekordu,ktory ma byc zlaczony przegladana jest wewnetrzna tabela celem znalezienia pasujacych rekordów. Algorytm ten generuje bardzo duzo operacji wejścia-wyjścia, natomiast w dosc niewielkim stopniu obciaza procesor.
Sort-merge
W przypadku algorytmu sort-merge obie tabele, ktore laczymy są odczytywane i sortowane, nastepnie jednoczesnie obie tabele są przegladane od ich poczatku. Podczas tego przegladania rekord z pierwszej tabeli porownywany jest z rekordami znajdujacymi się w poczatkowej czesci drugiej tabeli. Jako, ze obie tabele są posortowane, nie ma problemu, ze stwierdzeniem, ze rekordy do siebie nie pasuja. Proces ten jest powtarzany az do konca tabeli.
Hash join
Algorytm hash join polega na stworzeniu specjalnej funkcji haszujacej za pomocą ktorej nastepuje laczenie rekordów dwoch tabel. Liczba operacji wejścia-wyjścia jest tu najmniejsza ze wszystkich metod złączania. Niestety procesor jest tu najbardziej obciążony.
EXPLAIN PLAN |
Polecenie EXPLAIN PLAN jest wykorzystywane do strojenia zapytań SQL. Jako parametr przyjmuje ono analizowane zapytanie SQL, a jako wynik podaje plan wykonania tego zapytania w zależności od wybranego w systemie optymalizatora. Jest to narzędzie ułatwiające analizę efektów poczynionych zmian w systemie, np. po utworzeniu lub usunięciu indeksów, czy tez tez po wprowadzeniu podpowiedzi do zapytania itp. Wyniki tego polecenia są umieszczane w specjalnej tabeli. Domyślna nazwa tej tabeli jest plan_table. Taka tabele można utworzyć skryptem utlxplan.sql.
Przykłądowe wywołanie tego polecenia ma postać:
EXPLAIN PLAN
SET STATEMENT_ID='Nazwa raportu'
INTO plan_table
FOR
SELECT *
FROM tab1,tab2 where tab1.id=tab2.id
ORDER BY tab1.id;
Wyniki polecenia zapisane są w tabeli plan_table. Aby przeanalizować wyniki zapytania można wykonać poniższe zapytanie:
SELECT SUBSTR(LPAD(' ', 2*( LEVEL-1))||operation,1,30)
|| ' ' || SUBSTR(options,1,15)
|| ' ' || SUBSTR(object_name,1,15)
|| ' ' || SUBSTR(DECODE(id,0, 'Kosz: ' || position),1,12)
"Plan wykonania",
SUBSTR(optimizer,1,10) "Optymalizator"
FROM plan_table
START WITH id=0
AND statement_id='Nazwa raportu'
CONNECT BY PRIOR id=parent_id
AND statement_id='Nazwa raportu'
Dynamiczny SQL |
Do wykonywania dynamicznych instrukcji SQL wykorzystywane jest wyrażenie EXECUTE IMMEDIATE.
Przykład:
EXECUTE IMMEDIATE 'create table tab1(id NUMBER)';
Przykład:
query:='create table :name (id NUMBER)';
tabname:='tab1';
EXECUTE IMMEDIATE query USING tabname;
W powyższym przykładzie do zmiennej przypisaliśmy łańcuch, w którym zamiast :name spodziewamy się zmiennej dynamicznej podanej podczas wywoływania wyrażenia EXECUTE IMMEDIATE zaraz za słowem USING. Ważne jest, że zapytanie może mieć więcej dynamicznych zmiennych. W takiej sytuacji pod pierwszy argument zapytania zostanie podstawiona pierwsza zmienna znajdująca się po klauzuli USING. Pod drugi argument zostanie podstawiona druga zmienna itd.
Przykład:
query:='grant :right ON :object TO :trustee';
right:='INSERT';
object:='table1';
trustee:='user8';
EXECUTE IMMEDIATE query USING right, object, trustee;
Przykład:
query:='update tab1 set col1=100 where col2=:1 RETURNING col2 INTO :2';
EXECUTE IMMEDIATE query USING value1 RETURNING value2;
PL/SQL |
Struktura bloku
Zmienne, stale
Atrybuty zmiennych
Kolekcje
Kursory
Obsługa błędów
Procedury i funkcje
Pakiety
Rekordy
Transakcje
Autonomiczne transakcje
Typy obiektowe
Struktura bloku |
Struktura bloku PL/SQL ma postać :
[DECLARE
deklaracje stałych i zmiennych
BEGIN
blok główny
[EXCEPTION
obsługa wyjątków ]
END;
Zmienne, stale |
Deklaracje zmiennych i stałych używanych w bloku PL/SQL umieszczane są w sekcji DECLARE bloku.
Przykład deklaracji zmiennych może wyglądać następująco:
...
num NUMBER(3);
bln BOOLEAN;
...
Następnie w sekcji BEGIN do zmiennych możemy przypisać wartości :
...
num :=100;
bln :=TRUE;
...
Przykład deklaracji stałych natomiast może wyglądać następująco:
...
num CONSTANT NUMBER(3):=100;
bln CONSTANT BOOLEAN:=TRUE;
...
Atrybuty zmiennych |
Deklarując zmienne w PL/SQL system Oracje daje nam możliwość zadeklarowania pośrednio typ danych, określając, żę dana zmienna będzie takiego same typu jak kolumna w konkretnej bazie, lub jak rekord tabeli lub widoku, lub kursora.
Typy te określa się przez poniższe atrybuty:
%TYPE : Atrybut określający typ zmiennej na typ odpowiadający zadanej kolumnie w tabeli, widoku lub kursora.
Przykład:
value table1.col2%TYPE;
Powyższa składnia oznacza, że zmienna value będzie tego samego typu, co kolumna o nazwie col2 tabeli table1.
%ROWTYPE : Atrybut określający typ zmiennej na typ rekordowy odpowiadający kolumną w tabeli, widoku lub kursorze.
Przykład:
rec1 tab%ROWTYPE;
Przykład określa, że zmienna rec1 będzie typu rekordowego, której pola będą odpowiadały kolumną tabeli tab.
I tak, jeśli tabela tab będzie zawierać kolumnę col1, to do pobrania wartości tej kolumny z rekordu rec1 będziemy używać notacji kropkowej.
Przykład:
colvalue:=rec1.col1;
Kolekcje |
Kolekcja jest uporządkowaną grupą elementów tego samego typu. Rozróżniamy trzy zasadnicze typy kolekcji: VARRAY, tabela zagnieżdżona i tabela INDEX BY.
Kolekcja typu VARRAY
Kolekcja typu VARRAY jest stosowana raczej do kolekcji mniejszych rozmiarów. Przy tego typu kolekcji przy definicji kolekcji wymagane jest podanie maksymalnej jej wielkości. Kolekcje te są zawsze gęste, tzn., ze operacje usuwania elementów nie powoduje powstawania luk w kolekcji.
Składnia definicji:
TYPE nazwa IS {VARRAY | VARYING ARRAY} (rozmiar) OF typ elementu [NOT NULL]
Tabela zagnieżdżona
Kolekcje te są początkowo gęste, lecz podczas usuwania elementów z kolekcji stają sie one rzadkie. W przypadku kolekcji typu TABLE nie podajemy jej rozmiaru. Poniższe typy danych nie być elementami tabel zagnieżdżonych: BINARY_INTEGER, pls_integer, boolean, blob, clob, (kolekcje typu VARRAY), long, long raws, natural, naturaln, nchar, nclob, nvarchar2, obiekty z elementami typu BLOB lub CLOB, positive, positiven, ref coursor, signtype, string, table, varray.
Składnia definicji:
TYPE nazwa IS TABLE OF typ elementu [NOT NULL]
Kolekcja typu INDEX BY
Kolekcje te są uporządkowanymi elementami dostępnymi jedynie w języku PL/SQL, a nie w bazie danych. Są one początkowo rzadkie. Kolekcja ta nie ma ograniczeń co do typu możliwych elementów, jak to ma miejsce w przypadku tabel zagnieżdżonych.
Składnia definicji:
TYPE nazwa IS TABLE OF typ elementu [NOT NULL] INDEX BY BINARY_INTEGER
Przykłądy deklaracji kolekcji
type dates is VARRAY(366) OF DATE;
type list is table of mytab.col2%TYPE;
type v1 is VARRAY of coursor1%ROWTYPE;
type entry list is VARRAY OF somerecord;
Inicjowanie kolekcji
Inicjowanie kolekcji odbywa się poprzez konstruktor systemowy o nazwie odpowiadającej nazwie typy kolekcji. W konstruktorze tym jako parametry możemy podać początkowe elementy kolekcji
Przykład
type Names is VARRAY(60) OF VARCHAR2(30);
namelist1 Names;
namelist2 Names;
namelist1 := Names("Marcin","Zygmunt");
namelist2 := Names();
W powyższym przykładzie zdefiniowaliśmy typ kolekcji Names, następnie zadeklarowaliśmy kolekcje namelist1 oraz namelist2 typu Names. Zmienną namelist1 zainicjowaliśmy dwoma elementami, zmienna namelist2 natomiast zainicjowaliśmy pustym konstruktorem bezparametrowym
Odwoływanie się do elementów kolekcji
Odwołując się do elementów kolekcji używamy nazwy kolekcji wraz z podanym w nawiasach indeksem elementu w kolejce. Indeks ten zawiera się w przedziale od -231 do 231 dla tabel INDEX BY, od 1 do 231 dla tabel zagnieżdżonych oraz od 1 do wartości określonej w deklaracji dla kolekcji typu VARRAY.
Przykład
IF names(1)='Marcin' THEN
names(1)='Marcin K'
END IF;
Porównywanie kolekcji
W związku z tym, że tabele zagnieżdżone i kolekcje typu VARRAY mogą być automatycznie puste kolekcje te możemy tylko przyrównywać do wartości null określającej, że kolekcja jest pusta jak to ma miejsce w poniższym przykładzie.
IF members IS NULL THEN ...
Niestety kolekcje nie mogą być porównywane między sobą. Poniższa sentencja wywoła błąd.
IF coll1=coll2 THEN ...
Przykład wykorzystywania tabel zagnieżdżonych
create type Worker as Object (
name VARCHAR2(30),
firstname VARCHAR2(30));
create type WorkerList as table of Worker;
create table firms(
name VARCHAR2(30),
address VARCHAR2(60),
Workers WorkerList)
NESTED TABLE Workers STORE AS Workers_tab;
Przykład dodania rekordu do tabeli zawierającej kolekcje
insert into firms values ('Nazwa','Adres',
WorkerList(Worker('Jan','Kowalski'),
Worker('Edward','Nowak')));
W ten sposób dodaliśmy do bazy danych nowy rekord z zagnieżdżoną tabelą WorkerList zawierającą dwa wpisy.
Przykład aktualizacji rekordu
declare
new_Workers WorkerList:=WorkerList(
Worker('Jan','Kowalski'),
Worker('Jerzy', 'Kowal'),
Worker('Edward','Nowak'));
begin
update firms set workers=new_Workers where name='Nazwa';
end;
W ten sposób w rekordzie zmieniliśmy całą tabele zagnieżdżoną.
Przypisywanie tabeli zagnieżdżonej do zmiennej
declare
Workers1 WorkerList
begin
select Workers into Workers1 from firms where name='Nazwa';
...
end;
Funkcje TABLE w zapytaniach SQL
Funkcja ta przypisuje kolekcje do tabeli bazy danych (na kolekcjach możemy operować jak na zwykłych tabelach).
Przykład
Insert into TABLE(select Workers from firms where name='Nazwa' values
('Jan','Kowalski');
Powyższe zapytanie dodaje rekord do listy pracowników firmy o podanej nazwie
update TABLE(select Workers from firms where name='Nazwa') set name='Bocian'
where name='Źaba' and first_name='Eliza';
Zmienia nazwisko pani Źaby na Bocian
Uogólniając składnia zwracającą rekordy z tabeli zagnieżdżonej ma postać: TABLE(zapytanie SQL), gdzie:
zapytanie SQL : zapytanie SQL określające o jaką tabelę zagnieżdżoną nam chodzi i jakiej tabeli 'standardowej' jest ona elementem.
Dodatkowo przez klauzulę where możemy zawęzić listę rekordów z tabeli zagnieżdźonej
select * from TABLE(select tabela_zagnieżdżona from tabela_rodzic where kolumna_tabeli_zagnieżdzonej=jakaś_wartość);
Powyższy przykład wyświetla wszystkie rekordy z tabeli zagnieżdżonej, zawężając jednocześnie ilość popranych rekordów poprzez podany warunek.
Standardowe metody kolekcji |
Metody te pozwalają w łatwy sposób operować na kolekcji. Wywołąnie następuje poprzez notacje kropkową: kolejka.metoda[(argument)]
Metody kolekcji nie mogą być wywoływane w zapytaniach SQL. Metody EXTEND i TRIM nie mogą być używane z tabelami typu INDEX BY. Metody EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR i NEXT są funkcjami, EXTEND, TRIM i DELETE są procedurami. Metody EXISTS, PRIOR, NEXT, EXTEND, TRIM i DELETE wywoływane są z argumentami.
W przypadku pustej kolekcji możemy wywołać tylko metodę EXISTS, w innym przypadku zostanie wygenerowany wyjątek COLLECTION_IS_NULL.
COUNT : Zwraca liczbę elementów w kolekcji.
Dla kolekcji typu VARRAY zwraca tą samą wartość co funkcja LAST. W przypadku tabel zagnieżdżonych COUNT jest równoważne LAST tylko przed usunięciem jakiegokolwiek rekordu z kolekcji.
DELETE : usuwa wszystkie elementy kolekcji.
DELETE(i) : usuwa element o indeksie i z kolekcji.
DELETE(i,j) : usuwa z kolekcji typu nested table i index-by table elementy o indeksie z przedziału od i do j.
EXISTS(i) : zwraca wartość logiczną określającą, czy istnieje w kolekcji element na pozycji określonej jako argument.
IF nestedtab.EXISTS(2) then ...
W przypadku podania jako argumentu wartości będącej poza dozwolonym przedziałem zostanie wygenerowany wyjątek SUBSCRIPT_OUTSIDE_LIMIT.
EXTEND : dodaje jeden pusty element do kolekcji.
EXTEND[i] : dodaje i pustych elementów do kolekcji.
EXTEND(i,j) : dodaje i kopii j-tego elementu do kolekcji.
FIRST : jeśli kolekcja jest pusta, funkcja zwraca NULL, w przeciwnym razie zwraca indeks pierwszego elementu kolekcji.
LAST : jeśli kolekcja jest pusta, funkcja zwraca NULL, w przeciwnym razie zwraca indeks ostatniego elementu kolekcji.
LIMIT : W przypadku tabel zagnieżdżonych nie mamy do czynienia z
ograniczeniem ilości rekordów, więc funkcja ta zwraca wartość NULL. Dla kolekcji typu VARRAY funkcja zwraca maksymalną ilość rekordów, jaką może pomieścić ta kolekcja. Wartość tą podaje się zawsze w deklaracji kolekcji typu VARRAY.
PRIOR(i) : zwraca indeks elementu w kolekcji poprzedzający element o indeksie i. Jeśli element nie istnieje zwraca NULL.
NEXT(i) : zwraca indeks elementu w kolekcji następującego po elemencie o indeksie i. Jeśli element nie istnieje zwraca NULL.
Przykład przejścia po wszystkich elementach kolekcji
...
i:=collection.FIRST
while i IS NOT NULL LOOP
...
i:=collection.NEXT(i);
END LOOP
TRIM : usuwa jeden element z końca kolekcji.
TRIM(i) : usuwa i elementów z końca kolekcji (włączając to rekordy usunięte przez metodę DELETE).
Jeżeli n jest większe od wartości zwróconej przez metodę COUNT system wygeneruje wyjątek SUBSCRIPT_BEYOND_COUNT. Osobiście nie polecam korzystać z tej metody. Preferuje metodę DELETE, gdzie mam większą kontrolę przy usuwaniu elementów.
Wyjątki związane z kolekcją |
COLLECTION_IS_NULL : Próba wykonania operacji na pustej kolekcji
NO_DATA_FOUND : Próba wykonania operacji na elemencie kolekcji, który nie istnieje
SUBSCRIPT_BEYOND_COUNT : Próba wykonania operacji na elemencie, którego indeks przewyższa liczbę elementów w kolekcji
SUBSCRIPT_OUTSIDE_LIMIT : Próba wykonania operacji na elemencie, którego indeks jest poza dozwolonym przez system Oracle zakresem (np. indeks o wartości -1)
VALUE_ERROR : Próba wykonania operacji na elemencie o indeksie nie będącym liczbą (np. wartość NULL)
Wiązania masowe |
Jest to mechanizm stosowany przy kolekcjach pozwalający w zdecydowanym stopniu ograniczyć komunikację pomiędzy PL/SQL, a silnikiem SQL. Wyobraźmy sobie sytuację, kiedy musimy duży blok danych dodać lub pobrać z tabeli. Najprostrzy sposób dodania takiego bloku, to zastosowanie pętli FOR i instrukcji SQL - INSERT. W takim przypadku jednak przy każdym przejściu pętli następuje odwołanie do silnika bazy danych, co w znacznym stopniu wydłuża czas wykonania pętli.
Mechanizm wiązań masowych pozwala nam na użycie takiej pętli, przy której następuje tylko jedno połączenie z silnikiem bazy danych.
Do jednorazowego wykonania zapytań SQL z wykorzystaniem kolekcji używamy pętli FOR ALL
Przykład
declare
type NamesList IS VARRAY(20) OF NUMBER;
Names NamesList:= NamesList('Kowal','Nowak','Bocian');
Begin
...
FORALL i IN Names.FIRST..Names.LAST
INSERT INTO Namestab VALUES(Names(i));
END;
W powyższym przykładzie pętla FOR ALL wykona się 3 razy przy jednorazowym połączeniu z silnikiem SQL.
Pętla FORALL, a odwoływanie transakcji
W przypadku, kiedy w pętli FOR ALL jakaś instrukcja SQL wygeneruje błąd, który nie jest przechwytywany wszystkie instrukcje w transakcji są cofane. Jeśli natomiast błąd przechwycimy poprzednie instrukcje z pętli FORALL (te przed wygenerowanie wyjątku) nie są cofane. Dodatkowo następuje zatrzymanie pętli FORALL.
%BULK_ROWCOUNT
Jest to atrybut kursora SQL tworzony przez system podczas wykonywania zapytań SQL. Jest on wykorzystywany w pętli FORALL i określa indeks zapytania aktualnie wykonywanego w pętli. Wykorzystywany tylko i wyłącznie w zapytaniach typu UPDATE i DELETE. W przypadku zapytań typu INSERT wartość ta zawsze wynosi 1.
Ograniczenia pętli FORALL
Pętli FORALL można używać tylko w programach server-side (nie client-side).
W zapytaniach INSERT, UPDATE, DELETE musi nastąpić przynajmniej jedno odwołanie do kolekcji.
Wszystkie elementy w kolekcji muszą istnieć.
Indeks kolekcji w zapytaniu nie może być wyrażeniem.
W zapytaniach SQL i kolekcjach złożonych typów nie możemy się odwoływać do kolekcji, tylko bezpośrednio do danego atrybutu elementu kolekcji.
...
FORALL I IN Workers.FIRST..WORKERS.LAST
INSERT INTO WorkersTab VALUES(Workers(I).name, Workers(I).firstname)
END;
Atrybut %BULK_ROWCOUNT nie może być przypisany do innych kolekcji. Nie może on być również podawany jako parametr wywoływanych podprogramów.
Klauzula BULK_COLLECT
Klauzula BULK_COLLECT jest odpowiednikiem pętli FORALL działającym w odwrotnym kierunku. Pozwala ona na pobranie danych do kolekcji z silnika za pomocą jednej transakcji pomiędzy silnikiem, a blokiem PL/SQL.
Przykład 1
declare
type Collecttab1 IS TABLE OF tab.column1%TYPE;
type Collecttab2 IS TABLE OF tab.column2%TYPE;
Collect1 Collecttab1
Collect2 Collecttab2
begin
select column1, column2 BULK COLLECT INTO Collect1, Collect2 from tab;
...
END;
Przykład 2
CREATE TYPE POINT AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE grids (num NUMBER, pointer POINT);
INSERT INTO grids VALUES (1, POINT(0,0));
INSERT INTO grids VALUES (2, POINT(20,60));
declare
type Points IS TABLE OF POINT;
points Pointstab;
begin
select pointer BULK COLLECT INTO points from grids;
...
END;
W powyższym przykładzie następuje zapisanie z tabeli grids do kolekcji points par liczb (0,0) oraz (20,60)
Przykład 3
declare
type NameList IS TABLE OF tab.column1%TYPE;
coursor c1 IS select name from tab;
names NameList
...
begin
OPEN c1;
FETCH c1 BULK COLLECT INTO names;
...
END;
Klauzula LIMIT
Klauzula ta jest stosowana w wyrażeniach FETCH z opcją BULK COLLECT i pozwala ona ograniczyć liczbę rekordów pobranych z bazy. Jej składnia wygląda następująco:
FETCH ... BULK COLLECT INTO ... LIMIT liczba_rekordów
Liczba rekordów może być stała, zmienna, lub wyrażeniem.
Zwracanie BULK COLLECT
Sentencja BULK COLLECT może być również używana w klauzuli RETURNING INTO zapytań typu INSERT, UPDATE lub DELETE.
Przykład
declare
type NumList IS TABLE OF tab.col2%TYPE;
nums NumList;
begin
delete from tab1 where col1=10 returning col2 BULK COLLECT INTO nums;
...
END;
Powyższy przykład poza usunięciem konkretnych rekordów spowoduje zapisanie do kolekcji nums wartości kolumny col2 z tabeli tab1, gdzie kolumna col1 ma wartość 10.
Ograniczenia klauzuli BULK COLLECT
Klauzuli te można używać tylko w programach server-side (nie client-side).
Zmienne zapisywane przez tę klauzulę muszą być kolekcją.
Nie można używać sentencji FETCH w stosunku do kursora z klauzulą BULK COLLECT i kolekcją rekordów.
Przykład
declare
type RecsTab IS TABLE OF tab.col1%ROWTYPE;
Recs RecsTab;
coursor c1 is select col1 from tab1;
begin
OPEN c1;
FETCH c1 BULK COLLECT INTO Recs;-- niepoprawne
...
END;
Klauzula RETURNING INTO nie może być używana ze złożonymi danymi (np. obiektami).
Połączenie klauzuli FORALL i BULK COLLECT razem
Przykład
...
FORALL i IN depts.FIRST..depts.LAST
delete from emp where empno=depts(i) RETURNING empno BULK COLLECT INTO enums;
...
Nie można używać sentencji wyrażenia select ... BULK COLLECT w pętli FORALL.
Kursory |
Atrybuty kursorów |
%FOUND
Atrybut ma wartość NULL po otworzeniu kursora, a przed przeczytaniem jego pierwszego wiersza. , TRUE - jeśli ostatnie pobranie wiersza zakończyło się powodzeniem, FALSE - jeśli nie powiodło się ostatnie pobranie wiersza.
Przykład:
IF Coursor1%FOUND THEN ...
%ISOPEN
Zwraca wartość logiczną określającą, czy kursor jest otwarty.
%NOTFOUND
Logiczne przeciwieństwo %FOUND.
Przykład:
LOOP
FETCH c1 INTo variable;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
%ROWCOUNT
Określa ilość dotychczas pobranych rekordów poprzez klauzulę FETCH. Jeśli dany kursor nie jest otworzony generuje wyjątek INVALID_COURSOR.
Przykład:
IF Coursor1%ROWCOUNT>10 THEN ...
Systemowy kursor SQL |
Kursor ten jest tworzony przez system Oracle do obsługi zapytań wygenerowanych w bloku PL/SQL.
%FOUND
Atrybut ten określa, czy jakiekolwiek rekordy tabeli wzięły udział w ostatnim zapytaniu SQL typu INSERT, UPDATE lub DELETE.
Przykład:
...
DELETE from tab1 WHERE col1=value
IF SQL%FOUND THEN ...
%NOTFOUND
Logiczne przeciwieństwo %FOUND.
%ROWCOUNT
Określa ile rekordów wzięło udział w ostatnim poleceniu typy INSERT, UPDATE lub DELETE.
Obsługa błędów |
W bloku PL/SQL aby ułatwić obsługę wyjątkowych sytuacji istnieje podblok EXCEPTION definiujący zachowanie w przypadku wszelkiego rodzaju błędów zaistniałych w bloku PL/SQL.
Przykład:
DECLARE
div_value NUMBER;
BEGIN
Select col1/col2 into div_value from tab where id=8;
INSERT INTO stats(id,div) Values (8,div_value);
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO stats(id,div) Values (8,NULL);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
Ważną cechą wyjątków jest to, że nie ma potrzeby sprawdzać, czy nastąpił jakikolwiek błąd po każdym zapytaniu SQL. Możemy wykonać kilka zapytań SQL, a następnie w podbloku EXCEPTION określamy zachowanie w przypadku wystąpienia określonych zdarzeń podczas wykonywania wcześniejszych zapytań.
W przypadku obsługi kilku błędów poprzez identyczny kod w podbloku EXCEPTION za słowem kluczowym WHEN nazwy wyjątków możemy łączyć operatorem logicznym OR, np:
WHEN exception1 OR exception2 OR exception3 THEN ...
Do obsługi błędów nieopisanych z nazwy w bloku EXCEPTION używamy znacznika OTHERS, jak to ma miejsce w przykładzie powyżej, co oznacza obsługę pozostałych wyjątków.
Deklarowanie własnych wyjątków
SQLCODE i SQLERRM
Funkcja raise_application_error
Deklarowanie własnych wyjątków |
Aby zadeklarować i obsłużyć własny wyjątek należy w pierwszej kolejności w bloku DECLARE zadeklarować włąsny wyjątek poprzez jego nazwę:
nazwa_wyjątku EXCEPTION;
W bloku głównym aby wygenerować wyjątek należy użyć polecenia RAISE, gdzie jako parametr podajemy nazwe zadeklarowanego wyjątku. Następnie w bloku EXCEPTION należy zdefiniowany wyjątek obsłużyć poprzez :
WHEN nazwa_wyjątku THEN ...
Przykład:
DECLARE
bad_number EXCEPTION;
my_number NUMBER;
BEGIN
...
Select col1 into my_number from tab where col1=3;
IF my_number<1 THEN
RAISE bad_number'
END IF;
EXCEPTION
WHEN bad_number THEN
ROLLBACK;
END;
SQLCODE i SQLERRM |
Przy obsłudze błędów w bloku PL/SQL mamy do dyspozycji dwie wbudowane funkcje: SQLCODE i SQLERRM.
Funkcja SQLCODE zwraca numer błędu związany z wykonanymi ostatnio operacjami SQL. Wartość 0 oznacza operacje zakończoną sukcesem.
Funkcja SQLERRM zwraca opis błędu o numerze podanym jako parametr.
Ważne jest, że funkcji tych nie można używać bezpośrednio w wyrażenia SQL. Wartość zwracane przez te funkcje należy przypisać do zmiennych i następnie dopiero te zmienne wykorzystać w wyrażeniach SQL.
Przykład:
DECLARE
sql_code NUMBER;
sql_errm VARCHAR2(200);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
sql_code:=SQLCODE;
sql_errm:=SUBSTR(SQLERRM,1,100);
Insert Into logs Values(sql_code,sql_errm);
END;
Do przypisania kodu błedu do zdefiniowanego przez użytkownika wyjątku stosujemy wyrażenie PRAGMA EXCEPTION_INIT w bloku DECLARE.
Przykład:
DECLARE
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(my_exception,-80) ;
BEGIN
...
EXCEPTION
WHEN my_exception THEN
...
END;
Funkcja raise_application_error |
Aby ułatwić komunikację aplikacji z użytkownikiem system Oracle udostępnia nam funkcję raise_application_error o składni:
raise_application_error(error_number, error_message[,TRUE | FALSE]);
Funkcja ta może być wygenerowana wyłącznie z podprogramu i zwraca do aplikacji błąd użytkownika z jego opisem.
Argumenty tej procedury to :
error_number : kod błędu z zakresu -20999 do -20000
error_message : Opis błędu (do 2048 znaków)
Trzeci parametr jest opcjonalny. Jeśli przyjmie on wartość TRUE, błąd jest umieszczany w kolejce wraz z poprzednimi błędami. W przypadku wartości FALSE (wartość domyślna) błąd zastępuje poprzednie błędy.
Przykład:
Create procedure raise_salary(tabid NUMBER, amount NUMBER) AS
curr_sal NUMBER;
BEGIN
Select sal into curr_sal From tab Where id=tabid;
IF curr_sal IS NULL THEN
raise_application_error(-20101,'Salary is missing');
ELSE
Update tab set sal=curr_sal+amount Where id=tabid;
END IF;
END raise_salary;
Procedury i funkcje |
Podprogramy są normalnymi blokami PL/SQL, które mogą przyjmować argumenty i zwracać wartości. Struktura podprogramu składa się z takich samych podbloków, jak zwykły blok PL/SQL tj., z części deklaracyjnej, części głównej i części obsługi błędów.
Składnia tworząca procedurę ma postać
CREATE [OR REPLACE]
PROCEDURE nazwa_procedury [(param1 [, param2]...)]
[AUTHID { DEFINER | CURRENT_USER] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[deklaracje lokalne]
BEGIN
...
[EXCEPTION
...]
END [nazwa_procedury];
Składnia parametru ma postać:
[IN|OUT [NOCOPY]] typ_parametru
[{:= | DEFAULT} wartość początkowa]
Funkcje są swego rodzaju odmianą procedur, z tym, że funkcja zwraca wartość.
Składnia tworząca funkcję ma postać
CREATE [OR REPLACE]
FUNCTION nazwa_funkcji [(param1 [, param2]...)] return typ_zwracany
[AUTHID { DEFINER | CURRENT_USER]
[PARALLEL_ENABLE]
[DETERMINISTIC] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[deklaracje lokalne]
BEGIN
...
return zwracana_wartość
[EXCEPTION
...]
END [nazwa_funkcji];
Przykład
CREATE OR REPLACE function is_ok(value REAL) return boolean is
min_value real=1.0;
max_value real=10.0;
return (value>=min_value) and (value<=mx_value);
end is_ok;
Typy argumentów:
IN argument jest przekazywany do podprogramu i tam jest traktowany jak stała, w związku z czym w bloku podprogramu argumentowi nie możemy przypisać wartości.
OUT argument w bloku podprogramu jest traktowany jak zwykła zmienna lokalna, do której można przypisywać wartości. Zmiany wewnątrz podprogramu są widoczne w bloku wywałującym podprogram. Argument typu OUT musi być zmienną, nie może to być stała lub wyrażenie.
IN OUT jest to połączenie argumentu IN i OUT
NOCOPY jest to rozszerzenie argumentu IN, OUT i IN OUT, określające że argument jest przekazywany przez nazwy, a nie przez wartość. Argument ten jest stosowany do dużych obiektów ponieważ zaoszczędza on czas potrzebny na kopiowanie danych pomiędzy blokiem głównym, a blokiem podprogramu i odwrotnie jak to ma miejsce w przypadku przekazywania argumentu przez wartość
Wywoływanie podprogramów:
W systemie Oracle mamy dwa możliwe rodzaje wywołań podprogramów różniące się sposobem przekazywania argumentów:
Notacja pozycyjna
Jest to notacja stosowana w większości języków programowania, gdzie pierwszy argument wywołania jest przyporządkowany do pierwszego argumentu definicji. Kolejne argumenty są przyporządkowane analogicznie.
Przykład
definicja procedury : procedure credit (acct_no INTEGER, amount REAL) IS ...
wywołanie : credit (acct, amnt);
W powyższym wywołaniu zmienna acct przypisana jest argumentowi acct_no, zmienna amnt argumentowi amount.
Notacja nazewnicza
Przy tej notacji nie jest istotna kolejność argumentów w wywołaniu i definicji, ponieważ w wywołaniu jasno określamy jakiemu argumentowi definicji przypisujemy dany argument wywołania.
Przykład
definicja procedury : procedure credit (acct_no INTEGER, amount REAL) IS ...
wywołanie : credit (acct_no=>acct, amount=>amnt);
wywołanie : credit (amount=>amnt, acct_no=>acct,);
W powyższym przykładzie widzimy iż nie jest istotna przy wywołaniu kolejność argumentów w procedurze i równie dobrze możemy argumenty w wywołaniu podać w odwrotnej kolejności znając wyłącznie nazwy argumentów z definicji procedury.
Wartości domyślne argumentów podprogramu
Wartości domyślne argumentów podprogramu pozwala nam określić wartość danego argumentu, w przypadku, gdy spodziewamy się, że dana wartość będzie bardzo często podawana w wywołaniu. W takim przypadku, jeśli nie podamy argumentu w wywołaniu, zostanie mu przypisana wartość domyślna. W przypadku, kiedy w definicji podprogramu istnieją równocześnie argumenty z wartościami domyślnymi. jak również i bez nich, najlepiej wtedy stosować notację przez nazwę.
Przykład
deklaracja procedury
procedure create_dept (dep_name VARCHAR2 DEFAULT 'nazwa',
new_loc VARCHAR2 DEFAULT 'lokal') IS ...
możliwe wywołania
create_dept; : obu argumentom zostanie przypisana wartość domyślna
create_dept('NAME'); : pierwszemu argumentowi zostanie przypisana wartość 'NAME'
create_dept(new_loc=>'LOC'); : argumentowi o nazwie new_loc zostanie przypisana wartość 'LOC'
Pakiety |
Pakiet jest obiektem grupującym logicznie połączone typy PL/SQL, podprogramy tworząc w ten sposób jeden moduł do obiektów którego możemy się dowoływać poprzez notację kropkową:
x:=nazwa_pakietu.zmienna_zadeklarowana_w_pakiecie;
Pakiet skłąda się z dwóch części. W pierwszej części (część specyfikacji) znajdują się deklaracje dostępne dla użytkownika (publiczne). W części BODY następujepełna definicja obiektów prywatnych. Część BODY nie jest wymagana.
Przykład:
Create Or Replace PACKAGE users_package AS
PROCEDURE adduser(name VARCHAR2, first_name VARCHAR2);
PROCEDURE deluser(userid NUMBER);
END users_package;
Create Or Replace PACKAGE BODY users_package AS
PROCEDURE adduser(name VARCHAR2, first_name VARCHAR2) IS
BEGIN
INSERT INTO users VALUES (users_seq.NEXTVAL, name, first_name);
END adduser;
PROCEDURE deluser(userid NUMBER) IS
BEGIN
DELETE FROM users WHERE id=userid;
END deluser;
PROCEDURE proc1 IS
BEGIN
...
END proc1;
END users_package;
W powyższym przykładzie zdefiniowaliśmy trzy prodecury w pakiecie, z tym że, tylko dwie z nich zadeklarowalismy jako publiczne (adduser i deluser) ponieważ tylko ich deklaracje znajdują się w części specyfikacji. Trzecia procedura proc1 nie może być wywoływana z zewnątrz przez uzytkowników. Może być jedynie wywoływane przez inne podprogramy z tego samego pakietu.
Ważną cechą pakietu jest ich wydajność. Kiedy następuje wywołanie podprogramu z pakietu po raz pierwszy cały pakiet jest ładowany do pamięci. Przy kolejnych odwołaniach do pakietu dane pobierane są z pamięci, a nie z dysku co redukuje częstotliwość operacji dyskowych.
Rekordy |
Rekord jest złożonym typem danych, na który składają się podstawowe typy danych unikalnie nazwane w ramach tego rekordu.
Składnia wyrażenia definiującego typ rekordowy ma postać
TYPE nazwa_typu IS RECORD (pole [,pola2,...]);
gdzie pole ma postać:
nazwa_pola typ_danych [[NOTNULL]{:= | DEFAULT } wartość]
Typem danych w tym przypadku może być dowolny typ danych poza REF CURSOR.W przeciwieństwie do typów VARRAY i tabel zagnieżdzonych typ rekordowy nie może byc tworzony i magazynowany w bazie.Przykładowa definicja typu rekordowego :
TYPE workerrec IS RECORD (
id NUMBER,
first_name VARCHAR(20),
sur_name VARCHAR(30));
Pole typu rekordowego poza typami standardowymi może być typu obiektowego, kolekcją, czy też może on być innym typem rekordowym. Odwoływanie się do poszczególnych pól rekordu odbywa się za pomocą notacji kropkowej:
tmp_value=rekord.pole
To samo się dotyczy jeśli wywołyjemy funkcję zwracającą rekord i chcemy pobrać wraz z z wywołaniem funkcji jedno z pól zwracanego rekordu:
tmp_value=nazwa_funkcji().pole;
Transakcje |
Transakcją w bloku PL/SQL możemy nazwać grupę zmian wygenerowanych podczas sesji użytkownika, które to zmiany możemy w pewnych okolicznościach anulować, bądź je potwierdzać. Do obsługi transakcji wykorzystujemy dwa wyrażenia : COMMIT, ROLLBACK, SAVEPOINT oraz ROLLBACK TO.
COMMIT
Wyrażenie to kończy bieżącą transakcje z potwierdzeniem wszystkich zmian dokonanych podczas jej trwania.
ROLLBACK
Wyrażenie to kończy bieżącą transakcje odwołując wszystkie zmiany dokonane podczas jej trwania.
SAVEPOINT
Nazywa i określa charakterystyczny punkt w transakcji umożliwiając w ten sposób wycofanie tylko części transakcji za pomocą wyrażenia ROLLBACK TO.
ROLLBACK TO
Kończy bieżącą transakcje odwołując wszystkie zmiany dokonane podczas transakcji, które nastąpiły po ustawieniu punktu transakcji podanego jako parametr.
Przykład:
BEGIN
UPDATE ...
DELETE ...
SAVEPOINT mypoint
INSERT INTO ...
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO mypoint
END;
W powyższym przykładzie blok SQL wykonuje dwie instrukcje SQL (UPDATE oraz INSERT), następnie ustawia punkt transakcji o nazwie mypoint. W kolejnej linii wykonuje polecenie INSERT. W podbloku EXCEPTION określamy, że kiedy zostanie wygenerowany wyjątek DUP_VAL_ON_INDEX (tj. przy próbie dodania do tabeli rekordu o wartości kolumny indeksowanej, która już istnieje w tabeli) następuje wycofanie tylko tych instrukcji, które wystąpiły po ustawieniu punktu transakcji mypoint. W tym przypadku będzie to instrukcja INSERT. Wszystkie instrukcje wykonane przed ustawieniem punktu transakcji będą potwierdzone.
Autonomiczne transakcje |
Autonomiczna transakcja jest neizależną transakcją uruchomioną wewnątrz innej. W swoim działaniu autonomiczna transakcja zawiesza transakcję, która ja wywołała, wykonuje włąsne instrukcje, następnie je potwierdza lub wycofywuje i zwraca kontrolę do transakcji która ja wywołała. Definicja autonomicznej transakcji następuje poprzez dyrektywę PRAGMA AUTONOMOUS_TRANSACTION dla bloku PL/SQL, funkcji lub procedury.
Przykład:
create procedure proc1(id INTEGER) as
PRAGMA AUTONOMOUS_TRANSACTION;
...
BEGIN
...
END;
Autonomiczną transakcją nie możemy oznaczyć całego pakietu. Należy to zrobić dla poszczególnych podprogramów pakietu
Słownik bazy danych |
ALL_ALL_TABLES : Wszystkie dostępne tablice
ALL_CATALOG : Wszystkie dostępne tablice, perspektywy, synonimy, sekwencje
ALL_COL_COMMENTS : Wszystkie dostępne komentarze kolumn
Typy obiektowe |
Typy obiektowepozwalają na łatwe zarządzanie dużymi systemami informatycznymi dając możliwość tworzenia komponentó w sposób modułowy. Obiekty są złożonymi danymizawierającymi inne typy danych (rónież inne typy obiektowe). W ciele obiektu również mogą się znajdować procedury i funkcje. Struktura typu obiektowego skłąda się z dwóch części tak samo jak to ma miejsce w strukturze pakietów. Pierwsza część zawiera listę publicznych pół i metod dostępnych na zewnątrz obiektu. Druga część zawiera implementację tych metod. Metoda jest zwykłym podprogramem (funkcją lub procedurą) poprzedzonym słowem kluczowym MEMBER lub STATIC. Składnia MEMBER oznacza, że metoda jest związana z obiektem stworzonym na podstawie danego typu obiektowego i wywoływana poprzez nazwę tego obiektu, np.:
nazwa_obiektu.nazwametody();
Składnia STATIC oznacza, że jest to metoda statyczną z danym typem obiektowym, co oznacza, że żeby wywołać nie ma potrzeby tworzyć obiektu, żeby taką metodę wykonać.
Przykład:
nazwa_typu_obiektowego.nazwa_metody();
Przykład obiektu:
create TYPE Stack as Object(
top INTEGER,
MEMBER function full return Boolean,
MEMBER procedure push (n IN INTEGER)
);
create TYPE BODY Stack AS
MEMBER procedure push (n IN INTEGER) IS
BEGIN
IF NOT full Then
top:=top+1;
END push;
END;
Każda metoda typu MEMBER ma możliwość obsługi wbudowanego parametru o nazwie SELF określającego obiekt, z którego ta metoda została wywołana. Tak jak to ma miejsce w przypadku pakietów metody mogą gyć przeciążane, tj. metody mogą mieć taką samą nazwę rózniącą się tylko parametrami wywołania
.Do pól, jak i do metod obiektu odwołujemy się poprzez notacje kropkową:
ale:=person.age;
Person.changeName('Owak');
Obiekty mogą być magazynowane w tabelach za pomocą zapytania:
CREATE TABLE nazwa_tabeli OF typ_obiektu;
Tabele te można wykorzystywać w standardowych zapytaniach SQL. Poza tym w zapytaniach SQL można wykorzystywać funkcje VALUE zwracającą zmienną obiektową powstałą z obiektu współzależnego podanego jako parametr.
DECLARE
Person1 Person;
BEGIN
Select VALUE(pers) INTO Person1 from Person pers where pers.name='Nowak';
END;
Konstruktor obiektu
Porównywanie obiektów
Wskaźniki na obiekty
Konstruktor obiektu |
Każdy obiekt posiada konstruktor, czyli metodę (w przypadku Oracle metoda ta jest tworzona przez system), którą tworzymy instancje obiektu. Konstruktor ma taką samą nazwę jak typ obiektu. Parametrami konstruktora są zmienne o takiej samej nazwie i typie jak pola obiektu i ułożone w tej samej kolejności.
Przykład:
Person person:=Person('Jan','Kowalski','01121971');
Porównywanie obiektów |
Porównywanie obiektów tego samego typu odbywa się za pomocą funkcji MAP i ORDRER. W obiekcie może być tylko jedna metoda oznaczona takimi znacznikami MAP lub ORDER. Nigdy nie mogą występować oba te znaczniki naraz. Metody te są wykorzystywane do sortowania obiektów. Metoda ze znacznikiem MAP w swoim działaniu zwraca wartość, która może charakteryzować położenie obiektu na liście względem innych obiektów. Dla przykładu dla obiektu określającego osobę metoda ta może zwrócić jego pole określające pesel osoby. W ten sposób posortujemy obiekty wg numeru pesel.
Przykład:
Create TYPE Person AS Object (
Pesel INTEGER,
MAP MEMBER FUNCTION compare RETURN INTEGER,
...
);
Create TYPE Body Person AS
MAP MEMBER FUNCTION compare RETURN INTEGER IS
BEGIN
RETURN Pesel;
END compare;
END:
...
Metoda typu ORDER jako parametr przyjmuje drugi obiekt tego samego typu i jak to ma miejsce w typowych metodach porównawczych zwraca -1 jeśli obiekt jest "mniejszy" od obiektu podawanego jako parametr, 1 jeśli jest "większy" i 0 jeśli obiekty są równe. Określenie większy i mniejszy jest zależne od programisty. Dla naszego przykładowego obiektu Person nasza metoda porównawcza typu ORDER może mieć postać:
ORDER MEMBER function compare (other Person) RETURN INTEGER IS
BEGIN
IF pesel
ELSEIF pesel>other.pesel THEN RETURN 1;
ELSE RETURN 0;
END IF:
END;
Warto tu zauważyć iż mając na względzie kwestie wydajnościowe lepiej jest używać funkcji porównującej typu MAP.
Wskaźniki na obiekty |
Do zaznaczenia, że dana zmienna jest wskaźnikiem na obiekt lub pole obiektu jest typu wskaźnikowego stosujemy słowo kluczowe REF przed nazwa zmiennej lub pola.
Przykład:
-- Zmienna typu wskaźnikowego
DECLARE pobject REF MyObject;
-- Pole obiektu typu wskaźnikowego
CREATE TYPE Person AS Object (
firstname VARCHAR2(20),
surname VARCHAR2(20),
...
adress REF Adres,
...
);
Ważne jest iż w przypadku wskaźników nie możemy bezpośrednio odwoływać się do pól typu wskaźnikowego. W pierwszym etapie musimy dokonać dereferencji wskaźnika za pomocą funkcji DEREF.
Przykład:
-- wskaźnik na obiekt Person
DECLARE
pobject REF Person;
-- obiekt Person
object Person;
name VARCHAR2(40);
BEGIN
...
object:=DEREF(pobject);
name:=object.name;
Funkcja REF
Funkcja mająca odwrotne działanie do funkcji DEREF jest funkcja REF zwracająca referencje obiektu. Poniższy przykład dodaje do tabeli referencji obiektów Person kolejne pozycje:
INSERT INTO person_refs Select REF(pers) FROM persons pers;
Przykład:
DECLARE
pPerson REF Person;
name VARCHAR(40);
BEGIN
Select REF(pers), pers.name INTO pPeron,name FROM persons pers WHERE pers.name='Kowalski';
...
END;
Rozważmy tabele, której jedna z kolumn zawiera wskaźniki na obiekt zapisane w innej tabeli. Jeśli teraz skasujemy obiekt z tabeli obiektów, to w takiej sytuacji ewentualny wskaźnik na ten obiekt z pierwszej tabeli nie będzie połączony z konkretnym obiektem. Do stwierdzenia, czy powyższa sytuacja ma miejsce wykorzystujemy sentencje IS DANGLING.
Przykład:
Delete from tab where col_ref IS DANGLING;
Strojenie bazy |
Pakiet DBMS_STATS |
Do generowania statystyk dla optymalizatora kosztowego wykorzystuje się procedury pakietu DBMS_STATS. Optymalizator kosztowy na podstawie bieżących statystyk generuje plan wykonania zapytań SQL. Ważne jest, aby nie zaniedbać generowania tych statystyk,aby danie w nich się znajdujące odzwierciedlały aktualna charakterystykę obiektów bazy danych. Ogólnie pakiet DBMS_STATS służy do zarządzania tymi statystykami. Można powiedzieć, że jest to swego rodzaju alternatywa dla polecenia ANALYZE. Do utworzenia tabeli na potrzeby statystyk można użyć procedury CREATE_STAT_TABLE.
Parametry tej procedury, to:
ownname : nazwa schematu, w którym będą tworzone statystyki
stattab : nazwa tabeli
tblspace : nazwa przestrzeni tabel. Jeśli parametr ten nie zostanie podany przyjmuje się tu nazwę domyślnej przestrzeni tabel użytkownika.
Przykład:
DBMS_STATS.CREATE_STAT_TABLE('scott','stats_table');
Do zbierania statystyk należy użyć procedury GATHER_TABLE_STATS pakietu DBMS_STATS.
Parametrami tej funkcji są:
ownname : nazwa właściciela obiektu
tabname : nazwa analizowanego obiektu
partname : nazwa partycji analizowanego obiektu
estimate_percent : procent rekordów, które maja być odczytane podczas oceniania statystyk
block_sample : opcja określająca, czy pobieramy cale bloki zamiast pojedynczych rekordów
method_opt : dodatkowe opcje zbierania statystyk
degree : stopień zrównoleglenia
granularity : stopień dokładności podczas zbierania statystyk
cascade : parametr określający, czy zostaną zebrane statystyki indeksów
stattab : nazwa tabeli, w której zbierane są statystyki
statid : identyfikator statystyk
statown : nazwa schematu, w którym są przechowywane statystyki.
Przykład:
DBMS_STATS.GATHER_TABLE_STATS ('scott','tab1',1, stattab=>'stats_table');
Pakiet STATSPACK |
STATSPACK jest jednym z kilku mechanizmów Oracle pozwalający na badanie wydajności bazy danych. Można go uznać za pewnego rodzaju udoskonalenie skryptów UTLBSTAT i UTLESTAT. Poza informacjami generującymi przez te skrypty pakiet STATSPACK dysponuje dodatkowymi informacjami.
W celu zainstalowania pakietu należy uruchomić skrypt spcreate.sql znajdujący się w standardowym katalogu ORACLE_HOME/rdbms/admin/.
Skrypt ten tworzy specjalnego użytkownika PERFSTAT wraz z obiektami potrzebnymi do badania wydajności i za pomocą tego pakietu. Ze względu na bezpieczeństwo zaraz po uruchomieniu skryptu spcreate.sql należy zmienić hasło użytkownika PERFSTAT, które standardowo ustawiono na PERFSTAT. Wszystkie operacje za pomocą tego pakietu dalej powinny być wykonywane przez użytkownika PERFSTAT.
Za pomocą tego pakietu generowane są specjalne migawki zawierające informacje wydajnościowe opisujące stan bazy podczas generowania tej migawki. Możemy generować kilka takich migawek, a następnie za pomocą pakietu STATSPACK możemy porównywać różnice pomiędzy migawkami wydajnościowymi zebranymi w rożnym czasie. Dzieje się tak dlatego, że każda migawka ma swój własny identyfikator, który w tabelach wydajnościowych utworzonych skryptem spcreate.sql grupuje informacje związane z jedna migawka. Aby wygenerować migawkę należy uruchomić procedurę SNAP pakietu STATSPACK:
execute STATSPACK.SNAP;
Wszystkie parametry związane z generowaniem tej migawki można modyfikować procedura MODIFY_STATSPACK_PARAMETER w przypadku, kiedy nie odpowiadają nam domyślne wartości tych parametrów. Parametrami tymi są:
i_snap_level : poziom migawki
i_ucomment : komentarz do migawki
i_executions_th : próg liczby wykonań
i_disc_reads_th : próg operacji odczytu z dysku
i_parse_cals_th : próg liczby parsowań
i_bufer_gets_th : próg operacji odczytu z bufora
i_sharable_mem_th : próg wykorzystania wspólnej pamięci
i_version_count_th : próg dla liczby wersji instrukcji SQL
i_sesion_id : identyfikator sesji, w przypadku, jeśli statystyki dotyczy konkretnej sesji
i_modify_parameter : wartość logiczna TRUE lub FALSE określa, czy zmiana parametru ma dotyczyć również kolejnych migawek.
Przykład:
execute STATSPACK.MODIFY_STATSPACK_PARAMETER( i_snap_level->4, i_modify_parameter->TRUE);
Aby wygenerować raport należy uruchomić skrypt spreport.sql. Ważne jest, aby przed uruchomieniem tego skryptu dokonać analizy schematu STATSPACK za pomocą polecenia:
execute DBMS_UTILITY_SCHEMA('PERFSTAT','COMPUTE');
W celu usunięcia starych niepotrzebnych danych statystycznych należy uruchomić skrypt sppurge.sql. Innym sposobem usunięcia niepotrzebnych danych znajdujących się w tabelach wydajnościowych jest obcięcie tabel za pomocą skryptu sptrunc.sql. W celu odinstalowania pakietu STATSPACK należy wykonać skrypt spdrop.sql.
Śledzenie zapytań - TKPROF |
Często w celu poprawiania wydajności aplikacji analizuje się jakie zapytania SQL są generowane w systemie i jak one obciążają system. Dokonuje się tego poprzez włączenie monitorowania instancji, czy tez konkretnej sesji. Śledzenie powoduje zbieranie informacji o liczbie parsowań, wykonań i pobrań.
Ze śledzeniem zapytań SQL związane są następujące parametry instancji:
TIMED_STATISTICS : jeśli ten parametr ustawimy na TRUE w danych statystycznych znajda się statystyki czasowe.
MAX_DUMP_FILE_SIZE : parametr określający maksymalna długość pliku śladu.
USER_DUMP_DEST : parametr określający położenie plików śladu. Domyślnie jest położenie zrzutów systemu operacyjnego.
Włączenie śledzenia bieżącej sesji odbywa się poprzez polecenie: ALTER SESSION SET SQL_TRACE = TRUE; lub poprzez procedurę DBMS_SESSION.SET_SQL_TRACE.
Aby włączyć śledzenie sesji innej niż bieżąca należy znać informacje o tej zewnętrznej sesji. Mając nazwę użytkownika tej zewnętrznej sesji informacje te możemy pobrać za pomocą polecenia:
SELECT sid, serial#, osuser FROM v$session WHERE osuser = 'scott';
Teraz mając te dane możemy włączyć lub wyłączyć śledzenie dla tej sesji za pomocą polecenia:
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid, serial#, TRUE );
Dwa pierwsze parametry są informacjami pobranymi z poprzedniego zapytania, trzeci parametr przyjmuje wartości TRUE lub FALSE i określa, czy włączamy,czy wyłączamy tą sesje.
W przypadku włączania śledzenia na poziomie całej instancji należy parametr konfiguracyjny SQL_TRACE ustawić na TRUE. Wartością domyślną jest tutaj FALSE. Włączenie śledzenia dla całej instancji powoduje dość duże obciążenie systemu dlatego raczej zaleca się stosowanie śledzenia sesji, a śledzenie całej instancji stosować w wyjątkowych sytuacjach.
Do zapisu danych ze śledzenia w postaci zrozumiałej dla człowieka służy polecenie TKPROF. Składnia tego polecenia ma postać:
TKPROF infile outfile [parametry]
gdzie:
infile : plik wygenerowany w trakcie śledzenia
outfile : nazwa pliku wynikowego zawierającego raport śledzenia.
Dla każdego zapytania znajdującego się w raporcie przedstawione są następujące informacje:
count : liczba wywołań procedur OCI (procedury pozwalające na uzyskanie dostępu do bazy)
CPU : czas CPU wykonania w sekundach
elapsed : czas oczekiwania na rezultat zapytania w sekundach
disk : liczba fizycznych odczytów z dysku
query : liczba odczytów bloków utrzymanych w trybie spójnym (z wykorzystaniem segmentu wycofania)
current : liczba odczytów bloków utrzymanych w trybie bieżącym (bez wykorzystania segmentu wycofania)
rows : liczba przetworzonych rekordów podczas pobierania lub wykonywania.
Strojenie operacji wejścia-wyjścia |
W rozdziale tym zastanowimy się w jaki sposób możemy przyspieszyć operacje wejścia - wyjścia. Warto tu zauważyć iż operacji tych może być bardzo dużo, trwają one zdecydowanie dłużej niż operacje na pamięci i nawet drobne zmiany polepszające wydajność w tym zakresie może skutkować dużą poprawą wydajności.
Parametr DB_BLOCK_SIZE
Bloki o nie standardowej wielkości
Migracja rekordów
Łańcuchy bloków
Dynamiczne rozszerzanie
Parametry PCTFREE oraz PCTUSED
Parametr DB_BLOCK_SIZE |
Parametr ten określa wielkość bloku danych. Jest on bardzo ważny dla strojenia bazy, ponieważ właściwe jego ustawienie pozwoli ograniczyć liczbę niechcianych zjawisk takich jak migracja oraz łańcuchowanie,czy tez przyspieszyć operacje odczytu rekordów bazy danych. Warto tu zauważyć, ze parametr ten jest wykorzystywany podczas tworzenia bazy danych i nie można go zmienić już w trakcie pracy bazy danych. Wielkość tego parametru zależy od wielkości rekordów znajdujących się w bazie danych oraz sposobu dostępu do tych danych. Jeśli mamy do czynienia z systemem DSS, gdzie często mamy do czynienia z przeszukiwaniami całej tabeli wskazane jest, aby wielkość ta była duża, ponieważ wtedy jednorazowo będziemy odczytywali wiekszą porcję danych. Dla systemów OLTP z kolei zaleca się stosowanie mniejszych bloków danych. Większe bloki danych nie przyniosą ta żadnych korzyści. Wręcz przeciwnie mogą powodować pogorszenie wydajności, ponieważ jeśli blok danych będzie większy niż rekord w tabeli system niepotrzebnie będzie odczytywał te dodatkowe dane i niepotrzebnie te dodatkowe dane będą buforowane niepotrzebnie zajmując miejsce w buforze. Zbyt mała wartość tego parametru może spowodować zwiększenie operacji wejścia - wyjścia, ze względu na pojawienie się takich zjawisk jak migracja i łańcuchowanie. Mogą tu się pojawić problemy z zarządzaniem pamięcią ponieważ więcej czasu zajmuje wypełnienie mniejszych bloków w miarę optymalny sposób. Z kolei zbyt duża wartość tego parametru w odniesieniu do wielkości rekordów w bazie może spowodować zwieszenie operacji wejścia - wyjścia, ze względu na to, że bufor zmieści mniej takich bloków i czeskiej będziemy mieli do czynienia ze zwalnianiem miejsca dla nowych bloków w buforze. Zwalnianie to polega na zapisie najstarszych danych z bufora na dysk. Operacje dyskowe są bardzo czasochłonne, więc wydajność może spaść tu dość znacznie. Dodatkowa jedna operacja wejścia - wyjścia powoduje przetwarzanie większej ilości danych. Byłoby to zaleta, gdy w tej porcji danych znajda się tylko i wyłącznie interesujące nas danych. Jeśli parametr będzie zbyt duży podczas jednej operacji wejścia - wyjścia w odczytanej porcji danych znajda się dodatkowo dane nas nieinteresujące, które niepotrzebnie zostały odczytane.
UWAGA: Należy zwrócić uwagę, aby wielkość bloku bazodanowego była wielokrotnością bloku systemu operacyjnego.
Bloki o nie standardowej wielkości |
Zastrzeżenie dotyczy bazy danych Oracle, mówiące o tym, ze wielkość bloku danych ustawiane jest podczas tworzenia bazy i nie można go zmienić już w trakcie pracy bazy danych dotyczy bazy danych w wersji 8i i niższych. W wersji 9i wprowadzono pojęcie bloków nie standardowej wielkości. Istnieje tu możliwość tworzenia przestrzeni tabel o nie standardowej wielkości bloków danych. W takiej sytuacji wszystkie obiekty umieszczone w tej przestrzeni tabel będą miały bloki danych o nie standardowej wielkości.
Przykład:
CREATE TABLESPACE table_space_dss
DATAFILE 'ts_dss_01.dat' SIZE 300M,
DATAFILE 'ts_dss_02.dat' SIZE 300M
BLOCKSIZE 8K;
CREATE TABLESPACE table_space_oltp
DATAFILE 'ts_oltp_01.dat' SIZE 100M,
DATAFILE 'ts_oltp_02.dat' SIZE 100M
BLOCKSIZE 2K;
Zaletą używania bloków o nie standardowej wielkości jest możliwość łączenia w jednej bazie systemów DSS oraz OLTP.
Migracja rekordów |
Migracja rekordów występuje podczas operacji UPDATE. Jeśli aktualizacja rekordu powoduje iż nowy rekord nie mieści się w swoim bloku system musi odnaleźć nowy blok danych i tam przenieść zaktualizowany rekord. są to operacje, które dodatkowo obciążają procesor i dysk i wskazane jest, aby operacje takie się nie pojawiały lub było ich względnie mało.
Łańcuchy bloków |
Łańcuchy bloków występują kiedy rekord bazodanowy znajduje się w więcej niż jednym bloku danych. W takim przypadku odczytanie takiego rekordu zajmuje więcej niż jedna operacje wejścia - wyjścia.
Aby sprawdzić, czy w tabeli znajdują się rekordy łańcuchowe wykorzystujemy polecenie ANALIZE. Przed wykonaniem tego polecenia należy stworzyć odpowiednią tabelę, w której znajdą się wyniki polecenia ANALIZE. Dokonujemy tego poleceniem:
CREATE TABLE chained_rows (
owner_name VARCHAR2(30),
table_name VARCHAR2(30),
cluster_name VARCHAR2(30),
partition_name VARCHAR2(30),
subpartition_name VARCHAR2(30),
head_rowid ROWID,
analyze_timestamp DATE
);
Teraz mając już taka tabele stworzoną możemy uruchomić proces analizowania tabeli w poszukiwaniu rekordów łańcuchowych poleceniem:
ANALYZE TABLE tab1 LIST CHAINED ROWS;
Po tej operacji tabela chained_rows zawiera listę rekordów łańcuchowych tabeli tab1. Wskazane jest aby tabela ta była pusta bądź zawierała niewielką liczbę rekordów. Jeśli tych rekordów jest wiele wskazane jest, aby wyeksportować tabele, jeszcze raz przeanalizować wielkość bloku danych oraz utworzyć bazę od nowa. Warto tu zauważyć, ze są takie tabele, w których z założenia spodziewamy się, że będą zawierały rekordy łańcuchowe. Przykładem będą tu rekordy z kolumnami typu LONG lub długimi kolumnami CHAR lub VARCHAR2.
Dynamiczne rozszerzanie |
Dynamiczne rozszerzanie jest specjalnym mechanizmem polegającym na automatycznym rozszerzeniu segmentu w przypadku kiedy jego wielkość osiągnie maksimum i należy dodać dodatkowe ekstenty. Operacja ta generuje dodatkowe operacje wejścia - wyjścia oraz dodatkowe systemowe zapytania SQL zwane zapytaniami rekursywnymi, co wpływa na wydajność. Aby odnaleźć rekursywne zapytania w systemie należy odczytać dynamiczną perspektywę v$sysstat za pomocą polecenia:
SELECT name, value FROM v$sysstat WHERE name = 'recursive calls';
Rozmiar ekstentu powinien być ustalony w taki sposób, aby ograniczyć liczbę dynamicznych alokacji celem zwiększenia wydajności aplikacji. Innym rozwiązaniem jest monitorowanie wielkości ekstentów za pomocą perspektywy DBA_EXTENTS i kiedy zauważymy, że zbliża się on do wielkości, kiedy jest wykonywane dynamiczne rozszerzanie, możemy tego dokonać ręcznie, kiedy system będzie mniej obciążony, np. w godzinach nocnych.
Parametry PCTFREE oraz PCTUSED |
Bardzo często do zmniejszenia liczby operacji wejścia - wyjścia i tym samym do polepszenia wydajności systemu stosuje się parametry PCTFREE oraz PCTUSED klauzuli STORAGE w poleceniach tworzących obiekty bazodanowe. Parametry te służą do zarządzania blokami danych przez system. Balansując pomiędzy rożnymi wartościami tych parametrów możemy w odniesieniu do danego obiektu kłaść większy nacisk na wydajność odwołań do tego obiektu, czy tez na ilość miejsca zajmowanego przez ten obiekt.
Parametr PCTUSED podawany w procentach określa ze do bloku bazodanowego beda dodawane nowe rekordy jesli poziom zajetosci spadnie ponizej tej wartosci.
Parametr PCTFREE okreslany w procentach okresla ze do bloku bazodanowego będą dodawane nowe rekordy dopóki poziom wolnego miejsca w tym bloku będzie powyżej tej wartości.
Parametr PCTFREE okreslany w procentach okresla ze do bloku bazodanowego będą dodawane nowe rekordy dopóki poziom wolnego miejsca w tym bloku będzie powyżej tej wartości. Parametry PCTFREE oraz PCTUSED inaczej są nazywane wskaźnikami niskiej i wysokiej wody. Suma tych parametrów nie może być większa niż 100, ale nic nie stoi na przeszkodzie, aby suma ta była mniejsza od 100. Warto tu zauważyć, że im suma ta bardziej zbliża się do 100 lub różnica parametrów PCTFREE oraz PCTUSED zbliża się do 0, tym bardziej obciążany jest system, natomiast przestrzeń dyskowa jest bardziej efektywnie wykorzystywane. Aby zachować umiar pomiędzy wydajnością systemu, a zajetością przestrzeni dyskowej wskazane jest aby pomiędzy tymi parametrami zachować różnice jednego rekordu.
Jeżeli na danej tabeli dość często są wykonywane operacje UPDATE zwiększające wielkość rekordu wskazane jest aby parametr PCTFREE ustawić na wysoka wartość (np. 25), a PCTUSED na niską wartość (np. 40). W takiej sytuacji zawsze jest pozostawiona odpowiednia ilość miejsc w rekordzie na ewentualne operacje UPDATE zwiększające objętość tych rekordów.
W przypadku dużej ilości operacji INSERT i operacji UPDATE, które w małym stopniu zwiększają objętość rekordów wskazane jest aby parametr PCTFREE ustawić na niską wartość (np. 5), a PCTUSED na wartość względnie neutralną (np. 50). W sytuacji, kiedy najważniejsza jest wydajność systemu, a optymalne wykorzystanie przestrzeni dyskowej jest mało istotne wskazane jest aby obydwa te parametry ustawić na niską wartość (np. 30). Taka konfiguracja tych parametrów powoduje dość istotne marnowanie przestrzeni dyskowej, natomiast w znacznym stopniu ograniczamy tu takie zjawiska jak łańcuchowanie, czy też migracja.
W sytuacji, kiedy najważniejsza jest optymalne wykorzystanie przestrzeni dyskowej, a wydajność systemu jest mniej istotne wskazane jest aby parametr PCTFREE ustawić na niską wartość (np. 5), a PCTUSED na dość dużą wartość (np. 90). W takiej sytuacji dbamy o maksymalne wykorzystywanie przestrzeni dyskowej, natomiast musimy zdać sobie sprawę z tego, że będą tu występowały takie zjawiska jak łańcuchowanie czy też migracja.
Transakcje |
Wyrażenie SET TRANSACTION pozwala nam określić tryb bieżącej transakcji. Najczęściej jest ono wykorzystywane do ustawienia transakcji tylko do odczytu. Wyrażenie to musi być pierwszym w transakcji i może w niej występować tylko raz. Transakcje typu READ ONLY są stosoweane, kiedy wykorzystywane w nich są instrukcje Select INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT i ROLLBACK, czyli instrukcje, które nie wprowadzają zmian do tabel w bazie.
Wyszukiwarka
Podobne podstrony:
struktura w s.v. werder bremen, Materiały Szkoleniowe Łukasz, uefa b kurs 2013
strukturalne modele pamieci
Struktura systemu edukacji nauczycieli w Polsce., kurs pedagogiczny
Turbo Pascal kurs, Technik Informatyk, Programowanie strukturalne i obiektowe Turbo Pascal
Kurs Excel`a, Lekcja 06, Lekcja 6 - zmiana nazw arkuszy i ich struktury
Twój Darmowy Kurs Techniki Pamięciowe
Kijewska św Augustyn Struktura duszy Pamięć
Lekcja 1 Czym jest zapamietywanie i pamiec Kilka slow o czym jest ten kurs
STRUKTURA TRENINGU
03 Odświeżanie pamięci DRAMid 4244 ppt
wykład 12 pamięć
30 Struktury zaleznosci miedzy wskaznikami zrow rozw K Chmura
KURS ETYKI
8 Dzięki za Pamięć
więcej podobnych podstron