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.

Rudens – pļaujas laiks

Tā vien izskatās, ka pasākumi par datubāzēm birst gandrīz straumēm. Tātad visi, kam vārds Microsoft SQL Server kaut ko izsaka, var iepētīt sīkāk .NET grupas mājaslapas aktualitātes. Citēju:

“Grupas biedriem labi pazīstamais cilvēks Macejs Pilecki, ir izteicis vēlmi dalīties pieredzē, šoreiz apskatot atmiņas izmantošanas īpatnības MS SQL serverī. Prezentācijas tēma – „Dude, Where Is My Memory? Understanding Microsoft SQL Server Memory Usage and Management”. Jāatzīmē, ka prezentācijai piešķirts augstākais tehniskais līmenis (Level 400). Lai vieglāk uztvertu domu, ir padomāts par neformālāku norises vietu. Esat laipni gaidīti 30. septembrī, plkst. 18:00 – 20:00, Beļģu alus kafejnīcā Braserija „Bon Vivant”, Mārstaļu iela 8, Rīga.”

Ziņas autors Andrejs Mamontovs man teica, ka pasākums neesot tikai grupas biedriem, kaut gan tiešā tekstā oriģinālajā ziņā tas nav minēts :)

Par to, kas ir ziņā minētais Maciej Pilecki, sīkāk zin stāstīt google.

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.

Jaunu tabulu veidošana no esošajām

Īsti vīri, kā zināms, bekapus (latviski gan būtu rezerves kopijas) netaisa. Un šis arī īsti nebūs raksts par rezerves kopijām to klasiskajā izpratnē, bet par to kā ātri, ērti un vienkārši izveidot kopijas tabulai (-lām) līdz ar visiem datiem, kuru datus Jūs taisaties mainīt vai arī gribat patestēt, vai arī jebkādiem citiem nolūkiem. Un te nu mēs nonākam pie situācijas, kad kārtējo reizi katra DBVS dara tā kā pašai patīk (drīzāk gan jau kā tās izstrādātāji nolēmuši) un vismaz trīs zemāk apskatītajās (Oracle, Microsoft SQL Server un MySQL) katrā tas ir savādāk.

Oracle

Lai izveidotu jaunu tabulu Oracle tiek lietots CREATE TABLE DDL teikums. Tas paliek spēkā arī tad, ja tabula tiek veidota balstoties uz esošu vienu vai vairākām tabulām, t.i., apakšvaicājumu. Tātad ļooooooti vienkāršota sintakse ir šāda:

CREATE TABLE <tabulas nosaukums>
[<kolonu definīcijas>]
[<fiziskie atribūti>]
[<tabulas atribūti>]
AS <apakšvaicājums>

CREATE TABLE <tabulas nosaukums>

Atslēgvārdi CREATE TABLE sāk jebkuras tabulas izveides SQL teikumu. Tālāk tiek rakstīts tabulas nosaukums. Šeit jāatceras, ka Oracle noklusēti visus tabulas nosaukumus un vispār identifikatorus pārvērš uz lielajiem burtiem, tātad datu vārdnīcā Jūsu izveidotā tabula tabulas_nosaukums tiks pārveidota kā TABULAS_NOSAUKUMS. Ja nu Jums vien zināmu iemeslu dēļ tā nevēlaties, tad ir iespējams arī saglabāt tabulas nosaukumu precīzi tādu kā ievadījāt, ja to iekļauj pēdiņās. Piemēram, tabula “manas šausmas $%” tieši tā arī tiks saglabāta datubāzē, taču ar to nekādā ziņā nevajadzētu aizrauties, jo pēc tam Jums vienmēr nāksies nosaukumu rakstīt pēdiņās, kā arī tas ir perfekts veids kā rakstīt neuzturamu kodu. Protams nevajadzētu lietot arī rezervētos vārdus, jo tādu nosaukumu tabulas Oracle neļaus izveidot (ja vien atkal nelietosiet pēdiņas).

