Ibhalansi ibhala kwaye ifundeka kwisiseko sedatha

Ngaphambili nqaku Ndiyichaze ingqikelelo kunye nokuphunyezwa kovimba weenkcukacha owakhelwe phezu kwesiseko semisebenzi, endaweni yeetheyibhile kunye nemimandla njengakuvimba wolwazi wobudlelwane. Inike imizekelo emininzi ebonisa iingenelo zale ndlela yokufundisa ngaphezu kweyakudala. Abaninzi babafumanisa bengaqinisekanga ngokwaneleyo.

Kule nqaku, ndiza kubonisa indlela le ngcamango ikuvumela ukuba ubhale ngokukhawuleza kwaye ulungelelanise ubhala kwaye ufunde kwi-database ngaphandle kotshintsho kwi-logic yokusebenza. Ukusebenza okufanayo kuye kwazanywa ukuba kuphunyezwe kwii-DBMS zentengiso zanamhlanje (ngokukodwa, i-Oracle kunye ne-Microsoft SQL Server). Ekupheleni kwenqaku ndiza kubonisa ukuba oko bakwenzileyo, ukuyibeka kakuhle, akuzange kusebenze kakuhle.

inkcazelo

Njengangaphambili, ukuqonda ngcono ndiya kuqala inkcazo ngemizekelo. Masithi kufuneka siphumeze ingqiqo eya kubuyisela uluhlu lwamasebe anenani labasebenzi kuwo kunye nomvuzo wabo uwonke.

Kwisiseko sedatha esisebenzayo kuya kujongeka ngolu hlobo:

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);


Ubunzima bokwenza lo mbuzo kuyo nayiphi na i-DBMS buyakulingana O(inani labasebenzi)kuba olu balo lufuna ukuskena itheyibhile yonke yabasebenzi kwaye emva koko babekwe ngokwamaqela ngokwesebe. Kuya kubakho nabanye abancinci (sikholelwa ukuba baninzi abasebenzi abangaphezu kwamasebe) abongezelelekileyo ngokuxhomekeke kwisicwangciso esikhethiweyo O(inombolo yabasebenzi) okanye O(inani lamasebe) ukulungiselela amaqela kunye nokunye.

Kucacile ukuba i-overhead yokubulawa ingahluka kwii-DBMS ezahlukeneyo, kodwa ubunzima abuyi kutshintsha nangayiphi na indlela.

Kuphunyezo olucetywayo, i-DBMS esebenzayo iya kuvelisa i-subquery enye eya kubala amaxabiso afunekayo kwisebe, kwaye emva koko yenza i-JOIN kunye netafile yesebe ukufumana igama. Nangona kunjalo, kumsebenzi ngamnye, xa ubhengeza, kunokwenzeka ukuseta uphawu olukhethekileyo lwe-MATERIALIZED. Inkqubo iya kudala ngokuzenzekela indawo ehambelanayo yomsebenzi ngamnye onjalo. Xa utshintsha ixabiso lomsebenzi, ixabiso lentsimi liya kutshintsha kwakhona kwintengiselwano efanayo. Xa ufikelela kulo msebenzi, indawo ebalwe kwangaphambili iyakufikelelwa.

Ngokukodwa, ukuba umisela i-MATERIALIZED yemisebenzi count Abasebenzi и umvuzoSum, ngoko ke amabala amabini aya kongezwa kwitheyibhile noluhlu lwamasebe, aya kugcina inani labasebenzi kunye nomvuzo wabo uwonke. Nanini na xa kukho utshintsho kubasebenzi, imivuzo yabo okanye ukunxulumana nesebe, inkqubo iya kutshintsha ngokuzenzekelayo amaxabiso ala macandelo. Lo mbuzo ungentla uza kufikelela kule mimandla ngokuthe ngqo kwaye iya kuphunyezwa kuyo O(inani lamasebe).

Ziziphi izithintelo? Inye kuphela into: umsebenzi onje kufuneka ube nenani eliqingqiweyo lamaxabiso egalelo apho ixabiso lawo lichazwe khona. Ngaphandle koko, kuya kuba nzima ukwakha itafile egcina onke amaxabiso ayo, kuba ayinakuba netafile enenani elingapheliyo lemiqolo.

Umzekelo:

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


Lo msebenzi uchazwe kwinani elingenasiphelo lamaxabiso e-N (umzekelo, naliphi na ixabiso elingalunganga lifanelekile). Ke ngoko, awukwazi ukubeka i-MATERIALIZED kuyo. Ke lo ngumda osengqiqweni, hayi owobugcisa (oko kukuthi, hayi kuba singenako ukuwuphumeza). Ngaphandle koko, akukho zithintelo. Ungasebenzisa amaqela, ukuhlela, KUNYE kunye OKANYE, UKWAHLUKANA, ukuphindaphinda, njl.

