Pirmo N ierakstu atlase

Atlasīt pirmos N (2, 3, 5, 10, 20, 100 utt.) ierakstus ir diezgan izplatīta prasība. Atlasīt N lielākos, N mazākos, N jaunākos utt. Angliski to parasti sauc kā Top N analysis vai Top N Query. Savukārt (parasti webiskās aplikācijās) mēdz būt vēl viena diezgan tipiska prasība – lapošana (pagination) – ierakstu atlase pa porcijām. Šīs ir tās lietas, kur datu bāzu vadības sistēmu atšķirības spīd visā savā spožumā. Tikai relatīvi pēdējā laikā izmantojot analītiskās funkcijas vismaz dažās DBVS var mēģināt izlīdzēties ar vienu un to pašu SQL teikumu pirmo N ierakstu atlasē un lapošanā. Kā jau vienmēr esmu mēģinājis uzsvērt, tas gan nebūtu jāuzskata par milzīgu trūkumu, jo arī, iekāpjot cita ražotāja automašīnā, Jūs negaidat, ka ātruma pārslēgs slēgsies precīzi tāpat ka iepriekšējā. Truli laužot to pierastajā pozīcijā, Jūs varat iegūt tikai salauztu mašīnu un ar datubāzēm, protams, ir līdzīgi. Tiktāl ievads, bet tagad pēc kārtas par Oracle, MySQL, SQL Server un analītisko funkciju universālo risinājumu.

SQL teikuma izpildes plāna iegūšana – Oracle, MySQL, SQL Server

SQL teikumu izpildes plāns (query execution plan) – kurš gan nopietni interesējoties par datubāzu izstrādi tādu terminu nav dzirdējis? Vaicājums lēni strādā – paskaties izpildes plānu! Tiesa gan vairumā gadījumu tiek pašas par sevi pieņemtas divas (varbūt pat trīs) lietas:

• persona zin, kā izpildes plānu dabūt;
• persona zin, ko iegūtais izpildes plāns nozīmē;
• persona zin, kā panākt, lai izpildes plāns būtu optimālāks nekā iegūtais vai arī saprot, ka nekas labāks nevar sanākt (neobligātais solis).

Šai rakstā apspriedīšu pirmo soli jau parasti izvēlētajām DBVS – Oracle, MySQL, SQL Server. Kā jau Jūs noteikti zinat visas DBVS ir atšķirīgas, attiecīgi atšķirīgas ir arī metodes, kā izpildes plānu iegūt. Tātad pēc kārtas.

Oracle

Oraclē ir vairākas metodes, kā iegūt SQL teikuma izpildes plānu, ar dažādu sarežģītību un ticamības pakāpi. Ja izmantojat rīku SQL*Plus, tad visvienkāršāk ir izmantot šī rīka komandu autotrace. Es šeit gari nekāvēšos pie šī rīka un komandas apraksta, jo tie abi ir augšminētajās saitēs, šeit tikai īss piemērs. Visiem turpmākajiem piemēriem tiks izmantotas tabulas no raksta par Dekarta reizinājumu.

Indeksu spožums un posts

Kas ir indekss?

Indeksi popularitātes ziņā droši vien ir nākošie objekti datubāzēm pēc tabulām. Tos bieži izmanto vietā un iespējams gandrīz tikpat bieži patiesībā lieto nevajadzīgi. Bet tātad vispirms būtu jāsaprot, kas tad tie ir un kāda aptuveni ir to uzbūve.

Parasti runājot par indeksiem vispirms tos saprot kā kokveidīgu struktūru, kuras pirmais un galvenais uzdevums ir ātri atrast datus tabulā, kuras dati, ja vien tā nav ļoti speciāla veida tabula, ir nesakārtoti. Nākošajā attēlā ir parādīta konceptuāla indeksa struktūra. Protams, ka reālajās implementācijās katrā DBVS tas ir mazliet atšķirīgi, bet ideja paliek viena un tā pati – koks, pa kuru ātri nonāk līdz nepieciešamajai vērtībai un tad taisnā ceļā izmantojot norādes dodas uz tabulu, no kuras var nolasīt konkrēto ierakstu. Nevajadzētu, protams, arī uztvert datu bloku skaitīšanu kā precīzu algoritmu, kas pie šīm vērtībām tā arī notiek, bet drīzāk kā konceptuālu ideju.

