Balancing amalemba ndikuwerenga mu database

M'mbuyomu nkhani Ndinalongosola lingaliro ndi kukhazikitsidwa kwa nkhokwe yomangidwa pamaziko a ntchito, osati matebulo ndi minda monga muzosungirako zokhudzana ndi ubale. Inapereka zitsanzo zambiri zosonyeza ubwino wa njira imeneyi kuposa yachikale. Ambiri anawapeza osakhutiritsa mokwanira.

M'nkhaniyi, ndikuwonetsani momwe lingaliro ili limakupatsani mwayi wowerengera mwachangu komanso moyenera ndikuwerenga ku database popanda kusintha kulikonse pamalingaliro ogwiritsira ntchito. Zofananazo zayesedwa kuti zigwiritsidwe ntchito mu DBMS zamalonda zamakono (makamaka, Oracle ndi Microsoft SQL Server). Kumapeto kwa nkhaniyi ndikuwonetsa kuti zomwe adachita, kunena mofatsa, sizinayende bwino.

mafotokozedwe

Monga kale, kuti ndimvetsetse bwino ndiyamba kufotokozera ndi zitsanzo. Tinene kuti tikuyenera kukhazikitsa malingaliro omwe angabweretse mndandanda wamadipatimenti omwe ali ndi kuchuluka kwa ogwira nawo ntchito ndi malipiro awo onse.

Mu database yogwira ntchito zitha kuwoneka motere:

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


Kuvuta kochita funsoli mu DBMS iliyonse kudzakhala kofanana ndi O (chiwerengero cha antchito)chifukwa kuwerengetsaku kumafuna kusanthula tebulo lonse la ogwira ntchito ndiyeno kuwaika m'magulu malinga ndi dipatimenti. Padzakhalanso ena ang'onoang'ono (timakhulupirira kuti pali antchito ambiri kuposa madipatimenti) owonjezera kutengera dongosolo lomwe lasankhidwa O (chiwerengero cha anthu ogwira ntchito) kapena O (chiwerengero cha madipatimenti) za kupanga magulu ndi zina zotero.

N'zoonekeratu kuti kuphedwa kwapamwamba kungakhale kosiyana mu DBMS zosiyanasiyana, koma zovuta sizidzasintha mwanjira iliyonse.

Pakukhazikitsa komwe akufunsidwa, DBMS yogwira ntchito ipanga gawo limodzi lomwe lidzawerengere zofunikira za dipatimentiyo, kenako ndikupanga JOIN ndi tebulo la dipatimenti kuti mupeze dzina. Komabe, pa ntchito iliyonse, polengeza, ndizotheka kukhazikitsa chikhomo chapadera cha MATERIALIZED. Dongosolo lidzapanga zokha gawo lolingana ndi ntchito iliyonse yotere. Posintha mtengo wa ntchito, mtengo wamunda udzasinthanso muzochitika zomwezo. Mukapeza ntchitoyi, gawo lowerengedwa kale lidzafikiridwa.

Makamaka, ngati muyika MATERIALIZED kuti mugwiritse ntchito countAntchito и salarySm, ndiye minda iwiri idzawonjezedwa patebulo ndi mndandanda wa madipatimenti, omwe adzasungira chiwerengero cha antchito ndi malipiro awo onse. Nthawi zonse pakakhala kusintha kwa ogwira ntchito, malipiro awo kapena ma dipatimenti awo, dongosololi limangosintha zikhalidwe za magawowa. Funso lomwe lili pamwambapa lipeza magawowa mwachindunji ndipo lidzayankhidwa O (chiwerengero cha madipatimenti).

Kodi zoletsa ndi zotani? Chinthu chimodzi chokha: ntchito yotereyi iyenera kukhala ndi chiwerengero chochepa chamtengo wapatali chomwe mtengo wake umatanthauzidwa. Apo ayi, sikutheka kupanga tebulo lomwe limasungira zikhalidwe zake zonse, popeza sipangakhale tebulo lokhala ndi mizere yopanda malire.

Chitsanzo:

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


Ntchitoyi imatanthauzidwa ndi nambala yopanda malire ya N (mwachitsanzo, mtengo uliwonse woipa ndi woyenera). Chifukwa chake, simungayike MATERIALIZED pamenepo. Chifukwa chake ichi ndi malire omveka, osati aukadaulo (osati chifukwa sitinathe kuzikwaniritsa). Apo ayi, palibe zoletsa. Mutha kugwiritsa ntchito magulu, kusanja, NDI ndi OR, GAWO, kubwereza, ndi zina.

Mwachitsanzo, pavuto 2.2 la nkhani yapitayi, mutha kuyika MATERIALIZED pa ntchito zonse ziwiri:

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;


Dongosolo lokha lidzapanga tebulo limodzi ndi makiyi amtundu kasitomala, mankhwala и ZOCHITIKA, idzawonjezera magawo awiri kwa izo ndipo idzasintha magawo omwe ali nawo ndi kusintha kulikonse. Kuyitanira kwina kuzinthu izi kupangidwa, sikuwerengedwa, koma mfundo zake zimawerengedwa kuchokera m'magawo ofanana.

Pogwiritsa ntchito makinawa, mutha, mwachitsanzo, kuchotsa zobwerezabwereza (CTE) pamafunso. Makamaka, lingalirani magulu omwe amapanga mtengo pogwiritsa ntchito ubale wa mwana/makolo (gulu lililonse limakhala ndi ulalo kwa kholo lawo):

parent = DATA Group (Group);


Mu database yogwira ntchito, malingaliro obwereza amatha kufotokozedwa motere:

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;


Kuyambira kwa ntchito ndiParent yalembedwa MATERIALIZED, ndiye tebulo lomwe lili ndi makiyi awiri (magulu) lidzapangidwira, momwe gawolo ndiParent zidzakhala zoona kokha ngati kiyi yoyamba ili mwana wachiwiri. Chiwerengero cha zolemba mu tebulo ili chidzakhala chofanana ndi chiwerengero cha magulu ochulukitsa ndi kuzama kwa mtengo. Ngati mukufuna, mwachitsanzo, kuwerengera kuchuluka kwa mbadwa za gulu linalake, mutha kugwiritsa ntchito izi:

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


Sipadzakhala CTE pafunso la SQL. M'malo mwake padzakhala gulu losavuta la GROUP BY.

Pogwiritsa ntchito makinawa, mutha kusinthanso database mosavuta ngati kuli kofunikira:

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

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


Pamene kuyitana ntchito tsiku kwa mzere wa dongosolo, gawo lomwe pali index lidzawerengedwa kuchokera patebulo ndi mizere ya dongosolo. Tsiku la dongosolo likasintha, dongosolo lokhalo lidzawerengeranso tsiku losasinthika pamzere.

ubwino

Kodi njira yonseyi ndi yotani? M'ma DBMS akale, osalembanso mafunso, wopanga mapulogalamu kapena DBA amatha kungosintha ma index, kudziwa ziwerengero ndikuwuza wokonza mafunso momwe angawachitire (ndipo ma HINTs amapezeka mu DBMS zamalonda). Ngakhale atayesetsa bwanji, sangathe kumaliza funso loyamba m'nkhaniyo O (chiwerengero cha madipatimenti) popanda kusintha mafunso kapena kuwonjezera zoyambitsa. Muchiwembu chomwe chaperekedwa, pagawo lachitukuko simuyenera kuganizira za momwe mungasungire deta komanso magulu oti mugwiritse ntchito. Zonsezi zikhoza kusinthidwa mosavuta pa ntchentche, mwachindunji ntchito.

Pochita zimawoneka chonchi. Anthu ena amapanga logic mwachindunji malinga ndi ntchito yomwe ali nayo. Samvetsetsa ma algorithms ndi zovuta zawo, mapulani akupha, mitundu yolumikizirana, kapena gawo lina lililonse laukadaulo. Anthu awa ndi akatswiri azamalonda kuposa omanga. Ndiye, zonsezi zimalowa mu kuyesa kapena ntchito. Imathandizira kutsitsa mafunso kwanthawi yayitali. Funso lalitali likapezeka, ndiye anthu ena (zaukadaulo kwambiri - makamaka DBA) amasankha kuyatsa MATERIALIZED pa ntchito ina yapakatikati. Izi zimachepetsa kujambula pang'ono (popeza zimafuna kukonzanso gawo lina muzochitikazo). Komabe, sikuti funsoli limafulumizitsa kwambiri, komanso ena onse omwe amagwiritsa ntchito ntchitoyi. Panthawi imodzimodziyo, kusankha ntchito yoti mukhale ndi thupi kumakhala kosavuta. Zigawo ziwiri zazikulu: kuchuluka kwa zomwe zotheka zolowera (umu ndi kuchuluka kwa zolemba zomwe zidzakhale patebulo lofananira), komanso kangati zomwe zimagwiritsidwa ntchito pazinthu zina.

Malemba

Ma DBMS amakono amalonda ali ndi njira zofanana: MATERIALIZED VIEW yokhala ndi FAST REFRESH (Oracle) ndi INDEXED VIEW (Microsoft SQL Server). Mu PostgreSQL, MATERIALIZED VIEW sikungasinthidwe pakugulitsa, koma pokhapokha popempha (komanso ndi zoletsa zokhwima), kotero sitiziganizira. Koma ali ndi zovuta zingapo zomwe zimalepheretsa kugwiritsa ntchito kwawo.

Choyamba, mutha kuloleza kupanga zinthu ngati mwapanga kale VIEW wamba. Kupanda kutero, mudzayenera kulembanso zopempha zotsalazo kuti mupeze mawonekedwe opangidwa kumene kuti mugwiritse ntchito izi. Kapena siyani zonse momwe zilili, koma sizikhala zothandiza ngati pali zina zomwe zidawerengedwa kale, koma mafunso ambiri sagwiritsa ntchito nthawi zonse, koma amawerengeranso.

Kachiwiri, ali ndi zoletsa zambiri:

Oracle

5.3.8.4 Zoletsa Zazambiri pa Kutsitsimutsa Mwamsanga

Funso lofotokozera la mawonekedwe opangidwa ndi thupi limaletsedwa motere:

• Maonekedwe owoneka bwino asakhale ndi mawu osabwerezabwereza ngati SYSDATE ndi ROWNUM.
• Maonekedwe opangidwa ndi thupi asakhale ndi maumboni RAW or LONG RAW mitundu ya data.
• Sangakhale ndi a SELECT list subquery.
• Singakhale ndi ntchito zowunikira (mwachitsanzo, RANK) mu SELECT mawu.
• Sizingatchule tebulo lomwe a XMLIndex index imafotokozedwa.
• Sangakhale ndi a MODEL mawu.
• Sangakhale ndi a HAVING ndime yokhala ndi subquery.
• Sichingakhale ndi mafunso omwe ali nawo ANY, ALLkapena NOT EXISTS.
• Sangakhale ndi a [START WITH …] CONNECT BY mawu.
• Sizingakhale ndi zolemba zambiri pamawebusayiti osiyanasiyana.
• ON COMMIT mawonedwe owoneka bwino sangakhale ndi ma tebulo akutali.
• Mawonekedwe opangidwa ndi zisa ayenera kukhala ndi cholumikizira kapena kuphatikiza.
• Mawonekedwe ophatikizidwa ndi mawonedwe ophatikizidwa ndi a GROUP BY Chiganizo sichingasankhe kuchokera pa tebulo la ndondomeko.

5.3.8.5 Zoletsa pa Kutsitsimutsa Mwamsanga pa Mawonedwe Opangidwa Ndi Olowa Pokha

Kufotokozera mafunso amalingaliro opangidwa ndi anthu okhala ndi majowina okha ndipo palibe zophatikiza zili ndi zoletsa zotsatirazi pakutsitsimutsa mwachangu:

• Zoletsa zonse kuchokera ku «Zoletsa Zonse pa Kutsitsimutsa Mwamsanga".
• Sangakhale nazo GROUP BY zigawo kapena aggregates.
• Mizere ya matebulo onse mu FROM list iyenera kuwonekera mu SELECT mndandanda wafunso.
• Makanema owoneka bwino amayenera kukhala ndi mizere pamatebulo onse oyambira mu FROM mndandanda wafunso.
• Simungathe kupanga mawonekedwe osinthika mwachangu kuchokera pamagome angapo okhala ndi zolumikizira zosavuta zomwe zimaphatikizapo ndime yamtundu wa chinthu SELECT mawu.

Komanso, njira yotsitsimutsa yomwe mwasankha sikhala yothandiza ngati:

• Funso lofotokozera limagwiritsa ntchito cholumikizira chakunja chomwe chimakhala ngati cholumikizira mkati. Ngati funso lofotokozera lili ndi kulumikizana koteroko, lingalirani kulembanso funso lofotokozera kuti likhale ndi lolowera mkati.
• The SELECT mndandanda wa mawonedwe opangidwa ndi thupi uli ndi mawu pamizere kuchokera pamagome angapo.

5.3.8.6 Zoletsa pa Kutsitsimutsa Mwamsanga pa Mawonedwe Opangidwa Ndi Aggregates

Kufotokozera mafunso amalingaliro opangidwa ndi anthu ophatikizika kapena ojowina ali ndi zoletsa zotsatirazi pakutsitsimutsa mwachangu:

• Zoletsa zonse kuchokera ku «Zoletsa Zonse pa Kutsitsimutsa Mwamsanga".

Kutsitsimutsa mwachangu kumathandizidwa ndi onse awiri ON COMMIT ndi ON DEMAND malingaliro opangidwa ndi thupi, komabe zoletsa zotsatirazi zikugwira ntchito:

• Matebulo onse omwe ali ndi mawonekedwe owoneka bwino ayenera kukhala ndi zolemba zowoneka bwino, ndipo zolemba zowoneka bwino ziyenera:
  • Muli ndi magawo onse a tebulo lomwe lawonetsedwa mu mawonekedwe owoneka bwino.
  • Nenani ndi ROWID ndi INCLUDING NEW VALUES.
  • Tchulani SEQUENCE ndime ngati tebulo likuyembekezeka kukhala ndi zosakaniza zoyika / zowongolera, zochotsa, ndi zosintha.

• Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN ndi MAX amathandizidwa kuti atsitsimutse mwachangu.
• COUNT(*) ziyenera kufotokozedwa.
• Ntchito zophatikizika ziyenera kuchitika ngati mbali yakunja ya mawuwo. Ndiko kuti, aggregates monga AVG(AVG(x)) or AVG(x)+ AVG(x) saloledwa.
• Pagulu lililonse monga AVG(expr), zogwirizana COUNT(expr) ayenera kukhalapo. Oracle amalimbikitsa zimenezo SUM(expr) kutchulidwa.
• If VARIANCE(expr) or STDDEV(expr) zimanenedwa, COUNT(expr) ndi SUM(expr) ziyenera kufotokozedwa. Oracle amalimbikitsa zimenezo SUM(expr *expr) kutchulidwa.
• The SELECT Mzere mufunso lofotokozera singakhale mawu ovuta omwe ali ndi mizati kuchokera kumagulu angapo apansi. Njira yothanirana ndi izi ndikugwiritsa ntchito mawonekedwe opangidwa ndi zisa.
• The SELECT list iyenera kukhala ndi zonse GROUP BY mzati.
• Maonekedwe opangidwa ndi thupi sachokera pa tebulo limodzi kapena angapo akutali.
• Ngati mugwiritsa ntchito CHAR mtundu wa data muzosefera za chipika chowoneka bwino, ma seti amtundu wa tsamba la masters ndi mawonekedwe owoneka bwino ayenera kukhala ofanana.
• Ngati mawonekedwe opangidwa ndi thupi ali ndi chimodzi mwa izi, ndiye kuti kutsitsimutsa mwachangu kumathandizidwa pokhapokha pazoyika za DML ndi katundu wachindunji.
  • Mawonedwe akuthupi ndi MIN or MAX magulu
  • Mawonekedwe akuthupi omwe ali nawo SUM(expr) koma ayi COUNT(expr)
  • Mawonekedwe akuthupi popanda COUNT(*)

  Kaonedwe ka thupi kotereku kakutchedwa kuti kawonedwe ka thupi kamene kamayika.

• Mawonekedwe opangidwa ndi thupi MAX or MIN imatsitsimutsidwa mwachangu pambuyo pochotsa kapena kusakaniza mawu a DML ngati ilibe a WHERE mawu.
  Kutsitsimutsa kwapamwamba kwambiri/mphindi mukatha kufufuta kapena kusakanikirana kwa DML sikukhala ndi machitidwe ofanana ndi oyika-pokha. Imachotsa ndikuwerengeranso kuchuluka kwa max/min pamagulu omwe akhudzidwa. Muyenera kudziwa zotsatira zake.
• Mawonedwe opangidwa ndi mawonedwe otchulidwa kapena ma subqueries mu FROM chiganizochi chikhoza kutsitsimutsidwa mwamsanga pokhapokha malingaliro atha kuphatikizidwa. Kuti mumve zambiri momwe mawonedwe angaphatikizidwe, onani Oracle Database SQL Language Reference.
• Ngati palibe majowina akunja, mutha kukhala ndi zosankha mosasamala ndikujowina WHERE mawu.
• Mawonedwe ophatikizidwa ndi zolumikizira akunja amatsitsimutsidwa mwachangu pambuyo pa DML wamba komanso katundu wachindunji, pokhapokha tebulo lakunja lisinthidwa. Komanso, zopinga zapadera ziyenera kukhala pamizere yolumikizirana mkati mwa tebulo lamkati. Ngati pali zolumikizira zakunja, zolumikizira zonse ziyenera kulumikizidwa ndi ANDs ndipo ayenera kugwiritsa ntchito kufanana (=) woyang'anira.
• Kwa mawonedwe opangidwa ndi thupi ndi CUBE, ROLLUP, magulu amagulu, kapena kuphatikizika kwawo, zoletsa zotsatirazi zikugwira ntchito:
  • The SELECT list ikhale ndi chosiyanitsa chamagulu chomwe chingakhale a GROUPING_ID ntchito pa zonse GROUP BY mawu kapena GROUPING ntchito imodzi kwa aliyense GROUP BY mawu. Mwachitsanzo, ngati GROUP BY chiganizo cha mawonekedwe a thupi ndi "GROUP BY CUBE(a, b)", ndiye SELECT list iyenera kukhala ndi "GROUPING_ID(a, b)"kapena"GROUPING(a) AND GROUPING(b)»kuti mawonekedwe owoneka bwino akhale otsitsimula mwachangu.
  • GROUP BY zisapangitse magulu obwerezabwereza. Mwachitsanzo, "GROUP BY a, ROLLUP(a, b)"sikutsitsimutsidwa mwachangu chifukwa zimabweretsa magulu obwereza "(a), (a, b), AND (a)".

5.3.8.7 Zoletsa pa Kutsitsimutsa Mwachangu pa Mawonedwe Opangidwa ndi UNION ONSE

Mawonekedwe akuthupi ndi UNION ALL set operator thandizo ndi REFRESH FAST njira ngati zotsatirazi zikwaniritsidwa:

• Funso lofotokozera liyenera kukhala ndi UNION ALL woyendetsa pa mlingo wapamwamba.

  The UNION ALL woyendetsa sangathe kuphatikizidwa mkati mwa subquery, kupatulapo chimodzi: The UNION ALL akhoza kukhala mu subquery mu FROM chigamulo chimapereka funso lofotokozera ndi mawonekedwe SELECT * FROM (onani kapena fufuzani ndi UNION ALL) monga mu chitsanzo chotsatirachi:

  PANGANI KUONA view_with_unionall AS
  (SANKANI c.rowid crid, c.cust_id, 2 umaki
   KWA makasitomala c KUMENE c.cust_last_name = 'Smith'
   UNION ONSE
   SANKANI c.rowid crid, c.cust_id, 3 umaki
   KUCHOKERA kwa makasitomala c KUMENE c.cust_last_name = 'Jones');
  
  PANGANI MAwonedwe OTHANDIZA unionall_inside_view_mv
  ONANI ZONSE POFUNIKA MONGA
  SANKHANI * KUCHOKERA view_with_unionall;
  

  Onani kuti mawonekedwe view_with_unionall imakwaniritsa zofunikira zotsitsimutsa mwachangu.

• Funso lirilonse mu block UNION ALL funso liyenera kukwaniritsa zofunikira za mawonekedwe osinthika osinthika mwachangu ndi ma aggregates kapena mawonekedwe osinthika mwachangu ndi majoin.

  Zolemba zowoneka bwino zowoneka bwino ziyenera kupangidwa pamatebulo momwe zimafunikira kuti pakhale mawonekedwe owoneka bwino otsitsimutsa.
  Zindikirani kuti Oracle Database imalolanso kuti pakhale mawonekedwe apadera a tebulo limodzi lokhala ndi zolumikizira pokhapokha ROWID column yaphatikizidwa mu SELECT list ndi mu chipika chowoneka bwino. Izi zikuwonetsedwa mu funso lofotokozera la mawonekedwe view_with_unionall.

• The SELECT mndandanda wafunso lililonse uyenera kukhala ndi a UNION ALL marker, ndi UNION ALL Mzere uyenera kukhala ndi manambala osasinthasintha kapena mtengo wa chingwe pagawo lililonse UNION ALL nthambi. Kupitilira apo, gawo la chikhomo liyenera kuwoneka m'malo omwewo a ordinal mu SELECT mndandanda wamafunso aliwonse. Onani "UNION ALL Marker ndi Kulembanso Mafunso»kuti mumve zambiri UNION ALL zolembera.
• Zina monga zolumikizira akunja, mafunso ophatikizika ophatikizika okha ndi matebulo akutali sagwiritsidwa ntchito kuti muwone mawonekedwe ndi UNION ALL. Komabe, dziwani kuti mawonedwe owoneka bwino omwe amagwiritsidwa ntchito pobwerezabwereza, omwe alibe zolumikizira kapena zophatikiza, amatha kutsitsimutsidwa mwachangu pamene UNION ALL kapena matebulo akutali amagwiritsidwa ntchito.
• Gawo loyambira logwirizana liyenera kukhazikitsidwa ku 9.2.0 kapena kupitilira apo kuti apange mawonekedwe osinthika osinthika ndi UNION ALL.

Sindikufuna kukhumudwitsa mafani a Oracle, koma poyang'ana mndandanda wa zoletsedwa, zikuwoneka kuti makinawa sanalembedwe mwachisawawa, pogwiritsa ntchito mtundu wina wa chitsanzo, koma ndi zikwi za Amwenye, kumene aliyense anapatsidwa mwayi wochita masewera olimbitsa thupi. lemba nthambi yake, ndipo aliyense wa iwo anachita chimene akanatha, ndipo anachita. Kugwiritsa ntchito njira imeneyi polingalira zenizeni kuli ngati kuyenda m'malo osungiramo mabomba. Mutha kupeza mgodi nthawi iliyonse pomenya chimodzi mwazoletsa zosadziwika bwino. Momwe zimagwirira ntchito ndi funso losiyana, koma silingathe kufotokozedwa ndi nkhaniyi.

Microsoft SQL Server

Zofunikira Zowonjezera

Kuphatikiza pa zosankha za SET ndi zofunikira zogwirira ntchito, zotsatirazi ziyenera kukwaniritsidwa:

• Wogwiritsa ntchito CREATE INDEX ayenera kukhala mwiniwake wa mawonekedwe.
• Mukapanga index, dinani IGNORE_DUP_KEY njira iyenera kukhazikitsidwa kuti ZIMIRI (zosasintha).
• Matebulo ayenera kutchulidwa ndi mayina a magawo awiri, schema.tablename m'mawonedwe ofotokozera.
• Ntchito zomwe zimatanthauzidwa ndi ogwiritsa ntchito zomwe zikufotokozedwa muzowona ziyenera kupangidwa pogwiritsa ntchito ma WITH SCHEMABINDING mwina.
• Ntchito zilizonse zomwe zimatanthauzidwa ndi ogwiritsa ntchito zomwe zatchulidwa m'mawonedwewo ziyenera kutchulidwa ndi mayina a magawo awiri, ..
• Malo ofikira deta a ntchito yofotokozedwa ndi wogwiritsa ntchito ayenera kukhala NO SQL, ndi katundu wolowera kunja ayenera kukhala NO.
• Ntchito za Common Language Runtime (CLR) zitha kuwoneka pamndandanda wosankhidwa wamawonedwe, koma sizingakhale gawo la tanthauzo la kiyi ya clustered index. Ntchito za CLR sizingawonekere mu KULI ndime yowonera kapena ON clause ya JOIN yowonera.
• Ntchito za CLR ndi njira za mitundu yofotokozedwa ndi ogwiritsa ntchito ya CLR yomwe imagwiritsidwa ntchito pakutanthauzira kowonera ziyenera kukhala ndi zinthu zomwe zikuwonetsedwa patebulo lotsatirali.

  katundu
  Zindikirani

  DETERMINISTIC = ZOONA
  Ayenera kulengezedwa momveka bwino ngati mawonekedwe a Microsoft .NET Framework njira.

  ZOONA = ZOONA
  Ayenera kufotokozedwa momveka bwino ngati njira ya .NET Framework.

  DATA ACCESS = NO SQL
  Zimatsimikiziridwa pokhazikitsa mawonekedwe a DataAccess ku DataAccessKind.None ndi SystemDataAccess yogwirizana ndi SystemDataAccessKind.None.

  KUPEZEKA KWAKHALIDWE = NO
  Katunduyu amasinthidwa kukhala NO pamayendedwe a CLR.

• Chiwonetserocho chiyenera kupangidwa pogwiritsa ntchito mawonekedwe WITH SCHEMABINDING mwina.
• Mawonedwewo akuyenera kutchula ma tebulo oyambira okha omwe ali mu database yofanana ndi mawonekedwe. Mawonedwe sangathe kufotokoza malingaliro ena.
• Mawu a SELECT mu tanthauzo la mawonedwe sikuyenera kukhala ndi zinthu zotsatirazi za Transact-SQL:

  COUNT
  ROWSET ntchito (OPENDATASOURCE, OPENQUERY, OPENROWSETNDIPO OPENXML)
  OUTER amalumikizana (LEFT, RIGHTkapena FULL)

  Gome lotengedwa (lotanthauziridwa pofotokoza a SELECT statement mu FROM mawu)
  Zodziphatikiza
  Kutchula mizati pogwiritsa ntchito SELECT * or SELECT <table_name>.*

  DISTINCT
STDEV, STDEVP, VAR, VARPkapena AVG
  Common table expression (CTE)

  sungunulani1, lemba, ntext, chithunzi, XMLkapena filestream mizati
  Subquery
  OVER ndime, yomwe imaphatikizapo kusanja kapena kuphatikizira ntchito zazenera

  Mawu oyambira (CONTAINS, FREETEXT)
  SUM ntchito yomwe imatchula mawu osasinthika
  ORDER BY

  CLR yotanthauzidwa ndi ogwiritsa ntchito aggregate
  TOP
CUBE, ROLLUPkapena GROUPING SETS ntchito

  MIN, MAX
UNION, EXCEPTkapena INTERSECT ntchito
  TABLESAMPLE

  Zosintha patebulo
  OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

  Magawo ochepa
  Inline (TVF) kapena multi-statement table-valued function (MSTVF)
  OFFSET

  CHECKSUM_AGG

  1 Mawonedwe a indexed akhoza kukhala sungunulani mizati; komabe, zigawo zotere sizingaphatikizidwe mu kiyi ya clustered index.

• If GROUP BY ilipo, tanthauzo la VIEW liyenera kukhala COUNT_BIG(*) ndipo sichiyenera kukhala HAVING. Izi GROUP BY zoletsa zimagwira ntchito pa tanthauzo lolozera. Funso litha kugwiritsa ntchito mawonekedwe a indexed mu dongosolo lake lokonzekera ngakhale silikukwaniritsa izi GROUP BY zoletsedwa.
• Ngati tanthauzo la mawonedwe lili ndi a GROUP BY clause, chinsinsi cha index clustered chapadera chingathe kutchula zigawo zomwe zafotokozedwa mu GROUP BY mawu.

Zikuwonekeratu apa kuti amwenye sanatenge nawo mbali, popeza adaganiza zochita molingana ndi dongosololi "tichita pang'ono, koma bwino." Ndiko kuti, ali ndi migodi yambiri pamunda, koma malo awo ndi owonekera. Chokhumudwitsa kwambiri ndi ichi:

Mawonedwewo akuyenera kutchula ma tebulo oyambira okha omwe ali mu database yofanana ndi mawonekedwe. Mawonedwe sangathe kufotokoza malingaliro ena.

