Jednym z projektów na studiach było stworzenie dużej bazy danych (około 1 000 000 rekordów), a następnie jej optymalizacja.

Moja pierwsza wersja bazy stworzona była według podanego powyżej schematu.

Wygenerowanie tylu sztucznych danych było możliwe dzięki stworzeniu kilku procedur i funkcji PL/SQL. Ostatecznie baza składała się z tylu elementów:

Niestety, przy podstawowym zapytaniu wydobywającym podstawowe dane (meteorogram i komentarz do niego):

SELECT meteorogramy.meteorogram,
 komentarze.tekst_komentarza
FROM meteorogramy,
 komentarze
WHERE GodzinaDaty(meteorogramy.godzina) = 0
AND meteorogramy.id_meteorogramu IN
 (SELECT id_meteorogramu
 FROM meteorogramy_prognoz
 JOIN prognozy USING(id_prognozy)
 WHERE prognozy.data = to_date('1994-01-01 00:00', 'YYYY-MM-DD HH24:MI')
 )
AND meteorogramy.id_meteorogramu IN
 (SELECT id_meteorogramu
 FROM meteorogramy_miejscowosci
 JOIN miejscowosci USING(id_miejscowosci)
 WHERE miejscowosci.nazwa_miejscowosci = 'Gdynia'
 )
AND komentarze.id_komentarza =
 (SELECT id_komentarza
 FROM prognozy
 WHERE data = to_date('1994-01-01 00:00', 'YYYY-MM-DD HH24:MI')
 );

plan tego zapytania nie był optymalny:

Optymalizacja polegała na wyeliminowaniu złączeń (JOIN) i nałożeniu indeksów, co wiązało się z przeprojektowaniem bazy:

Wówczas nowe zapytanie wyglądało tak:

SELECT meteorogramy.meteorogram,
  prognozy.tekst_komentarza
FROM meteorogramy,
  prognozy
WHERE meteorogramy.igodzina = 0
AND meteorogramy.id_meteorogramu IN
  (SELECT id_meteorogramu
  FROM meteorogramy_prognoz
  WHERE meteorogramy_prognoz.data = to_date('1994-01-01 00:00', 'YYYY-MM-DD HH24:MI')
  )
AND meteorogramy.id_meteorogramu IN
  (SELECT id_meteorogramu
  FROM meteorogramy_miejscowosci
  JOIN miejscowosci USING(id_miejscowosci)
  WHERE miejscowosci.nazwa_miejscowosci = 'Gdynia'
  )
AND prognozy.data = to_date('1994-01-01 00:00', 'YYYY-MM-DD HH24:MI');

A plan zapytania tak:

Wnioski

Wzrost wydajności jest wielokrotny (CPU_COST – ok. 395 razy mniejszy, IO_COST – ok. 32 razy mniejszy w porównaniu do 1-go planu zapytania). Spowodowane to jest tym, że Oracle już nie musi wiele razy sięgać do tabeli prognozy, opiera się jedynie na indeksach z jednej tabeli, dzięki czemu optymalizator nie musi wykonywać pętli zagnieżdżonych wielokrotnie, co wielokrotnie odzwierciedla się w koszcie CPU i IO. A drugi JOIN jest z małą tabelą miejscowosci, więc nie zajmuje on dużo czasu, a i projektowo ta tabela nie rozrośnie się tak szybko jak meteorogramy, więc nie trzeba tego optymalizować (im mniej indeksów, tym lepiej).