Indekss un tabula

Tātad, ja mēs meklējam vērtību “Kuldīga” (nosacījums WHERE pilsetas_nosaukums = ‘Kuldīga’) tabulā, tad mums ir divas iespējas:

7 lietas, ko nevajag darīt

Arī mani ir sasniegusi sērga par 7 lietām, kas neesot par mani zināmas. Bet nu nekā nebija, es šeit netaisos pagaidām apspriest savas privātās lietas vai kaut ko tādu, kas pārāk tālu attālinās no datubāzēm un ar to saistīto. Ja vien mani nenovedīs līdz tam briestošo priekšvēlēšanu laikā Bet tagad nē. Tagad Jums būs 7 lietas saistītas ar datubāzes ātrdarbību (faktiski lēndarbību), ar kurām visām  es pašlaik cīnos vai man ir nācies cīnīties un par kurām varu izteikties tikai [cenzēts], [cenzēts], [cenzēts].

1. Nelietojiet SQL vaicājumos lietotāja definētas (user-defined) funkcijas. Nelietojiet tās lieliem datu apjomiem. Vēl vairāk nelietojiet tās, ja tajās atkal kaut kas tiek lasīts no datubāzes. Un tas attiecas tikpat labi uz Oracle, kā uz SQL serveri. Izbaudu to uz savas ādas gandrīz katru reizi, kad nākas cīnīties ar bremzīgiem Select vaicājumiem. Kāpēc? Oracle tas ir tāpēc, ka vaicājums tiek nosacīti izpildīts zem SQL dziņa (SQL engine), savukārt jebkurš lietotāja definēts funkcijas izsaukums prasa PL/SQL dzini (PL/SQL engine) un mētāšanās no viena uz otru ir relatīvi dārgs process. To pašu esmu novērojis SQL Server, nezinu precīzu tehnisku skaidrojumu, bet tā vien izskatās, ka tur ir kaut kas ļoti līdzīgs.
2. Nelasiet no datubāzes liekus datus. Nerakstiet SELECT * no 50 tabulām (jā 50!), rakstiet tikai tās kolonas no tām tabulām, ko Jums vajag. Tas ir pilnīgs ārprāts, ja Jūs atlasat 80 kolonas, jā 80! un pēc tam attēlojat 10. Un tas viss tikai tāpēc, ka šis vaicājums tagad derēs visās 17 vietās, kur Jums kaut ko no tā vajag attēlot. Autors par laimi (viņam) bija jau atlaists, citādi tas noteikti beigtos ar asinsizliešanu
3. Nekārtojiet liekus datus. Ja nu ir jākārto (ORDER BY) kādi dati, tad vienmēr raugieties, lai kārtojamo kolonu skaits nebūtu vairāk nekā nepieciešams. Nerakstiet SELECT * ORDER BY <1, 2, 3 kolonas>, ja izrādās, ka * nozīmē 20 kolonas no kurām tālāk Jūs lietojat tikai 10. Katra lieka kolona ir lieki dati, kas datubāzes serverim jātur atmiņā un jāvazā līdz kārtošanas laikā. Tā ir papildus atmiņa, papildus apstrādes laiks un bezjēdzīgi iztērēti resursi.
4. Neatlasiet unikālās kolonas (DISTINCT jeb UNIQUE) tikai tāpat vien, drošības pēc. DBVS nezin, ka tas ir tikai tāpat, aiz nekā darīt. Ja tas patiesi ir pārāk bieži nepieciešams, tad diezgan droši tā ir kļūda datubāzes modelī. Kāpēc to nevajag? Tāpēc, ka katrs DISTINCT prasa unikālo ierakstu atlasi un jo vairāk kolonas tiks atlasītas, jo vairāk atmiņas un resursu tam būs nepieciešami. Ja tas ir izdarīts uz ierakstu kopu, kas jau tāpat ir ar unikāliem elementiem, tad tie ir burtiski zemē nomesti CPU cikli, atmiņas operācijas un iespējams pat diska apgriezieni.
5. Nerakstiet dinamisko SQLu ar iešūtām mainīgo vērtībām. Datu bāzu vadības sistēmās (Oracle, MS SQL Server), kuru izstrādātāji ir ilgi pūlējušies, lai izveidotu kopīgu atmiņas apgabalu visiem SQL teikumiem (attiecīgi shared pool un procedure cache), maz kas var būt paradoksālāk kā nezinoši vai piedodiet par izteicienu stulbi izstrādātāji, kas katru savu SQL teikumu uzģenerē ar iešūtām mainīgo vērtībām, līdz ar to visus sākotnējos pūliņus izslaukot miskastē. Iešūtas mainīgo vērtības nozīmē praktiski nulles varbūtību, ka izveidotais SQL teikuma izpildes plāns būs lietojams arī nākošajam lietotājam, jo viņa SQL teikums būs gandrīz tāds pats, bet tikai gandrīz. Tajā atšķirsies iešūtie identifikatori vai arī kādi citi mainīgie un hopsā – tas vairs nav tas pats SQL teikums, kuram izpildes plāns jau bija zināms. Tā ir viena no tūkstoš hidras galvām, kam atkal jāpārbauda tiesības uz objektiem, jāģenerē savienojumu iespējamās kombinācijas un viss SQL teikuma izpildes plāns. Sīkums vienam lietotājam, kas reizi pusstundā palaiž SQL teikumu uz 15 minūtēm, bet nežēlīga sāpe kaut vai 10 lietotājiem, kuri katrs izpildītu desmitiem vai pat simtiem SQL teikumu sekundē, ja vien katram SQL teikumam nebūtu analīzes (parse) fāze, kas konstanti aizņem sekundi…
6. Nelietojiet procedurālo valodu ciklus, lai apstrādātu ierakstus pa vienam, jo sevišķi, ja iterāciju skaits ir proporcionāls datu apjomam. Visizplatītākā lieta – kursori. Mans mazais skaistais kursoriņš, kurš izpildās zibenīgi uz dažiem ierakstiem, pārvēršas par milzīgu nekustīgu monstru, ja tas tikpat akli dodas caur reālajiem produkcijas miljons ierakstiem. Cikli, kuru iterāciju skaits ir tieši proporcionāls datu daudzumam nav savienojami ar vārdu ātrdarbība. Tie var būt tikai un vienīgi atbilstoši vārdam lēndarbība.
7. Testējiet uz reālu datu apjomu, kādu Jūsu sistēma sasniegs pēc gada, diviem. Tas, ka Jūsu vaicājums izstrādes vidē uz 10 ierakstiem atgriež funkcionāli pareizu rezultātu zibenīgi ir nekas. Burtiski nekas. Sliktākajā gadījumā saģenerējiet datus pats, ja esošus reālus daudz nevar dabūt. Jo tad, kad Jūsu vaicājums sastapsies ar miljons ierakstiem, kam nāksies katram izsaukt lietotāja definētu funkciju, nolasīt visu ierakstu 50 kolonas, visu miljonu sakārtot dēļ liekā Distinct, un to darīs vismaz 10 lietotāji vienlaicīgi palaižot jūsu dinamiski ģenerēto SQL teikumu, lūk tad iestāsies brīdis, kad klients pacels cepuri un dosies pie Jūsu konkurentiem. Jo diemžēl lielāks dzelzis nederēs. Neviens dzelzis pie kaut cik ievērojama datu apjoma nespēj pārciest šeit uzskaitīto, tā lai lietotāji nesāktu dusmās vārīties un klusiņām lādēt izstrādātāju.

