Articles

SQL CHIT CHAT … Blogi Sql Serveristä

On 29 marraskuun, 2021 by

Yhteenveto

Yhteiset taulukkokohtaiset lausekkeet (Common Table Expressions) otettiin käyttöön SQL Server 2005:ssa. Ne edustavat yhtä useista Sql Serverissä käytettävissä olevista taulukkolausekkeiden tyypeistä. Rekursiivinen CTE on CTE-tyyppi, joka viittaa itseensä. Sitä käytetään yleensä hierarkioiden ratkaisemiseen.
Tässä postauksessa yritän selittää, miten CTE-rekursio toimii, mihin se sijoittuu Sql Serverissä käytettävissä olevien taulukko-lausekkeiden ryhmässä ja muutamia tapauskohtaisia skenaarioita, joissa rekursio loistaa.

Taulukko-lausekkeet

Taulukko-lauseke on nimetty kyselylauseke, joka edustaa relaatiotaulua. Sql Server tukee neljää erilaista taulukko-lauseketta;

  • Derived tables
  • Views
  • ITVF (Inline Table Valued Functions eli parametrisoidut näkymät)
  • CTE (Common Table Expressions eli yleiset taulukko-lausekkeet)
    • Rekursiiviset CTE:t

    • Yleisesti ottaen taulukko-lausekkeita ei materialisoida levylle. Ne ovat virtuaalisia taulukoita, jotka ovat läsnä vain RAM-muistissa (ne voivat valua levylle esim. muistipaineen, virtuaalitaulukon koon jne. seurauksena). Taulukko-lausekkeiden näkyvyys voi vaihdella, esim. näkymät ja ITVF ovat tietokantaobjekteja, jotka näkyvät tietokantatasolla, kun taas taulukko-lausekkeiden soveltamisala on aina SQL-lausekkeiden tasolla – taulukko-lausekkeet eivät voi toimia eri sql-lausekkeiden välillä erän sisällä.

    Taulukko-lausekkeiden edut eivät liity kyselyn suoritustehoon, vaan koodin loogiseen puoleen. Lausekkeet määritellään ulomman kyselyn FROM-lausekkeessa. Johdettujen taulukoiden soveltamisala on aina ulompi kysely.

    Seuraava koodi edustaa johdettua taulukkoa nimeltä AUSCust.

    Transact-SQL

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT AUSCust.*
    FROM (
    SELECT custid
    ,companyname
    FROM dbo.Customers
    WHERE country = N’Australia’
    ) AS AUSCust;
    –AUSCust on johdettu taulukko

    Johdettu taulukko AUSCust näkyy vain ulommassa kyselyssä, ja sen laajuus rajoittuu sql-lauseeseen.

    Näkymät

    Näkymät (joita joskus kutsutaan virtuaalisiksi relaatioiksi) ovat uudelleenkäyttökelpoisia taulukko-ilmauksia. Näkymän määritelmä tallennetaan Sql Server -objektina yhdessä sellaisten objektien kanssa, kuten käyttäjän määrittelemät taulukot, triggerit, funktiot, tallennetut proseduurit jne.
    Näkymien tärkein etu muihin taulukkoilmaisuihin verrattuna on niiden uudelleenkäytettävyys eli johdettujen kyselyjen ja CTE:iden laajuus rajoittuu yhteen lausekkeeseen.
    Näkymiä ei materialisoida, mikä tarkoittaa, että näkymien tuottamia rivejä ei tallenneta pysyvästi levylle. Indeksoidut näkymät on Sql Server (samanlainen mutta ei sama kuin materialisoidut näkymät muissa tietokanta-alustoissa) ovat erityyppisiä näkymiä, joiden tulosjoukko voidaan tallentaa pysyvästi levylle – lisää indeksoiduista näkymistä löytyy täältä.

    Vain muutamia perusohjeita SQL-näkymien määrittelyyn.

    • SELECT * näkymämäärittelyn yhteydessä käyttäytyy eri tavalla kuin silloin, kun sitä käytetään kyselyn elementtinä batchissa.

      Transact-vwTest

      AS
      SELECT *
      FROM dbo.T1
      …

      Näkymän määrittely sisältää kaikki sarakkeet taustalla olevasta taulusta dbo.T1 näkymän luontihetkellä. Tämä tarkoittaa, että jos muutamme taulukon skeemaa (eli lisäämme ja/tai poistamme sarakkeita), muutokset eivät näy näkymässä – näkymän määritelmä ei muutu automaattisesti tukemaan taulukon muutoksia. Tämä voi aiheuttaa virheitä tilanteissa, joissa esim. näkymä yrittää valita ei-olemassa olevia sarakkeita taustalla olevasta taulukosta.
      Ongelman korjaamiseksi voimme käyttää jompaakumpaa kahdesta järjestelmäproseduurista: sys.sp_refreshview tai sys.sp_refreshsqlmodule.
      Käyttäytymisen estämiseksi noudata parasta käytäntöä ja nimeä sarakkeet nimenomaisesti näkymän määrittelyssä.

    • Näkymät ovat taulukko-ilmaisuja eikä niitä siksi voi tilata. Näkymät eivät ole kursoreita! On kuitenkin mahdollista ”väärinkäyttää” TOP/ORDER BY -konstruktiota näkymän määrittelyssä yrittäen pakottaa lajitellun tulosteen. esim .

      Transakt-MyCursorView

      AS
      SELECT TOP(100 PERCENT) *
      FROM dbo.SomeTable
      ORDER BY column1 DESC

      Query-optimointiohjelma hylkää TOP/ORDER BY:n, koska taulukko-lausekkeen tulos on aina taulukko – TOP(100 PERCENT):n valitsemisessa ei ole kuitenkaan mitään järkeä. Taulukkorakenteiden idea on johdettu relaatiotietokantateorian käsitteestä, joka tunnetaan nimellä Relation.

    • Näkymään viittaavan kyselyn käsittelyn aikana näkymämäärittelyn kysely puretaan tai laajennetaan ja toteutetaan pääkyselyn yhteydessä. Tämän jälkeen konsolidoitu koodi(kysely) optimoidaan ja suoritetaan.

      • ITVF (Inline Table Valued Functions)

      ITVF:t ovat uudelleenkäytettäviä taulukkoilmaisuja, jotka tukevat syöttöparametreja. Funktioita voidaan käsitellä parametrisoituina näkyminä.

      CTE (Common Table Expressions)

      Yleiset taulukko-lausekkeet ovat samankaltaisia kuin johdetut taulukot, mutta niillä on useita tärkeitä etuja;

      CTE määritellään käyttämällä WITH-lauseketta, jota seuraa taulukko-lausekkeen määrittely. Epäselvyyksien välttämiseksi (TSQL käyttää WITH-avainsanaa muihin tarkoituksiin, esim. WITH ENCRYPTION jne.) CTE:n WITH-lauseketta edeltävä lauseke PITÄÄ päättää puolisarakkeeseen. Tämä ei ole tarpeen, jos WITH-lauseke on erän ensimmäinen lauseke eli VIEW/ITVF-määrittelyssä)

      Huomautus: ANSI-standardi tukee lausekkeen päättävää puolipylvästä, ja sitä suositellaan käytettäväksi osana TSQL-ohjelmointikäytäntöä.

      Rekursiivinen CTE

      SQL-palvelin tukee rekursiivisia kyselyominaisuuksia, jotka on toteutettu rekursiivisten CTE:iden avulla versiosta 2005(Yukon) lähtien.

      Rekursiivisen CTE:n elementit

      1. Ankkurijäsen(t) – Kyselymäärittelyt, jotka;
        1. palauttavat kelvollisen relaatiotulostaulukon
        2. suoritetaan AINOASTAAN KERRAN kyselyn suorituksen alussa
        3. sijoittuvat aina ennen ensimmäistä rekursiivista jäsenmäärittelyä
        4. viimeisen ankkurijäsenen perässä on oltava UNION ALL-operaattori. Operaattori yhdistää viimeisen ankkurijäsenen ja ensimmäisen rekursiivisen jäsenen
      2. UNION ALL-monijoukkooperaattori. Operaattori toimii
      3. rekursiivisella jäsenellä
      4. Rekursiivinen jäsen(t) – Kyselymäärittelyt, jotka;
        1. palauttavat kelvollisen relaatiotulostaulukon
        2. viittaavat CTE-nimeen. Viittaus CTE:n nimeen edustaa loogisesti edellistä tulosjoukkoa suoritusten sarjassa. ts. ensimmäinen ”edellinen” tulosjoukko sarjassa on tulos, jonka ankkurijäsen palautti.
      5. CTE:n kutsu – Lopullinen lauseke, joka kutsuu rekursiota
      6. Vikasietoinen mekanismi – MAXRECURSION-optio estää tietokantajärjestelmän äärettömät silmukat. Tämä on valinnainen elementti.

      Termination check

      CTE:n rekursiivisessa jäsenessä ei ole eksplisiittistä rekursion lopetustarkastusta.
      Monissa ohjelmointikielissä voidaan suunnitella metodi, joka kutsuu itseään – rekursiivinen metodi. Jokainen rekursiivinen metodi on lopetettava, kun tietyt ehdot täyttyvät. Tämä on eksplisiittinen rekursiopäättäminen. Tämän jälkeen metodi alkaa palauttaa arvoja. Ilman lopetuskohtaa rekursio voi päätyä kutsumaan itseään ”loputtomasti”.
      CTE:n rekursiivisen jäsenen lopetustarkistus on implisiittinen , mikä tarkoittaa, että rekursio pysähtyy, kun edellisestä CTE:n suorituksesta ei palauteta yhtään riviä.

      Tässä on klassinen esimerkki imperatiivisen ohjelmoinnin rekursiosta. Alla oleva koodi laskee kokonaisluvun faktoriaalin käyttämällä rekursiivista funktio(metodi)kutsua.

      Konsoliohjelman täydellinen koodi löytyy täältä.

      MAXRECURSION

      Kuten edellä mainittiin, rekursiiviset CTE:t samoin kuin mikä tahansa rekursiivinen operaatio voivat aiheuttaa loputtomia silmukoita, jos niitä ei ole suunniteltu oikein. Tämä tilanne voi vaikuttaa kielteisesti tietokannan suorituskykyyn. Sql Server -moottorissa on vikasietoinen mekanismi, joka ei salli loputtomia suorituksia.

      Oletusarvoisesti rekursiivista jäsentä voidaan kutsua vain 100 kertaa (tähän ei lasketa kertaluonteista ankkurin suoritusta). Koodi epäonnistuu rekursiivisen jäsenen 101. suorituksen yhteydessä.

      Msg 530, Level 16, State 1, Line xxx
      The statement terminated. Rekursion maksimimäärä 100 on käytetty loppuun ennen lausekkeen päättymistä.

      Rekursioiden määrää hallitaan MAXRECURSION n -kyselyoptiolla. Vaihtoehto voi ohittaa oletusarvoisen suurimman sallitun rekurssien lukumäärän. Parametri (n) edustaa rekursiotasoa. 0<=n <=32767

      Tärkeä huomautus:: MAXRECURSION 0 – poistaa rekursiorajan käytöstä!

      Kuvassa 1 on esimerkki rekursiivisesta CTE:stä ja sen elementeistä


      Kuva 1, Rekursiivisen CTE:n elementit

      Deklaratiivinen rekursio on aivan erilainen kuin perinteinen, imperatiivinen rekursio. Erilaisen koodirakenteen lisäksi voidaan havaita ero eksplisiittisen ja implisiittisen lopetustarkastuksen välillä. CalculateFactorial-esimerkissä eksplisiittinen lopetuskohta on selkeästi määritelty ehdolla: if (number == 0) then return 1.
      Yllä olevassa rekursiivisen CTE:n tapauksessa lopetuskohta on implisiittisesti määritelty INNER JOIN-operaatiolla, tarkemmin sanottuna sen ON-lausekkeen loogisen lausekkeen tuloksella: ON e.MgrId = c.EmpId. Taulukko-operaation tulos määrää rekursioiden määrän. Tämä tulee selvemmäksi seuraavissa kappaleissa.

      Rekursiivisen CTE:n käyttö Employee-hierarkian ratkaisemiseen

      On monia skenaarioita, joissa voimme käyttää rekursiivisia CTE:itä esim. elementtien erottamiseen jne. Yleisin skenaario, johon olen törmännyt monien vuosien jaksottamisen aikana, on ollut rekursiivisen CTE:n käyttäminen erilaisten hierarkkisten ongelmien ratkaisemiseen.

      Työntekijöiden puuhierarkia on klassinen esimerkki hierarkkisesta ongelmasta, joka voidaan ratkaista rekursiivisten CTE:iden avulla.

      Esimerkki

      Sitotaan, että meillä on organisaatio, jossa on 12 työntekijää. Sovelletaan seuraavia liiketoimintasääntöjä;

      • Työntekijällä on oltava yksilöllinen tunniste, EmpId
        • pakottaa: Primary Key constraint on EmpId column
      • Työntekijällä voi olla 0 tai 1 esimies.
        • enforced by: PK on EmpId, FK on MgrId ja NULLable MgrId-sarake
      • Päällikkö voi johtaa yhtä tai useampaa työntekijää.
        • enforced by: Foreign Key constraint(self referenced) on MgrId column
      • Päällikkö ei voi johtaa itseään.
        • enforced by: CHECK constraint on MgrId column

      Puuhierarkia on toteutettu taulussa nimeltä dbo.Employees. Skriptit löytyvät täältä.


      Kuvio 2, Employees-taulu

      Kuva 2, Employees-taulu

      Perehdytään rekursiivisen CTE:n toimintatapaan vastaamalla kysymykseen: Keitä ovat esimiehen, jonka EmpId = 3, suorat ja epäsuorat alaiset?

      Kuvassa 2 olevasta hierarkiapuusta näemme selvästi, että esimies (EmpId = 3) johtaa suoraan työntekijöitä; EmpId=7, EmpId=8 ja EmpId=9 ja epäsuorasti; EmpId=10, EmpId=11 ja EmpId=12.

      Kuvassa 3 on esitetty empId=3-hierarkiapuu ja odotettu tulos. Koodi löytyy täältä.


      Kuvio 3, EmpId=3 suorat ja epäsuorat alaiset

      Miten saimme siis lopputuloksen.

      Tässä iteraatiossa rekursiivinen osa viittaa aina edelliseen tulokseensa edellisestä iteraatiosta. Tuloksena on taulukko-lauseke (tai virtuaalinen taulukko) nimeltä cte1 (INNER JOINin oikealla puolella oleva taulukko). Kuten näemme, cte1 sisältää myös ankkuriosan. Aivan ensimmäisessä ajossa (ensimmäisessä iteraatiossa) rekursiivinen osa ei voi viitata edelliseen tulokseensa, koska edellistä iteraatiota ei ollut. Tämän vuoksi ensimmäisessä iteraatiossa suoritetaan vain ankkuriosa ja vain kerran koko prosessin aikana. Ankkurikyselyn tulosjoukko antaa rekursiiviselle osalle sen edellisen tuloksen toisessa iteraatiossa. Ankkuri toimii ikään kuin vauhtipyöränä 🙂

      Lopputulos rakentuu iteraatioiden kautta eli Ankkurin tulos + iteraation 1 tulos + iteraation 2 tulos …

      Looginen suoritusjärjestys

      Testauskysely suoritetaan noudattamalla alla olevaa loogista suoritusjärjestystä:

      1. Logiikkalausekkeen SELECT-lauseen ulkopuolella oleva lauseke cte1-lausekkeessa kutsuu rekursion. Ankkurikysely suoritetaan ja se palauttaa virtuaalisen taulukon nimeltä cte1. Rekursiivinen osa palauttaa tyhjän taulukon, koska sillä ei ole sen edellistä tulosta. Muista, että joukkoihin perustuvassa lähestymistavassa lausekkeet arvioidaan kaikki kerralla.
        Kuva 4, cte1:n arvo 1. iteraation jälkeen
      2. Toinen iteraatio alkaa.Tämä on ensimmäinen rekursio. Ankkuriosa pelasi osansa ensimmäisessä iteraatiossa ja palauttaa tästä eteenpäin vain tyhjiä joukkoja. Rekursiivinen osa voi kuitenkin nyt viitata edelliseen tulokseensa (cte1-arvo ensimmäisen iteraation jälkeen) INNER JOIN -operaattorissa. Taulukko-operaatio tuottaa toisen iteraation tuloksen alla olevan kuvan mukaisesti.
        Kuvio 5, cte1-arvo toisen iteraation jälkeen
      3. Toinen iteraatio tuottaa ei-tyhjän joukon, joten prosessi jatkuu kolmannella iteraatiolla eli toisella rekursiolla. Rekursiivinen elementti viittaa nyt toisen iteraation cte1-tulokseen.

        FIgure 6, cte1-arvo 3. iteraation jälkeen
      4. Kiinnostava asia tapahtuu neljännessä iteraatiossa – kolmannessa rekursioyrityksessä. Edellisen mallin mukaisesti rekursiivinen elementti käyttää edellisen iteraation cte1-tulosta. Tällä kertaa INNER JOIN -operaation tuloksena ei kuitenkaan palauteta yhtään riviä, ja rekursiivinen elementti palauttaa tyhjän joukon. Tämä on edellä mainittu implisiittinen lopetuskohta. Tässä tapauksessa INNER JOINin loogisen lausekkeen arviointi määrää rekursioiden määrän.
        Koska viimeinen cte1-tulos on tyhjä tulosjoukko, neljäs iteraatio (tai kolmas rekursio) ”peruutetaan” ja prosessi on onnistuneesti päättynyt.

        Kuva 7, Viimeinen iteraatio
        3. rekursion looginen peruuntuminen (viimeistä rekursiota, joka tuotti tyhjän tulosjoukon, ei lasketa) selviää paremmin seuraavassa, rekursiivisen CTE:n suoritussuunnitelman analyysiosassa.Voimme lisätä kyselyn loppuun OPTION(MAXRECURSION 2) -kyselyvaihtoehdon, joka rajoittaa sallittujen rekurssien määrän kahteen. Kysely tuottaa oikean tuloksen, joka osoittaa, että tähän tehtävään tarvitaan vain kaksi rekursiota.Huomautus: Fyysisen suorituksen näkökulmasta tulosjoukko lähetetään asteittain (rivejä kuplaamalla) verkon puskureihin ja takaisin asiakassovellukseen.

      Viimein vastaus yllä olevaan kysymykseen on :
      On kuusi työntekijää, jotka raportoivat suoraan tai epäsuorasti Emp:lle = 3. Kolme työntekijää, EmpId=7, EmpId=8 ja EmpId=9 ovat suoraan alaisia ja EmpId=10, EmpId=11 ja EmpId=12 ovat epäsuorasti alaisia.

      Tuntemalla rekursiivisen CTE:n mekaniikan voimme helposti ratkaista seuraavat ongelmat.

      • löydä kaikki työntekijät, jotka ovat hierarkkisesti EmpId=10:n yläpuolella (koodi tässä)
      • löydä EmpId=8:n suorat ja toisen tason alaiset(koodi tässä)

      Tässä toisessa esimerkissä kontrolloimme hierarkian syvyyttä rajoittamalla rekursioiden lukumäärää.
      Ankkurielementti antaa meille hierarkian ensimmäisen tason, tässä tapauksessa suorat alaiset. Jokainen rekursio siirtyy sitten yhden hierarkiatason alaspäin ensimmäisestä tasosta. Esimerkissä lähtökohtana on EmpId=8 ja hänen suorat alaisensa. Ensimmäinen rekursio siirtyy vielä yhden tason alaspäin hierarkiassa, jossa EmpId=8:n toisen tason alaiset ”asuvat”.

      Ympyräviittausongelma

      Yksi mielenkiintoinen asia hierarkioissa on se, että hierarkian jäsenet voivat muodostaa suljetun silmukan, jossa hierarkian viimeinen elementti viittaa ensimmäiseen elementtiin. Suljettua silmukkaa kutsutaan myös ympyräviittaukseksi.
      Tällaisissa tapauksissa implisiittinen lopetuskohta, kuten aiemmin selitetty INNER JOIN -operaatio, ei yksinkertaisesti toimi, koska se palauttaa aina ei-tyhjän tulosjoukon, jotta seuraava rekursio voi jatkua. Rekursio-osa jatkuu, kunnes se osuu Sql Serverin vikasietoiseen, MAXRECURSION-kyselyvaihtoehtoon.

      Kierrosviittaustilanteen havainnollistamiseksi aiemmin perustetun testiympäristön avulla meidän on

      • Poistettava Primary- ja Foreign key -rajoitukset dbo.Employees-taulukosta suljettujen silmukoiden skenaarioiden mahdollistamiseksi.
      • Luo ympyräviittaus (EmpId=10 hallinnoi epäsuoraa esimiestään , EmpId = 3)
      • Lisäämme edellisissä esimerkeissä käytettyä testikyselyä, jotta voimme analysoida suljetun silmukan elementtien hierarkiaa.

      Lisätty testikysely löytyy täältä.

      Katsotaanpa ennen kuin jatkamme ympyräviittaus-esimerkin käsittelyä, miten laajennettu testikysely toimii. Kommentoidaan WHERE-lausekkeen predikaatit pois(kaksi viimeistä riviä) ja ajetaan kysely alkuperäiselle dbo.Employee-taululle

      Kuva 8, Kiertosilmukoiden olemassaolon havaitseminen hierarkioissa

      Lisätyn kyselyn tulos on täsmälleen sama kuin edellisessä kokeilussa kuvassa 3 esitetty tulos. Tulos on laajennettu sisältämään seuraavat sarakkeet

      • pth – Esittää graafisesti nykyisen hierarkian. Aluksi ankkuriosassa lisätään yksinkertaisesti ensimmäinen alainen MgrId=3:lle, johtajalle, josta lähdemme liikkeelle. Nyt jokainen rekursiivinen elementti ottaa edellisen pth-arvon ja lisää siihen seuraavan alaisen.
      • recLvl – edustaa nykyistä rekursiotasoa. Ankkurin suoritus lasketaan recLvl=0
      • isCircRef – havaitsee ympyräviittauksen olemassaolon nykyisessä hierarkiassa(rivillä). Osana rekursiivista elementtiä se etsii sellaisen EmpId:n olemassaoloa, joka sisältyi aiemmin pth-merkkijonoon.
        i.e jos edellinen pth näyttää 3->8->10 ja nykyinen rekursio lisää ” ->3 ”, (3->8 >10 -> 3) mikä tarkoittaa, että EmpId=3 ei ole vain EmpId=10:n epäsuora esimies, vaan myös EmpId=10:n alainen – minä olen pomo tai sinun pomosi, ja sinä olet minun pomoni -tyyppinen tilanne 😐

      Tehdäänkö nyt tarpeelliset muutokset dbo:han.Employees nähdäksemme laajennetun testikyselyn toiminnassa.

      Poistetaan PK- ja FK-rajoitukset ympyräviittausten sallimiseksi ja lisätään taulukkoon ”paha poika ympyräviittaus”.

      Ajetaan laajennettu testikysely ja analysoidaan tulokset (älä unohda poistaa aiemmin kommentoitua WHERE-lauseketta skriptin lopussa)
      Skripti suorittaa 100 rekursiota, ennen kuin se keskeytyy oletusarvoisella MAXRECURSIONilla. Lopputulos rajoittuu kahteen rekursioon .. AND cte1.recLvl <= 2; jota tarvitaan EmpId=3:n hierarkian ratkaisemiseen.

      Kuvassa 9 näkyy suljetun silmukan hierarkia, suurin sallittu rekursioiden määrä uupunut virhe ja tuloste, joka osoittaa suljetun silmukan.

      Kuva 10, Ympyräviittaus havaittu

      Muutama huomautus ympyräviittausskriptistä.
      Skripti on vain ajatus siitä, miten hierarkioista voidaan löytää suljettuja silmukoita. Se ilmoittaa vain ensimmäisen ympyräviittauksen esiintymisen – yritä poistaa WHERE-lauseke ja tarkkailla tulosta.
      Skriptiä (tai sen vastaavia versioita) voidaan mielestäni käyttää tuotantoympäristössä esim. vianmääritykseen tai estämään ympyräviittausten luomista olemassa olevaan hierarkiaan. Se on kuitenkin suojattava sopivalla MAXRECURSION n:llä, jossa n on hierarkian odotettu syvyys.

      Tämä skripti on ei-relationaalinen ja perustuu traversaalitekniikkaan. On aina paras lähestymistapa käyttää deklaratiivisia rajoitteita (PK, FK, CHECK…), jotta estetään suljetut silmukat tiedoissa.

      Toteutussuunnitelman analyysi

      Tässä segmentissä selitetään, miten Sql Serverin kyselyoptimoija(QO) toteuttaa rekursiivisen CTE:n. On olemassa yleinen malli, jota QO käyttää rakennettaessa suoritussuunnitelmaa. Suorita alkuperäinen testikysely ja sisällytä varsinainen suoritussuunnitelma

      Kuten testikyselyssä, myös suoritussuunnitelmassa on kaksi haaraa: ankkurihaara ja rekursiivinen haara. Konkatenaatio-operaattori, joka toteuttaa UNION ALL -operaattorin, yhdistää näiden kahden haaran tulokset muodostaen kyselyn tuloksen.

      Yritetään sovittaa yhteen aiemmin mainittu looginen suoritusjärjestys ja prosessin todellinen toteutus.


      Kuva 11, Rekursiivisen CTE:n suoritussuunnitelma

      Datavirtaa seuraten (oikealta vasemmalle suuntautuen) prosessi näyttää seuraavalta:

      Ankkurielementti (suoritetaan vain kerran)

      1. Clustered Index Scan -operaattori – Järjestelmä suorittaa indeksin haun. Tässä esimerkissä se soveltaa lauseketta MgrId = @EmpId jäännöspredikaattina. Valitut rivit(sarakkeet EmpId ja MgrId) välitetään (rivi riviltä) takaisin edelliselle operaattorille.
      2. Compute Scalar Operaattori lisää sarakkeen tulosteeseen. Tässä esimerkissä lisätyn sarakkeen nimi on . Tämä edustaa rekurssien lukumäärää. Sarakkeen alkuarvo on 0; =0
      3. Concatenation – yhdistää kahden haaran syötteet. Ensimmäisessä iteraatiossa operaattori saa rivejä vain ankkurihaarasta. Se muuttaa myös tulossarakkeiden nimet. Tässä esimerkissä uudet sarakkeiden nimet ovat:
        1. = tai * * pitää sisällään rekursiivisessa haarassa annettujen rekursioiden määrän. Sillä ei ole arvoa ensimmäisessä iteraatiossa.
        2. = EmpId(ankkuriosasta) tai EmpId(rekursiivisesta osasta)
        3. = MgrId(ankkuriosasta) tai MgrId (rekursiivisesta osasta)
      4. Indeksikierukka (Lazy Spool) Tämä operaattori tallentaa Concatenation-operaattorista saadun tuloksen työtaulukkoon. Sen ominaisuudeksi ”Logical Operation” on asetettu ”Lazy Spool”. Tämä tarkoittaa, että operaattori palauttaa syöttämänsä rivit välittömästi eikä kerrytä kaikkia rivejä ennen kuin se saa lopullisen tulosjoukon (Eager Spool) . Työtaulu on rakenteeltaan klusteroitu indeksi, jonka avainsarake on rekursionumero. Koska indeksin avain ei ole yksilöllinen, järjestelmä lisää indeksin avaimeen sisäisen 4 tavun yksilöintitunnisteen varmistaakseen, että kaikki indeksin rivit ovat fyysisen toteutuksen näkökulmasta yksilöitävissä. Operaattorilla on myös ominaisuus ”With Stack”, jonka arvoksi on asetettu ”True”, mikä tekee tästä spool-operaattorin versiosta Stack Spool Stack Spool -operaattorilla on aina kaksi komponenttia – Index Spool, joka rakentaa indeksirakenteen, ja Table Spool, joka toimii Index Spoolin rakentamaan työtaulukkoon tallennettujen rivien kuluttajana.
        Tässä vaiheessa Index Spool -operaattori palauttaa rivejä SELECT-operaattorille ja tallentaa samat rivit työtaulukkoon.
      5. SELECT-operaattori palauttaa EmpId- ja MgrId-tiedot ( , ). Se sulkee tuloksesta pois. Rivit lähetetään verkkopuskuriin sitä mukaa, kun ne saapuvat seuraavilta operaattoreilta

      Kun kaikki Index Scan -operaattorin rivit on käytetty loppuun, Concatenation-operaattori vaihtaa kontekstin rekursiiviseen haaraan. Ankkurihaaraa ei suoriteta uudelleen prosessin aikana.

      Rekursiivinen elementti

      1. Taulukkopooli (Lazy Spool). Operaattorilla ei ole syötteitä, ja kuten kohdassa (4) mainittiin, se toimii Index Spoolin tuottamien ja klusteroituun työpöytään tallennettujen rivien kuluttajana. Sen ominaisuudeksi ”Primary Node” on asetettu 0, joka osoittaa Index Spool Node Id:n. Se korostaa näiden kahden operaattorin riippuvuutta. Operaattori
        1. poistaa edellisessä rekursiossa lukemansa rivit. Tämä on ensimmäinen rekursio, eikä aiemmin luettuja poistettavia rivejä ole. Työtaulussa on kolme riviä (kuva 4).
        2. Lukee rivejä, jotka on lajiteltu indeksiavaimen + uniquifierin mukaan laskevaan järjestykseen. Tässä esimerkissä ensimmäinen luettu rivi on EmpId=9, MgrId=3.

        Viimeiseksi operaattori nimeää tulossarakkeiden nimet uudelleen. =, = ja tulee .
        Huomautus: Taulukkokierrätysoperaattori voidaan havaita INNER JOINin oikealla puolella olevana cte1-lausekkeena (kuva 4)

      2. Compute Scalar Operaattori lisää 1 aiemmin sarakkeeseen.tallennettuun nykyiseen rekursioiden lukumäärään.Tulos tallennetaan uuteen sarakkeeseen, . = + 1 = 0 + 1 = 1. Operaattori tulostaa kolme saraketta, kaksi taulukkokierrosta ( ja ) ja
      3. Nested Loop(I) -operaattori vastaanottaa rivejä ulommasta syötteestään, joka on Compute Scalar edellisestä vaiheesta, ja käyttää sitten – edustaa EmpId:tä taulukkokierroksen operaattorista, jäännöspredikaattina indeksin etsintä (Index Scan) -operaattorissa, joka on sijoitettu silmukan sisempään syötteeseen. Sisempi sisääntulo suoritetaan kerran jokaista ulomman sisääntulon riviä kohden.
      4. Index Scan -operaattori palauttaa kaikki kvalifioidut rivit dbo.Employees-taulusta (kaksi saraketta; EmpId ja MgrId) sisäkkäisen silmukan operaattorille.
      5. Sisäkkäinen silmukka(II): Operaattori yhdistää ulommasta syötteestä sekä EmpId- ja MgrId-tiedot sisäisestä syötteestä ja siirtää kolme sarakkeen riviä seuraavalle operaattorille.
      6. Assert-operaattoria käytetään sellaisten ehtojen tarkistamiseen, jotka edellyttävät kyselyn keskeyttämistä virheilmoituksella. Rekursiivisten CTE:iden tapauksessa assert-operaattori toteuttaa ”MAXRECURSION n” -kyselyvaihtoehdon. Se tarkistaa, onko rekursiivinen osa saavuttanut sallitun (n) rekursioiden määrän vai ei. Jos nykyinen rekursioiden määrä (ks. vaihe 7) on suurempi kuin (n), operaattori palauttaa arvon 0 aiheuttaen ajovirheen. Tässä esimerkissä Sql Server käyttää oletusarvoa MAXRECURSION 100. Lauseke näyttää seuraavalta: CASE WHEN > 100 THEN 0 ELSE NULL Jos päätämme sulkea pois vikasietoisuuden lisäämällä MAXRECURSION 0, assert-operaattoria ei sisällytetä suunnitelmaan.
      7. Konkatenointi yhdistää kahden haaran syötteet. Tällä kertaa se saa syötteen vain rekursiivisesta osasta ja tulostaa sarakkeet/ruudut kuten vaiheessa 3.
      8. Index Spool (Lazy Spool) lisää konkatenaatio-operaattorin tuloksen työtaulukkoon ja siirtää sen sitten SELECT-operaattorille. Tässä vaiheessa työtaulukossa on yhteensä neljä riviä: kolme riviä ankkurin suorituksesta ja yksi ensimmäisestä rekursiosta. Työtaulukon klusteroidun indeksirakenteen mukaisesti uusi rivi tallennetaan työtaulukon loppuun

      9. Prosessi jatkuu nyt vaiheesta 6. Table spool -operaattori poistaa aiemmin luetut rivit (kolme ensimmäistä riviä) työtaulusta ja lukee viimeiseksi lisätyn rivin, neljännen rivin.

      Johtopäätös

      CTE(Common table expressions) on Sql Serverissä käytettävissä oleva taulukkolausekkeiden tyyppi. CTE on itsenäinen taulukkolauseke, joka voidaan nimetä ja johon voidaan viitata kerran tai useammin pääkyselyssä.
      Yksi tärkeimmistä CTE:iden käyttökohteista on rekursiivisten kyselyiden kirjoittaminen. Rekursiiviset CTE:t noudattavat aina samaa rakennetta – ankkurikysely, UNION ALL -monijoukkooperaattori, rekursiivinen jäsen ja lauseke, joka kutsuu rekursiota. Rekursiivinen CTE on deklaratiivista rekursiota, ja sillä on erilaiset ominaisuudet kuin sen imperatiivisella vastineella, esim. deklaratiivisen rekursioinnin lopetustarkistus on luonteeltaan implisiittinen – rekursioprosessi pysähtyy, kun edellisessä cte:ssä ei ole yhtään riviä palautettu.

Vastaa

Sähköpostiosoitettasi ei julkaista.