Umzekelo, kwingxaki 2.2 yenqaku elidlulileyo, unokubeka i-MATERIALIZED kuyo yomibini 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;


Inkqubo ngokwayo iya kudala itafile enye kunye nezitshixo zohlobo umthengi, imveliso и NGAPHAKATHI, iyakongeza imihlaba emibini kuyo kwaye iya kuhlaziya amaxabiso entsimi kuwo naluphi na utshintsho. Xa ezinye iifowuni zenziwa kule misebenzi, aziyi kubalwa, kodwa amaxabiso aya kufundwa kwiindawo ezihambelanayo.

Ukusebenzisa le ndlela, ungenza, umzekelo, ukulahla i-recursions (CTE) kwimibuzo. Ngokukodwa, qwalasela amaqela enza umthi esebenzisa ubudlelwane bomntwana/nomzali (iqela ngalinye linekhonkco kumzali walo):

parent = DATA Group (Group);


Kwisiseko sedatha esisebenzayo, i-recursion logic ingachazwa ngolu hlobo lulandelayo:

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;


Ukususela kumsebenzi nguMzali iphawulwe MATERIALIZED, ngoko itafile enezitshixo ezibini (amaqela) izakuyenzelwa yona, apho umhlaba nguMzali iya kuba yinyani kuphela ukuba isitshixo sokuqala ngumntwana wesibini. Inani lamangenelo kule theyibhile liyakulingana nenani lamaqela aphindaphindwe ngomyinge wobunzulu bomthi. Ukuba ufuna, umzekelo, ukubala inani lenzala yeqela elithile, ungasebenzisa lo msebenzi:

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


Akuyi kubakho CTE kumbuzo weSQL. Endaweni yoko kuzakubakho iQELA elilula BY.

Usebenzisa olu matshini, unokwenza ngokulula ukuba isiseko sedatha singabi sisiqhelo xa kuyimfuneko:

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

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


Xa ufowunela umsebenzi Umhla kumgca womyalelo, intsimi apho kukho isalathisi iya kufundwa kwitheyibhile ngemigca yomyalelo. Xa usuku lokuodola lutshintsha, inkqubo ngokwayo iya kubala ngokuzenzekelayo umhla owenziwe uhlengahlengiso kumgca.

iingenelo

Yenzelwe ntoni le ndlela yonke? Kwii-DBMS zakudala, ngaphandle kwemibuzo yokubhala kwakhona, umphuhlisi okanye i-DBA inokutshintsha kuphela izalathisi, imisele amanani kwaye uxelele umcwangcisi wombuzo ukuba ayenze njani (kunye neeHINTs zifumaneka kuphela kwii-DBMS zorhwebo). Nokuba bazama kangakanani na, abayi kuba nako ukugqiba umbuzo wokuqala kwinqaku kwi O (inani lamasebe) ngaphandle kokutshintsha imibuzo okanye ukongeza izichukumisi. Kwiskimu esicetywayo, kwinqanaba lophuhliso akufanele ucinge malunga nesakhiwo sokugcinwa kwedatha kunye nokuba yeyiphi i-aggregations yokusebenzisa. Konke oku kunokutshintshwa ngokulula kwi-fly, ngokuthe ngqo ekusebenzeni.

Ngokwenza oko kubonakala ngathi. Abanye abantu baphuhlisa ingqiqo ngokuthe ngqo ngokusekelwe kumsebenzi okhoyo. Abaziqondi ii-algorithms kunye nobunzima bazo, okanye izicwangciso zokuphumeza, okanye iintlobo zokujoyina, okanye nayiphi na enye icandelo lobugcisa. Aba bantu ngabahlalutyi bezoshishino ngaphezu kwabaphuhlisi. Emva koko, konke oku kuya kuvavanyo okanye ukusebenza. Ivumela ukuloga kwemibuzo yexesha elide. Xa umbuzo omde uchongiwe, ngoko ke abanye abantu (ubuchwephesha obungakumbi - ngokusisiseko i-DBA) bathatha isigqibo sokuvumela i-MATERIALIZED kumsebenzi othile ophakathi. Oku kunciphisa ukurekhoda kancinci (ekubeni kufuna ukuhlaziya intsimi eyongezelelweyo kwintengiselwano). Nangona kunjalo, ayisiyiwo lo mbuzo kuphela okhawuleziswa ngokukhawuleza, kodwa nabo bonke abanye abasebenzisa lo msebenzi. Kwangaxeshanye, ukugqiba ukuba ngowuphi umsebenzi oza kuwenza akulula. Iiparamitha ezimbini eziphambili: inani lamaxabiso akhoyo (oku kukuba zingaphi iirekhodi eziya kuba kwitafile ehambelanayo), kwaye isetyenziswa kangaphi kweminye imisebenzi.