<kolonu definīcijas>

Šī daļa ir neobligāta. Pamatā šeit var definēt divas lietas – kolonu nosaukumus un ierobežojumus. Tas, protams, nebūt nav viss iespējamais, bet vairumā gadījumu jo sevišķi šai gadījumā ar to pietiek. Jāņem vērā vairākas lietas:

• kolonu nosaukumiem jāatbilst pieļaujamiem identifikatoru nosaukumiem Oraclē;
• kolonu skaitam jāsakrīt ar kolonu skaitu apakšvaicājumā;
• datu tipu definēt šeit nedrīkst, jo to nosaka apakšvaicājuma atgrieztais rezultāts.

<fiziskie atribūti>

Parasti šeit neko norādīt nevajag, jo visas noklusētās vērtības ir gana labas un pieņemamas. Taču iespēja ir un norādīt var tādas lietas kā:

• tabultelpu (tablespace);
• atribūtus ar kādiem, tabula fiziski tiks glabāta (pctfree, pctused u.c.);
• vai tabula tiks kompresēta (compressed);
• tabulas veidu (heap – parastā tabula, index organized – glabāta kā indeksa struktūra, external – ārējā), katrai no šiem tabulas veidiem vēl ir milzums atribūti;
• kādā klāsterī tā tiks glabāta;
• u.c.

<tabulas atribūti>

Līdzīgi kā iepriekšējā klauza, šī parasti ātrai tabulu izveidošanai nav nepieciešama, taču šeit var norādīt tādas lietas, kā:

• vai tabula tiks particionēta (partitioned);
• tabulas paralelitātes atribūtus;
• vai tabulai tiks glabāta noklusēta kolona (rowdependencies) norādot pēdējo sistēmas izmaiņu kārtas numuru (scn, system change number);
• iespējot/atspējot ierakstu pārvietošanu (row movement);
• u.c.

Šo un iepriekšējo klauzu es pieminu tikai tāpēc, lai saprastu, ka principā šeit ir iespējams definēt praktiski visas iespējamās lietas, ko Oracle atļauj CREATE TABLE teikumam. Aptuvenai apjoma sapratnei tās ir 73 lapas no dokumentācijas, kurā iespējas ir pamatā tikai uzskaitītas, nevis sīki apstāstīts, ko katra nozīmē

AS <apakšvaicājums>

Šeit grūti ko piebilst – šis var būt jebkurš Select teikums, sākot ar relatīvi vienkāršu Select teikumu, tajā var būt iekļautas kopas operācijas, kā arī nekas nekavē lietot kādu no savienojumu veidiem vai, piemēram, analītiskās funkcijas. Īsumā – visu, ko Jūs spējat Select teikumā sarakstīt, var arī izveidot kā CREATE TABLE AS <apakšveicājums> teikumu. Dažas piezīmes un nelieli triki, kas var kādreiz noderēt:

• dažkārt ir vēlme izveidot tabulu, kas būtu ar tādām pašām kolonām kā kāda cita (vai citu apvienojums), bet datus tai nepievienot. Savukārt kolonu skaits ir liels un slinkums visas tās pārskaitīt. Tad vienkārši jāpievieno SELECT apakšvaicājumam WHERE klauza, kas visām rindām dod aplamu rezultātu, piemēram, WHERE 1 = 0 (skat. piemēru 2).
• dažkārt gribas pievienot papildus kolonu ar nedefinētām (NULL) vērtībām, kas SELECT teikumā tieši atlasīta netiek, bet kura tiks vēlāk koriģēta izmantojot UPDATE teikumu. Tādā gadījumā select teikumam vienkārši jāpievieno jauna kolona, kurā atlasam NULL un tipizējam to ar noteiktu funkciju, nodrošinot to, ka kolona ir ar nepieciešamo datu tipu (skat. piemēru 6).

Oracle piemēri