Kolejność warunków w WHERE

W tym kursie opiszę działanie mechanizmu kolejności wykonywania warunków o klauzuli WHERE. Odpowiem na pytania:

• Czym jest i czy baza Oracle stosuje minimalną ewaluację warunków?
• Czy kolejność warunków w WHERE ma znaczenie?
• Czym jest hint ORDERED_PREDICATES?

Ten wpis jest częścią Kursu Oracle

Minimalna ewaluacja w WHERE

Minimalna ewaluacja czyli eng.”Short-circuit evaluation, minimal evaluation, McCarthy evaluation” w najprostszych słowach jest skróceniem do minimum sprawdzenia pojedyńczych warunków dla określenia poprawności całego warunku. Dla przykładowego warunku:

FOO AND BAR

W sytuacji gdy FOO ma wartości False (0) nie ma konieczności sprawdzenia warunku BAR. Dzieje się tak dlatego, że niezależnie od wartości warunku BAR cały warunek przyjmie wartość False (0). Jeżeli natomiast FOO przyjmie wartość True (1) to aby stwierdzić wartość całego warunku należy sprawdzić również BAR. W inny przykładnie:

FOO OR BAR

W tym przypadku gdy FOO przyjmie wartość True(1) nie ma konieczności sprawdzania BAR ponieważ zasady logiki gwarantują, że całe wyrażenie będzie True(1). Sprawdzenie BAR ma sens tylko wtedy gdy FOO jest Flase(0).

Jeżeli jakiś język nie stosuje minimalnej ewaluacji to oznacza, że zawsze sprawdza wynik każdego z warunków dla oceny całego.

Baza Oracle przy wykonywaniu zapytań WHERE w SQL korzysta z minimalnej ewaluacji.

Dane testowe

Aby sprawdzić powyższe przeprowadźmy test. Mianowicie utwórzmy dwie funkcje w PL/SQL których celem będzie zalogowanie ich użycia oraz zwrócenie wartości do porównania NUMBER:

CREATE OR REPLACE FUNCTION return_one_test
RETURN NUMBER 
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Uzycie funkcji return_one_test');
  RETURN 1;
END return_one_test;

CREATE OR REPLACE FUNCTION return_one_test2
RETURN NUMBER 
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Uzycie funkcji return_one_test2');
  RETURN 1;
END return_one_test2;
/

Powyższe funkcje logują użycie oraz zwracają wartość 1.

Wykorzystanie minimalnej ewaluacji

Gdy mamy już przygotowaną funkcję napiszmy proste zapytanie SELECT z warunkiem WHERE gdzie sprawdzimy, czy faktycznie baza wykorzystuje minimalną ewaluację.

SELECT
    *
FROM DUAL
WHERE 1 = RETURN_ONE_TEST()
  AND 1 = RETURN_ONE_TEST2();

BEGIN
  DBMS_OUTPUT.PUT_LINE('---');
END;

Wynik powyższego zapytania w DBMS_OUTPUT:

Uzycie funkcji return_one_test2
Uzycie funkcji return_one_test
---

W testowym zapytaniu w klauzuli WHERE funkcje RETURN_ONE_TEST oraz RETURN_ONE_TEST2 są wołane natomiast w output widzimy użycie obu funkcji. Dzieje się tak ponieważ aby warunek w AND był spełniony obie strony muszą zwrócić True. Gdy natomiast napiszemy nieco inne zapytanie:

SELECT
    *
FROM DUAL
WHERE 1 = RETURN_ONE_TEST()
  AND 0 = RETURN_ONE_TEST2();

BEGIN
  DBMS_OUTPUT.PUT_LINE('---');
END;

Output:

Uzycie funkcji return_one_test2
---

W powyższym output widzimy tylko wywołanie funkcji RETURN_ONE_TEST2. Dzieje się tak, ponieważ warunek 0=RETURN_ONE_TEST2 zwraca false. Zgodnie z zasadą minimalnej ewaluacji nie ma konieczności sprawdzania dalszych warunków ponieważ niezależnie co zwróci 1=RETURN_ONE_TEST wynik całego AND będzie False.

Uwaga: Istnieje możliwość, że powyższe zapytanie wykona obie funkcje ponieważ baza może w pierwszej kolejności wykonać 1 = RETURN_ONE_TEST(), w tej sytuacji konieczne jest dalsze sprawdzenie warunków. Aby sprawdzić kolejność najlepiej zobaczyć plan zapytania:

Z planu zapytania wynika, że w pierwszej kolejności wykonane zostanie sprawdzenie RETURN_ONE_TEST2=0 jednak kolejność może być odwrotna. Jeżeli wiemy jaka jest pracochłonność danego ograniczenia warto brać to pod uwagę przy pisaniu zapytania.

Czy kolejność warunków w WHERE ma znaczenie?

Pytanie który chyba każdy sobie zadał czy jest jakakolwiek kolejność filtrowania w klauzuli WHERE? Otóż w przypadku gdy użyjemy hintu ORDERED_PREDICATES to kolejność wykonywania zapytania będzie po kolei. W sytuacji gdy nie użyjemy hintu ORDERED_PREDICATES baza sama wybierze kolejność wykonania warunków zgodnie z kolejnością określoną w dokumentacji:

