Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Isti upit izražen na različite načine

Sunday, 28.10.2007 – Srdjan

Većina upita može da se napiše na različite načine.  C.J. Date je u online članku “Fifty Ways to Quote Your Query” prikazao listu od 52 načina da se iskaže jedan upit! Nije mi namera da se u ovome takmičim sa Date-om, već želim da prikažem nekoliko praktičnih i pravolinijskih metoda pomoću kojih se izvesna forma upita može zameniti adekvatnom (a efikasnijom) formom.

Prikazaću dve slične metode za transformaciju upita.

1. Prvom metodom se upit oblika … IN (SELECT …) pretvara u upit oblika … EXISTS (SELECT …), a naknadno se EXISTS zamenjuje sa INNER JOIN.

2. Drugom metodom se upit oblika … NOT IN (SELECT …) pretvara u upit oblika … NOT EXISTS (SELECT …), a naknadno se EXISTS zamenjuje sa LEFT OUTER JOIN

Cilj ovih transformacija je da se pronađe oblik upita koji najbolje odgovara SQL optimizatoru konkretnog SUBP-a (sistema za upravljanje bazama podataka), to jest da se koristi oblik upita koji se najbrže izvršava.

Struktura testnih tabela i testni podaci

Prvo ću dati strukturu i podatke, koji će mi pomoći u demonstraciji metoda transformacije upita. (Strukturu sam pozajmio iz jednog od mojih predhodnih postova.)

CREATE TABLE partneri ( 
  sifra_partnera INTEGER NOT NULL, 
  ime_partnera VARCHAR(50) NOT NULL, 
  CONSTRAINT pk_par 
    PRIMARY KEY (sifra_partnera) 
);    

CREATE TABLE adrese ( 
  sifra_partnera INTEGER NOT NULL, 
  opis_adrese VARCHAR(20) NOT NULL, 
  CONSTRAINT pk_tra 
    PRIMARY KEY (sifra_partnera, opis_adrese), 
  CONSTRAINT fk_tra_par 
    FOREIGN KEY (sifra_partnera) REFERENCES partneri 
    ON DELETE CASCADE ON UPDATE CASCADE );    

INSERT INTO partneri (sifra_partnera, ime_partnera) 
  VALUES (1, 'Mika str'); 
INSERT INTO partneri (sifra_partnera, ime_partnera) 
  VALUES (2, 'Pera doo'); 
INSERT INTO partneri (sifra_partnera, ime_partnera) 
  VALUES (3, 'MELANIJA'); 
INSERT INTO partneri (sifra_partnera, ime_partnera) 
  VALUES (4, 'Joca doo'); 
INSERT INTO partneri (sifra_partnera, ime_partnera) 
  VALUES (5, 'Doo ZIKA i SONS');    

INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (1, 'prodavnica'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (2, 'prodavnica br 1'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (2, 'prodavnica br 2'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (2, 'Kafana kod Pere'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (4, 'uprava'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (4, 'skladiste'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (4, 'prodavnica'); 
INSERT INTO adrese (sifra_partnera, opis_adrese) 
  VALUES (5, 'Kafana Sinovi');

Prva metoda – zamena upita oblika … IN (SELECT …)

Ovu metodu ću demonstrirati na upitu koji daje odgovor na pitanje: Treba prikazati partnere koji imaju barem dve adrese.

Jedan od upita koji daje odgovor na ovo pitanje je upit koji koristi IN izraz:

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
 WHERE p.sifra_partnera IN 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
         GROUP BY a.sifra_partnera 
        HAVING COUNT(a.sifra_partnera) > 1 
       ) 
 ORDER BY p.sifra_partnera

Kako od ovog upita napraviti ekvivalentan upit upotrebom izraza EXISTS? Ponoviću ponovo predhodan upit, ali ću bojom istaći ključne elemente upita koji učestvuju u transformaciji.

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
 WHERE p.sifra_partnera IN 
       (SELECT a.sifra_partnera
          FROM adrese AS a 
         GROUP BY a.sifra_partnera 
        HAVING COUNT(a.sifra_partnera) > 1 
       ) 
 ORDER BY p.sifra_partnera

Ekvivalentan upit je sledeći upit u kojem sam istakao šta se desilo sa ključnim elementima iz predhodnog upita. Treba primetiti da je p.sifra_partnera iz spoljašnjeg upita ušla u unutrašnji upit u sastavu WHERE klauzule.

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p  WHERE EXISTS 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
         WHERE p.sifra_partnera = a.sifra_partnera 
         GROUP BY a.sifra_partnera 
        HAVING COUNT(a.sifra_partnera) > 1 
       ) 
 ORDER BY p.sifra_partnera

Kako od ovog upita doći do upita koji koristi INNER JOIN? Isti ključni elementi i dalje učestvuju u transformaciji i dobija se donji upit. Treba primetiti da konstrukcija p.sifra_partnera = a.sifra_partnera izlazi iz unutrašnjeg upita i pojavljuje se kao uslov spajanja u spoljašnjem upitu.

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
       INNER JOIN 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
         GROUP BY a.sifra_partnera 
        HAVING COUNT(a.sifra_partnera) > 1 
       ) AS pa 
         ON p.sifra_partnera = pa.sifra_partnera 
 ORDER BY p.sifra_partnera

Ceo postupak, korak po korak, sam bolje demonstrirao pomoću Power Point prezentacije Isti upit 1.

Rezultat izvršavanja sva tri gornja upita je isti:

sifra_partnera   ime_partnera 
--------------   ------------ 
             2   Pera doo 
             4   Joca doo

Druga metoda – zamena upita oblika … NOT IN (SELECT …)

Ovu metodu ću demonstrirati na upitu koji daje odgovor na pitanje: Treba prikazati partnere koji nemaju adresu.

Jedan od upita koji daje odgovor na ovo pitanje je upit koji koristi IN izraz:

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
 WHERE p.sifra_partnera NOT IN 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
       ) 
 ORDER BY p.sifra_partnera

Kako od ovog upita napraviti ekvivalentan upit upotrebom izraza NOT EXISTS? Ponoviću ponovo predhodan upit, ali ću bojom istaći ključne elemente upita koji učestvuju u transformaciji.

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
 WHERE p.sifra_partnera NOT IN 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
       ) 
 ORDER BY p.sifra_partnera

Ekvivalentan upit je sledeći upit u kojem sam istakao šta se desilo sa ključnim elementima iz predhodnog upita. Treba primetiti da je p.sifra_partnera iz spoljašnjeg upita ušla u unutrašnji upit u sastavu WHERE klauzule.

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
 WHERE NOT EXISTS 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
         WHERE p.sifra_partnera = a.sifra_partnera 
       ) 
 ORDER BY p.sifra_partnera

Kako od ovog upita doći do upita koji koristi LEFT OUTER JOIN? Isti ključni elementi i dalje učestvuju u transformaciji i dobija se donji upit. Treba primetiti da konstrukcija p.sifra_partnera = a.sifra_partnera izlazi iz unutrašnjeg upita i pojavljuje se kao uslov spajanja u spoljašnjem upitu. Takođe je bitno napomenuti kako je potrebno dodati WHERE klauzulu pa.sifra_partnera IS NULL.

SELECT p.sifra_partnera, p.ime_partnera 
  FROM partneri AS p 
       LEFT OUTER JOIN 
       (SELECT a.sifra_partnera 
          FROM adrese AS a 
       ) AS pa 
         ON p.sifra_partnera = pa.sifra_partnera 
 WHERE pa.sifra_partnera IS NULL 
 ORDER BY p.sifra_partnera

Slično kao i kod prethodne metode, i ovde sam ceo postupak demonstrirao pomoću Power Point prezentacije Isti upit 2.

Rezultat izvršavanja sva tri gornja upita je isti:

sifra_partnera   ime_partnera 
--------------   ------------ 
             3   MELANIJA

Zaključak

Koji od prikazanih upita daje rezultat najbrže? To se mora proveriti u zavisnosti od konkretnog SUBP-a, količine podataka, postojanja indeksa. Može se desiti da pojedini (stariji?) sistemi ni ne podržavaju sve prikazane verzije upita.

Moja iskustva sa PostgreSQL 8.1 i 8.2 sistemima pokazuju, da se najbolje ponašaju verzije upita koje koriste INNER JOIN i LEFT OUTER JOIN.