Pułapki języka SQL

Pułapki języka SQL
• O czym będę mówił:
–
–
–
–
–
Co to jest SQL?
Historia SQL
Dlaczego mówię o SQL a nie o OQL?
Standardy SQL
Dane numeryczne w SQL
• Numeryczne typy danych i ich konwersja (zaokrąglanie i obcinanie)
• Konwersja typów znakowych
• Czterofunkcyjna matematyka
– Brakujące dane w SQL
• Wartości NULL zawsze oznaczają kłopoty
– Optymalizacja zapytań
• Optymalizacja sprzętowa
• Optymalizacja programowa
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
2
Pułapki języka SQL
• Nie będę mówił o:
– projektowaniu baz danych
• DDL Database Definition Language
– podstawach matematycznych modelu relacyjnego
– normalizacji
– szczegółach
• nie jest to kurs programowania
• chciałbym aby ten wykład zasygnalizował pewne problemy które możemy
napotkać w SQL
• chciałbym podać niektóre przykłady ich rozwiązywania
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
3
Co to jest SQL ?
• Structured Query Language:
– Strukturalny Język Zapytań
– Język zarządzania relacyjną bazą danych
• Sposób działania:
– Jest opisem przedmiotu naszych żądań - system samodzielnie
rozpracowuje sposób ich spełnienia
– Nie posiada instrukcji wejścia i wyjścia
– Nie jest językiem proceduralnym (operacje na danych)
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
4
Język proceduralny
•
Język proceduralny jest szczegółowym opisem sposobu realizowania zadania i jednorazowo
operuje na jednym rekordzie lub jednej jednostce danych. Tego rodzaju języki posiadają również
nazwę 3GL (3-rd generation languages). Są to znane języki programowania, do których należą
Fortran, Cobol, BASIC, ADA oraz Pascal. Kiedy te języki operują na danych, wykorzystują pliki
o następujących właściwościach:
– 1. Dane są przechowywane w plikach wierszy danych. Aby można było wykorzystać plik,
programista musi znać jego lokalizację fizyczną, lokalizację logiczną oraz strukturę.
– 2. Program jest wyposażony w informacje potrzebne do odczytu pliku i uzyskuje
jednorazowo dostęp do jednego wiersza danych.
– 3. Struktura wierszy danych jest zdefiniowana w obrębie każdego programu.
– 4. Kolejność wierszy danych w obrębie pliku ma istotne znaczenie.
– 5. Istotne znaczenie posiada kolejność danych w obrębie wiersza.
– 6. Wiersze mogą być złożonymi strukturami danych.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
5
Język nieproceduralny
•
Jest opisem przedmiotu naszych żądań; system samodzielnie rozpracowuje sposób ich
spełnienia. Nie jest uważany za język przeznaczony dla użytkownika końcowego; nie posiada
instrukcji wejścia i wyjścia, zatem musi być wykorzystywany za pomocą języka nadrzędnego,
który obsługuje te funkcje. Operując na danych, SQL modeluje rzeczywistość za pomocą tabel,
posiadających następujące właściwości.
– 1. Dane są przechowywane w bazie danych utworzonej z tabel; tabele te mogą być
tabelami rzeczywistymi lub wirtualnymi. Użytkownicy są połączeni z całą bazą danych, a
nie z poszczególnymi tabelami.
– 2. Program żąda od SQL-a zwrócenia danych, nie zajmując się wcale fizyczną strukturą ani
lokalizacją danych.
– 3. Struktury tabel są zdefiniowane w obrębie tej bazy danych, a nie w obrębie każdego
programu.
– 4. Nie ma znaczenia kolejność tabel w bazie danych. Są one identyfikowane przez nazwy.
– 5. Kolejność kolumn w tabeli nie jest istotna. Są one identyfikowane przez nazwy.
– 6. Kolejność wierszy w obrębie tabeli nie ma znaczenia. Są one identyfikowane przez
wartości w obrębie ich kolumn (klucze).
– 7. Dane są zawsze przedstawiane użytkownikowi jako tabele, niezależnie od wewnętrznej
struktury stosowanej przez konkretną bazę danych.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
6
Historia SQL-a
• Powstał z języka Sequel w laboratoriach firmy IBM na początku lat
siedemdziesiątych
• Pierwszy SZBD to „System R” (IBM) -1973r.
• Publikacje IBM-a 1973-1979r.
• Pierwsze komercyjne zastosowanie 1977r. - Oracle
• Standardy SQL-a:
–
–
–
–
1987r. - pierszy standard ISO
1989r. - drugi standard ISO (SQL-89)
1992r. - trzeci standard ISO (SQL2)
……. - SQL3 z uwzględnieniem cech obiektowych
• Dla porównania wersja 1.1 ODMG-93 wersja 1.1 (grudzień 1993)
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
7
Dlaczego mówię o SQL a nie o OQL?
• Dlaczego używamy relacyjnych baz danych:
– bardzo duża popularność i pozornie prosta budowa języka SQL
– istniejące implementacje systemów informatycznych realizowanych w
oparciu o technologie relacyjnych baz danych
– łatwa przenaszalność kodu SQL, niezależność od platform, szeroka gama
sterowników ODBC, JDBC i inne.
• Dlaczego nie chcemy używać obiektowych baz danych:
– brak jednolitego standardu dla obiektowych baz danych
• SQL3
• ODMG, CORBA
– wysokie koszty implemetacji systemów obiektowych baz danych
– brak wiązań do języków programowania
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
8
Standardy SQL-a
• Obecnie obowiązują dwie normy ANSI/ISO: SOL-89, SQL-92
• SQL-92 to trzy poziomy zgodności: wstępny, pośredni i pełny
• Istniejące ciała normalizacyjne:
– ANSI (American National Standard Institute)
• W szczególności Komisja ANSI X3H2 ds. standardów baz danych
– ISO (International Standard Organization)
– NIST (Notional Institute Standards and Technology)
• Opracował: FIST (Federalne Standardy Przetwarzania Informacji)
– SQL/Access Group
• Przyłączalność różnych systemów baz danych
– X/Open
• Przenaszalność kodu pomiędzy różnymi bazami danych
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
9
Dane numeryczne
Numeryczne typy danych:
– Bardzo obszerny zakres numerycznych typów danych
– Idea zapewnienia każdemu językowi nadrzędnemu (bazowemu)
numerycznego typu danych SQL-a, który odpowiadałby jednemu z jego
własnych typów danych
– Pośród tych typów danych można znaleźć kilka typów stanowiących
rozszerzenia producenta oprogramowania.
• Np.: MONEY jako rozszeżenie DECIMAL lub NUMERIC
– Dzielą się na dokładne (NUMERIC, DECIMAL, INTEGER) i przybliżone
(FLOAT(P), REAL, DOUBLE PRECISION)
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
10
Konwersja numerycznych typów danych
• Kiedy ma miejsce ?
• Metody konwersji (niestety, zależne od implementacji):
– zaokrąglanie (na podstawie usuwanej liczby):
• Metoda naukowa - jeśli ta cyfra jest równa 0, l, 2, 3 lub 4, zostaje usunięta, a cyfra
wyższego rzędu po jej lewej stronie pozostaje bez zmiany. Gdy ta cyfra jest równa 5,
6, 7, 8 lub 9, zostaje usunięta, a sąsiednia cyfra po lewej stronie ulega zwiększeniu o
jeden.
• Metoda handlowa - jak wyżej ale w przypadku, gdy cyfra równa 5, możemy zechcieć
zastosować jakąś regułę, dzięki której nastąpi zaokrąglenie w górę w połowie
przypadków. Jedna z takich reguł polega na analizie najbliższej sąsiedniej cyfry po
lewej stronie. Jeśli cyfra jest nieparzysta, pozostanie niezmieniona. Gdy jest parzysta,
nastąpi jej zwiększenie.
– obcinanie
• zdefiniowane jako obcinanie w kierunku zera; tzn. wartość 1,5 -> 1, a -1,5-> -1.
• Nie jest to prawdą w przypadku wszystkich języków programowania; wszystkie są
zgodne jedynie w kwestii obcinania liczb dodatnich w kierunku do zera, ale może się
okazać niespodziewanie, że liczba -1,5 zostanie obcięta do wartości -2.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
11
Konwesja znakowych typów danych
• Kiedy ma miejsce ?
– Ma miejsce przy operacjach poównania, zapisywania w bazie danych,
aktualizacji danych - wtedy gdy np.: porównywane łańcuchy znakowe są
różnych typów.
• W jaki sposób jest realizowana ?
– Jeżeli porównujemy wartość typu CHAR(4) np.: „ABCD”
z CHAR(8) np.: „ABCDEFG” to pierwsza wartość zostanie uzupełniona
znakami pustymi z prawej strony (do 8 znaków).
• Niespodzianki
– W przypadku porównania łańcuchy 'Smith' oraz 'Smith
końcowymi) są równe.
' (z trzema spacjami
– W przypadku predykatu LIKE nie następuje uzupełnianie i rezultatem będzie
FALSE.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
12
Czerofunkcyjna matematyka
•
SQL posiada możliwości rachunkowe słabsze od kieszonkowego kalkulatora.
Dwuargumentowe operatory +, -, * oraz / reprezentują odpowiednio dodawanie,
odejmowanie, mnożenie i dzielenie.
•
Operatory mnożenia i dzielenia posiadają równy priorytet i są wykonywane przed
dwuargumentowymi operatorami plus i minus.
Dopuszczalne jest stosowanie jednoargumentowych operatorów plus i minus.
Możemy ich używać, umieszczając je w dowolnej liczbie przed numeryczną
wartością zmiennych. Jednak trzeba uważać ponieważ w SQL-u stosuje się także
komentarze w stylu języka Ada, gdzie wiersz komentarza wstawia się pomiędzy dwie
kreski i znak nowego wiersza.
Optymalizacja wyrażeń matematycznych :-)
•
•
– Rozważmy na przykład takie działanie złożone (duża liczba + duża liczba - duża liczba),
gdzie duża liczba jest maksymalną wartością, jaką można przedstawić za pomocą jej
numerycznego typu danych. Jeśli pogrupujemy wyrażenie w kierunku od lewej strony do
prawej, otrzymamy ((duża liczba + duża liczba) - duża liczba) = błąd przepełnienia! Jeśli
jednak grupowanie zostanie przeprowadzone od prawej strony ku lewej, otrzymujemy
(duża liczba + (duża liczba - duża liczba)) = duża liczba.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
13
Brakujące dane w SQL
• Co to jest NULL ?
– Zwyczajny opis wartości NULL mówi, że reprezentuje ona obecnie nieznaną
wartość, która w przyszłości może być zastąpiona wartością rzeczywistą, kiedy
zdobędziemy brakujące informacje.
– W przypadku SQL-a wartość NULL jest jedynym sposobem obsługi brakujących
wartości.
– NULL jest tworem globalnym, który nie przynależy do żadnego z typów danych,
ale jest w stanie zastępować dowolne ich wartości.
– W raporcie Interim Report 75-02-08 to the ANSI X3 (SPARC Study Group 1975)
przedstawiono 14 różnych rodzajów niepełnych danych, jakie mogą wystąpić w
roli wyników działań lub wartości atrybutów. Grupa ta obejmuje takie przypadki,
jak arytmetyczny niedomiar i przepełnienie, dzielenie przez zero, obcięcie
łańcucha znakowego, podniesienie wartości zero do potęgi zero oraz inne błędy
rachunkowe, a także wartości brakujące lub nieznane.
– Regułą dla matematyki z udziałem wartości NULL jest to, że wartości te
rozprzestrzeniają się.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
14
Brakujące dane w SQL
•
Wykonajmy zapytanie zapisane poniżej:
SELECT test / 0 FROM Wypelniona_wartosciami_NULL;
SELECT test / 0 FROM Wypelniona_Jedynkami;
SELECT test / 0 FROM Wypelniona_Zerami;
•
Przewidywany rezultat to w matematyce błąd !
•
Jednak okazuje się, że operacja arytmetyczna z wartością NULL zwraca wartość
NULL wyniku.
W dodatku wyrażenie (NULL / 0) nie jest spójne dla różnych implementacji SQL-a.
•
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
15
Brakujące dane w SQL
Produkt
NULL/0
Jeden/zero
Zero/zero
Błąd operacji zmiennoprzecinkowej, brak da-nych
Błąd dzielenia przez 0, brak
danych
Błąd operacji zmiennoprzecinkowej, brak da-nych
Błąd dzielenia przez 0, brak
danych
NULL
Błąd dzielenia przez 0, brak
danych
Ingres 6.4/03
NULL
Oracle 6.0/8.0
NULL
Progress 6.2
NULL
R:BASE 4.0a
NULL
NULL
Błąd dzielenia przez 0, brak
danych
błąd obcinania podczas
wykonania programu wskutek
dzielenia przez 0
błąd obcinania podczas
wykonania programu wskutek
dzielenia przez 0
błąd obcinania podczas
wykonania programu wskutek
dzielenia przez 0
SQL Server 4.2/7.0
NULL
NULL oraz błąd
NULL oraz błąd
SQL Base 5.1
NULL
Plus nieskończoność
Plus nieskończoność
Sybase 4.9
NULL
NULL oraz błąd
NULL oraz błąd
WATCOM SQL
NULL
XDB 2.41
NULL
NULL
Błąd dzielenia przez 0, brak
danych
NULL
Błąd dzielenia przez 0, brak
danych
RDB
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
16
Brakujące dane w SQL
Porównywanie wartości NULL
•
wartości NULL nie można porównywać z innymi wartościami NULL za pomocą
czegoś, co programiści nazywają operatorem porównania (równe, nierówne, mniejsze
niż, większe niż itd.)
•
Jeżeli wykonujemy:
SELECT * FROM Jakaś_Tabela WHERE Pewna_Kolumna=2;
i następnie wykonujemy:
SELECT * FROM Jakaś_Tabela WHERE Pewna_Kolumna<>2;
spodziewamy się otrzymać w rezultacie wszystkie wiersze z Jakaś_Tabela. Jednakże
oprócz powyższych, należałoby wykonać dodatkowo zapytanie:
SELECT * FROM Jakaś_Tabela WHERE Pewna_Kolumna IS NULL;
aby uzyskać spodziewany wynik.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
17
Brakujące dane w SQL
• Funkcje agregujące
– Funkcja COUNT
• Zwraca liczbę wierszy w tabeli. Funkcja zajmuje się wierszami a nie wartościami
kolumn więc liczone są także wartości NULL.
– Operacje sumowania SUM:
• brakujące dane są usuwane przed zliczaniem.
– Funkcja AVG:
• Jest mniej więcej tym samym co (SUM(x)/COUNT(*)) - różnica polega na tym, że
SUM odrzuca puste wartości a COUNT nie !
– Funkcje MIN i MAX
• Wartości NULL nie są brane pod uwagę
• Zbiór pusty zwraca wartość NULL
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
18
Jak pozbyć się brakujących danych
• Odpowiednio skonstruowany schemat bazy danych. Brakujące dane powinny
być przesunięte do innych tabel powiązanych z główną.
• Dobrym zwyczajem byłoby deklarowanie wszystkich tabel bazowych z
ograniczeniami NOT NULL na wszystkich kolumnach, kiedy tylko to
możliwe.
• Gdy istnieje potrzeba przedstawiania brakujących wartości, należy
zaprojektować dla nich schematy kodowania ze stosownymi wartościami. Na
przykład, istnieją następujące kody płci ISO: 0 = nieznana, l = męska,
2 = żeńska, 9 = nie dotyczy.
• Można stosować łańcuch znakowy typu '9999-99-99 23:59:59.999999’ dla
wartości typu 'wieczność' albo 'kres czasu'. (Uwaga na procedury
statystyczne!)
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
19
Optymalizacja zapytań
• Rodzaje optymalizatorów:
– Optymalizator pracujący w oparciu o reguły językowe przegląda składnię
zapytania i planuje sposób jego wykonania nie dbając o rozmiary tabel, ani nie
uwzględniając rozkładu statystycznego danych. Taki Optymalizator (np. Oracie
w wersji wcześniejszej niż 7.0) dokona analizy składni zapytania oraz wykona go
w takiej kolejności działań, w jakiej zostało ono napisane, wprowadzając
ewentualnie pewną reorganizację zapytania do postaci równoważnej,
wykorzystując przy tym pewne reguły składniowe.
– Optymalizator, działający w oparciu o koszt przetwarzania, analizuje zarówno
zapytanie, jak i dane statystyczne dotyczące samej bazy danych oraz podejmuje
decyzje o sposobie najkorzystniejszego wykonania zapytania. Te decyzje
obejmują kwestie wykorzystania indeksów, zastosowania kodowania,
przeniesienia pewnych tabel do pamięci głównej, doboru metody sortowania itd.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
20
Optymalizacja zapytań
• Metody dostępu
– Dostęp sekwencyjny
• Skanowanie tabeli polega na sekwencyjnym odczycie wszystkich danych w
kolejności, w jakiej występują one w obszarze fizycznym pamięci, pobierając
jednorazowo jedną stronę pamięci. Większość baz danych nie usuwa fizycznie
wierszy, które zostały skasowane, więc tabela może zajmować sporo fizycznej
przestrzeni i zawierać niewiele danych.
– Dostęp indeksowy
• W przypadku dostępu indeksowego zwracany jest jednorazowo jeden wiersz.
– Dostęp mieszający
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
21
Optymalizacja zapytań
• Wyrażenia i niezagnieźdżone zapytania
– Optymalizator powinien rozpracowywać sytuacje, kiedy dwa zapytania są takie
same oraz nie dawać nabierać się, kiedy dwa zapytania mają to samo znaczenie,
lecz inną składnię.
– Na przykład, standard SQL definiuje zapytanie:
SELECT *
FROM Magazyn
WHERE ilość IN (SELECT ilość FROM Sprzedaż);
jako identyczne z następującym:
SELECT *
FROM Magazyn
WHERE ilość = ANY (SELECT ilość FROM Sprzedaż);
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
22
Optymalizacja zapytań
• Proste wyrażenia
– Dobra reguła praktyczna polega na tym, że dla danego łańcucha predykatów, zawierającego
operatory AND i testującego stałe wartości, najpierw należy umieszczać najbardziej
restrykcyjne.
– Na przykład, zapytanie:
SELECT *
FROM Studenci
WHERE płeć = 'kobieta’ AND ocena = 'A’;
będzie prawdopodobnie działać wolniej niż następujące:
SELECT *
FROM Studenci
WHERE ocena = 'A’ AND płeć = 'kobieta’;
ponieważ występuje mniej studentów z ocenami „a" niż studentów-kobiet.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
23
Optymalizacja zapytań
• Proste wyrażenia cd.
– Na przykład, aby znaleźć kogoś w tabeli Irlandia, kto nie jest katolikiem, napisalibyśmy
zapewne:
SELECT *
FROM Irlandia
WHERE religia <> 'Katolik';
Sposób obejścia tego problemu polega na rozłożeniu tej nierówności w następujący sposób:
SELECT *
FROM Irlandia
WHERE religia < 'Katolik’ OR religia > 'Katolik’;
i wymuszeniu użycia indeksu. Jednak bez indeksu na kolumnie religia wersja predykatu z
OR mogłaby potrzebować więcej czasu na wykonanie.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
24
Optymalizacja zapytań
• Proste wyrażenia cd.
– Zamiast wyrażenia:
SELECT *
FROM Sprzedaż
WHERE kod_literowy IS NOT NULL;
Lepiej jednak napisać:
SELECT *
FROM Sprzedaż
WHERE kod_literowy > 'AAA';
ponieważ uniknie się dodatkowych odczytów.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
25
Optymalizacja zapytań
• Proste wyrażenia cd.
– Zapytanie:
SELECT *
FROM Studenci
WHERE nazwisko LIKE 'Sm_th';
nie będzie wykorzystywać indeksu na kolumnie nazwi sko, jednakże zapytanie:
SELECT *
FROM Studenci
WHERE nazwisko BETWEEN 'Smath' AND 'Smzth'
może wykorzystywać indeks na tej kolumnie.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
26
Optymalizacja zapytań
• Nadmiarowość w zapytaniach
– Im więcej informacji będzie zawartych w zapytaniu, tym będzie większa szansa, że
optymalizator będzie w stanie znaleźć ulepszony plan wykonania zapytania.
SELECT * FROM Tabela1, Tabela2, Tabela3
WHERE Tabela2.wspólna_kolumna = Tabela3.wspólna_kolumna
AND Tabela3.wspólna_kolumna = Tabela1.wspólna_kolumna;
– alternatywne zapytanie:
SELECT * FROM Tabela1, Tabela2, Tabela3
WHERE Tabela1.wspólna_kolumna = Tabela2.wspólna_kolumna
AND Tabela1.wspólna_kolumna = Tabela3.wspólna_kolumna;
– Niektóre Optymalizatory będą wykonywać złączenia par tabel w oparciu o kryteria
równozłączenia w klauzuli WHERE w takiej kolejności, w jakiej występują. Załóżmy, że
Tabela1 jest bardzo mała, a Tabela2 i Tabela3 są dużymi tabelami. W pierwszym
przedstawionym powyżej zapytaniu wykonanie w pierwszej kolejności złączenia Tabela2Tabela3 zwróci duży zbiór wynikowy, który następnie zostanie obcięte przez złączenie
Tabelal-Tabela2.
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
27
Optymalizacja zapytań
• Nadmiarowość w zapytaniach
– W przypadku drugiego zapytania, wykonanie w pierwszej kolejności złączenia
Tabela1-Tabela2 zwróci mały zbiór wynikowy, który następnie zostanie dopasowany do
małego zbioru wynikowego złączenia Tabela1-Tabela3
– Jednak najlepszym posunięciem jest zapewnienie wyczerpującej informacji, tak aby
optymalizator mógł samodzielnie zdecydować, kiedy nastąpią zmiany rozmiarów tabel.
Prowadzi to do nadmiarowości w klauzuli WHERE:
SELECT *
FROM Tabela1, Tabela2, Tabela3
WHERE Tabelal.wspólna_kolumna = Tabela2.wspólna_kolumna
AND Tabela2.wspólna_kolumna = Tabela3.wspólna_kolumna
AND Tabela3.wspólna_kolumna = Tabela1.wspólna_kolumna;
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
28
Bibliografia
•
•
•
Lech Banachowski „Bazy danych”
Joe Celko „SQL for Smarties - Advanced SQL Programing”
Strony internetowe „DBMS Magazine”
.
Pułapki
języka SQL - Maciej Kuske - Styczeń
29