LATERAL: zabawa w chowanego

Schemat: HR
Wersja:Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

Lateral - specyficzny join wprowadzony w wersji Oracle 12c. Join ten umożliwia złączenie dwóch tabel w sposób skorelowany - czyli podzapytanie z prawej strony złączenia będzie wykonane dla każdego wiersza tabeli z lewej strony.

Napiszmy zapytanie z wykorzystaniem JOIN LATERAL zwracające nazwę departamentu oraz nazwisko pracownika zarabiającego najwięcej w danym departamencie (tak tak, wiem że można to zapytanie napisać na wiele różnych sposobów nie wykorzystując LATERAL. Ale jednak - spróbujmy ten sposób).

Zapytanie 1.

select department_name, q.last_name
	from   departments d 
       join lateral (select last_name, email
                     from   employees e 
                     where  e.department_id = d.department_id 
                     order by salary asc 
                     fetch first rows with ties
                     ) q
       on  1=1 
order by 1, 2;

DEPARTMENT_NAME                LAST_NAME                
------------------------------ -------------------------
Accounting                     Gietz                    
Administration                 Whalen                   
Executive                      De Haan                  
Executive                      Kochhar                  
Finance                        Popp                     
Human Resources                Mavris                   
IT                             Lorentz                  
Marketing                      Fay                      
Public Relations               Baer                     
Purchasing                     Colmenares               
Sales                          Kumar                    
Shipping                       Olson      

Dzięki złączeniu LATERAL dla każdego wiersza z tabeli DEPARTMENTS wykonaliśmy zapytanie top-n wyszukujące pracownika zarabiającego najwięcej.

Najwięcej? Ale ile to jest najwięcej?

Dodajmy do zapytania kolumnę SALARY.

Zapytanie 2.

select department_name, q.last_name, salary
	from   departments d 
       join lateral (select last_name, email
                     from   employees e 
                     where  e.department_id = d.department_id 
                     order by salary asc 
                     fetch first rows with ties
                     ) q
       on  1=1 
order by 1, 2;

ORA-00904: "SALARY": invalid identifier https://docs.oracle.com/error-help/db/ora-00904/
The identifier or column name entered was invalid
Error at Line: 1 Column: 36

Oczywiście - błąd. W podzapytaniu nie mamy wylistowanej kolumny SALARY więc oczywistym jest - że nie jesteśmy w stanie tej kolumny wyświetlić w głównym zapytaniu.

Dodajmy kolumnę do podzapytania:

Zapytanie 3.

select department_name, q.last_name, salary
	from   departments d 
       join lateral (select last_name, email, salary
                     from   employees e 
                     where  e.department_id = d.department_id 
                     order by salary asc 
                     fetch first rows with ties
                     ) q
       on  1=1 
order by 1, 2;

DEPARTMENT_NAME                LAST_NAME                     SALARY
------------------------------ ------------------------- ----------
Accounting                     Gietz                           8300
Administration                 Whalen                          4400
Executive                      De Haan                        17000
Executive                      Kochhar                        17000
Finance                        Popp                            6900
Human Resources                Mavris                          6500
IT                             Lorentz                         4200
Marketing                      Fay                             6000
Public Relations               Baer                           10000
Purchasing                     Colmenares                      2500
Sales                          Kumar                           6100
Shipping                       Olson                           2100

Brawo!

Zmieńmy teraz podzapytanie na inne. 

Wyszukajmy departamenty oraz pracowników zarabiających powyżej 15000 w tych departamentach.

Zapytanie 4.

select department_name, q.last_name  
	from   departments d 
       join lateral (select last_name, email, salary
                     from   employees e 
                     where  e.department_id = d.department_id 
                     and salary > 15000
                     order by salary asc 
                     ) q
       on  1=1 
order by 1, 2;

DEPARTMENT_NAME                LAST_NAME                
------------------------------ -------------------------
Executive                      De Haan                  
Executive                      King                     
Executive                      Kochhar                 

Aj, zapomnieliśmy kolumny SALARY.

Zapytanie 5.

select department_name, q.last_name, salary
	from   departments d 
       join lateral (select last_name, email
                     from   employees e 
                     where  e.department_id = d.department_id 
                     and salary > 15000
                     order by salary asc 
                   ) q
       on  1=1 
order by 1, 2;

DEPARTMENT_NAME                LAST_NAME                     SALARY
------------------------------ ------------------------- ----------
Executive                      De Haan                        17000
Executive                      King                           24000
Executive                      Kochhar                        17000

Udało się!

Ale czekaj... Nie dodaliśmy kolumny SALARY do podzapytanie do tabeli EMPLOYEES. Skąd zapytanie zatem pobrało dane z kolumny SALARY? Przecież ani w podzapytaniu ani w tabeli DEPARTMENTS nie ma kolumny SALARY!!!???

👇

👇

👇

👇

👇

👇

👇

👇

👇

👇

👇

👇

👇

👇

Za całą zagadkę odpowiada optymalizator i jego umiejętność transformowania zapytania SQL. Zapytanie po transformacji możemy podejrzeć w trace 10053. Zerknijmy na trace zapytania pierwszego:

begin

        SYS.DBMS_SQLDIAG.DUMP_TRACE(p_sql_id =>  'c5vgpaj0zrdcp',
                p_child_number => 0, 
                p_component => 'Compiler', 
                p_file_id => 'sql1');
        
end;
/


SELECT "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",

       "VW_LAT_2D0B8FC8"."LAST_NAME_0" "LAST_NAME" 
FROM "HR"."DEPARTMENTS" "D", 
LATERAL( (SELECT "from$_subquery$_004"."LAST_NAME" "LAST_NAME_0" 
        FROM  

            (SELECT "E"."LAST_NAME" "LAST_NAME",

                    "E"."EMAIL" "EMAIL",

                     "E"."SALARY" "rowlimit_$_0",RANK() OVER 
             ( ORDER BY "E"."SALARY") "rowlimit_$$_rank" 
        FROM "HR"."EMPLOYEES" "E" 
        WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") "from$_subquery$_004"
 WHERE "from$_subquery$_004"."rowlimit_$$_rank"<=1)) "VW_LAT_2D0B8FC8" 
ORDER BY "D"."DEPARTMENT_NAME","VW_LAT_2D0B8FC8"."LAST_NAME_0";

Jak przeanalizujemy zapytanie, to faktycznie, żeby wyświetlić SALARY w zapytaniu głównym, musimy dodać tę kolumnę na listę SELECT również w podzapytaniu pobierającym dane z tabeli EMPLOYEES.

Podejrzyjmy teraz trace dla zapytanie nr 5:

begin

        SYS.DBMS_SQLDIAG.DUMP_TRACE(p_sql_id =>  'fm2dqgt82jtv8',
                p_child_number => 0, 
                p_component => 'Compiler', 
                p_file_id => 'sql5');
        
end;
/

SELECT "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
        "E"."LAST_NAME" "LAST_NAME",
        "E"."SALARY" "SALARY" 
FROM "HR"."DEPARTMENTS" "D","HR"."EMPLOYEES" "E" 
WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" 
        AND "E"."SALARY">15000 
ORDER BY "D"."DEPARTMENT_NAME","E"."LAST_NAME";

Oracle podstępnie przetransformował zapytanie z joinem LATERAL na postać JOIN dwóch tabel. A przy takim zapisie - jak najbardziej możemy wyświetlić kolumnę SALARY  z tabeli EMPLOYEES, gdyż obecnie nie jest ona w podzapytaniu ale w JOIN i mamy bezpośredni dostęp do wszystkich tabel w tej tabeli.