Indeks typu B-Tree

„Relacyjne Bazy Danych (Oracle)”
Prezentacja jest współfinansowana przez
Unię Europejską w ramach
Europejskiego Funduszu Społecznego w projekcie pt.
growany rozwój Politechniki Łódzkiej - zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolno
Prezentacja dystrybuowana jest bezpłatnie
Projekt współfinansowany przez Unię Europejską
w ramach Europejskiego Funduszu Społecznego
Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83
www.kapitalludzki.p.lodz.pl
1
Podział języka SQL
DDL (Data Definition Language) – polecenia
CREATE, DROP, TRUNCATE, ALTER
DML (Data Manipulation Language) – polecenia
INSERT, UPDATE
DCL (Data Control Language) – polecenia
GRANT i REVOKE
Zapytania (Query)
Polecenie INSERT
Wykorzystując polecenie INSERT można dodać
nowe wiersze do tabeli
INSERT INTO tabela VALUES (val1,val2,...)
Lista wartości musi być podana w takiej samej
kolejności jak kolumny tabeli, ciągi znaków oraz
daty muszą być ujęte w pojedyncze cudzysłowy
Polecenie INSERT
Polecenie INSERT można wykorzystać do
wstawienia wartości części kolumn
INSERT INTO tabela (col1,col2) VALUES (v1,v2)
Nieuzupełnione kolumny będą wypełnione
wartościami domyślnymi
Należy wpisać wartości do kolumn typu NOT NULL
nie posiadających wartości domyślnej
Polecenie UPDATE
UPDATE – uaktualnianie istniejących wierszy
nowymi danymi
UPDATE tabela SET kol1=val1,kol2=val2
WHERE warunek
Tabele
Utworzenie tabeli
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200
/statements_7002.htm
Tabele
Tabelę można utworzyć na podstawie wyniku
zapytania na innych tabelach
CREATE TABLE pracownicy_bez_pensji AS
SELECT id,imie,nazwisko FROM pracownicy;
CREATE TABLE imiona AS SELECT
id,UPPER(imie) FROM pracownicy;
Tabela z kluczem głównym
Tabela z kompozytowym kluczem
Przykład: Kluczem w tabeli jest zestawienie
trzech kolumn
Dodawanie klucza
Dodawanie klucza do istniejącej tabeli
ALTER TABLE tabela
ADD CONSTRAINT nazwa_klucza PRIMARY
KEY (col1, col2, col3, ...);
Usuwanie klucza
ALTER TABLE tabela
drop CONSTRAINT nazwa_klucza
Dodawanie klucza obcego
Dodawanie klucza obcego do istniejącej tabeli
ALTER TABLE tabela
ADD CONSTRAINT nazwa_klucza
FOREIGN KEY (col1,col2,col3,...)
REFERENCES tabela2 (col1, col2, col3,...);
Usuwanie tabeli
DROP TABLE pracownicy
Usunięcie tabeli może się nie udać – mogą istnieć
inne tabele z danymi, które wskazują na obecną
tabelę relacją klucza obcego
Indeksy
Indeks – struktura w bazie danych pozwalająca na
szybsze odnalezienie wierszy posiadających
określone w zapytaniu wartości kolumn
Jeżeli dana kolumna nie posiada indeksu, aby
sprawdzić czy dany wiersz spełnia określone
kryteria, należy odczytać wartość kolumny z
dysku/bufora i porównać ją z wartością szukaną
Operacja taka nosi nazwę pełnego skanowania tabeli
(Full Table Scan)
Indeksy
Kluczowym pojęciem w przypadku stosowania
indeksów jest tzw: liczność zbioru (ang:
Cardinality)
Liczność określa ilość unikatowych wartości w danej
kolumnie w odniesieniu do wartości kolumny we
wszystkich wierszach tabeli
Np: dla 1000000 wierszy
100 unikatowych wartości – mniejsza liczność
300000 unikatowych wartości – większa liczność
Index B-Tree
Indeks podobny do postaci drzewa binarnego
(jednak „B” w nazwie nie oznacza „Binary”)
Pozwala na szybkie wyszukiwanie znanej wartości lub jej
części
Pozwala na automatyczne sortowanie wyników
Index B-Tree
Indeks można utworzyć na kilku kolumnach,
każda kolumna może mieć inny kierunek
sortowania
CREATE INDEX imie_pensja_idx ON osoby (imie ASC,
pensja DESC);
Utworzenie indeksu na kilku kolumnach
wykorzystywanych w klauzuli WHERE pozwala
zmniejszyć liczbę operacji I/O przy wyszukiwaniu
Index typu B-Tree
Indeks tego typu warto zastosować dla kolumn, które
posiadają często powtarzające się wartości
Dla kolumn, które są unikatowe zastosowanie indeksu
typu B-Tree może nawet spowolnić wykonanie zapytania
– konieczność przetworzenia drzewa o dużej głębokości
W większości baz danych indeksowanie kolumn
sekwencyjnych (np: id) prowadzi do złego zbalansowania
indeksu – Implementacja Oracle nie posiada tej wady
(większość indeksów osiąga maksymalnie głębokość 3,
w bardzo rzadkich przypadkach 4, teoretycznie
głębokość może być jednak większa)
Indeks typu B-Tree
Przebudowanie indeksu
ALTER INDEX imie_pensja_idx REBUILD
Kiedy warto przebudować indeks?
Po dużych operacjach typu INSERT (np: po wykonaniu
operacji typu ETL)
Jeżeli w tabeli została usunięta duża liczba wierszy
Oracle nie udostępnia wolnego miejsca powstałego po usunięciu
wiersza, jedyny przypadek to taki, gdy wstawiana wartość jest
identyczna z poprzednio usuniętą
W przypadku usunięcia dużej ilości wpisów z jednego liścia
drzewa, istnieje mała szansa na ponowne wykorzystanie
powstałego pustego miejsca
Index typu Bitmap
Indeks ma formę dwuwymiarowej tablicy
Kolumnami tablicy są unikatowe wartości indeksowanej
kolumny
Wierszami są wszystkie wiersze z danej tabeli (ROWID)
Indeks przetrzymywany jest w postaci
skompresowanej, zostaje rozpakowany do
pamięci operacyjnej przy pierwszym użyciu –
ogranicza to liczbę operacji I/O przy odczytaniu
indeksu
Bitmap index
Ze względu na swoją strukturę indeksy bitmapowe
powinny być wykorzystywane w przypadkach, gdy
dana kolumna nie posiada dużo unikatowych
wartości
Rozmiar indeksu powiększa się z każdym nowym
unikatowym wpisem
Bitmap index
Przykład: Firma produkująca samochody, tabela z
10 milionami wierszy
Kolumny: rok produkcji, kolor, model posiadają często
powtarzające się wartości (przy 10mln wierszy)
SELECT * FROM cars WHERE prod_year=1998
AND car_color='Yellow' AND car_model='Ford
Mustang'
Bitmap Index
Kiedy warto stosować indeks typu Bitmap?
Kolumna posiada niewiele wartości unikatowych w
porównaniu do całkowitej liczby wierszy
W zapytaniach wykorzystywane są wartości dokładne
(operator „=”)
W zapytaniach występuje kilka warunków
wykorzystujących operator „=” połączonych operatorami
OR lub AND
Bitmap Index
Kiedy NIE wypada stosować indeksu typu Bitmap
W kolumnie występuje dużo unikatowych wartości –
rozmiar indeksu będzie zbyt duży, konieczność czytania
wielu bloków z dysku, oraz wykonywania operacji typu
AND lub OR na dużej ilości danych
W warunkach wykorzystywane są inne operatory niż „=”
np: „>” lub „<”, w takich przypadkach indeks typu Bitmap
nie zostanie wykorzystany
Tabela nie jest często modyfikowana (niewielka ilość
DML, lub czas wykonania operacji DML nie jest kwestią
kluczową)
Indeks Bitmap Join
Podobny do indeksu typu Bitmap,
wykorzystywany do łączenia tabel
Dla każdej wartości danej kolumny w indeksie
przechowywane są wartości ROWID dla drugiej tabeli
wykorzystywanej przy klauzuli JOIN spełniające dany
warunek
Indeks tego typu działa tylko w przypadkach kiedy
warunek połączenia tabel korzysta z dokładnego
porównania (operator „=”)
Indeksy funkcyjne
Indeks utworzony na wyniku funkcji wykonanej na
danej kolumnie
CREATE INDEX prac_nazwisko_idx on
pracownicy (UPPER(nazwisko))
SELECT * FROM pracownicy WHERE
UPPER(nazwisko) = 'KOWALSKI';
Tabele typu IOT
W przypadku gdy wszystkie kolumny w danej tabeli
posiadają indeks, lub część kolumn posiada indeksy, a
średnia długość wiersza jest względnie mała można
pominąć przechowywanie tabeli w oddzielnym miejscu
na dysku
IOT (Index Organized Table) – tabela przechowuje dane
wierszy razem z danymi indeksu
Brak konieczności wykonywania oddzielnego odczytu w
celu pobrania wartości z danego wiersza
Tabele IOT muszą posiadać klucz główny
Tabele typu IOT
„Relacyjne Bazy Danych (Oracle)”
Prezentacja jest współfinansowana przez
Unię Europejską w ramach
Europejskiego Funduszu Społecznego w projekcie pt.
growany rozwój Politechniki Łódzkiej - zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolno
Prezentacja dystrybuowana jest bezpłatnie
Projekt współfinansowany przez Unię Europejską
w ramach Europejskiego Funduszu Społecznego
Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83
www.kapitalludzki.p.lodz.pl
28