Man principā diez ko nepatīk visādas ķēdes un piramīdas, galu galā Medofs arī slikti beidza , taču gribu pievērst lasītāju uzmanību vienam visnotaļ interesantam un (cerams, ka arī turpmāk) daudzsološam blogam, kurš raksta arī par lietām, kas saistās ar tīmekli un ātrdarbību.

Lasīt arī:

• Efektīva SQL un PLSQL vadlīnijas;
• Paradigmas maiņa – SQL ātrdarbības atslēga.

IN un EXISTS salīdzināšanas operatori

Vairums SQL izstrādātāju droši vien ir dzirdējuši par IN un EXISTS operatoriem. Tos parasti izmanto WHERE klauzā kā apakšvaicājumus, lai ierobežotu atgriežamo datu apjomu. Tad nu paskatīsimies sīkāk ko tie dara un kādas īpašības tiem piemīt.

IN

IN operatora sintakse ir šāda:

WHERE <izteiksme> IN <apakšvaicājums>

<izteiksme> ir tas ko mēs gribam pārbaudīt, savukārt apakšvaicājums ir pilnīgi normāls pastāvīgs SQL teikums (tas var būt arī tiešs vērtību uzskaitījums kā redzams piemēros zemāk). Parasti izteiksme ir vienkārša kolona un apakšvaicājums atgriež arī vienu kolonu. Tādā gadījumā šādu SQL vaicājumu:

