SQL “with” klauza

Ja kāds vēl nav pamanījis, tad jau labu laiciņu dažās DBVS Select teikums var sākties nevis ar Select, bet ar atslēgas vārdu With. Tas ir kaut kādā mērā viens no apakšvaicājumu veidiem, kā tas tika apskaidrots iepriekšējā rakstā apakšvaicājumi – ievads.

Tātad kad to pielietot un kādi ir tā galvenie labumi? Ir divas galvenās lietas kādēļ ir vērts to lietot – lasāmībai un ātrdarbības uzlabošanai.

• Lasāmību tas var uzlabot tādā veidā, ka, ja Jums ir SQL teikums, kurā vairākkārt tiek lietots viens un tas pats (vai līdzīgs) apakšvaicājums, tad Jūs varat izdalīt no tā ārā kopīgo daļu – tāda kas ir kopīga visos gadījumos un to rakstīt pašā sākumā zem šī With un tālāk atsaukties jau kā uz zināmu defīnīciju.
• Savukārt ātrdarbībai optimizatoram tagad ir 2 iespējas. Viena iespēja ir ievietot Jūsu apakšvaicājuma definīciju pa taisno iekšā SQL teikumā visur, kur tas tiek izmantots, tādējādi it kā nonākot atpakaļ pie situācijas, kāda bija bez šīs klauzas pielietošanas. Otra iespēja ir vispirms materializēt Jūsu apakšvaicājumu pagaidu tabulā un tālāk veikt pamata SQL teikuma izpildi jau izmantojot šo materializēto pagaidu tabulu.

Es turpmāk apskatīšu sīkāk šīs With klauzas implementāciju divās DBVS – Oracle un SQL Server. Tradicionāli es mēdzu rakstīt arī par MySQL, bet tur šāda klauza nav pieejama.

Sintakse

WITH <vaicājuma nosaukums1>
AS (<apakšvaicājums1>)
[, <vaicājuma nosaukums2> AS (<apakšvaicājums2>), ...]

Tātad kā redzams viss sākas ar atslēgas vārdu WITH, pēc tam nāk <vaicājuma nosaukums>. Šeit ir nepieciešams vienkārši piešķirt vārdu šim apakšvaicājumam, lai vēlāk būtu iespējams uz to atsaukties. Pēc tam ir atslēgas vārds AS, aiz kura iekavās seko <apakšvaicājums>. Apakšvaicājums parasti var būt pilnvērtīgs Select teikums ar savienojumiem un citiem apakšvaicājumiem. Vienlaicīgi var būt vairāki šādā veidā definēti apakšvaicājumi, kas tiek viens no otra atdalīti ar komatu. It kā nekas sarežģīts tāpēc pārejam pie konkrētām DBVS un konkrētiem piemēriem.

Oracle

Oracle šo klauzu sauc par subquery factoring (apakšvaicājumu sadalīšanas ???) clause. Dažas raksturīgas With klauzas iezīmes Oracle DBVSā:

• Tā ir pieejama kopš 9.0.1 versijas;
• With klauza ir pieejama tikai un vienīgi Select teikumiem. Protams, Jūs varat lietot arī Insert, Delete, Update un Merge ar with klauzu, bet tad tai ir jāatrodas apakšvaicājumā, kuram Jūs tad varat pielietot with klauzu;
• With klauza nedrīkst būt rekursīva, t.i., tā nedrīkst atsaukties pati uz sevi. Vairāku apakšvaicājumu gadījumā drīkst atsaukties uz augstāk (iepriekš) definētu apakšvaicājumu.

Izveidojam tabulu ar šādiem datiem. 

CREATE TABLE personas (
  prs_id INTEGER NOT NULL PRIMARY KEY,
  prs_vards VARCHAR(15) NOT NULL,
  prs_nodala VARCHAR(15) NOT NULL,
  prs_alga INTEGER NOT NULL);
INSERT INTO personas VALUES (1, 'Valdis', 'Priekšnieki', 1200);
INSERT INTO personas VALUES (2, 'Vaira', 'Priekšnieki', 900);
INSERT INTO personas VALUES (3, 'Ivars', 'MazPriekšnieki', 900);
INSERT INTO personas VALUES (4, 'Aigars', 'MazPriekšnieki', 800);
INSERT INTO personas VALUES (5, 'Gundars', 'Vāvuļnieki', 900);
INSERT INTO personas VALUES (6, 'Jānis', 'Vāvuļnieki', 700);
INSERT INTO personas VALUES (7, 'Ingrīda', 'Vāvuļnieki', 400);
COMMIT;

Pirmajā piemērā centīsimies atrast tikai tās personas, kuru alga ir lielāka kā vidējā un atlasīt par cik tad viņu alga ir lielāka nekā vidējā. Ja lietotu parasto sintaksi (un nemēģinātu pievilkt klāt analītiskās funkcijas, kas arī varētu šai gadījumā palīdzēt), tad tas izskatītos apmēram šādi (skatamies arī izpildes plānu izmantojot SQL*Plus komandu autotrace):

Piemērs 1. Vaicājums, kurā izmantoti divi identiski apakšvaicājumi.