Analogs

Ii-DBMS zentengiso zanamhlanje zineendlela ezifanayo: UKUBONWA KWEMATERIALIZED nge-FAST REFRESH (Oracle) kunye ne-INDEXED VIEW (Iseva ye-Microsoft SQL). Kwi-PostgreSQL, i-MATERIALIZED VIEW ayinakuhlaziywa kwintengiselwano, kodwa kuphela ngesicelo (kwaye nakwizithintelo ezingqongqo kakhulu), ngoko asiyicingi. Kodwa baneengxaki ezininzi ezinciphisa kakhulu ukusetyenziswa kwazo.

Okokuqala, unokwenza kuphela ukwenza izinto ezibonakalayo ukuba sele wenze i-VIEW eqhelekileyo. Kungenjalo, kuya kufuneka uphinde ubhale izicelo ezishiyekileyo ukuze ufikelele kumbono omtsha owenziweyo ukuze usebenzise olu tshintsho. Okanye ushiye yonke into njengoko injalo, kodwa iya kuba ingasebenzi ukuba kukho idatha ethile esele ibalwe kwangaphambili, kodwa imibuzo emininzi ayisoloko iyisebenzisa, kodwa yibale kwakhona.

Okwesibini, banenani elikhulu lezithintelo:

Oracle

5.3.8.4 Izithintelo ngokuBanzi ekuHlaziyweni ngokukhawuleza

Umbuzo ochazayo wembono eyenziweyo uthintelwe ngolu hlobo lulandelayo:

• Imbono eyenziweyo mayingaqulathi iimbekiselo zokungaphindaphindi amabinzana afana SYSDATE kwaye ROWNUM.
• Umbono owenziweyo kufuneka ungaqulathi iireferensi RAW or LONG RAW iintlobo zedatha.
• Ayinakuqulatha a SELECT uluhlu lokubuza imibuzo.
• Ayinakuqulatha imisebenzi yohlalutyo (umzekelo, RANK) kwi SELECT isigatya.
• Ayinakubhekisa kwitafile apho i- XMLIndex isalathisi sichaziwe.
• Ayinakuqulatha a MODEL isigatya.
• Ayinakuqulatha a HAVING igatya elinombuzo ongaphantsi.
• Ayinakuqulatha imibuzo ebekwe kwindlwane enayo ANY, ALL, okanye NOT EXISTS.
• Ayinakuqulatha a [START WITH …] CONNECT BY isigatya.
• Ayinakuqulatha iitheyibhile zeenkcukacha ezininzi kwiindawo ezahlukeneyo.
• ON COMMIT iimbono ezenziweyo azinakuba neetafile zeenkcukacha ezikude.
• Iimboniselo ezifakwe kwindlwane kufuneka zibe nokudibanisa okanye ukudibanisa.
• Iimbono zokudityaniswa kwezinto ezibonakalayo kunye nezimvo ezidityanisiweyo kunye GROUP BY Igatya alinakukhetha kwitafile elungelelanisiweyo yesalathiso.

5.3.8.5 Izithintelo kuHlaziyo oluKhawuleza kwiiMbono zeMathiriyeli ngokuManywa kuphela

Ukuchaza imibuzo yeembono ezenziweyo ngokudibanisa kuphela kwaye akukho zidityaniso zinezithintelo zilandelayo ekuhlaziyeni ngokukhawuleza:

• Zonke izithintelo ezivela kwi «Izithintelo ngokuBanzi ekuHlaziyweni ngokukhawuleza«.
• Abanakuba nayo GROUP BY amagatya okanye aggregates.
• Imikrozo yazo zonke iitafile kwi FROM uluhlu kufuneka luvele kwi SELECT uluhlu lombuzo.
• Iilog zokujonga ezibonakalayo kufuneka zibekho kunye nemiqolo yazo zonke iitafile ezisezantsi kwi FROM uluhlu lombuzo.
• Awungekhe udale umbono okhawulezayo ovuselelekayo ovela kwiitafile ezininzi kunye nokudibanisa okulula okubandakanya uhlobo lwekholamu kwi SELECT ngxelo.

