W przypadku złożonych zapytań SQL problem wydajnościowy może czasami wynikać z nieoptymalnej kolejności ewaluacji predykatów w klauzuli WHERE. Warto w tym miejscu przypomnieć dwa ważne fakty dotyczące optymalizacji i wykonywania zapytań w systemie Oracle Database:
- Oracle Database stosuje skróconą ewaluację (short-circuit evaluation) warunków logicznych – przykładowo, gdy w warunku „WHERE a=1 AND b=2 AND c=3” niespełniony będzie pierwszy predykat (a=1), to w ogóle nie zostaną przebadane pozostałe predykaty, gdyż wynik jest już znany (false).
- Oracle Database ewaluuje predykaty w warunku WHERE w następującej kolejności (dokumentacja): (1) predykaty bez funkcji definiowanych przez użytkownika i bez podzapytań – w kolejności podanej w WHERE, (2) predykaty z funkcjami definiowanymi przez użytkownika, dla których optymalizator był w stanie wyznaczyć koszty – w kolejności rosnących kosztów, (3) predykaty z funkcjami definiowanymi przez użytkownika, dla których optymalizator nie był w stanie wyznaczyć kosztów – w kolejności podanej w WHERE, (4) predykaty oparte o podzapytania – w kolejności podanej w WHERE.
Pokażemy teraz kilka przykładów zapytań, których wydajność może ucierpieć z powodu powyższych założeń. Wykorzystamy następujące obiekty źródłowe: tabelę ARMIA(ID,PLEC), zawierającą 100 rekordów żołnierzy – 90 mężczyzn i 10 kobiet, tabelę SEX_TO_PLEC(SEX,PLEC), która tłumaczy kody płci pomiędzy językiem angielskim i polskim (2 rekordy), funkcję POWOLNA_FUNKCJA(), która została „złośliwie” zaimplementowana tak, aby po upływie 1s zwrócić wartość, którą po prostu otrzymała jako argument wywołania, funkcję SZYBKA_FUNKCJA(), która wykonuje identyczne działanie, lecz bez „złośliwego” opóźnienia.
Oto nasze pierwsze zapytanie:
select * from armia
where powolna_funkcja(id)=1
and szybka_funkcja(id)=1;
Elapsed: 00:01:40.12
Relatywnie długi czas realizacji tego zapytania wynikał z konieczności wykonania funkcji POWOLNA_FUNKCJA() dla każdego ze 100 rekordów tabeli. Wprawdzie warunek POWOLNA_FUNKCJA(id)=1 był spełniony tylko przez jeden rekord, ale samo sprawdzanie wszystkich rekordów spowodowało 100-sekundowy czas wykonania.
Zobaczmy, co się stanie, gdy zmienimy kolejność predykatów w klauzulu WHERE:
select * from armia
where szybka_funkcja(id)=1
and powolna_funkcja(id)=1;
Elapsed: 00:00:01.01
Tym razem każdy ze stu rekordów tabeli został zbadany najpierw przez funkcję SZYBKA_FUNKCJA, co w ramach skróconej ewaluacji spowodowało odrzucenie 99 z nich i tylko dla jednego nastąpiło wykonanie funkcji POWOLNA_FUNKCJA(). Wynik zapytania jest taki sam, ale wydajność – stukrotnie lepsza.
Nasuwa się oczywiste pytanie, czy optymalizator zapytań nie mógł odpowiednio ustawić kolejności ewaluacji naszych funkcji-predykatów? Nie mógł, ponieważ nie potrafił odgadnąć kosztu wykonania funkcji PL/SQL. Na szczęście istnieje możliwość wytłumaczenia optymalizatorowi, że POWOLNA_FUNKCJA() jest bardzo kosztowna w wykonaniu, w przeciwieństwie do funkcji SZYBKA_FUNKCJA(). Służy do tego celu polecenie ASSOCIATE STATISTICS (dokumentacja):
associate statistics with functions powolna_funkcja default cost (100,0,0);
associate statistics with functions szybka_funkcja default cost (1,0,0);
Teraz kolejność ewaluacji warunków będzie automatycznie określana przez optymalizator tak, aby minimalizować koszt wywołań naszych funkcji PL/SQL. Zwróćmy uwagę na czas wykonania i zawartość planu wykonania dla obu form zapisu naszego zapytania.
select * from armia
where szybka_funkcja(id)=1
and powolna_funkcja(id)=1;
Elapsed: 00:00:01.01
select * from armia
where powolna_funkcja(id)=1
and szybka_funkcja(id)=1;
Elapsed: 00:00:01.01
A teraz kolej na drugi przykład:
select * from armia
where plec=(select plec from sex_to_plec where sex='F')
and powolna_funkcja(id)=1;
Elapsed: 00:01:40.09
Czas wykonania jest kiepski, ponieważ zgodnie z zasadami kolejności ewaluacji predykatów, Oracle Database w pierwszej kolejności wykonuje funkcję POWOLNA_FUNKCJA(), a ewentualnie dopiero później ewaluuje predykat oparty o podzapytanie. Rozumiemy, że ponieważ rekordów-kobiet jest tylko 10 w całej tabeli, to korzystniej byłoby najpierw filtrować je według podzapytania, a dopiero później wykonywać funkcję POWOLNA_FUNKCJA().
Ratunkiem w takiej sytuacji może być zastosowanie starej, zapomnianej już chyba wskazówki-Hintu o nazwie ORDERED_PREDICATES. Wskazówka ta narzuca optymalizatorowi zapytań dokładnie taką kolejność ewaluacji predykatów, jaka wynika z kolejności ich zapisania w klauzuli WHERE.
select /*+ ORDERED_PREDICATES */ * from armia
where plec=(select plec from sex_to_plec where sex='F')
and powolna_funkcja(id)=1;
Elapsed: 00:00:10.02
Sukces! Teraz czas wykonania odpowiada logice zapytania – jest tylko 10 kobiet, które wyławiamy za pomocą podzapytania, a następnie dla każdego z rekordów wykonujemy naszą 1-sekundową funkcję.
PS. Niezależnie od wszystkiego, stosowanie funkcji PL/SQL w klauzuli WHERE zapytania SQL jest złym pomysłem…