SQL> set autot on explain
SQL> SELECT prs_vards,
  2    prs_alga - (SELECT avg(prs_alga) FROM personas) starpiba
  3  FROM personas
  4  WHERE prs_alga > (SELECT avg(prs_alga) FROM personas);
PRS_VARDS         STARPIBA
--------------- ----------
Valdis          371,428571
Vaira           71,4285714
Ivars           71,4285714
Gundars         71,4285714
Execution Plan
----------------------------------------------------------
Plan hash value: 2093874170
-----------------------------------------------------------------
|Id|Operation           |Name    |Rows|Bytes|Cost(%CPU)|Time    |
-----------------------------------------------------------------
| 0|SELECT STATEMENT    |        |  1 |   11|   6   (0)|00:00:01|
| 1| SORT AGGREGATE     |        |  1 |    3|          |        |
| 2|  TABLE ACCESS FULL |PERSONAS|  7 |   21|   3   (0)|00:00:01|
|*3| TABLE ACCESS FULL  |PERSONAS|  1 |   11|   3   (0)|00:00:01|
| 4|  SORT AGGREGATE    |        |  1 |    3|          |        |
| 5|   TABLE ACCESS FULL|PERSONAS|  7 |   21|   3   (0)|00:00:01|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("PRS_ALGA"> (SELECT AVG("PRS_ALGA") FROM "PERSONAS"
              "PERSONAS")) 

Redzam, ka tabula tiek lasīta vismaz 3 reizes. Tagad liekam lietā jaunās zināšanas un vidējās vērtības aprēķināšanai izmantojam With klauzu:

Piemērs 2. Vaicājums, kurā izmantota subquery factoring klauza, lai vienkāršotu divus identiskos apakšvaicājumus.

SQL> WITH a AS (SELECT avg(prs_alga) v FROM personas)
  2  SELECT prs_vards,
  3    prs_alga - (SELECT v FROM a) starpiba
  4  FROM personas
  5  WHERE prs_alga > (SELECT v FROM a);
PRS_VARDS         STARPIBA
--------------- ----------
Valdis          371,428571
Vaira           71,4285714
Ivars           71,4285714
Gundars         71,4285714
Execution Plan
----------------------------------------------------------
Plan hash value: 2401820441
--------------------------------------------------------------------
|Id|Operation                 |Name     |Rows|Bytes|Cost  |Time    |
|  |                          |         |    |     |(%CPU)|        |
--------------------------------------------------------------------
| 0|SELECT STATEMENT          |         |  1 |   11|8  (0)|00:00:01|
| 1| VIEW                     |         |  1 |   13|2  (0)|00:00:01|
| 2|  TABLE ACCESS FULL       |SYS_TEMP_|  1 |    3|2  (0)|00:00:01|
| 3| TEMP TABLE TRANSFORMATION|         |    |     |      |        |
| 4|  LOAD AS SELECT          |PERSONAS |    |     |      |        |
| 5|   SORT AGGREGATE         |         |  1 |    3|      |        |
| 6|    TABLE ACCESS FULL     |PERSONAS |  7 |   21|3  (0)|00:00:01|
|*7|  TABLE ACCESS FULL       |PERSONAS |  1 |   11|3  (0)|00:00:01|
| 8|   VIEW                   |         |  1 |   13|2  (0)|00:00:01|
| 9|    TABLE ACCESS FULL     |SYS_TEMP_|  1 |    3|2  (0)|00:00:01|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("PRS_ALGA"> (SELECT "V" FROM 
       (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "V"
       FROM "SYS"."SYS_TEMP_0FD9D6606_24CBBE" "T1") "A"))

Izpildes plāns ir nedaudz sašaurināts, lai saietu ekrānā. Tātad redzam, to, ka ir parādījušies klāt soļi 3-6 (iezīmēti treknrakstā), kas izveido jaunu pagaidu tabulu (global temporary table) ar sistēmas ģenerētu nosaukumu, kas vietas trūkuma dēļ šeit nav attēlots pilnībā, un tālāk dati divas reizes tiek ņemti no šīs pagaidu tabulas katram apakšvaicājumam.

Šajā gadījumā Oracle optimizators (cost based optimizer, CBO) pats izdomā vai veidot pagaidu tabulu, vai nē. Bet vispār ir pieejami divi hinti, ar kuriem ir manuāli iespējams pateikt, lai pagaidu tabulas noteikti veidotu, vai tieši otrādi noteikti neveidotu – attiecīgi materialize un inline. Tā kā šajā gadījumā Oracle CBO vēlas izveidot pagaidu tabulu, tad var viegli ilustrēt hinta inline darbību, kuru pievienojot With klauzas Select teikumam mēs iegūstam atpakaļ sākotnējo izpildes plānu – tātad With klauzas apakšvaicājums vienkārši ir pārnests un ielikts galvenajā vaicājumā.

Piemērs 3. Vaicājums, kurā subquery factoring klauzā lietots hints inline.

SQL> WITH a AS (SELECT /*+ inline */ avg(prs_alga) v FROM personas)
  2  SELECT prs_vards,
  3    prs_alga - (SELECT v FROM a) starpiba
  4  FROM personas
  5  WHERE prs_alga > (SELECT v FROM a);