Kwakhona, indlela yokuhlaziya oyikhethayo ayizukusebenza kakuhle ukuba:

• Umbuzo ochazayo usebenzisa udibaniso lwangaphandle olusebenza njengedibaniso yangaphakathi. Ukuba umbuzo ochazayo uqulathe udibaniso olunjalo, cinga ngokuphinda ubhale umbuzo ochazayo ukuze uqulathe ukudibanisa kwangaphakathi.
• The SELECT Uluhlu lwemboniselo ebonakalayo luqulathe amabinzana akwikholamu ezisuka kwiitafile ezininzi.

5.3.8.6 Izithintelo kuHlaziyo oluKhawuleza kwiiMbono zeMathiriyeli ngeeAggregates

Ukuchaza imibuzo yeembono ezenziweyo kunye ne-aggregates okanye ukujoyina kunezithintelo zilandelayo ekuhlaziyeni ngokukhawuleza:

• Zonke izithintelo ezivela kwi «Izithintelo ngokuBanzi ekuHlaziyweni ngokukhawuleza«.

Ukuhlaziya ngokukhawuleza kuxhaswa zombini ON COMMIT kwaye ON DEMAND iimbono ezenziweyo, nangona kunjalo, le miqobo ilandelayo iyasebenza:

• Zonke iitheyibhile ezikumbono wezinto ezibonakalayo kufuneka zibe neendawo zokujonga izinto ezibonakalayo, kwaye iilogi zokujonga ezibonakalayo kufuneka:
  • Ziqulathe zonke iikholamu ezivela kwitheyibhile ekhankanyiweyo kumbono owenziweyo.
  • Chaza nge ROWID kwaye INCLUDING NEW VALUES.
  • Cacisa SEQUENCE igatya ukuba itheyibhile kulindeleke ukuba ibe nomxube wokufakela/umthwalo othe ngqo, ucinywe, kunye nohlaziyo.

• kuphela SUM, COUNT, AVG, STDDEV, VARIANCE, MIN kwaye MAX zixhaswa ukuhlaziya ngokukhawuleza.
• COUNT(*) kufuneka ichazwe.
• Imisebenzi edibeneyo kufuneka yenzeke kuphela njengenxalenye engaphandle yentetho. Oko kukuthi, aggregates ezifana AVG(AVG(x)) or AVG(x)+ AVG(x) akuvumelekanga.
• Kwi-aggregate nganye efana AVG(expr), ehambelanayo COUNT(expr) kufuneka abekho. U-Oracle uyakucebisa oko SUM(expr) kucaciswa.
• If VARIANCE(expr) or STDDEV(expr) kucacisiwe, COUNT(expr) kwaye SUM(expr) kufuneka ichazwe. U-Oracle uyakucebisa oko SUM(expr *expr) kucaciswa.
• The SELECT ikholamu kumbuzo ochazayo ayinakuba yintetho entsonkothileyo enezintlu ezisuka kwisiseko seetafile ezininzi. Indlela enokwenzeka yokusombulula oku kukusebenzisa umbono owenziwe ngendlwane.
• The SELECT uluhlu kufuneka luqulathe zonke GROUP BY Iikholamu.
• Imbono eyenziweyo ayisekelwanga kwitafile enye okanye ngaphezulu ezikude.
• Ukuba usebenzisa i- CHAR uhlobo lwedatha kwiikholamu zokucoca zelog yembonakalo ebonakalayo, iiseti zabalinganiswa bendawo enkulu kunye nembonakalo ebonakalayo kufuneka ifane.
• Ukuba umbono wezinto ezibonakalayo unenye yezi zilandelayo, ngoko ukuhlaziya ngokukhawuleza kuxhaswa kuphela kwiifakelo eziqhelekileyo ze-DML kunye nemithwalo ethe ngqo.
  • Iimbono eziphathekayo nge MIN or MAX Amanqaku ewonke
  • Iimbono eziphathekayo eziye SUM(expr) kodwa hayi COUNT(expr)
  • Iimbono eziphathekayo ngaphandle COUNT(*)

  Umbono onjalo wezinto ezibonakalayo ubizwa ngokuba ngumbono wokufaka kuphela.

• Umbono obonakalayo kunye MAX or MIN iyahlaziyeka ngokukhawuleza emva kokucima okanye ukuxuba iingxelo ze-DML ukuba ayinayo WHERE isigatya.
  Ukuhlaziywa ngokukhawuleza okukhulu/umzuzu emva kokucima okanye i-DML exutyiweyo ayinayo indlela yokuziphatha efana neyokufaka kuphela. Icima kwaye ibuyise amaxabiso aphezulu/min kumaqela achaphazelekayo. Kufuneka uqaphele impembelelo yayo yokusebenza.
