Indeksu spožums un posts

marts 4, 2009

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

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: Lasīt pārējo šī ieraksta daļu »


7 lietas, ko nevajag darīt

janvāris 23, 2009

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ī:


IN un EXISTS salīdzināšanas operatori

janvāris 8, 2009

Vairums SQL izstrādātāju droši vien ir dzirdējuši par IN un EXISTS operatoriem. Tos parasti izmanto WHERE klauzā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


Oracle datu vārdnīca

decembris 4, 2008

Kas tas ir?

Datu vārdnīca ir kopējs apzīmējums dažādām sistēmas iekšējām tabulām un skatījumiem, kas satur Oracle DBVS nepieciešamo informāciju tās darbināšanai. Kā Jūs domājat, kur tiek glabāta informācija par jaunu izveidotu tabulu, skatījumu, tabulai pieliktu kolonu vai ierobežojumu? Pareizi uzminējāt – tabulās. Tikai šoreiz tās ir sistēmas iekšējās tabulas, tā saucamie metadati jeb dati par datiem. Līdzīgi ir ne tikai attiecībā uz mūsu izveidotajiem permanentajiem objektiem datubāzē, bet arī ar krietni īslaicīgākajām lietām – sesijām, izpildītajiem SQL teikumiem, statistiku, kādas darbības un cik esam veikuši sesijas laikā – tas viss arī ir atrodams datu vārdnīcā.

Datu vārdnīcas virsbūve

Jebkuram cilvēkam, kas domā kaut ko nopietni darīt Oracle DBVS, būtu vismaz konceptuāli jāzin Oracle datu vārdnīcas pamatlietas ļoti vienkārša iemesla dēļ – tas ļoti bieži atvieglo dzīvi. Tātad datu vārdnīcas redzamākā daļa pārsvarā sastāv no statiskajiem un dinamiskajiem skatījumiem. Statiskajos skatījumos ir infomācija par Oracle datubāzē esošajiem objektiem – lietotājiem, tabulām, to kolonām, skatījumiem, trigeriem, datu failiem utt. Lielākajai daļai statiskajiem skatījumiem ir trīs varianti, kuru struktūra praktiski neatšķiras – ALL* un DBA* skatījumiem nāk klāt kolona owner, bet pārējās kolonas ir vienādas visiem variantiem.

  • Tādi, kas satur informāciju tikai par lietotāja shēmā esošajiem objektiem – sākas ar USER, piemēram, USER_OBJECTS, USER_TABLES, USER_VIEWS. Pieejami jebkuram sistēmas lietotājam.
  • Tādi, kas satur informāciju par lietotāja shēmā esošajiem objektiem un arī citās shēmās esošajiem objektiem, uz kuriem lietotājam ir piešķirtas tiesības – sākas ar ALL, piemēram, ALL_OBJECTS, ALL_TABLES, ALL_VIEWS. Pieejami jebkuram sistēmas lietotājam.
  • Tādi, kas satur informāciju par visiem Oracle datubāzē esošajiem objektiem – sākas ar DBA, piemēram, DBA_OBJECTS, DBA_TABLES, DBA_VIEWS. Pieejami tikai privileģētiem lietotājiem ar speciālām tiesībām.

Dažiem statiskajiem skatījumiem nav kāda no variantiem. Tas ir tiem, kuros informācija ir par sistēmas līmeņa objektiem, piemēram, lomām (tikai DBA_ROLES), direktorijām (tikai ALL/DBA_DIRECTORIES). Daži no biežāk izmantotajiem skatījumiem varētu būt: Lasīt pārējo šī ieraksta daļu »


Rekursīvie vaicājumi

novembris 11, 2008

Veidojot datu modeli mēdz būt situācijas, kad nākas sastapties ar rekursijām (entītijai saite pašai uz sevi). Tipiskākie piemēri iz dzīves – organizācijas struktūra, kur ir viena virsiestāde un zem tās dažādos līmeņos pakļautās iestādes. Līdzīgi arī darbinieku pakļautība, ir galvenais boss, tam pakļautie un tā tālāk līdz pat katrai apkopējai. Vispārīgā gadījumā rekursija, protams, nav bezgalīga (vismaz datubāzu pasaulē), bet tai arī nav zināms maksimālais dziļums vai arī tas ir mainīgs un/vai pārāk liels, lai veidotu fiksētu hierarhisku datu struktūru, kurā datus glabāt. Rekursija, protams, vispārīgā gadījumā var būt arī vizuāla, kā tas redzams nākošajā fotogrāfijā.

Vizuāla rekursija
Vizuāla rekursija

Konceptuālais datu modelis jau iepriekšminētajam, piemēram, par darbinieku pakļautību izskatās šāds. Ir entītija darbinieks. Katram darbiniekam var būt viens boss (kas atkal savukārt ir darbinieks). Katram darbiniekam var būt viens vai vairāki pakļautie, kas, savukārt, ir darbinieki.

Rekursija konceptuālajā datu modelī
Rekursija konceptuālajā datu modelī

Savukārt loģiskais datu modelis šai pašā situācijā izskatās šāds. Ir tabula DARBINIEKI. Ir naturālā primārā atslēga uz PERSONAS KODU. Ir ārējā atslēga BOSA_PERSONAS_KODS pašai tabulai uz sevi, kas norāda priekšnieku.

Rekursija loģiskajā datu modelī
Rekursija loģiskajā datu modelī

Šo tabulu arī tad izmantosim turpmāko vaicājumu veidošanā. Lasīt pārējo šī ieraksta daļu »