Zapytania działają Ci za wolno? Chciałbyś je przyspieszyć? Szukasz prostej recepty? Prezentuję zapytania SQL które podpowiedzą Ci, które kolumny bazy danych należy poindeksować.
Często spotykam problem, kiedy użytkownicy nie wiedzą na których kolumnach w tabelach bazodanowych pozakładać indeksy. Równie często występuje sytuacja, kiedy mamy już gotowy system i chcemy upewnić się czy jakiejś kolumny nie przegapiliśmy podczas indeksowania. Można taką informację uzyskać oczywiście z bazy danych, a ściślej ze słowników systemowych. Podstawowe pytanie jakie się pojawia w takim momencie – kiedy na kolumnie należy założyć indeks. Przyjąć można, że kandydatami do indeksowania są:
– kolumny kluczy obcych
– kolumn często występujących w warunkach where
Wyjaśnienie techniczne dla dociekliwych:
Kolumny kluczy obcych: O ile na kluczach głównych i kolumnach unikalnych indeksy zakładane są z automatu, o tyle o indeksowanie kluczy obcych musimy troszczyć się sami. Oracle nie zakłada na takich kolumnach indeksów, a ich brak może prowadzić do stosowania mniej wydajnych algorytmów realizacji złączenia. Przykładowo: bez indeksu na kluczu obcym Oracle uczyni wszystko, byleby tylko nie dokonać złączenia algorytmem pętli zagnieżdżonych (ang. nested loop) z tabelą nadrzędną jako wiodącą, ponieważ podrzędną (tą bez indeksu na kluczu obcym) musiałby czytać całą (full table scan) tyle razy ile jest rekordów w tabeli nadrzędnej. Gdyby taki indeks istniał, a algorytm nested loops wraz z użyciem indeksu byłby najwydajniejszą metodą to oczywiście brak takiego indeksu negatywnie wpływa na wydajność operacji łączenia tabel (i najczęściej prowadzi do algorytmu hash join).
Kolumny często wykorzystywane w warunkach where:
W przypadkach, kiedy kolumna służy nam do filtrowania rekordów, Oracle może (ale nie musi) skorzystać z indeksu celem szybszego znalezienia wierszy w tabeli, których szukamy. Tak jak my szukając hasła w książce, korzystamy z indeksu, aby znaleźć konkretną stronę, tak Oracle szuka wierszy korzystając z indeksu na tabeli. Brak indeksu może spowodować, że Oracle musi przeczytać całą tabelę (full table scan) do znalezienia wierszy, o które nam chodzi. Różnica może być duża – wyobraźmy sobie sytuację, kiedy musimy przeczytać 300 stron książki po to aby znaleźć dwustronicowy fragment na szukany przez nas temat.
Informacje zarówno o nieindeksowanych kluczach obcych, oraz nieindeksowanych, a często występujących w WHERE, kolumnach da nam Oracle ze słowników systemowych (dictionary).
Poniższe zapytanie zwraca niepoindeksowane klucze obce na schemacie na którym jesteśmy zalogowani:
SELECT * FROM user_cons_columns
WHERE (owner, table_name, constraint_name) IN
(SELECT owner, table_name, constraint_name
FROM user_constraints
WHERE constraint_type = 'R') AND
(owner, table_name, column_name) NOT IN
(SELECT owner, table_name, column_name
FROM user_ind_columns);
Zapytanie jest krótkie – wykorzystuje informacje o kolumnach wykorzystywanych w ograniczeniach kluczy obcych (user_cons_columns, typ ograniczenia ‘R’) oraz dane o poindeksowanych kolumnach z user_ind_columns
Znalezienie kolumn często wykorzystywanych w zapytaniach i jednocześnie niepoindeksowanych jest już trochę bardziej wymagające i wymaga uprawnień administratorskich (korzystamy bowiem z tabeli użytkownika SYS). Oracle prowadzi statystyki użycia kolumn w zapytaniach w tablicy SYS.col_usage$. Służy ona podjęciu decyzji na temat zbierania histogramów dla kolumn przez pakiet dbms_stats, gdy tą decyzję zostawiamy Oracle’owi. My użyjemy tej tabeli do wyłapania kolumn często występujących w zapytaniach i skonfrontujemy tą wiedzę z informacjami na temat poindeksowanych kolumn (user_ind_columns):
Poniższe zapytanie zwraca niepoindeksowane kolumny, wraz z informacją jak często kolumna występuje w zapytaniach (na schemacie na którym jesteśmy zalogowani, wymaga uprawnienia DBA, lub select na sys.col_usage$):
SELECT table_name, column_name, equality_preds + equijoin_preds + nonequijoin_preds + range_preds + like_preds AS ile_razy_uzyte FROM
(SELECT
o.name AS table_name,
c.name AS column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
(SELECT 1 FROM user_ind_columns WHERE table_name = o.name AND column_name = c.name AND ROWNUM = 1)
AS jest_index
FROM
sys.col_usage$ u
join sys.obj$ o on u.obj# = o.obj#
join sys.col$ c on u.obj# = c.obj# and u.intcol# = c.col#
WHERE (SELECT username FROM dba_users WHERE user_id = o.owner#) = USER
) WHERE jest_index IS NULL
ORDER BY equality_preds + equijoin_preds + nonequijoin_preds + range_preds + like_preds DESC;
Oczywiście sprawa indeksowania jest bardziej skomplikowana. Indeksowanie kolumn spowalnia operacje wstawiania i modyfikacji rekordu, a przyspiesza jedynie (i oczywiście nie zawsze) wyszukiwanie rekordów. Do tego dochodzą różne rodzaje indeksów, kardynalność kolumn, częstotliwość ich użycia oraz rozkład danych w kolumnach. Słowem – temat indeksowania to temat rzeka i nie ma jednoznacznej, ogólnej odpowiedzi czy konkretną kolumnę indeksować, czy nie, bez analizowania całgo zestawu zapytań jakie trafiają do bazy danych i wielu innych czynników. Należy zatem powyższe zapytania SQL traktować jedynie jako wskazówkę, a nie pewny wyznacznik konieczności założenia indeksów.