• Iimbono eziphathekayo ezinezimvo ezichaziweyo okanye imibuzo kwi FROM Igatya linokuhlaziywa ngokukhawuleza ukuba iimbono zinokudityaniswa ngokupheleleyo. Ngolwazi malunga nokuba zeziphi iimbono eziza kudityaniswa, bona Oracle Database SQL Language Reference.
• Ukuba akukho zidibaniselwano zangaphandle, unokuba nokhetho olungenasizathu kwaye uzibandakanye kwi WHERE isigatya.
• Iimbono ezidityanisiweyo ezidityanisiweyo kunye nezidibanisi zangaphandle zihlaziyeka ngokukhawuleza emva kwe-DML eqhelekileyo kunye nemithwalo ethe ngqo, ngaphandle kokuba itafile yangaphandle kuphela ilungisiwe. Kwakhona, imiqobo eyodwa kufuneka ibekho kwiikholamu zokudityaniswa kwetafile yokudibanisa yangaphakathi. Ukuba kukho ukudityaniswa kwangaphandle, onke amakhonkco kufuneka adityaniswe ngawo ANDkwaye kufuneka basebenzise ukulingana (=) umsebenzisi.
• Ngeembono ezenziweyo nge CUBE, ROLLUP, iiseti zamaqela, okanye ukudityaniswa kwazo, ezi zithintelo zilandelayo ziyasebenza:
  • The SELECT uluhlu kufuneka luqulathe umahluli wamaqela onokuba ngu a GROUPING_ID umsebenzi kubo bonke GROUP BY intetho okanye GROUPING imisebenzi enye nganye GROUP BY intetho. Umzekelo, ukuba i GROUP BY igatya lembono eyenziweyo ngu "GROUP BY CUBE(a, b)", emva koko SELECT uluhlu kufuneka luqulathe nokuba "GROUPING_ID(a, b)"okanye"GROUPING(a) AND GROUPING(b)» ukuze umbono owenziweyo uhlaziyeke ngokukhawuleza.
  • GROUP BY akufuneki kubangele nawaphi na amaqela aphindiweyo. Umzekelo, "GROUP BY a, ROLLUP(a, b)" ayihlaziyeki ngokukhawuleza kuba iphumela kumaqela aphindiweyo "(a), (a, b), AND (a)«.

5.3.8.7 Izithintelo kuHlaziyo oluKhawuleza kwiiMbono zeMathiriyeli kunye ne-UNION BONKE

Iimbono eziphathekayo kunye ne UNION ALL misela inkxaso yomsebenzisi i REFRESH FAST ukhetho ukuba le miqathango ilandelayo yanelisiwe:

• Umbuzo ochazayo kufuneka ube ne UNION ALL umqhubi kwinqanaba eliphezulu.

  The UNION ALL umsebenzisi akanakulungiselelwa ngaphakathi kwesicelo esingaphantsi, ngaphandle kokunye: I UNION ALL inokuba kwi-subquery kwi FROM Igatya elinikeziweyo umbuzo ochazayo uyifom SELECT * FROM (jonga okanye ubuze nge UNION ALL) njengakumzekelo ulandelayo:

  YENZA IMBONO yokujonga_with_unionall AS (KHETHA c.rowid crid, c.cust_id, 2 umakishayo UKUSUKA kubathengi c APHO c.cust_last_name = 'Smith' UNION BONKE KHETHA c.rowid crid, c.cust_id, 3 umakishayo UKUSUKA kubathengi c APHO c.cust_last_ 'Jones'); YENZA UMBONO OLUBONAKALISWEYO unionall_inside_view_mv HLAZIYA NGOKUKHAWULEZA KWIMFUNO NJENGOKO KHETHA * UKUSUKA kumbono_nge_unionall;
  

  Qaphela ukuba umbono view_with_unionall iyanelisa iimfuno zokuhlaziya ngokukhawuleza.