PRS_VARDS         STARPIBA
--------------- ----------
Valdis          371,428571
Vaira           71,4285714
Ivars           71,4285714
Gundars         71,4285714
Execution Plan
----------------------------------------------------------
Plan hash value: 1090409739
------------------------------------------------------------------
|Id|Operation            |Name    |Rows|Bytes|Cost(%CPU)|Time    |
------------------------------------------------------------------
| 0|SELECT STATEMENT     |        |  1 |  11 |   6   (0)|00:00:01|
| 1| VIEW                |        |  1 |  13 |   3   (0)|00:00:01|
| 2|  SORT AGGREGATE     |        |  1 |   3 |          |        |
| 3|   TABLE ACCESS FULL |PERSONAS|  7 |  21 |   3   (0)|00:00:01|
|*4| TABLE ACCESS FULL   |PERSONAS|  1 |  11 |   3   (0)|00:00:01|
| 5|  VIEW               |        |  1 |  13 |   3   (0)|00:00:01|
| 6|   SORT AGGREGATE    |        |  1 |   3 |          |        |
| 7|    TABLE ACCESS FULL|PERSONAS|  7 |  21 |   3   (0)|00:00:01|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("PRS_ALGA"> (SELECT "V" FROM  (SELECT AVG("PRS_ALGA") "V"
              FROM "PERSONAS" "PERSONAS") "A"))

Savukārt, ja mums gribās, lai vienmēr tiktu veidota pagaidu tabula, tad varam izmantot hintu materialize (raksta tieši turpat kur inline, kā augšminētajā piemērā). 

Pārējiem DML teikumiem neskaitot Select, With klauza tieši nav piemērojama, bet to var izmantot apakšvaicājumos.

Piemērs 4. Insert teikumā tieši izmantojot With klauzu iegūst kļūdu.

SQL> WITH a AS (
  2    SELECT prs_id + 7 id, prs_vards,
  3           prs_nodala, prs_alga
  4    FROM personas
  5  )
  6  INSERT INTO personas
  7  SELECT * FROM a;
INSERT INTO personas
*
ERROR at line 6:
ORA-00928: missing SELECT keyword

Piemērs 5. Insert teikumā ir iespējams izmantot With klauzu apakšvaicājumā.

SQL> INSERT INTO personas
  2  WITH a AS (
  3    SELECT prs_id + 7 id, prs_vards,
  4           prs_nodala, prs_alga
  5    FROM personas
  6  )
  7  SELECT * FROM a;
7 rows created.

SQL Server

Microsoft SQL Server šo klauzu sauc par common table expression (CTE, kopējās tabulas izteiksme). Dažas raksturīgas With klauzas iezīmes SQL Serverī:

• Tā ir pieejama kopš SQL Server 2005 versijas;
• With klauza ir pieejama Select, Insert, Update, Delete un Merge teikumiem;
• With klauza var būt rekursīva t.i. atsaukties pati uz sevi. Tas faktiski arī ir veids, kā kopš SQL Server 2005 veikt rekursīvos vaicājumus, bet šai rakstā sīkāk tas netiks apskatīts;
• With klauzu nevar pielietot apakšvaicājumos.

Piemēra tabulu ar tās datiem skat augstāk. Piemērus izpildīsim līdzīgus, kā Oraclē.

Piemērs 6. Vaicājums, kurā izmantoti divi identiski apakšvaicājumi.

SELECT prs_vards,
  prs_alga - (SELECT avg(prs_alga) FROM personas) starpiba
FROM personas
WHERE prs_alga > (SELECT avg(prs_alga) FROM personas);
Valdis 372
Vaira 72
Ivars 72
Gundars 72

Piemērs 7. Vaicājums, kurā izmantota common table expression klauza, lai vienkāršotu divus identiskos apakšvaicājumus.

WITH a AS (SELECT avg(prs_alga) v FROM personas)
SELECT prs_vards,
  prs_alga - (SELECT v FROM a) starpiba
FROM personas
WHERE prs_alga > (SELECT v FROM a);
Valdis 372
Vaira 72
Ivars 72
Gundars 72 
SQL Server var izmantot With klauzu arī pārējiem DML teikumiem, ne tikai Select. 

Piemērs 8. With klauza, kas izmantota Insert teikumā.
WITH a AS (
SELECT prs_id + 7 id, prs_vards,
prs_nodala, prs_alga
FROM personas
)
INSERT INTO personas
SELECT * FROM a;
(7 row(s) affected)
Atšķirībā no Oracle With klauzu nevar izmantot apakšvaicājumos.

Piemērs 9. With klauzu nevar izmantot apakšvaicājumos.
SELECT * FROM (
  WITH a AS (SELECT * FROM personas)
  SELECT * FROM a) q
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression, an xmlnamespaces clause or a change tracking context
clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

Tālākā lasāmviela

Oracle Database SQL Language Reference Subquery factoring clause;
SQL Server 2008 Books Online WITH common_table_expression (Transact-SQL).