Bazy danych 1
1 Optymalizacja SQL
Optymalizacja SQL [11]:
1. Filtrowanie wierszy przy pomocy frazy WHERE zamiast pobierania wszystkich wierszy i póz
niejszej
selekcji
2. Złączenia tabel zamiast wielu zapytań
3. Stosowanie w pełnie kwalifikowanych odwołań do kolumn
4. Używanie wyrażeń CASE zamiast wielu zapytań
5. Indeksy
6. Jeżeli to możliwe należy stosować frazę WHERE zamiast HAVING
7. Jeżeli to możliwe należy używać UNION ALL zamiast UNION
8. Jeżeli to możliwe należy stosować EXISTS zamiast IN
9. Jeżeli to możliwe należy stosować EXISTS zamiast DISTINCT
10. Jeżeli to możliwe należy używać GROUPING SETS zamiast CUBE
11. Jeżeli to możliwe należy stosować zmienne dowiązane.
12. Wskazówki dla optymalizatora
2 Optymalizacja PL/SQL
Unikanie kosztów związanych z procesorem [12]
" efektywne instrukcje SQL,
" efektywne wywołania funkcji: parametry OUT, INOUT, NOCOPY,
" efektywne pętle (FORALL, BULKCOLLECT),
" użycie wbudowanych funkcji do przetwarzania tekstu,
" przy złożonych warunkach najmniej kosztowne sprawdzenia należy wykonać jako pierwsze, pierwsze
należy wykonać takie sprawdzenia, które jak najszybciej pozwolą zakończyć obliczanie całego warunku,
" zminimalizuj konwersje pomiędzy typami,
" dla operacji arytmetycznych całkowitoliczbowych stosuj PLS INTEGER lub SIMPLE INTEGER,
" dla operacji arytmetycznych zmiennoprzecinkowych stosuj BINARY FLOAT, BINARY DOUBLE, SIM-
PLE FLOAT, oraz SIMPLE DOUBLE,
2. Unikanie kosztów związanych z pamięcią
" deklaruj zmiennej typu VARCHAR2 o długości 4000 lub więcej znaków,
" grupuj powiązane ze sobą podprogramy w pakiety, dzięki czemu można jej wczytywać wspólnie,
" przytwierdzanie w obszarze wspólnym (ang. shared pool), dzięki czemu pakiet nie zostanie automatycz-
nie usunięty z niego zgodnie z algorytmem LRU (least recently used - najdłużej nieużywany),
" stosuje się do ostrzeżeń kompilatora.
Kompilacja do kodu macierzystego wpływa na prędkość wykonywania kodu proceduralnego, natomiast
nie wpływa na prędkość wykonania SQL a.
1
3 Aplikacja kliencka
3.1 Uruchomienie z wiersza poleceń (Windows)
Należy wejść do katalogu, w którym znajduje się plik run.bat i zmienić w pliku parametry połączenia
sqlplus NazwaUżytkownika/HasłoUżytkownika@AdresBazyDanych/SIDBazyDanych @main.sql
np.
sqlplus student/student@localhost/ORCL @main.sql
lub
sqlplus student/student@localhost/XE @main.sql
Parametry połączenia można znalez
ć w pliku tnsnames.ora, który znajduje się w podkatalogu katalogu, w któ-
rym została zainstalowana baza danych. Następnie należy uruchomić skrypt run.bat.
3.2 Uruchomienie z programu SQLDeveloper
Należy uruchomić program SQLDeveloper. Następnie przy jego pomocy otworzyć skrypt z lokalizacji
sql\main.sql i uruchomić go (F5).
3.3 Wykonywanie skryptów
Po włączeniu pojawi się lista możliwych do uruchomienia skryptów wraz z przypisanymi do nich sym-
bolami. W celu uruchomienia wybranego skryptu należy wcisnąć wybrany symbol (np. dla CREATE TABLES
symbol 1, a dla EXEC FUNCTIONS symbol $A$).
Menu:
1. CREATE TABLES
2. DROP TABLES
3. INSERT INTO TABLES
4. DELETE FROM TABLES
5. SELECT FROM TABLES
6. CREATE SEQUENCES
7. DROP SEQUENCES
8. CREATE FUNTIONS AND PROCEDURES
9. DROP FUNTIONS AND PROCEDURES
A. EXEC FUNCTIONS
B. EXEC PROCEDURES
0. KONIEC
?:
Wybranie skryptu może spowodować pojawienie się kolejnej listy wyboru, np. po wybraniu symbolu 5
(SELECT FROM TABLES) pojawi siÄ™:
Menu:
1. SELECT * FROM DUAL
2. SELECT * FROM USER_TABLES
3. SELECT * FROM student WHERE id = ???
4. SELECT * FROM student
?:
Skrypty wybierane są w taki sam sposób jak poprzednio, np. po wybraniu symbolu 1 pojawi się:
wybor = 1
SQL> SELECT * FROM dual;
D
-
X
SQL> SET ECHO OFF
# Wciśnij ENTER #
2
3.4 Wyłączenie
Wybranie 0 (lub Ctrl+C) powoduje zakończenie skryptu.
3.5 Struktura plików i katalogów
klient
run.bat
sql
main.sql
0
main.sql
1
main.sql
2
main.sql
3
main.sql
4
main.sql
5
1SQL.sql
2SQL.sql
3SQL.sql
4SQL.sql
main.sql
6
main.sql
7
main.sql
8
main.sql
9
main.sql
A
1SQL.sql
main.sql
B
1SQL.sql
main.sql
W katalogu sql znajduje się skrypt main.sql, który odpowiada za wyświetlenie głównej listy wyboru:
HOST CLS
PROMPT Menu:
PROMPT 1. CREATE TABLES
PROMPT 2. DROP TABLES
PROMPT 3. INSERT INTO TABLES
PROMPT 4. DELETE FROM TABLES
PROMPT 5. SELECT FROM TABLES
PROMPT 6. CREATE SEQUENCES
PROMPT 7. DROP SEQUENCES
PROMPT 8. CREATE FUNTIONS AND PROCEDURES
PROMPT 9. DROP FUNTIONS AND PROCEDURES
PROMPT A. EXEC FUNCTIONS
PROMPT B. EXEC PROCEDURES
PROMPT 0. KONIEC
ACCEPT wybor CHAR FORMAT  A1 DEFAULT  0 PROMPT  ?: 
HOST CLS
3
PROMPT wybor = &wybor
@&wybor\main.sql &wybor
PROMPT
PROMPT # Wcisnij ENTER #
PAUSE
@main.sql
Do wyświetlania tekstów użyta została instrukcja PROMPT. Do pobierania danych została użyta instruk-
cja ACCEPT, np. instrukcja ACCEPT wybor CHAR FORMAT  A1 DEFAULT  0 PROMPT  ?:  spowoduje wczyta-
nie do zmiennej wybor tekstu składającego się z jednego znaku ( A1 ) o wartości domyślnej  0 . Je-
żeli użytkownik wybierze np. 5 instrukcja @&wybor\main.sql &wybor spowoduje uruchomienie skryptu:
@5\main.sql 5 czyli skryptu main.sql z katalogu 5 z parametrem 5. W parametr przekazywany jest ka-
talog, w którym znajduje się aktualnie wybrany skrypt.
Skryptu main.sql z katalogu 5 wyświetla kolejną listę wyboru:
HOST CLS
PROMPT Menu:
PROMPT 1. SELECT * FROM DUAL;
PROMPT 2. SELECT * FROM USER_TABLES;
PROMPT 3. SELECT * FROM student WHERE id = ???;
PROMPT 4. SELECT * FROM student;
ACCEPT wybor CHAR FORMAT  A1 DEFAULT  0 PROMPT  ?: 
HOST CLS
DEFINE CURR_DIR= &1
PROMPT wybor = &wybor
@ &CURR_DIR/&wybor.sql
PROMPT # Wcisnij ENTER #
PAUSE
@main.sql
Instrukcja DEFINE CURR_DIR= &1 definiuje zmienną CURR_DIR, przechowującą ścieżkę do aktualnego katalo-
gu, o wartości równej pierwszemu przekazanemu parametrowi ( &1 ), czyli w tym przypadku 5. Podobnie
jak powyżej instrukcja ACCEPT wybor CHAR FORMAT  A1 DEFAULT  0 PROMPT  ?:  spowoduje wczytanie do
zmiennej wybor tekstu składającego się z jednego znaku ( A1 ) o wartości domyślnej  0 . Jeżeli użytkownik
wybierze np. 1 instrukcja @ &CURR_DIR/&wybor.sql spowoduje uruchomienie skryptu: @ 5/1.sql czyli skryp-
tu {1.sql z katalogu 5, czyli aktualnego. Ścieżki do skryptów podawane są w stosunku do katalogu, w którym
znajduje się skrypt main.sql, który odpowiada za wyświetlenie głównej listy wyboru.
Uwaga !!!
Skrypt wykorzystuje rekurencjÄ™. Pojawienie siÄ™ komunikatu:
SP2-0309: Procedury poleceń SQL*Plus mogą mieć głębokość zagnieżdżenia co najw. 20.
oznacza, że został osiągnięty maksymalny poziom zagnieżdżenia skryptu. W takim przypadku należy wyłączyć
skrypt poprzez wybranie 0 lub wciśniecie Ctrl + C.
4 Przykładowa treść laboratorium
Na podany przez prowadzącego temat każda grupa 2 osobowa powinna przygotować:
" spis założeń (np. student może być przypisany tylko do jednej grupy dziekańskiej),
" diagramy związków encji (instrukcja lab. nr 2),
" znormalizowany projekt bazy danych dla modelu relacyjnego (instrukcja lab. nr 3),
" skrypt zawierajÄ…cy instrukcje tworzÄ…ce tabele dla Oracle (instrukcja lab. nr 4).
Literatura
[1] Thomas Connolly, Carolyn Begg,  Systemy baz danych , Wydawnictwo RM, 2004
4
[2] Jeffrey D. Ullman, Jennifer Widom,  Podstawowy wykład z systemów baz danych , WNT, 2001
[3] Jeffrey D. Ullman, Jennifer Widom,  Systemy baz danych Pełny wykład , WNT, 2006
[4] Kevin Loney, Bob Bryla,  Oracle Database 10g Podręcznik administratora baz danych , Helion, 2008
[5] Scott Urman, Ron Hardman, Michael McLaughlin,  Oracle Database 10g Programowanie w języku
PL/SQL , Helion, 2008
[6] Ramez Elmasri, Shamkant B. Navathe,  Wprowadzenie do systemów baz danych , Helion, 2005
[7] Jose A. Ramalho,  Oracle 8i , Mikom, 2001
[8]  Oracle®Database SQL Language Reference 11g Release 1 (11.1) B28286-02 (b28286.pdf)
[9]  Oracle®Database SQL Language Quick Reference 11g Release 1 (11.1) B28285-02 (b28285.pdf)
[10] Joe Celko,  SQL Zaawansowane techniki programowania , PWN, 2008
[11] Jason Price,  Oracle Database 11g i SQL Programowanie , Helion, 2009
[12] Oracle®Database PL/SQL Language Reference 11g Release 1 (11.1) B28370-02 (B28370.pdf)
5