WHERE kolona IN (SELECT cita_kolona FROM cita_tabula)

kur apakšvaicājums atgriež vērtības 1, 2, …, n konkrētam gadījumam var loģiski pārrakstīt kā:

WHERE kolona = 1 OR kolona = 2 OR ... OR kolona = n

Tagad ķeramies pie piemēriem. Tiem tiks izmantotas 2 tabulas (šoreiz es necentīšos piešķirt nekādu lielo jēgu, vienkārši paspēlēsimies ar skaitlīšiem):

CREATE TABLE a (
  x1 integer,
  x2 integer,
  x3 integer);
INSERT INTO a VALUES (1, 1, 1);
INSERT INTO a VALUES (2, 2, 3);
INSERT INTO a VALUES (NULL, 1, 1);
CREATE TABLE b (   y1 integer,
  y2 integer,
  y3 integer);
INSERT INTO b VALUES (1, 1, 1);
INSERT INTO b VALUES (1, 2, 1);
INSERT INTO b VALUES (2, 2, 3);
INSERT INTO b VALUES (3, 1, 1);
INSERT INTO b VALUES (NULL, 1, 1);
INSERT INTO b VALUES (NULL, 1, 1);
COMMIT;

Piemērs 1. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības ir atrodamas tabulā a .

SQL> SELECT * FROM b
  2  WHERE y1 IN (
  3    SELECT x1
  4    FROM a);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         1          2          1
         2          2          3 

Kā redzams piemērā 1 rezultāts ir gandrīz tāds kā vairums ļaužu varētu iedomāties. Tātad atlasīti tie ieraksti, kuriem y1 vērtības ir 1 vai 2. Kāpēc 3 nav ir skaidrs, jo vērtība 3 tabulas a kolonā x1 neparādās. Bet kāpēc nav vērtības, kur y1 ir NULL? Ļoti vienkārši – atceramies NULL īpašības un jo sevišķi to, kā notiek salīdzināšana ar NULL. Tātād skatamies augstāk, kur es rakstīju kā var pārrakstīt SQL teikumu kurš ir ar IN salīdzināšanas operatoru. Šai gadījumā vaicājums būtu kā

Piemērs 2. Iepriekšējais piemērs kurš konkrētiem datiem pārveidots izmantojot loģisko VAI.

SQL> SELECT * FROM b
  2  WHERE y1 = 1 OR y1 = 2 OR y1 = NULL;
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         1          2          1
         2          2          3 

Protams NULL = NULL (no pēdējā loģiskā VAI y1 = NULL), nekad nav Patiess, tātad netiek arī atgriezts rezultātā.

Nu un galu galā ekvivalenti mēs šo SQL vaicājumu (konkrētajiem datiem!!! bet nevis vispārīgā gadījumā) varam pārrakstīt kā:

Piemērs 3. Vērtību uzskaitījums IN klauzā.

SQL> SELECT * FROM b
  2  WHERE y1 IN (1, 2, NULL);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         1          2          1
         2          2          3 

Augstāk minētie piemēri salīdzināšanu veic tikai vienas kolonas ietvaros. Taču tas ir iespējams arī vektoriem – kolonu pārim, trijniekam, četriniekam un tā tālāk. Skatamies piemēru:

Piemērs 4. Salīdzināšana kolonu vektoriem (šai gadījumā pārītim).

SQL> SELECT * FROM b
  2  WHERE (y1, y2) IN (
  3    SELECT x1, x2
  4    FROM a
  5  );
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3

Kā redzams rezultātā esam ieguvuši vienu ierakstu mazāk nekā iepriekšējos trīs piemēros. Kāpēc? Tāpēc, ka šai reizē vienādībai bija jāizpildās uz kolonu y1, y2 pārīti reizē. SQL serverī gan šādu vektoru salīdzināšanu Jums neizdosies paveikt, jo tas diemžēl to neatbalsta, bet MySQL gan nekādu problēmu nav.

