Jakiś czas temu na blogu Connora Mc'Donalda znalazłam ciekawą informację o pewnej sztuczce funkcji NVL. W przykładzie Connor porównuje zapytanie z opcjonalnym parametrem :p:
select *
from tab
where ( X = :P or :P is null)
do podobnego rozwiązania z użyciem funkcji NVL:
select *
from tab
where X = NVL(:P,X)
UWAGA: dwa powyższe zapytania są równoważne jedynie w przypadku, gdy kolumna X jest NOT NULL.
Oba zapytania przyjmują OPCJONALNY parametr :p - jeśli parametr :p ma wartość - zapytanie zwróci wybrane wiersze, dla których kolumna X = :p.
Jeśli zaś parametr :p pozostawimy NULL - zostaną zwrócone WSZYSTKIE wiersze z tabeli (where x = x).
Przykład wydał mi się na tyle intersujący, że postanowiłam zbadać tę sprawę osobiście. Dodatkowo do porównania dodam jeszcze funkcję COALESCE.
Baza danych DEMO
Schemat: HR
Wersja:Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ZAPYTANIE Z WARUNKIEM LOGICZNYM "OR"
Zapytanie wyszukuje w tabeli EMPLOYEES pracownika przekazanego parametrem :nEmpId.
Jeśli zaś parametr :nEmpId pozostawimy NULL - zapytanie zwróci wszystkie wiersze.
select count(*), sum(salary) from employees where employee_id = :nEmpId or :nEmpId is null; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMPLOYEES | 6 | 48 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------
Z planu wykonania widać, że niezależnie od tego, czy w warunku podstawimy konkretną wartość, czy też NULL, zapytanie i tak wykona FULL TABLE SCAN – mimo że na kolumnie EMPLOYEE_ID istnieje indeks będący jednocześnie kluczem głównym.
ZAPYTANIE Z COALESCE
Zapytanie z opcjonalnym parametrem możemy zapisać wykorzystując funkcję COALESCE.
select count(*), sum(salary) from employees where employee_id = coalesce(to_number(:nEmpId), employee_id); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 8 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------
Plan wykonania jest identyczny jak w przypadku zapytania z warunkiem logicznym OR. Również i w tym przypadku zapytanie pobierze dane za pomocą FULL TABLE SCAN.
ZAPYTANIE Z NVL
Już czas na sztuczkę magiczną. W tym zapytaniu zamiast funkcji COALESCE użyjemy funkcji NVL.
select count(*), sum(salary) from employees where employee_id = nvl(:nEmpId, employee_id); --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | VIEW | VW_ORE_B4851255 | 108 | 1404 | 4 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | |* 4 | FILTER | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 8 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | |* 7 | FILTER | | | | | | | 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(:NEMPID IS NOT NULL) 6 - access("EMPLOYEE_ID"=:NEMPID) 7 - filter(:NEMPID IS NULL)
Mimo, że funkcjonalnie zapytanie z NVL jest takie samo jak dwa poprzednie - to nagle plan wykonania się zmienił! Zamiast jednego FULL TABLE SCAN - mamy obecnie użyte dwie metody dostępu do danych:
FULL TABLE SCAN - operacja nr 8
INDEX UNIQUE SCAN - operacja nr 6
Ale jak to DWIE metody dostępu, jak mamy tylko jedną tabelę, z której pobieramy dane - EMPLOYEES?
Okazuje się, że polecenie NVL jest tak sprytne, że w sytuacji, gdy na kolumnie istnieje index a przekażemy parametrem wartość - optymalizator wybierze dostęp do danych za pomocą indeksu.
Statistics ----------------------------------------------------------- 3 Requests to/from client 2 consistent gets 2 consistent gets examination 2 consistent gets examination (fastpath) 2 consistent gets from cache
Jeśli jednak parametr :nEmpId pozostawimy pusty - Oracle zauważy, że chcemy pobrać wszystkie dane z tabeli - i w tym celu wykorzysta metodę dostępu - FULL TABLE SCAN.
Statistics ----------------------------------------------------------- 4 Requests to/from client 6 consistent gets 6 consistent gets from cache 6 consistent gets pin 6 consistent gets pin (fastpath)
Czyli w zależności, czy podamy wartość parametru czy też nie - OTRZYMAMY ZUPEŁNIE INNY PLAN WYKONANIA!
Całkiem sprytne :)
A co jeśli zmienimy bazę na wersję nowszą:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Zapytanie z NVL i COALESCE - pozostają bez zmian. COALSCE dalej twardo przeszukuje całą tablicę, bez względu na wartość parametru, za to NVL dalej chełpi się swoją magiczną sztuczką.
Ale co z zapytanieM z warunkiem logicznym OR?
select count(*), sum(salary) from employees where employee_id = :nEmpId or :nEmpId is null;
--------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | VIEW | VW_ORE_B4851255 | 108 | 1404 | 4 (0)| 00:00:01 | | 3 | UNION-ALL | | 108 | 436 | 4 (0)| 00:00:01 | |* 4 | FILTER | | | | | | | 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 428 | 3 (0)| 00:00:01 | |* 6 | FILTER | | | | | | | 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 8 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Okazuje się, że w najnowszej wersji 23ai zapytanie z OR zostało zoptymalizowane na wzór zapytania z NVL i tak jak zapytanie z NVL - również to zapytanie w zależności od wartości parametru :nEmpId - ALBO wykorzystuje do pobrania danych INDEX (operacja 8) ALBO wykonuje FULL TABLE SCAN (operacja 5).
Tylko COALESCE niczego się nie nauczyło i w dalszym ciągu pracowicie przegląda wszystkie bloki (full table scan).
Komentarze
Prześlij komentarz