1. Predykaty bez żadnych funkcji zdefiniowanych przez użytkownika, metod typu lub podzapytań są najpierw oceniane w kolejności określonej w klauzuli WHERE.
2. Predykaty z funkcjami zdefiniowanymi przez użytkownika i metodami typu, które mają koszty obliczone przez użytkownika, są oceniane w rosnącej kolejności ich kosztów.
3. Predykaty z funkcjami zdefiniowanymi przez użytkownika i metodami typu, które nie mają kosztu obliczonego przez użytkownika, są oceniane dalej, w kolejności określonej w klauzuli WHERE.
4. Predykaty nieokreślone w klauzuli WHERE (na przykład predykaty generowane przez optymalizator) są oceniane dalej.
5. Predykaty z podzapytaniami są oceniane jako ostatnie w kolejności określonej w klauzuli WHERE.

Zróbmy więc test czy faktycznie tak jest. Do tej pory przygotowaliśmy dwie funkcje RETURN_ONE_TEST oraz RETURN_ONE_TEST2. Mamy więc funkcję do testu pkt. 3 czyli funkcje bez określonego kosztu. w pkt. 2 opis dotyczy m.in. funkcji z określonym kosztem tak więc nadajmy jakiś koszt funkcji RETURN_ONE_TEST2 poprzez:

ASSOCIATE STATISTICS WITH FUNCTIONS return_one_test2 DEFAULT COST (1000,0,0);

Funkcja RETURN_ONE_TEST nie posiada kosztu natomiast funkcja RETURN_ONE_TEST2 koszt taki już posiada. Predykatu z pkt. 4 nie jesteśmy za bardzo w stanie sprawdzić. Predykaty z pkt. 1 i 5 natomiast możemy spokojnie użyć w zapytaniu.

Do testów potrzebować będziemy jeszcze zwykłej tabeli (dual nie jest dobra do takich testów) więc tabela testowa:

CREATE TABLE TEST_TABLE (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
  , FOO VARCHAR2(255)
  , BAR NUMBER
  , CONSTRAINT ID_PK PRIMARY KEY (ID)
);

Posiadając już wszystkie obiekty do testów czas na napisanie zapytania które pokaże nam kolejność w klauzuli WHERE:

SELECT
    *
FROM TEST_TABLE
WHERE BAR > RETURN_ONE_TEST()
  AND BAR < RETURN_ONE_TEST2()
  AND FOO > 'test'
  AND BAR < (SELECT BAR FROM TEST_TABLE WHERE BAR = 100);

Oraz plan zapytania:

Powyższy plan zapytania prezentuje kolejność w jakiej wykonywane będą warunki w klauzuli WHERE. Plan pokazuje kolejność wykonywania poszczególnych warunków w klauzuli WHERE:

• Pierwsza w kolejności operacja FOO > ‘test’ czyli “Predykat bez żadnych funkcji”
• Druga w kolejności operacja BAR < RETURN_ONE_TEST2() czyli “Funkcje z określonym kosztem”
• Trzecia w kolejności operacja BAR > RETURN_ONE_TEST() czyli “Funkcje bez określonego kosztu”
• Czwarta w kolejności operacja porównania BAR do podzapytania.

Funkcje wbudowane Oracle

Dla funkcji wbudowanych Oracle potrafi wyliczyć koszt oraz potrafi je “przetransformować” w prostszą postać. Dla przykładu wykorzystanie funkcji TO_CHAR:

SELECT
    *
FROM TEST_TABLE
WHERE BAR > RETURN_ONE_TEST()
  AND BAR < RETURN_ONE_TEST2()
  AND FOO > 'test'
  AND FOO < TO_CHAR(123)
  AND BAR < (SELECT BAR FROM TEST_TABLE WHERE BAR = 100);

Oraz plan zapytania:

Na powyższym planie wykonana została transformacja warunku FOO < TO_CHAR(123) do warunku FOO<‘123’. Takie wykorzystanie funkcji w pierwszej kolejności jest możliwe, ponieważ dokumentacja Oracle zaznacza “Predykaty bez żadnych “funkcji zdefiniowanych przez użytkownika”

Hint ORDERED_PREDICATES

Wspomniałem o hincie ORDERED_PREDICATES który powoduje, że optymalizator korzysta z kolejności dokładnie takiej jaka jest w warunku WHERE. Przykładowe uzycie hintu:

SELECT /*+ ORDERED_PREDICATES */
    *
FROM TEST_TABLE
WHERE BAR > RETURN_ONE_TEST()
  AND BAR < RETURN_ONE_TEST2()
  AND FOO > 'test'
  AND BAR < (SELECT BAR FROM TEST_TABLE WHERE BAR = 100);

Oraz plan zapytania:

Plan zapytania z użyciem hintu ORDERED_PREDICATES nie stosuje już domyślnej kolejności w warunku WHERE a stosuje dokładnie taką kolejność jaka została podana w klauzuli WHERE.

Podsumowanie

Baza danych Oracle wykorzystuje minimalną ewaluację. Czyli jeżeli kolejne warunki nie zmienią wyniku wyrażenia booleanowskiego baza ich nie wykona.

Pisząc warunek WHERE warto brać pod uwagę kolejności w jakiej baza danych wykona ograniczenia. Szczególnie wrażliwe jest używanie podzapytań oraz funkcji. Jeżeli wiemy, że podzapytanie zwróci tylko jedną wartość może lepiej wyciągnąć ją wcześniej i w warunku WHERE wykorzystywać już samą wartość. Natomiast używając funkcji w WHERE warto znać ich pracochłonność, choć użycie funkcji powinno zapalić lampkę czy aby na pewno nie da się napisać zapytania bez ich użycia.