Pie tam gribu uzsvērt, ka tas nav tas pats, kas ja mēs salīdzinātu šīs kolonas atsevišķi izmantojot loģisko UN! Nākošajā piemērā tas ir uzskatāmi redzams, ka, izmantojot divus atsevišķus apakšvaicājumus, mēs iegūstam nosacīti “maigāku” ierobežojumu un potenciāli lielāku rezultāta ierakstu kopu.

Piemērs 5. Salīdzināšana divām kolonām atsevišķi. Tas nav tas pats, kas salīdzināt kolonu pārīšus – skat piemēru 4.

SQL> SELECT * FROM b
  2  WHERE y1 IN (SELECT x1 FROM a)
  3    AND y2 IN (SELECT x2 FROM a);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3
         1          2          1 

Lai situācija būtu pavisam skaidra, salīdzināsim abus gadījumus tieši uzskaitot vērtības. Tātad 4. piemēru var pārrakstīt kā:

Piemērs 6. Salīdzināšana kolonu vektoriem (šai gadījumā pārītim) ar tiešu pārīšu vērtību uzskaitījumu.

SQL> SELECT * FROM b
  2  WHERE (y1, y2) IN (
  3    (1, 1), (2, 2), (NULL, 1));
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3

Piemērs 7. Salīdzināšana divām kolonām atsevišķi ar tiešu vērtību uzskaitījumu.

SQL> SELECT * FROM b
  2  WHERE y1 IN (1, 2, NULL)
  3    AND y2 IN (1, 2, 1);
        Y1         Y2         Y3
---------- ---------- ----------
         1          2          1
         1          1          1
         2          2          3 

NOT IN 

Pirmais un galvenais runājot par NOT IN – nekad nevajadzētu iedomāties, ka NOT IN ir apgrieztais operatoram IN jeb IN pārpalikums jeb, ka ar NOT IN varēs dabūt visus pārējos ierakstus, ko nedabūja ar IN. Tas tā nav vismaz tiklīdz runa ir par NULL vērtībām. NULL bija jau mazliet “aizdomīgas” IN operatoram, bet NOT IN gadījumā tas ir vēl mazāk saprotami pirmajā brīdī. Tātad ķeramies pie piemēriem. Ņemsim to pašu 1 piemēru, tikai pieliksim klāt NOT.

Piemērs 8. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības NAV atrodamas tabulā a.

SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (
  3    SELECT x1
  4    FROM a);
no rows selected

Opā! Kļūda vai ne? Mēs taču skaidri zinam, ka tabulā b ir ieraksts, kuram kolonā y1 ir vērtība 3, kas noteikti nav kolonā x1 tabulā a. Kur palicis šis ieraksts? Protams, ka tajā visā ir iesaistīts NULL. Pārrakstam piemēru uz tiešo vērtību uzskaitījumu:

Piemērs 9. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības NAV precīzā uzskaitījumā.

SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (1, 2, NULL);
no rows selected

Labāk nav kļuvis. Pārrakstam uz loģiskajiem VAI un ienesam iekšā noliegumu, kā mācīja matemātiskajā loģikā.

Piemērs 10. 9. piemēra transformācija ar mērķi saprast, kāpēc vaicājums neatgriež ierakstus.

SQL> SELECT * FROM b
  2  WHERE NOT (y1 = 1 OR y1 = 2 OR y1 = NULL);
no rows selected
SQL> SELECT * FROM b
  2  WHERE y1 <> 1 AND y1 <> 2 AND y1 <> NULL;

no rows selected

No pēdējā transformētā vaicājuma jau visiem vajadzētu būt skaidram, kur “suns” ir aprakts – salīdzināšanā ar NULL un tajā, ka izteiksme, kas sastāv no loģiskajiem UN var būt patiesa tad un tikai tad, ja visi tās locekļi ir patiesi. Tiklīdz kā mēs novācam nost NULL, tā uzreiz rezultāts ir tāds kā parasti cilvēki to sagaida:

Piemērs 11. Izvairoties no NULL vērtībām NOT IN operatorā iegūst vēlamo rezultātu.

