I-ProHoster > Блог > Ukuphatha > Ukulinganisa kubhala futhi kufundwe kusizindalwazi

Ukulinganisa kubhala futhi kufundwe kusizindalwazi

Ukulinganisa kubhala futhi kufundwe kusizindalwazi
Kwedlule isihloko Ngichaze umqondo kanye nokuqaliswa kwesizindalwazi esakhelwe phezu kwesisekelo semisebenzi, esikhundleni samathebula nezinkambu njengakusizindalwazi esihlobene. Inikeze izibonelo eziningi ezibonisa izinzuzo zale ndlela kuneyakudala. Abaningi babathole bengagculisi ngokwanele.

Kulesi sihloko, ngizobonisa ukuthi lo mqondo ukuvumela kanjani ukuthi ulinganise ngokushesha futhi kalula ukubhala nokufunda ku-database ngaphandle koshintsho ku-logic yokusebenza. Ukusebenza okufanayo kuzanywe ukusetshenziswa kuma-DBMS esimanje (ikakhulukazi, i-Oracle ne-Microsoft SQL Server). Ekupheleni kwalesi sihloko ngizobonisa ukuthi abakwenzile, ukukubeka kancane, akuzange kusebenze kahle kakhulu.

Incazelo

Njengangaphambili, ukuze ngiqonde kangcono ngizoqala incazelo ngezibonelo. Ake sithi sidinga ukusebenzisa umqondo ozobuyisela uhlu lweminyango nenani labasebenzi abakuyo kanye neholo labo lonke.

Ku-database esebenzayo izobukeka kanje:

CLASS Department ‘Отдел’;
name ‘Наименование’ = DATA STRING[100] (Department);

CLASS Employee ‘Сотрудник’;
department ‘Отдел’ = DATA Department (Employee);
salary ‘Зарплата’ =  DATA NUMERIC[10,2] (Employee);

countEmployees ‘Кол-во сотрудников’ (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
salarySum ‘Суммарная зарплата’ (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;

SELECT name(Department d), countEmployees(d), salarySum(d);

Ubunkimbinkimbi bokusebenzisa lo mbuzo kunoma iyiphi i-DBMS buzolingana nalo O(inani labasebenzi)ngoba lesi sibalo sidinga ukuskenwa kwetafula lonke labasebenzi bese kubahlanganisa ngomnyango. Kuzophinde kube nabancane (sikholelwa ukuthi baningi abasebenzi abaningi kuneminyango) okungezayo kuye ngohlelo olukhethiwe O(inombolo yelogi yabasebenzi) noma O(inombolo yeminyango) ukuqoqana nokunye.

Kuyacaca ukuthi ukukhishwa okuphezulu kungase kuhluke kuma-DBMS ahlukene, kodwa ubunzima ngeke bushintshe nganoma iyiphi indlela.

Ekusetshenzisweni okuhlongozwayo, i-DBMS esebenzayo izokhiqiza umbuzo owodwa omncane ozobala amanani adingekayo omnyango, bese yenza UKUJOYINA nethebula lomnyango ukuze uthole igama. Nokho, kumsebenzi ngamunye, lapho umemezela, kungenzeka ukusetha umaka okhethekile WE-MATERIALIZED. Isistimu izodala ngokuzenzakalelayo inkambu ehambisanayo yomsebenzi ngamunye onjalo. Lapho ushintsha inani lomsebenzi, inani lenkambu nalo lizoshintsha kumsebenzi ofanayo. Lapho ufinyelela lo msebenzi, inkambu ebalwe ngaphambilini izofinyelelwa.

Ikakhulukazi, uma usetha okuthi MATERIALIZED kumisebenzi countAbasebenzi и iholoIsamba, khona-ke izinkambu ezimbili zizokwengezwa etafuleni nohlu lweminyango, ezogcina isibalo sabasebenzi kanye nesamba somholo wabo. Noma nini lapho kuba noshintsho kubasebenzi, amaholo abo noma izinhlaka zomnyango, uhlelo luzoshintsha ngokuzenzakalelayo amanani ale mikhakha. Umbuzo ongenhla uzofinyelela lezi zinkambu ngokuqondile futhi uzosetshenziswa kuwo O(inombolo yeminyango).

Iyini imikhawulo? Into eyodwa kuphela: umsebenzi onjalo kufanele ube nenani elilinganiselwe lamanani wokufaka lapho inani lawo lichazwa khona. Uma kungenjalo, ngeke kwenzeke ukwakha itafula eligcina wonke amanani alo, ngoba angeke kube khona itafula elinenombolo engapheli yemigqa.

Isibonelo:

employeesCount ‘Количество сотрудников с зарплатой > N’ (Department d, NUMERIC[10,2] N) = 
    GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;

Lo msebenzi uchazwa ngenani elingapheli lamanani ka-N (isibonelo, noma yiliphi inani elingalungile lifanelekile). Ngakho-ke, awukwazi ukubeka i-MATRIALIZED kuyo. Ngakho-ke lokhu kuwumkhawulo onengqondo, hhayi owobuchwepheshe (okungukuthi, hhayi ngoba asikwazanga ukuwusebenzisa). Uma kungenjalo, ayikho imikhawulo. Ungasebenzisa amaqoqo, ukuhlunga, KANYE kanye NOMA, UKUHLUKANA, ukuphindaphinda, njll.

Isibonelo, enkingeni engu-2.2 ye-athikili edlule, ungafaka i-MATERIALIZED kuyo yomibili imisebenzi:

bought 'Купил' (Customer c, Product p, INTEGER y) = 
    GROUP SUM sum(Detail d) IF 
        customer(order(d)) = c AND 
        product(d) = p AND 
        extractYear(date(order(d))) = y MATERIALIZED;
rating 'Рейтинг' (Customer c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y MATERIALIZED;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;

Isistimu ngokwayo izodala ithebula elilodwa elinokhiye bohlobo ikhasimende, Product и INTEGER, izongeza izinkambu ezimbili kuyo futhi izobuyekeza amanani enkambu kuzo nganoma yiziphi izinguquko. Uma kwenziwa amanye amakholi kule misebenzi, ngeke abalwe, kodwa kunalokho amanani azofundwa ezinkambini ezihambisanayo.

Ngokusebenzisa lo mshini, ungakwazi, ngokwesibonelo, ukuqeda ukuphindaphinda (CTE) emibuzweni. Ikakhulukazi, cabangela amaqembu akha isihlahla esebenzisa ubuhlobo bengane/nomzali (iqembu ngalinye linesixhumanisi esiya kumzali walo):

parent = DATA Group (Group);

Kusizindalwazi esisebenzayo, i-recursion logic ingacaciswa kanje:

level (Group child, Group parent) = RECURSION 1l IF child IS Group AND parent == child
                                                             STEP 2l IF parent == parent($parent);
isParent (Group child, Group parent) = TRUE IF level(child, parent) MATERIALIZED;

Kusukela ngomsebenzi nguMzali imakwe ngokuthi MATERIALIZED, khona-ke itafula elinokhiye ababili (amaqembu) lizodalelwa lona, ​​lapho inkambu nguMzali kuzoba yiqiniso kuphela uma ukhiye wokuqala kuyingane yesibili. Inani lokufakiwe kuleli thebula lizolingana nenani lamaqembu eliphindwe ngokujula okumaphakathi kwesihlahla. Uma udinga, isibonelo, ukubala inani lenzalo yeqembu elithile, ungasebenzisa lo msebenzi:

childrenCount (Group g) = GROUP SUM 1 IF isParent(Group child, g);

Ngeke ibe khona i-CTE embuzweni we-SQL. Kunalokho kuzoba ne-GROUP BY elula.

Usebenzisa lo mshini, ungaphinda wenze i-database ibe i-normalize kalula uma kunesidingo:

CLASS Order 'Заказ';
date 'Дата' = DATA DATE (Order);

CLASS OrderDetail 'Строка заказа';
order 'Заказ' = DATA Order (OrderDetail);
date 'Дата' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;

Uma ubiza umsebenzi Usuku kumugqa we-oda, inkambu okukhona kuyo inkomba izofundwa etafuleni ngemigqa yokuhleleka. Uma idethi ye-oda ishintsha, isistimu ngokwayo izobala kabusha ngokuzenzakalelayo idethi eyenziwe i-denormalized kulayini.

Izinzuzo

Lo mshini uphelele ngani? Kuma-DBMS akudala, ngaphandle kwemibuzo yokubhala kabusha, umthuthukisi noma i-DBA angashintsha izinkomba kuphela, anqume izibalo futhi atshele umhleli wemibuzo ukuthi asebenzise kanjani (futhi ama-HINT atholakala kuphela kuma-DBMS ezentengiso). Kungakhathaliseki ukuthi bazama kangakanani, ngeke bakwazi ukuqedela umbuzo wokuqala esihlokweni esithi O (inani leminyango) ngaphandle kokushintsha imibuzo noma ukwengeza izibangeli. Kuhlelo oluhlongozwayo, esigabeni sokuthuthukiswa akudingeki ucabange ngesakhiwo sokugcinwa kwedatha nokuthi yiziphi izilinganiso ozozisebenzisa. Konke lokhu kungashintshwa kalula ku-fly, ngokuqondile ekusebenzeni.

Ngokwenza kubukeka kanje. Abanye abantu bathuthukisa ukucabanga okunengqondo ngokusekelwe emsebenzini abawenzayo. Abawaqondi ama-algorithms nokuba yinkimbinkimbi kwawo, noma izinhlelo zokusebenza, noma izinhlobo zokujoyina, nanoma iyiphi enye ingxenye yobuchwepheshe. Laba bantu bangabahlaziyi bebhizinisi abaningi kunonjiniyela. Bese, konke lokhu kungena ekuhlolweni noma ekusebenzeni. Inika amandla ukuloga kwemibuzo ehlala isikhathi eside. Uma kutholwa umbuzo omude, abanye abantu (abasebenza kakhulu - ikakhulukazi i-DBA) banquma ukunika amandla i-MATERIALIZED emsebenzini othile omaphakathi. Lokhu kunciphisa ukurekhoda kancane (njengoba kudinga ukubuyekeza inkambu eyengeziwe kumsebenzi). Kodwa-ke, akuwona kuphela lo mbuzo osheshisa kakhulu, kodwa nabo bonke abanye abasebenzisa lo msebenzi. Ngesikhathi esifanayo, ukunquma ukuthi yimuphi umsebenzi okufanele uwenze kulula. Amapharamitha amabili ayinhloko: inani lamanani okufaka okungenzeka (lokhu ukuthi mangaki amarekhodi azoba etafuleni elihambisanayo), nokuthi asetshenziswa kangaki kweminye imisebenzi.

Ama-Analog

Ama-DBMS okuhweba esimanje anezindlela ezifanayo: UKUBUKA KWEMATERIALIZED nge-FAST REFRESH (Oracle) kanye ne-INDEXED VIEW (Iseva ye-Microsoft SQL). Ku-PostgreSQL, UKUBUKA KWE-MATERIALIZED akukwazi ukubuyekezwa kuthengiselwano, kodwa kuphela ngesicelo (ngisho nangemikhawulo eqinile), ngakho-ke asikucabangi. Kodwa banezinkinga ezimbalwa ezinciphisa kakhulu ukusetshenziswa kwazo.

Okokuqala, ungavumela ukwenza izinto ezibonakalayo kuphela uma usuvele udale i-VIEW evamile. Uma kungenjalo, kuzodingeka ukuthi ubhale kabusha izicelo ezisele ukuze ufinyelele ukubuka okusanda kwakhiwa ukuze usebenzise lokhu kuguqulwa. Noma shiya yonke into injengoba injalo, kodwa ngeke isebenze okungenani uma kukhona idatha ethile esivele ibalwe ngaphambilini, kodwa imibuzo eminingi ayisebenzisi ngaso sonke isikhathi, kodwa ibala kabusha.

Okwesibili, banenani elikhulu lemikhawulo:

Oracle

5.3.8.4 Imikhawulo Ejwayelekile Yokuvuselela Ngokushesha

Umbuzo ochazayo wokubuka okwenziwe okwenyama ukhawulelwe kanje:

  • Ukubuka okwenziwe okwenyama akumele kuqukathe izinkomba zezinkulumo ezingaphindi ezifana SYSDATE futhi ROWNUM.
  • Umbono owenziwe izinto ezibonakalayo akumele uqukathe izinkomba kuwo RAW or LONG RAW izinhlobo zedatha.
  • Ayikwazi ukuqukatha a SELECT uhlu lokulandelayo.
  • Ayikwazi ukuqukatha imisebenzi yokuhlaziya (isibonelo, RANK) ku SELECT isigatshana.
  • Ayikwazi ukubhekisela etafuleni lapho u- XMLIndex Inkomba ichaziwe.
  • Ayikwazi ukuqukatha a MODEL isigatshana.
  • Ayikwazi ukuqukatha a HAVING isigatshana esine-subquery.
  • Ayikwazi ukuqukatha imibuzo esesidlekeni enayo ANY, ALL, noma NOT EXISTS.
  • Ayikwazi ukuqukatha a [START WITH …] CONNECT BY isigatshana.
  • Ayikwazi ukuqukatha amathebula emininingwane eminingi kumasayithi ahlukene.
  • ON COMMIT ukubukwa okwenyama akukwazi ukuba namathebula emininingwane ekude.
  • Ukubuka okwenziwe isidleke kufanele kube nokuhlanganisa noma ukuhlanganisa.
  • Ukubuka kokujoyina okwenziwe ngezinto ezibonakalayo kanye nokubukwa okuhlanganisiwe okwenziwe nge- GROUP BY isigatshana asikwazi ukukhetha kuthebula elihleliwe lenkomba.

5.3.8.5 Imikhawulo Yokuvuselela Ngokushesha Ekubukeni Okubalulekile Ngokujoyina Kuphela

Ukuchaza imibuzo yokubukwa okuphathekayo ngokujoyina kuphela futhi akukho zibalo ezinemikhawulo elandelayo yokuvuselela ngokushesha:

  • Yonke imikhawulo evela ku-«Imikhawulo Ejwayelekile Yokuvuselela Ngokushesha".
  • Ngeke babe nakho GROUP BY izigaba noma izilinganiso.
  • Imigqa yawo wonke amatafula ku FROM uhlu kumele luvele ku SELECT uhlu lombuzo.
  • Amalogi okubuka enziwe ngezinto kufanele abe nemigqa yawo wonke amathebula ayisisekelo ku FROM uhlu lombuzo.
  • Awukwazi ukudala ukubuka okuphathekayo okuvuseleleke ngokushesha kusuka kumathebula amaningi ngokuhlanganisa okulula okuhlanganisa ikholomu yohlobo lwento SELECT isitatimende.

Futhi, indlela yokuvuselela oyikhethayo ngeke isebenze kahle uma:

  • Umbuzo ochazayo usebenzisa ukujoyina kwangaphandle okufana nokujoyina kwangaphakathi. Uma umbuzo ochazayo uqukethe ukuhlanganisa okunjalo, cabanga ukubhala kabusha umbuzo ochazayo ukuze uqukathe ukujoyina kwangaphakathi.
  • The SELECT Uhlu lokubuka okwenziwe imizimba luqukethe izinkulumo kumakholomu asuka kumathebula amaningi.

5.3.8.6 Imikhawulo Yokuvuselela Ngokushesha Ekubukeni Okubalulekile Okuhlanganisiwe

Ukuchaza imibuzo yokubukwa okuphathekayo okuhlanganisiwe noma okuhlanganisiwe kunemikhawulo elandelayo yokuvuselela ngokushesha:

Ukuvuselela okusheshayo kusekelwa kokubili ON COMMIT futhi ON DEMAND imibono eyenziwe ngezinto ezibonakalayo, nokho imikhawulo elandelayo iyasebenza:

  • Wonke amathebula ekubukeni okwenyama kumele abe namalogi okubuka enziwe imizimba, futhi amalogi okubuka enziwe imizimba kumele:
    • Iqukethe wonke amakholomu asuka kuthebula okubhekiselwe kulo ekubukeni okwenyama.
    • Cacisa nge ROWID futhi INCLUDING NEW VALUES.
    • Cacisa SEQUENCE isigatshana uma ithebula kulindeleke ukuthi libe nenhlanganisela yokufaka/ukulayisha okuqondile, okususiwe, kanye nezibuyekezo.

  • kuphela SUM, COUNT, AVG, STDDEV, VARIANCE, MIN futhi MAX zisekelwa ukuze zivuseleleke ngokushesha.
  • COUNT(*) kufanele icaciswe.
  • Imisebenzi ehlanganisiwe kufanele yenzeke kuphela njengengxenye engaphandle yenkulumo. Okusho ukuthi, ama-aggregates afana nalokhu AVG(AVG(x)) or AVG(x)+ AVG(x) akuvunyelwe.
  • Ngesamba ngasinye njenge AVG(expr), okuhambisanayo COUNT(expr) kumele abe khona. U-Oracle uncoma lokho SUM(expr) kucaciswe.
  • If VARIANCE(expr) or STDDEV(expr) kucacisiwe, COUNT(expr) futhi SUM(expr) kufanele icaciswe. U-Oracle uncoma lokho SUM(expr *expr) kucaciswe.
  • The SELECT ikholomu embuzweni ochazayo ayikwazi ukuba yinkulumo eyinkimbinkimbi enamakholomu asuka kumathebula ayisisekelo amaningi. Isixazululo esingase sibe khona kulokhu ukusebenzisa umbono owenziwe isidleke.
  • The SELECT uhlu kufanele luqukathe konke GROUP BY amakholomu.
  • Ukubuka okwenziwe okwenyama akusekelwe kuthebula elikude elilodwa noma amaningi.
  • Uma usebenzisa a CHAR uhlobo lwedatha kumakholomu okuhlunga welogi yokubuka eyenziwe yaba yinto ebonakalayo, amasethi ezinhlamvu zesayithi eliyinhloko kanye nokubuka okwenziwe okwenyama kufanele kufane.
  • Uma ukubuka okwenyama kunokukodwa kwalokhu okulandelayo, khona-ke ukuvuselela okusheshayo kusekelwa kuphela okufakwayo okuvamile kwe-DML kanye nemithwalo eqondile.
    • Ukubuka okwenziwe ngezinto nge MIN or MAX abahlanganayo
    • Ukubuka okwenziwe ngezinto ezinakho SUM(expr) kodwa cha COUNT(expr)
    • Ukubuka okwenziwe ngezinto ngaphandle COUNT(*)

    Umbono onjalo owenziwe waba yinyama ubizwa ngokuthi umbono owenziwe ngezinto ezifakwayo kuphela.

  • Umbono owenziwe ngenyama MAX or MIN ivuseleleka ngokushesha ngemva kokususa noma izitatimende ezixubile ze-DML uma ingenayo a WHERE isigatshana.
    Ukuvuselela okusheshayo okukhulu/kwamaminithi ngemva kokususwa noma i-DML exutshiwe ayinakho ukuziphatha okufanayo nekesi lokufaka kuphela. Isusa futhi ibale kabusha amanani aphezulu/amaminithi emaqenjini athintekile. Udinga ukuqaphela umthelela wayo wokusebenza.
  • Ukubuka okwenziwe ngezinto ezinokubukwa okuqanjwe igama noma imibuzo emincane ku FROM isigatshana singavuselelwa ngokushesha inqobo nje uma imibono ingahlanganiswa ngokuphelele. Ukuze uthole ulwazi ngokuthi yikuphi ukubuka okuzohlanganiswa, bheka I-Oracle Database SQL Language Reference.
  • Uma kungabikho ukujoyina kwangaphandle, ungase ube nezinketho ezingafanele futhi ujoyine WHERE isigatshana.
  • Ukubuka okuhlanganisiwe okuhlanganisiwe okuhlangene kwangaphandle kuvuseleleka ngokushesha ngemva kwe-DML evamile kanye nemithwalo eqondile, inqobo nje uma kulungiswe ithebula elingaphandle kuphela. Futhi, imigoqo eyingqayizivele kufanele ibe khona kumakholomu okujoyina ethebula lokuhlanganisa langaphakathi. Uma kukhona ukuhlanganisa kwangaphandle, wonke amajoyinti kufanele axhunywe ngawo ANDfuthi kufanele asebenzise ukulingana (=) umqhubi.
  • Okokubuka okwenziwe ngenyama nge CUBE, ROLLUP, amasethi eqembu, noma ukuhlanganisa kwawo, imikhawulo elandelayo iyasebenza:
    • The SELECT uhlu kufanele luqukathe isihlukanisi seqembu esingaba a GROUPING_ID ukusebenza kukho konke GROUP BY izinkulumo noma GROUPING imisebenzi eyodwa ngayinye GROUP BY isisho. Isibonelo, uma i- GROUP BY isigatshana sombono owenziwe ngezinto ezibonakalayo sithi "GROUP BY CUBE(a, b)", bese kuthi SELECT uhlu kufanele luqukathe noma "GROUPING_ID(a, b)»noma «GROUPING(a) AND GROUPING(b)»ukuze umbono owenziwe ngezinto ezibonakalayo uvuseleleke ngokushesha.
    • GROUP BY akufanele kuphumele kunoma yimaphi amaqembu ayimpinda. Ngokwesibonelo, "GROUP BY a, ROLLUP(a, b)" ayivuseleleki ngokushesha ngoba iholela emaqenjini ayimpinda "(a), (a, b), AND (a)".

5.3.8.7 Imikhawulo Yokuvuselela Ngokushesha Ekubukeni Okuphathekayo nge-UNION ALL

Ukubukwa kwezinto ezibonakalayo nge UNION ALL setha ukusekelwa komsebenzisi i REFRESH FAST inketho uma izimo ezilandelayo zaneliswa:

  • Umbuzo ochazayo kufanele ube ne- UNION ALL opharetha ezingeni eliphezulu.

    The UNION ALL i-opharetha ayikwazi ukushumekwa ngaphakathi kombuzo omncane, ngaphandle kokukodwa: I UNION ALL kungaba ku-subquery ku FROM isigatshana esihlinzekwe ukuthi umbuzo ochazayo ungokwefomu SELECT * FROM (buka noma buza nge- UNION ALL) njengakusibonelo esilandelayo:

    DALA UKUBUKA UKUBUKWA_with_unionall AS (KHETHA c.rowid crid, c.cust_id, 2 umaka OVELA KUmakhasimende c LAPHO c.cust_last_name = 'Smith' UNION BONKE KHETHA c.rowid crid, c.cust_id, 3 umaka KUSUKA kumakhasimende c LAPHO c.cust_last_ 'Jones'); DALA UKUBUKA OKUNGEZESIFUNDAZWE unionall_inside_view_mv VUSELELA NGESIKHATHI NGESIDINGO NJENGOBA KHETHA * KUSUKA ku-view_with_unionall;

    Qaphela ukuthi umbono view_with_unionall yanelisa izimfuneko zokuvuselela ngokushesha.

  • Ibhulokhi ngayinye yombuzo ku UNION ALL umbuzo kufanele wenelise izimfuneko zokubuka okwenziwe kabusha okuvuseleleka ngokushesha ngamanani ahlanganisiwe noma ukubuka okuphathekayo okuvuselelekayo okuvuselelekayo okunamajoyinti.

    Amalogi okubuka enziwe imizimba efanelekile kufanele adalwe emathebulani njengoba kudingekile ohlotsheni oluhambisanayo lokubuka okwenziwe kabusha okuvuseleleka ngokushesha.
    Qaphela ukuthi i-Oracle Database iphinde ivumele isimo esikhethekile sokubuka okwenziwe kwethebula elilodwa ngokujoyina kuphela uma kunikezwe ROWID ikholomu ifakiwe ku SELECT ohlwini nakulogi lokubuka elenziwe labonakala. Lokhu kuboniswa embuzweni ochazayo wokubuka view_with_unionall.

  • The SELECT Uhlu lombuzo ngamunye kumele lubandakanye a UNION ALL umaka, kanye ne UNION ALL ikholomu kumelwe ibe nenani elingashintshi lenombolo noma iyunithi yezinhlamvu ngayinye UNION ALL igatsha. Ngokuqhubekayo, ikholomu yomaka kufanele ivele endaweni ye-ordinal efanayo ku- SELECT uhlu lwebhulokhi yombuzo ngamunye. Bona "I-UNION ALL Marker bese Ubhala kabusha Umbuzo»ukuthola ulwazi olwengeziwe mayelana UNION ALL izimpawu.
  • Ezinye izici ezifana nokujoyina kwangaphandle, imibuzo yokubuka ye-aggregate kuphela kanye namathebula akude awasekelwe ekubukeni okwenziwe ngenyama UNION ALL. Qaphela, nokho, ukuthi imibono eyenziwe yaba yinto esetshenziswa ekuphindaphindeni, engaqukathi ukuhlanganisa noma izilinganiso, ingavuselelwa ngokushesha uma UNION ALL noma amatafula akude asetshenziswa.
  • Ipharamitha yokuqalisa ukuhambisana kufanele isethelwe ku-9.2.0 noma ngaphezulu ukuze udale ukubuka okuphathekayo okuvuseleleke ngokushesha nge UNION ALL.

Angifuni ukucasula abalandeli be-Oracle, kodwa ngokwahlulela ngohlu lwabo lwemikhawulo, kubonakala sengathi lo mshini awubhalwanga esimweni esivamile, usebenzisa uhlobo oluthile lwemodeli, kodwa izinkulungwane zamaNdiya, lapho wonke umuntu wanikezwa ithuba bhala igatsha labo, futhi yilowo nalowo wenza lokho ayengakwenza. Ukusebenzisa le nqubo ngomqondo wangempela kufana nokuhamba endaweni yezimayini. Ungathola imayini nganoma yisiphi isikhathi ngokushaya eyodwa yemikhawulo engabonakali. Ukuthi isebenza kanjani futhi kungumbuzo ohlukile, kodwa kungaphezu kobubanzi balesi sihloko.

I-Microsoft SQL Server

Izidingo Ezengeziwe

Ngokungeziwe kuzinketho ze-SET kanye nezidingo zomsebenzi wokunquma, lezi zidingo ezilandelayo kufanele zihlangatshezwe:

  • Umsebenzisi owenza CREATE INDEX kufanele kube umnikazi wokubuka.
  • Uma udala inkomba, i IGNORE_DUP_KEY inketho kufanele isethwe kokuthi VALIWE (ukulungiselelwa okuzenzakalelayo).
  • Amathebula kufanele akhonjwe ngamagama anezingxenye ezimbili, i-schema.Igama letafula encazelweni yokubuka.
  • Imisebenzi echazwe ngumsebenzisi ebalulwe ekubukeni kumele idalwe kusetshenziswa i- WITH SCHEMABINDING inketho.
  • Noma yimiphi imisebenzi echazwe ngumsebenzisi ebalulwe ekubukeni kufanele ikhonjwe ngamagama anezingxenye ezimbili, ..
  • Indawo yokufinyelela idatha yomsebenzi ochazwe ngumsebenzisi kufanele ibe NO SQL, kanye nempahla yokufinyelela yangaphandle kufanele ibe NO.
  • Imisebenzi ye-Common Language Runtime (CLR) ingavela ohlwini olukhethiwe lokubuka, kodwa ayikwazi ukuba yingxenye yencazelo yokhiye wenkomba ohlanganisiwe. Imisebenzi ye-CLR ayikwazi ukuvela esigatshaneni esithi LAPHO sokubuka noma isigatshana esithi ON sokusebenza JOIN ekubukweni.
  • Imisebenzi ye-CLR nezindlela zezinhlobo ezichazwe ngumsebenzisi ze-CLR ezisetshenziswe encazelweni yokubuka kufanele zibe nezakhiwo ezisethwe njengoba kuboniswe kuthebula elilandelayo.

    Property
    Note

    I-DETERMINISTIC = TRUE
    Kufanele kumenyezelwe ngokusobala njengesibaluli sendlela ye-Microsoft .NET Framework.

    IQINISO = IQINISO
    Kufanele kumenyezelwe ngokusobala njengesibaluli sendlela ye-.NET Framework.

    UKUFINYELELA KWEDATHA = AYIKHO i-SQL
    Kunqunywa ngokusetha isibaluli se-DataAccess ku-DataAccessKind.None kanye nesibaluli se-SystemDataAccess ku-SystemDataAccessKind.None.

    UKUFINYELELA NGAPHANDLE = NO
    Lesi sici sishintsha ngokuzenzakalelayo sibe ngu-NO emigudwini ye-CLR.

  • Ukubuka kufanele kudalwe ngokusebenzisa i- WITH SCHEMABINDING inketho.
  • Ukubuka kufanele kubhekise kumathebula ayisisekelo kuphela akusizindalwazi esifanayo nokubuka. Ukubuka akukwazi ukubhekisela okunye ukubuka.
  • Isitatimende esithi KHETHA encazelweni yokubuka akumele sibe nezinto ezilandelayo ze-Transact-SQL:

    COUNT
    Imisebenzi ye-ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, KANYE OPENXML)
    OUTER ujoyina (LEFT, RIGHT, noma FULL)

    Ithebula elisuselwe (lichazwa ngokucacisa a SELECT isitatimende ku FROM isigatshana)
    Ukuzijoyina
    Icacisa amakholomu ngokusebenzisa SELECT * or SELECT <table_name>.*

    DISTINCT
    STDEV, STDEVP, VAR, VARP, noma AVG
    Inkulumo yethebula evamile (CTE)

    float1, umbhalo, umbhalo, isithombe, XML, noma i-filestream amakholomu
    I-Subquery
    OVER isigatshana, esihlanganisa ukukala noma ukuhlanganisa imisebenzi yewindi

    Izilandiso zombhalo ogcwele (CONTAINS, FREETEXT)
    SUM umsebenzi obhekisela kwisisho esingaguquleki
    ORDER BY

    Umsebenzi wokuhlanganisa ochazwe ngumsebenzisi we-CLR
    TOP
    CUBE, ROLLUP, noma GROUPING SETS opharetha

    MIN, MAX
    UNION, EXCEPT, noma INTERSECT opharetha
    TABLESAMPLE

    Okuguquguqukayo kwethebula
    OUTER APPLY or CROSS APPLY
    PIVOT, UNPIVOT

    Amasethi amakholomu amancane
    I-Inline (TVF) noma imisebenzi enenani lezitatimende eziningi (MSTVF)
    OFFSET

    CHECKSUM_AGG

    1 Ukubuka okunenkomba kungaqukatha float amakholomu; Nokho, amakholomu anjalo awakwazi ukufakwa kukhiye wenkomba ohlanganisiwe.

  • If GROUP BY ikhona, incazelo VIEW kumele iqukathe COUNT_BIG(*) futhi akufanele iqukathe HAVING. Lezi GROUP BY imikhawulo isebenza kuphela encazelweni yokubuka enenkomba. Umbuzo ungasebenzisa ukubuka okunenkomba ohlelweni lwayo lokusebenza noma ngabe kunganelisi lokhu GROUP BY imikhawulo.
  • Uma incazelo yokubuka iqukethe a GROUP BY isigatshana, ukhiye wenkomba ehlanganisiwe eyingqayizivele ungabhekisela kuphela kumakholomu ashiwo ku- GROUP BY isigatshana.

Kuyacaca lapha ukuthi amaNdiya ayengabandakanyeki, njengoba anquma ukukwenza ngokohlelo “kuncane esizokwenza, kodwa kahle.” Okusho ukuthi, banezimayini eziningi enkundleni, kodwa indawo yabo isobala. Into edumaza kakhulu yilo mkhawulo:

Ukubuka kufanele kubhekise kumathebula ayisisekelo kuphela akusizindalwazi esifanayo nokubuka. Ukubuka akukwazi ukubhekisela okunye ukubuka.

Kumagama ethu, lokhu kusho ukuthi umsebenzi awukwazi ukufinyelela omunye umsebenzi owenziwe imizimba. Lokhu kwehlisa yonke imibono ku-bud.
Futhi, lo mkhawulo (futhi ngokuqhubekayo embhalweni) unciphisa kakhulu izimo zokusetshenziswa:

Isitatimende esithi KHETHA encazelweni yokubuka akumele sibe nezinto ezilandelayo ze-Transact-SQL:

COUNT
Imisebenzi ye-ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, KANYE OPENXML)
OUTER ujoyina (LEFT, RIGHT, noma FULL)

Ithebula elisuselwe (lichazwa ngokucacisa a SELECT isitatimende ku FROM isigatshana)
Ukuzijoyina
Icacisa amakholomu ngokusebenzisa SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP, noma AVG
Inkulumo yethebula evamile (CTE)

float1, umbhalo, umbhalo, isithombe, XML, noma i-filestream amakholomu
I-Subquery
OVER isigatshana, esihlanganisa ukukala noma ukuhlanganisa imisebenzi yewindi

Izilandiso zombhalo ogcwele (CONTAINS, FREETEXT)
SUM umsebenzi obhekisela kwisisho esingaguquleki
ORDER BY

Umsebenzi wokuhlanganisa ochazwe ngumsebenzisi we-CLR
TOP
CUBE, ROLLUP, noma GROUPING SETS opharetha

MIN, MAX
UNION, EXCEPT, noma INTERSECT opharetha
TABLESAMPLE

Okuguquguqukayo kwethebula
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Amasethi amakholomu amancane
I-Inline (TVF) noma imisebenzi enenani lezitatimende eziningi (MSTVF)
OFFSET

CHECKSUM_AGG

UKUJOYINA NGAPHANDLE, UNION, ORDER BY nokunye akuvunyelwe. Kungaba lula ukucacisa ukuthi yini engasetshenziswa kunokuthi isetshenziswe yini. Uhlu cishe lungaba lufushane kakhulu.

Ukufingqa: isethi enkulu yemikhawulo kuzo zonke (ake siphawule ezentengiso) DBMS vs none (ngaphandle kokukodwa okunengqondo, hhayi ubuchwepheshe) kubuchwepheshe be-LGPL. Kodwa-ke, kufanele kuqashelwe ukuthi ukusebenzisa le nqubo ku-logic yokuxhumana kunzima kakhulu kunomqondo wokusebenza ochazwe.

Ukuqaliswa

Isebenza kanjani? I-PostgreSQL isetshenziswa “njengomshini obonakalayo”. Kukhona i-algorithm eyinkimbinkimbi ngaphakathi eyakha imibuzo. Lapha ikhodi yomthombo. Futhi alikho nje iqoqo elikhulu lama-heuristics anenqwaba yama-ifs. Ngakho-ke, uma unezinyanga ezimbalwa zokufunda, ungazama ukuqonda izakhiwo.

Ingabe isebenza ngempumelelo? Isebenza ngempumelelo. Ngeshwa, lokhu kunzima ukufakazela. Ngingasho nje ukuthi uma ucabangela izinkulungwane zemibuzo ekhona ezinhlelweni ezinkulu, lapho-ke ngokwesilinganiso zisebenza kahle kakhulu kunaleyo yonjiniyela omuhle. Umhleli omuhle kakhulu we-SQL angabhala noma yimuphi umbuzo ngempumelelo, kodwa ngemibuzo eyinkulungwane ngeke abe nesisusa noma isikhathi sokukwenza. Ukuphela kwento engingayibala njengobufakazi bokusebenza ukuthi amaphrojekthi amaningana asebenza endaweni eyakhelwe kule DBMS. Izinhlelo ze-ERP, ezinezinkulungwane zemisebenzi ehlukene YE-MATERIALIZED, enezinkulungwane zabasebenzisi nezizindalwazi ze-terabyte ezinamarekhodi angamakhulu ezigidi asebenza kuseva evamile yamaphrosesa amabili. Nokho, noma ubani angabheka/aphikise ukusebenza ngokulanda inkundla kanye ne-PostgreSQL, ukuvula ukufaka imibuzo ye-SQL nokuzama ukushintsha ingqondo nedatha lapho.

Ezihlokweni ezilandelayo, ngizophinde ngikhulume ngokuthi ungabeka kanjani imingcele emisebenzini, usebenze ngezikhathi zokushintsha, nokunye okuningi.

Source: www.habr.com

Engeza amazwana