• Ibhloko yombuzo ngamnye kwi UNION ALL Umbuzo kufuneka wanelise iimfuno zembono ekhawulezayo ehlaziyekayo eyenziwe ngezinto ezihlanganisiweyo okanye imbonakalo ekhawulezayo ehlaziyekayo eyenziwe ngezinto ezidibeneyo.

  Iilogi zembonakalo ezilungeleleneyo kufuneka zenziwe kwiitafile njengoko zifuneka kuhlobo oluhambelanayo lombono okhawulezayo ohlaziyekayo.
  Qaphela ukuba i-Database ye-Oracle ikwavumela imeko ekhethekileyo yetheyibhile enye imboniselo ebonakalayo kunye nokudibanisa kuphela kubonelelwe ROWID ikholam ibandakanyiwe kwi SELECT uluhlu nakwilog yembonakalo ebonakalayo. Oku kuboniswa kumbuzo ochazayo wembono view_with_unionall.

• The SELECT uluhlu lombuzo ngamnye kufuneka lubandakanye a UNION ALL umakishi, kunye ne UNION ALL ikholamu kufuneka ibenenani elahlukileyo elingaguqukiyo okanye ixabiso lomtya kwinto nganye UNION ALL isebe. Ngaphezu koko, ikholomu yokumakisha mayivele kwindawo efanayo kwi-ordinal position SELECT uluhlu lwebhloko yombuzo ngamnye. Bona"IMANYANO YONKE iMakisha kwaye uMbuzo uphinde ubhale»ukufumana ulwazi oluninzi malunga UNION ALL amakishi.
• Eminye imiba enje ngezidibaniselwano zangaphandle, faka-kuphela i-aggregate eyenziweyo yembonakalo yemibuzo kunye neetafile ezikude azixhaswanga kwiimbono ezenziweyo UNION ALL. Qaphela, nangona kunjalo, ukuba iimbono ezenziweyo ezisetyenziswe kuphindaphindo, ezingaqulathanga izidibaniselwano okanye iaggregates, zinokuhlaziyeka ngokukhawuleza xa UNION ALL okanye iitafile ezikude zisetyenziswa.
• Iparamitha yokuqalisa ukuhambelana kufuneka isetelwe ku-9.2.0 okanye ngaphezulu ukwenza umbono okhawulezayo ohlaziyekayo UNION ALL.

Andifuni ukubacaphukisa abalandeli be-Oracle, kodwa ngokujonga uluhlu lwabo lwezithintelo, kubonakala ngathi le ndlela ayibhalwanga kwimeko eqhelekileyo, isebenzisa uhlobo oluthile lwemodeli, kodwa ngamawaka amaNdiya, apho wonke umntu wayenikwe ithuba babhale isebe labo, kwaye ngamnye kubo wenza njengoko wayenako. Ukusebenzisa le ndlela yokuqiqa kufana nokuhamba kwindawo enezigcayiseli. Unokufumana umgodi nangaliphi na ixesha ngokubetha enye yezithintelo ezingabonakaliyo. Indlela esebenza ngayo ikwangumbuzo owahlukileyo, kodwa ingaphaya kwemida yeli nqaku.

IMicrosoft SQL Server

Iimfuneko ezongezelelweyo

Ukongeza kwiinketho ze-SET kunye neemfuno zomsebenzi wokumisela, ezi mfuno zilandelayo kufuneka zifezekiswe:

• Umsebenzisi osebenzayo CREATE INDEX kufuneka abe ngumnini wembono.
• Xa udala isalathisi, i IGNORE_DUP_KEY ukhetho kufuneka lusetwe ku CIMA (ulungiselelo olungagqibekanga).
• Iitheyibhile kufuneka zichazwe ngamagama amacandelo amabini, scheme.igama letafile kwinkcazo yokujonga.
• Imisebenzi echazwe ngumsebenzisi echazwe kwimboniselo kufuneka yenziwe ngokusebenzisa i WITH SCHEMABINDING ukhetho.
• Nayiphi na imisebenzi echazwe ngumsebenzisi ekubhekiselwa kuyo kwimboniselo kufuneka ibhekiswe ngamagama angamacandelo amabini, ..
• Ipropathi yokufikelela kwidatha yomsebenzisi-echazwe kufuneka ibe NO SQL, kunye nepropati yokufikelela yangaphandle kufuneka ibe NO.
• Ulwimi oluqhelekileyo lwexesha lokusebenza (CLR) imisebenzi inokuvela kuluhlu olukhethiweyo lwemboniselo, kodwa ayinakuba yinxalenye yenkcazelo yeqhosha elidityanisiweyo lesalathisi. Imisebenzi ye-CLR ayinakuvela kwigatya LAPHO lemboniselo okanye igatya elithi ON lomsebenzi we-JOIN kwimboniselo.
• Imisebenzi ye-CLR kunye neendlela ze-CLR-iintlobo ezichazwe ngumsebenzisi ezisetyenziswe kwinkcazo yembono kufuneka ibe neepropati ezibekwe njengoko kuboniswe kwitheyibhile elandelayo.

  ipropati
  Phawula

  I-DETERMINISTIC = TRUE
  Kufuneka ibhengezwe ngokucacileyo njengophawu lwendlela yeMicrosoft .NET Framework.

  OKUCHANILEYO = TRUE
  Kufuneka ibhengezwe ngokucacileyo njengophawu lwendlela ye-.NET Framework.

  UKUFIKELELA KWEDATHA = AKUKHO SQL
  Imiselwe ngokuseta uphawu loFikelelo lweDatha kwiDataAccessKind.None kunye neSistimDataUkufikelela uphawu kwiSistimDataAccessKind.None.

  UFIKELELO NGAPHANDLE = NO
  Le propati ayigqibekanga ku-NO kwiindlela ze-CLR.

• Umbono kufuneka udalwe ngokusebenzisa i WITH SCHEMABINDING ukhetho.
• Imbono kufuneka ibhekiselele kuphela kwisiseko seetafile ezikwisiseko sedatha enye njengemboniselo. Imbono ayikwazi ukubhekisa ezinye iimbono.
• Ingxelo KHETHA kwinkcazo yemboniselo mayingaqulathanga le miba ilandelayo yeTransact-SQL:

  COUNT
  ROWSET imisebenzi (OPENDATASOURCE, OPENQUERY, OPENROWSET, KUNYE OPENXML)
  OUTER ukujoyina (LEFT, RIGHT, okanye FULL)

  Itheyibhile ethathwayo (ichazwe ngokuchaza a SELECT ingxelo kwi FROM igatya)
  Ukuzibandakanya
  Ukuchaza imiqolo ngokusebenzisa SELECT * or SELECT <table_name>.*

  DISTINCT
STDEV, STDEVP, VAR, VARP, okanye AVG
  Inkcazo yetafile eqhelekileyo (CTE)

  edadayo1, umbhalo, ntext, umfanekiso, XML, okanye ifayile iikholamu
  Ukubuza imibuzo
  OVER igatya, elibandakanya uhlengahlengiso okanye udityaniso lwemisebenzi yefestile

  Izibhengezo ezipheleleyo (CONTAINS, FREETEXT)
  SUM umsebenzi obhekisa kwintetho engenakucimeka
  ORDER BY

  Umsebenzi we-CLR ochazwe ngumsebenzisi odibeneyo
  TOP
CUBE, ROLLUP, okanye GROUPING SETS ba sebenzi

  MIN, MAX
UNION, EXCEPT, okanye INTERSECT ba sebenzi
  TABLESAMPLE

  Iinguqu kwitheyibhile
  OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

  Iiseti zekholamu ezimbalwa
  Emgceni (i-TVF) okanye imisebenzi enexabiso letheyibhile yeengxelo ezininzi (MSTVF)
  OFFSET

  CHECKSUM_AGG

  1 Imboniselo enesalathiso inokuqulatha edadayo ikholamu; nangona kunjalo, imiqolo enjalo ayinakubandakanywa kwiqhosha lesalathisi esidityanisiweyo.

• If GROUP BY ikhona, inkcazo ye-VIEW kufuneka iqulathe COUNT_BIG(*) kwaye kufuneka ingaqulathi HAVING. Ezi GROUP BY Izithintelo zisebenza kuphela kwinkcazo yembono enesalathisi. Umbuzo unokusebenzisa imboniselo enesalathiso kwisicwangciso sayo sophumezo nokuba ayikonelisi ezi GROUP BY izithintelo.
• Ukuba inkcazo yemboniselo iqulathe a GROUP BY igatya, isitshixo sesalathiso esidityanisiweyo esisodwa singareferensi kuphela iikholamu ezichazwe kwi GROUP BY isigatya.

Kucacile apha ukuba amaIndiya ayengabandakanyeki, kuba agqibe kwelokuba ayenze ngokwenkqubo “kuncinci, kodwa kulungile.” Oko kukuthi, banemigodi emininzi ebaleni, kodwa indawo yabo icace ngakumbi. Eyona nto iphoxayo kakhulu ngulo mda:

Imbono kufuneka ibhekiselele kuphela kwisiseko seetafile ezikwisiseko sedatha enye njengemboniselo. Imbono ayikwazi ukubhekisa ezinye iimbono.

Kwisigama sethu, oku kuthetha ukuba umsebenzi awukwazi ukufikelela komnye umsebenzi owenziweyo. Oku kunciphisa zonke iingcamango kwi-bud.
Kwakhona, lo mda (kwaye ngakumbi kwisicatshulwa) unciphisa kakhulu iimeko zokusetyenziswa:

Ingxelo KHETHA kwinkcazo yemboniselo mayingaqulathanga le miba ilandelayo yeTransact-SQL:

COUNT
ROWSET imisebenzi (OPENDATASOURCE, OPENQUERY, OPENROWSET, KUNYE OPENXML)
OUTER ukujoyina (LEFT, RIGHT, okanye FULL)

Itheyibhile ethathwayo (ichazwe ngokuchaza a SELECT ingxelo kwi FROM igatya)
Ukuzibandakanya
Ukuchaza imiqolo ngokusebenzisa SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP, okanye AVG
Inkcazo yetafile eqhelekileyo (CTE)

edadayo1, umbhalo, ntext, umfanekiso, XML, okanye ifayile iikholamu
Ukubuza imibuzo
OVER igatya, elibandakanya uhlengahlengiso okanye udityaniso lwemisebenzi yefestile

Izibhengezo ezipheleleyo (CONTAINS, FREETEXT)
SUM umsebenzi obhekisa kwintetho engenakucimeka
ORDER BY

Umsebenzi we-CLR ochazwe ngumsebenzisi odibeneyo
TOP
CUBE, ROLLUP, okanye GROUPING SETS ba sebenzi

MIN, MAX
UNION, EXCEPT, okanye INTERSECT ba sebenzi
TABLESAMPLE

Iinguqu kwitheyibhile
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Iiseti zekholamu ezimbalwa
Emgceni (i-TVF) okanye imisebenzi enexabiso letheyibhile yeengxelo ezininzi (MSTVF)
OFFSET

CHECKSUM_AGG

OUTER JOINS, UNION, ORDER BY kunye nabanye akuvumelekanga. Bekunokuba lula ukucacisa ukuba yintoni na enokusetyenziswa kunokuba ingasetyenziswa. Uluhlu mhlawumbi lunokuba luncinci kakhulu.

Ukushwankathela: isethi enkulu yezithintelo kuzo zonke (makhe siqaphele urhwebo) DBMS vs none (ngaphandle kwengqiqo enye, kungekhona ubuchwepheshe) kwi-LGPL iteknoloji. Nangona kunjalo, kufuneka kuqatshelwe ukuba ukuphumeza le ndlela kwingqiqo yobudlelwane kunzima kakhulu kunendlela yokusebenza echazwe.

Ukuphunyezwa

Ingaba isebenza kanjani? I-PostgreSQL isetyenziswa “njengomatshini obambekayo”. Kukho i-algorithm entsonkothileyo ngaphakathi eyakha imibuzo. Apha imvelaphi. Kwaye akukho nje iseti enkulu ye-heuristics eneqela le-ifs. Ke, ukuba uneenyanga ezimbalwa zokufunda, ungazama ukuqonda uyilo.

Ngaba isebenza ngempumelelo? Isebenza kakuhle. Ngelishwa, oku kunzima ukukungqina. Ndingatsho kuphela ukuba ukuba ucinga amawaka emibuzo ekhoyo kwizicelo ezinkulu, ngoko ke ngokomndilili zisebenza kakuhle kunezo zomphuhlisi olungileyo. Umdwelisi wenkqubo weSQL ogqwesileyo unokubhala nawuphi na umbuzo ngokufanelekileyo, kodwa ngewaka lemibuzo akayi kuba nayo inkuthazo okanye ixesha lokuyenza. Ekuphela kwento endinokuyikhankanya njengobungqina bokusebenza kukuba iiprojekthi ezininzi zisebenza kwiqonga elakhiwe kule DBMS. Iinkqubo ze-ERP, enamawaka emisebenzi eyahlukeneyo ye-MATERIALIZED, kunye namawaka abasebenzisi kunye nogcino lwedatha ye-terabyte kunye namakhulu ezigidi zeerekhodi ezisebenza kwi-server yeprosesa ezimbini eziqhelekileyo. Nangona kunjalo, nabani na unokujonga / aphikise ukusebenza ngokukhuphela iqonga kunye nePostgreSQL, ukulayita ukuloga imibuzo yeSQL kwaye uzama ukutshintsha ingqiqo kunye nedatha apho.

Kumanqaku alandelayo, ndiza kuthetha malunga nendlela onokuthi ubeke ngayo izithintelo kwimisebenzi, usebenze ngeeseshoni zokutshintsha, kunye nokunye okuninzi.

umthombo: www.habr.com