SQL> SELECT * FROM b
  2  WHERE y1 <> 1 AND y1 <> 2;
        Y1         Y2         Y3
---------- ---------- ----------
         3          1          1
SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (
  3    SELECT x1
  4    FROM a
  5    WHERE x1 IS NOT NULL);
        Y1         Y2         Y3
---------- ---------- ----------
         3          1          1

EXISTS

EXISTS operatora sintakse ir šāda:

WHERE EXISTS <apakšvaicājums>

EXISTS predikāts dod vērtību Patiess tad, ja apakšvaicājums atgriež vismaz vienu ierakstu. Neko daudz vairāk arī tur nav ko piebilst un ķeramies pie piemēriem.

Piemērs 12. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības ir atrodamas tabulā a. Redzam, ka rezultāts ir identisks piemēram 1.

SQL> SELECT * FROM b
  2    WHERE EXISTS (
  3      SELECT 1
  4      FROM a
  5      WHERE a.x1 = b.y1)
  6  /
        Y1         Y2         Y3
---------- ---------- ----------
         1          2          1
         1          1          1
         2          2          3

Protams, ka apakšvaicājumā var salīdzināt arī vairākas kolonas reizē, tas būtu ekvivalenti ar IN operatoru salīdzinot vektorus.

Piemērs 13. Atlasa ierakstus tabulā b, kuriem kolonu pāra y1, y2 vērtības ir atrodamas tabulā a. Redzam, ka rezultāts ir identisks piemēram 4.

SQL> SELECT * FROM b
  2    WHERE EXISTS (
  3      SELECT 1
  4      FROM a
  5      WHERE a.x1 = b.y1
  6        AND a.x2 = b.y2);
        Y1         Y2         Y3
---------- ---------- ----------
         1          1          1
         2          2          3

NOT EXISTS

NOT EXISTS predikāts dod vērtību Patiess tad, ja apakšvaicājums neatgriež nevienu ierakstu. Atšķirībā no NOT IN, kas vispārīgā gadījumā nedod pretēju rezultātu IN operatoram, NOT EXISTS dod pretējo rezultātu EXISTS predikātam. Otra lieta, kam ir vērts pievērst uzmanību – funkcionāli vienādiem (tādi, kas dos vienu un to pašu rezultātu) SQL teikumiem ar IN un EXISTS, pieliekot noliegumu NOT rezultāts ir atšķirīgs (skat piemēru pārīšus 12, 1 un 14, 8).

Piemērs 14. Atlasa ierakstus tabulā b, kuriem kolonas y1 vērtības nav atrodamas tabulā a. Redzam, ka rezultātā iegūstam tos ierakstus no tabulas b, kuri netiak atlasīti piemērā 12.

SQL> SELECT * FROM b
  2  WHERE NOT EXISTS (
  3    SELECT 1
  4    FROM a
  5    WHERE a.x1 = b.y1);
        Y1         Y2         Y3
---------- ---------- ----------
                    1          1
                    1          1
         3          1          1 

Ko lietot labāk – IN vai EXISTS?

Tātad tradicionālais jautājums. Ar tradicionālu atbildi – atkarībā no apstākļiem un iespējams, ka patiesībā nav nav nekādas nozīmes, jo Oracle klusiņām fonā abus izpilda izmantojot vienu un to pašu izpildes plānu

Visi zemākie spriedumi pamatā būs balstīti izmantojot Oracle DBVS, ja Jūs izmantojat kaut ko citu, tad skatieties paši, var ļoti gadīties, ka nebūt ne viss Jums būs tāpat.

Pirmkārt kā redzams nākošajā piemērā Oracle var veikt vaicājuma transformācijas un rezultātā gan IN, gan EXISTS iegūst vienu un to pašu izpildes plānu:

Piemērs 15. IN un EXISTS var tikt izpildīti ar vienu un to pašu izpildes plānu.

SQL> set autot traceonly explain
SQL> SELECT * FROM b
  2  WHERE y1 IN (
  3    SELECT x1
  4    FROM a);
Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=22)
1 0   HASH JOIN (SEMI) (Cost=7 Card=2 Bytes=22)
2 1     TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=4 Bytes=32)
3 1     TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=2 Bytes=6)