M'mawu athu, izi zikutanthauza kuti ntchito singathe kupeza ntchito ina yakuthupi. Izi zimachepetsa malingaliro onse mumphukira.
Komanso, izi (komanso m'mawu) zimachepetsa kwambiri milandu yogwiritsira ntchito:

Mawu a SELECT mu tanthauzo la mawonedwe sikuyenera kukhala ndi zinthu zotsatirazi za Transact-SQL:

COUNT
ROWSET ntchito (OPENDATASOURCE, OPENQUERY, OPENROWSETNDIPO OPENXML)
OUTER amalumikizana (LEFT, RIGHTkapena FULL)

Gome lotengedwa (lotanthauziridwa pofotokoza a SELECT statement mu FROM mawu)
Zodziphatikiza
Kutchula mizati pogwiritsa ntchito SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARPkapena AVG
Common table expression (CTE)

sungunulani1, lemba, ntext, chithunzi, XMLkapena filestream mizati
Subquery
OVER ndime, yomwe imaphatikizapo kusanja kapena kuphatikizira ntchito zazenera

Mawu oyambira (CONTAINS, FREETEXT)
SUM ntchito yomwe imatchula mawu osasinthika
ORDER BY

CLR yotanthauzidwa ndi ogwiritsa ntchito aggregate
TOP
CUBE, ROLLUPkapena GROUPING SETS ntchito

MIN, MAX
UNION, EXCEPTkapena INTERSECT ntchito
TABLESAMPLE

Zosintha patebulo
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Magawo ochepa
Inline (TVF) kapena multi-statement table-valued function (MSTVF)
OFFSET

CHECKSUM_AGG

OUTER JOINS, UNION, ORDER BY ndi ena ndizoletsedwa. Zikanakhala zosavuta kutchula zomwe zingagwiritsidwe ntchito kusiyana ndi zomwe sizingagwiritsidwe ntchito. Mndandandawo ungakhale wocheperapo.

Kufotokozera mwachidule: zoletsa zazikulu muzonse (tiyeni tizindikire zamalonda) DBMS vs palibe (kupatulapo zomveka, osati zaukadaulo) muukadaulo wa LGPL. Komabe, ziyenera kuzindikirika kuti kugwiritsa ntchito njira iyi muzolinga zaubwenzi ndizovuta kwambiri kuposa momwe zimagwirira ntchito.

Реализация

Zimagwira ntchito bwanji? PostgreSQL imagwiritsidwa ntchito ngati "makina enieni". Pali algorithm yovuta mkati yomwe imapanga mafunso. Pano gwero. Ndipo palibe gulu lalikulu la ma heuristics okhala ndi mulu wa ngati. Chifukwa chake, ngati muli ndi miyezi ingapo yoti muphunzire, mutha kuyesa kumvetsetsa kamangidwe.

Kodi zimagwira ntchito bwino? Zothandiza kwambiri. Tsoka ilo, izi ndizovuta kutsimikizira. Ndikhoza kungonena kuti ngati mungaganizire masauzande a mafunso omwe amapezeka m'mapulogalamu akuluakulu, ndiye kuti pafupifupi amakhala opambana kuposa omwe amapanga mapulogalamu abwino. Wolemba mapulogalamu wa SQL wabwino amatha kulemba funso lililonse moyenera, koma ndi mafunso chikwi sangakhale ndi chilimbikitso kapena nthawi yoti achite. Chinthu chokha chomwe ndingatchule ngati umboni wogwira mtima ndikuti mapulojekiti angapo akugwira ntchito papulatifomu yomangidwa pa DBMS iyi. Machitidwe a ERP, omwe ali ndi masauzande amitundu yosiyanasiyana ya MATERIALIZED, okhala ndi ogwiritsa ntchito masauzande ambiri ndi ma terabyte database okhala ndi mazana mamiliyoni a ma rekodi omwe akuyenda pa seva yokhazikika ya ma processor awiri. Komabe, aliyense akhoza kuyang'ana / kutsutsa mphamvuyo potsitsa nsanja ndi PostgreSQL, kuyatsa kulemba mafunso a SQL ndikuyesera kusintha malingaliro ndi deta pamenepo.

M'nkhani zotsatirazi, ndilankhulanso za momwe mungakhazikitsire zoletsa pa ntchito, kugwira ntchito ndi magawo osintha, ndi zina zambiri.

Source: www.habr.com