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.