SQL> SELECT * FROM b
  2  WHERE EXISTS (
  3    SELECT 1
  4    FROM a
  5    WHERE a.x1 = b.y1);
Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=22)
1 0   HASH JOIN (SEMI) (Cost=7 Card=2 Bytes=22)
2 1     TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=4 Bytes=32)
3 1     TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=2 Bytes=6)

Protams, ka NOT IN un NOT EXISTS vispārīgā gadījumā tā nebūs, jo kā iepriekš jau noskaidrojām, tad tie dod funkcionāli atšķirīgu rezultātu.

Piemērs 16. NOT IN un NOT EXISTS vispārīgā gadījumā nevar tikt izpildīti ar vienu un to pašu izpildes plānu, jo to rezultāts ir atšķirīgs.

SQL> SELECT * FROM b
  2  WHERE y1 NOT IN (
  3    SELECT x1
  4    FROM a);
no rows selected
Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=4 Bytes=32)
1 0  FILTER
2 1    TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=6 Bytes=48)
3 1    TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=1 Bytes=3)
SQL> SELECT * FROM b
  2  WHERE NOT EXISTS (
  3    SELECT 1
  4    FROM a
  5    WHERE a.x1 = b.y1);
3 rows selected
Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=33)
1 0  HASH JOIN (ANTI) (Cost=7 Card=3 Bytes=33)
2 1    TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=6 Bytes=48)
3 1    TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=2 Bytes=6) 

Kā redzam NOT IN gadījumā tiek izmantota operācija FILTER, kas pēc būtības ir vienkāršs (un neefektīvs) cikls – priekš katra ieraksta tabulā B  skanējam visus ierakstus tabulā A. Savukārt NOT EXISTS gadījumā tiek vienreiz nolasīti visi tabulas A ieraksti, tad vienreiz visi tabulas B ieraksti un tad starp tiem veikta HASH JOIN operācija, kas apstrādā visus ierakstus vienā paņēmienā.

Kas būtu jādara, lai ar NOT IN panāktu šādu pašu efektīvu izpildes plānu? Ļoti vienkārši – jāizlabo dati, lai tabulā nebūtu NULL vērtības un JĀDARA ZINĀMS tas Oracle DBVS. Es nelabošu vērtības bet vienkārši izmantošu kolonas x2 un y2, kurām pielikšu NOT NULL ierobežojumus tā, lai Oracle būtu droša, ka NULL vērtības šajās kolonās nevar būt.

Piemērs 17. Ja Oracle zin, ka iesaitītajā kolonā NULL vērtības nevar būt, tad NOT IN arī var izmantot Hash Join Anti.

SQL> alter table a modify x2 not null;
Table altered.
SQL> alter table b modify y2 not null;
Table altered.
SQL> SELECT * FROM b
  2  WHERE y2 NOT IN (
  3    SELECT x2
  4    FROM a);
no rows selected
Execution Plan
----------------------------------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=11)
1 0  HASH JOIN (ANTI) (Cost=7 Card=1 Bytes=11)
2 1    TABLE ACCESS (FULL) OF 'B' (TABLE) (Cost=3 Card=6 Bytes=48)
3 1    TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=3 Card=3 Bytes=9)

Un lūk esam situācijā, kad arī NOT IN izpildās tikpat efektīvi!

Lietas, kas jāatceras:

• In klauza skatās tikai unikālās vērtības, tāpēc, ja IN klauzā vairākas reizes tieši iekļauj, vai arī apakšvaicājuma rezultātā iegūst vienu un to pašu vērtību, tas gala rezultātu nemaina.
• NOT IN un NOT EXISTS nav viens un tas pats skat. piemērus 14 un 8.
• NOT IN nav apgrieztais rezultāts IN skat. piemērus 1 un 8.
• Skatieties izpildes plānus un mēģiniet saprast, kāpēc viens vai otrs variants ir labāks, bet tikpat labi var būt, ka abi varianti iekšēji tiek izpildīti vienādi.

Tālākā lasāmviela

• Understand how NULLs affect IN and EXISTS
• Asktom foruma tēma par In un EXISTS – tiesa gan neaizmirstiet, ka atbilde ir no 2000 gada un kopš tā laika daudz ūdeņu ir aiztecējis
• Oracle: Is OUTER JOIN Better Than NOT EXISTS?
• NOT IN – Jonathan Lewis bloga ieraksts
• Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN