October 28, 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.
Objavljeno u: Best practice, PostgreSQL, SQL | 1 komentar »
I uglavnom užasno nefleksibilno. Pratimo specifikaciju doslovno, nema prostora za mrdanje levo ili desno. Posledica - za svaku, pa i najmanju izmenu, mora da se menja kod.