Átláthatóbb SQL-lekérdezések

Korábban már írtunk az allekérdezésekről, amelyek segíthetnek átláthatóbb SQL-utasításokat megfogalmazni. Most továbbmegyünk, és megmutatjuk, hogy az allekérdezéseket el is lehet nevezni, ami még kényelmesebbé és hatékonyabbá teheti a munkánkat. Erre szolgál a WITH konstrukció, amelyet az SQL:1999 szabványban definiáltak (ez volt az SQL-szabvány 4. kiadása). Ha kódoltál már Pythonban, Visual Basic-ben vagy esetleg Pascalban, akkor ismerős lehet számodra ez a megoldás. Nézzük meg röviden a WITH használatát SQL-ben.

Vegyünk a példa kedvéért egy butyuta allekérdezést, amely ezúttal a lekérdezés FROM részében szerepel:

SELECT c FROM (SELECT * FROM t)

Ezt persze egyszerűbben, allekérdezés nélkül is megfogalmazhattuk volna, de haladjunk most tovább ezzel a példával. A WITH kulcsszót használva emeljük ki a (SELECT * FROM t) allekérdezést, és nevezzük el úgy, hogy allek:

WITH allek AS (SELECT * FROM t)
SELECT c FROM allek;

Az ilyen kiemelést az Oracle allekérdezés-faktorizációnak (subquery factoring) nevezi, hiszen egy kicsit olyan ez, mint amikor egy algebrai átalakítás során kiemelünk egy faktort, például így: ax+bx = x(a+b). Persze azért ennél bonyolultabb a dolog.

Az elnevezett allekérdezésre később a lekérdezésben bárhol hivatkozhatunk. Az ilyen hivatkozás nemcsak a lekérdezésünk komplexitását csökkenti, hanem a lekérdezés végrehajtását is hatékonyabbá teheti. Egy névtelen allekérdezésre gondolhatunk úgy, mint részeredményhalmazra, vagy ideiglenes nézetre (inline view), amelyből egyébként ugyanúgy tudunk lekérdezni, mint egy táblából. Viszont amíg a névtelen allekérdezést minden egyes előfordulásakor végrehajtja az Oracle adatbázis (létrehoz egy nézetet), addig az elnevezett allekérdezésből ideiglenes táblát tud létrehozni, materializálja a részeredményhalmazt, így nem kell minden előfordulásakor újra meg újra végrehajtania az allekérdezést. Ha egy allekérdezés végrehajtása sokáig tart, akkor megéri materializálni a lekérdezés eredményét. (Ideiglenes táblát persze kézzel magunk is létrehozhatnánk, ám a WITH használata mégiscsak elegánsabb: dolgozzon helyettünk az adatbázis.)

Összetettebb lekérdezésekben több allekérdezésünk is lehet, és ezeket rendre elnevezhetjük, ha a WITH után felsoroljuk őket vesszővel elválasztva. Egy elnevezett allekérdezésre ezután hivatkozhatunk a főlekérdezésből, vagy egy másik allekérdezésből. Nézzünk egy ilyen példát:

WITH
turistak_ossz AS (SELECT orszag, COUNT(*) turistak_szama FROM turistak GROUP BY orszag), -- első allekérdezés
turistak_atl  AS (SELECT SUM(turistak_szama)/COUNT(*) turistak_atl_szama FROM turistak_ossz) -- második allekérdezés
SELECT * FROM turistak_ossz -- főlekérdezés
WHERE turistak_szama > (SELECT turistak_atl_szama FROM turistak_atl) -- harmadik allekérdezés
ORDER BY orszag;

A fenti példában három allekérdezés is van, ebből az első kettőnek adtunk nevet: turistak_ossz és turistak_atl. A turistak_atl nevű allekérdezés a turistak_ossz nevű allekérdezés eredményhalmazából kérdez le, és a főlekérdezés szintén ebből kérdez le. A harmadik, névtelen allekérdezés pedig a turistak_atl  nevű allekérdezés eredményhalmazából kérdez le. A turistak_atl  allekérdezés a turisták átlagos számát, a turistak_ossz allekérdezés az országonkénti turisták számát adja vissza, főlekérdezésünk eredményeként pedig visszakapjuk azokat az országokat, amelyekből az átlagosnál több turista érkezett, valamint hogy mennyi érkezett összesen ezekből az országokból.

A lekérdezés eredménye ilyen jellegű lesz:

ORSZAG             TURISTAK_SZAMA
-------------------- --------------
Németország              19252
Kína                     32336

Nézzük meg ennek a lekérdezésnek a végrehajtási tervét is:

--------------------------------------------------------------------------------------------------------
|Id|Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)  | Time     |
--------------------------------------------------------------------------------------------------------
|0| SELECT STATEMENT           |                           |    28 |   700 |     7     (0)| 00:00:01 |
|1| TEMP TABLE TRANSFORMATION  |                           |       |       |              |          |
|2| LOAD AS SELECT             | SYS_TEMP_0FD9D6603_57556E |       |       |              |          |
|3| HASH GROUP BY              |                           |    28 |   336 |     3     (0)| 00:00:01 |
|4| TABLE ACCESS FULL          | TURISTAK                  |    28 |   336 |     3     (0)| 00:00:01 |
|5| SORT ORDER BY              |                           |    28 |   700 |     4     (0)| 00:00:01 |
|6| VIEW                       |                           |    28 |   700 |     2     (0)| 00:00:01 |
|7| TABLE ACCESS FULL          | SYS_TEMP_0FD9D6603_57556E |    28 |   336 |     2     (0)| 00:00:01 |
|8| VIEW                       |                           |     1 |    13 |     2     (0)| 00:00:01 |
|9| SORT AGGREGATE             |                           |     1 |    13 |              |          |
|10|VIEW                       |                           |    28 |   364 |     2     (0)| 00:00:01 |
|11|TABLE ACCESS FULL          | SYS_TEMP_0FD9D6603_57556E |    28 |   336 |     2     (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Nem kell megértenünk a terv részleteit. Elég annyit észrevenni, hogy az Operation oszlopban szerepelnek VIEW bejegyzések, tehát nézetek jönnek létre a háttérben az allekérdezésekből, illetve az 1. sorban TEMP TABLE TRANSFORMATION bejegyzés szerepel, tehát az adatbázis tényleg temporális táblával operál.