Bancing inonyora uye inoverenga mudhatabhesi

Mune yapfuura chinyorwa Ndakatsanangura pfungwa uye kushandiswa kwedhatabhesi yakavakirwa pahwaro hwemabasa, pane matafura neminda senge mune zvehukama dhatabhesi. Yakapa mienzaniso yakawanda inoratidza zvakanakira nzira iyi pamusoro peiyo yekare. Vazhinji vakavawana vasina kugutsikana zvakakwana.

Muchikamu chino, ini ndicharatidza kuti pfungwa iyi inokubvumira sei kukurumidza uye nyore kuenzanisa kunyora uye kuverenga kune dhatabhesi pasina shanduko mune yekushanda logic. Kuita kwakafanana kwakayedzwa kushandiswa mune zvemazuva ano zvekutengesa DBMSs (kunyanya, Oracle uye Microsoft SQL Server). Pakupera kwechinyorwa ndicharatidza kuti zvavakaita, kutaura zvinyoro, hazvina kunyatsoshanda.

tsananguro

Sepakutanga, kuti ndinzwisise zviri nani ndichatanga tsananguro nemienzaniso. Ngatitii isu tinofanirwa kuita zvine musoro zvinodzosa rondedzero yemadhipatimendi nehuwandu hwevashandi mukati mavo uye muhoro wavo wese.

Mune database inoshanda inoita seizvi:

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


Iko kuomarara kwekuita uyu mubvunzo mune chero DBMS ichave yakaenzana O(nhamba yevashandi)nekuti kuverenga uku kunoda kutarisisa tafura yese yevashandi tozovaisa mumapoka nedhipatimendi. Pachavewo nezvimwe zvidiki (tinotenda kuti kune vashandi vakawanda kupfuura madhipatimendi) kuwedzera zvichienderana nehurongwa hwakasarudzwa O(log nhamba yevashandi) kana O(nhamba yemadhipatimendi) yekuunganidza uye zvichingodaro.

Zviri pachena kuti kuurayiwa kwepamusoro kunogona kunge kwakasiyana mumaDBMS akasiyana, asi kuoma hakuzoshanduki nenzira ipi zvayo.

Mukuita kwakarongwa, iyo DBMS inoshanda ichagadzira imwe subquery inoverengera inodiwa dhipatimendi, uyezve kuita JOIN netafura yedhipatimendi kuti uwane zita. Nekudaro, kune yega yega basa, kana uchizivisa, zvinokwanisika kuseta yakakosha MATERIALIZED marker. Iyo sisitimu inozogadzira otomatiki ndima inoenderana kune yega yega basa rakadaro. Paunenge uchishandura kukosha kwebasa, kukosha kwemunda kuchachinjawo mukutengeserana kwakafanana. Paunenge uchiwana basa iri, iyo pre-yakaverengerwa munda ichawanikwa.

Kunyanya, kana iwe ukaseta MATERIALIZED yemabasa countVashandi и salarySum, ipapo minda miviri ichawedzerwa patafura ine runyorwa rwemadhipatimendi, ayo achachengeta nhamba yevashandi uye yavo yese muhoro. Pese paine shanduko muvashandi, mihoro yavo kana mubatanidzwa wedhipatimendi, sisitimu inozochinja otomatiki hunhu hweminda iyi. Mubvunzo uri pamusoro uchawana minda iyi zvakananga uye uchaitwa mukati O(nhamba yemadhipatimendi).

Ndezvipi zvinorambidzwa? Chinhu chimwe chete: basa rakadaro rinofanira kunge riine nhamba inogumira yezvakakosha zvekupinza izvo kukosha kwayo kunotsanangurwa. Zvikasadaro, hazvizogoneki kuvaka tafura inochengeta zvese kukosha kwayo, sezvo hapangave netafura ine nhamba isingaperi yemitsara.

Muenzaniso:

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


Iri basa rinotsanangurwa kune risingaperi nhamba yezvinokosha zveN (semuenzaniso, chero ipi zvayo yakashata yakakosha). Naizvozvo, haugone kuisa MATERIALIZED pairi. Saka ichi chidziviso chine musoro, kwete chehunyanzvi (kureva kuti, kwete nekuti hatina kukwanisa kuchiita). Zvikasadaro, hapana zvirambidzo. Unogona kushandisa mapoka, kuronga, UYE uye OR, CHIKAMU, kudzokorora, nezvimwe.

Semuenzaniso, mudambudziko 2.2 yechinyorwa chakapfuura, unogona kuisa MATERIALIZED pamabasa ese ari maviri:

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;


Iyo system pachayo ichagadzira tafura imwe ine makiyi emhando kasitoma, chigadzirwa и INTEGER, ichawedzera minda miviri kwairi uye ichagadziridza iyo munda tsika mavari nechero shanduko. Kana mamwe mafoni kumabasa aya aitwa, haaverengerwe, asi kuti kukosha kunoverengwa kubva kuminda inoenderana.

Uchishandisa iyi michina, unogona, semuenzaniso, kubvisa kudzokorora (CTE) mumibvunzo. Kunyanya, funga mapoka anoumba muti uchishandisa hukama hwemwana/mubereki (boka rega rega rine chinongedzo kumubereki waro):

parent = DATA Group (Group);


Mune database inoshanda, recursion logic inogona kutsanangurwa seinotevera:

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;


Sezvo nokuda kwebasa isParent yakanyorwa MATERIALIZED, ipapo tafura ine makiyi maviri (mapoka) ichagadzirirwa iyo, umo munda isParent chichava chechokwadi chete kana kiyi yekutanga iri mwana wechipiri. Nhamba yezvinyorwa mutafura iyi ichave yakaenzana nenhamba yemapoka anowedzerwa neavhareji kudzika kwemuti. Kana iwe uchida, semuenzaniso, kuverenga nhamba yevazukuru verimwe boka, unogona kushandisa basa iri:

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


Pachave pasina CTE mumubvunzo weSQL. Pane kudaro pachave ne GROUP BY iri nyore.

Uchishandisa iyi michina, iwe unogona zvakare kurerutsa dhatabhesi kana zvichidikanwa:

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

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


Pakudaidza chiitiko zuva kune mutsara wekurongeka, munda une index ichaverengwa kubva patafura ine mitsara yekuraira. Kana zuva reodha rachinja, sisitimu yacho pachayo inozoverengera otomatiki iyo denormalized date mumutsara.

zvakwakanakira

Iyi nzira yese ndeyei? Muchinyakare maDBMS, pasina mivhunzo yekunyora patsva, mugadziri kana DBA anogona chete kushandura indexes, kuona manhamba uye kuudza query planner maitiro ekuita (uye HINTs inongowanikwa mukutengesa DBMSs). Kunyangwe vaedza zvakaoma sei, havazokwanisi kupedzisa mubvunzo wekutanga muchinyorwa chiri O (nhamba yemadhipatimendi) pasina kushandura mibvunzo kana kuwedzera zvinokonzeresa. Muchirongwa chakarongwa, padanho rekusimudzira haufanirwe kufunga nezve data rekuchengetedza chimiro uye kuti ndeapi aggregations ekushandisa. Zvose izvi zvinogona kuchinjwa nyore nyore pakubhururuka, zvakananga mukushanda.

Mukuita zvinoita seizvi. Vamwe vanhu vanogadzira pfungwa dzakananga zvichienderana nebasa riripo. Ivo havanzwisise maalgorithms uye kuoma kwavo, kana hurongwa hwekuuraya, kana mhando dzemajoini, kana chero chimwe chehunyanzvi chikamu. Vanhu ava ndivo vaongorori vebhizinesi kupfuura vanogadzira. Zvadaro, zvese izvi zvinopinda mukuyedzwa kana kushanda. Inogonesa kurogwa kwemibvunzo yenguva refu. Kana mubvunzo wakareba waonekwa, ipapo vamwe vanhu (zvakawanda zvehunyanzvi - zvakanyanya DBA) vanosarudza kugonesa MATERIALIZED pane rimwe basa repakati. Izvi zvinoderedza kurekodha zvishoma (sezvo zvinoda kuvandudza imwe ndima mukutengesa). Nekudaro, kwete chete mubvunzo uyu unomhanyisa zvakanyanya, asiwo vamwe vese vanoshandisa basa iri. Panguva imwecheteyo, kusarudza kuti ibasa ripi rekuita muviri kuri nyore. Miviri mikuru paramita: nhamba yezvinogoneka yekuisa kukosha (iyi ndiyo mangani marekodhi achange ari mutafura inoenderana), uye kangani iyo inoshandiswa mune mamwe mabasa.

Analogs

MaDBMS emazuva ano ekutengeserana ane nzira dzakafanana: MATERIALIZED VIEW ine FAST REFRESH (Oracle) uye INDEXED VIEW (Microsoft SQL Server). MuPostgreSQL, MATERIALIZED VIEW haigone kuvandudzwa mukutengeserana, asi nekukumbira chete (uye kunyangwe nezvirambidzo zvakanyanya), saka isu hatizvifunge. Asi vane matambudziko akati wandei anomisa zvakanyanya kushandiswa kwavo.

Chekutanga, unogona chete kugonesa kuita zvinhu kana iwe watogadzira yenguva dzose VIEW. Zvikasadaro, iwe uchafanirwa kunyora zvakare zvikumbiro zvasara kuti uwane iyo ichangobva kugadzirwa maonero kuti ushandise iyi materialization. Kana kuti siya zvese sezvazviri, asi zvichange zvisisashande kana paine imwe yatove yakaverengerwa data, asi mizhinji mibvunzo haiwanzo kuishandisa, asi iverenge zvakare.

Chechipiri, vane nhamba huru yezvirambidzo:

pangataura

5.3.8.4 General Restrictions paKukurumidza Kufefetera

Kutsanangura kubvunza kwekuona kwakagadziriswa kunodzikiswa sezvizvi:

• Maonero enyama haafanire kunge aine mareferensi kune asingadzokorore mataurirwo akadai SYSDATE uye ROWNUM.
• Maonero enyama haafanire kunge aine mareferensi RAW or LONG RAW data mhando.
• Haikwanise kuva nea SELECT list subquery.
• Haikwanise kuve nemabasa ekuongorora (semuenzaniso, RANK) mu SELECT chirevo.
• Haikwanise kunongedzera tafura ine XMLIndex index inotsanangurwa.
• Haikwanise kuva nea MODEL chirevo.
• Haikwanise kuva nea HAVING clause ine subquery.
• Haikwanise kuva nemibvunzo yakanyorwa ANY, ALL, kana NOT EXISTS.
• Haikwanise kuva nea [START WITH …] CONNECT BY chirevo.
• Haikwanise kuve nematafura akawanda eruzivo panzvimbo dzakasiyana.
• ON COMMIT maonerwo enyama haakwanise kuva nematafura eruzivo ari kure.
• Maonero akaitwa Nested anofanirwa kuve nekubatana kana kubatanidza.
• Maonero ekubatanidza akabatika uye akaitwa aggregate maonero ane a GROUP BY clause haigone kusarudza kubva patafura yakarongeka.

5.3.8.5 Zvirambidzo paKumutsiridza Nekukurumidza paMaonero Akagadzirwa Nemajoini Chete

Kutsanangura mivhunzo yemaonero enyama ane majoini chete uye hapana akaunganidzwa ane zvinotevera zvinorambidzwa pakukurumidza kuzorodza:

• Zvese zvinorambidzwa kubva ku «General Restrictions paFast Refresh".
• Havagoni kuva nazvo GROUP BY clauses kana aggregates.
• Mitsara yematafura ese ari mukati FROM list inofanira kuoneka mu SELECT list yemubvunzo.
• Materialized view logs anofanirwa kuvapo nemitsara yematafura ese ebhesi mu FROM list yemubvunzo.
• Iwe haugone kugadzira iyo inokurumidza kudzoreredzwa yekuona maitiro kubva kune akawanda matafura ane akareruka majoini anosanganisira chinhu chemhando yekoramu mu SELECT Statement.

Zvakare, iyo nzira yekuvandudza yaunosarudza haizonyatso shanda kana:

• Mubvunzo wekutsanangura unoshandisa chinongedzo chekunze chinoita sejoin yemukati. Kana mubvunzo wekutsanangura uine kujoinwa kwakadaro, funga kunyora patsva mubvunzo wekutsanangura kuti uve nejoin yemukati.
• The SELECT rondedzero yemaonero akaitwa ane mazwi ari pamakoramu kubva kumatafura akawanda.

5.3.8.6 Zvirambidzo paKumutsiridza Nekukurumidza paMaonero Akaitwa neAggregates

Kutsanangura mivhunzo yemaonero enyama ane aggregates kana majoini ane zvinotevera zvinorambidzwa pakukurumidza kuzorodza:

• Zvese zvinorambidzwa kubva ku «General Restrictions paFast Refresh".

Kukurumidza kuzorodza kunotsigirwa kune ese ari maviri ON COMMIT uye ON DEMAND maonerwo enyama, asi zvirambidzo zvinotevera zvinoshanda:

• Matafura ese ari muchimiro chenyama anofanirwa kunge aine matanda ekuona, uye maitirwo ekuona matanda anofanira:
  • Iine makoramu ese kubva patafura inotaridzwa muchimiro chechimiro.
  • Taura ne ROWID uye INCLUDING NEW VALUES.
  • Tsanangura iyo SEQUENCE clause kana tafura inotarisirwa kuve nemusanganiswa wekuisa / zvakananga-mitoro, inobvisa, uye inogadziridza.

• chete SUM, COUNT, AVG, STDDEV, VARIANCE, MIN uye MAX inotsigirwa kuti ivandudzwe nekukurumidza.
• COUNT(*) inofanira kutaurwa.
• Aggregate mabasa anofanira kuitika chete sechikamu chekunze chekutaura. Kureva, aggregates akadai AVG(AVG(x)) or AVG(x)+ AVG(x) hazvibvumirwi.
• Kune yega yega aggregate senge AVG(expr), zvinoenderana COUNT(expr) anofanira kuvapo. Oracle anokurudzira izvozvo SUM(expr) kutsanangurwa.
• If VARIANCE(expr) or STDDEV(expr) inotsanangurwa, COUNT(expr) uye SUM(expr) inofanira kutaurwa. Oracle anokurudzira izvozvo SUM(expr *expr) kutsanangurwa.
• The SELECT column mumubvunzo wekutsanangura haigoni kuva chitauriro chakaoma chine makoramu kubva kumatafura ebhesi akawanda. Iyo inogoneka workaround kune iyi ndeye kushandisa nested materialized maonero.
• The SELECT list inofanira kuva nezvose GROUP BY mbiru.
• Maonero enyama haana kubva pane imwe kana mamwe matafura ari kure.
• Kana iwe ukashandisa CHAR dhata yemhando mumasefa makoramu eiyo yakaitwa yekuona logi, iyo hunhu seti yeiyo master saiti uye maonero akagadzirwa anofanira kunge akafanana.
• Kana iyo yenyama yekuona iine imwe yeinotevera, ipapo kukurumidza kuzorodza kunotsigirwa chete pane zvakajairwa DML kuisa uye yakananga mitoro.
  • Materialized maonero ne MIN or MAX zviyero
  • Materialized maonero ane SUM(expr) asi kwete COUNT(expr)
  • Materialized maonero pasina COUNT(*)

  Maonero enyama akadaro anonzi maonero ekuisa chete.

• Maonero enyama ane MAX or MIN inokurumidza kuvandudzwa mushure mekudzima kana kusanganisa DML zvirevo kana isina WHERE chirevo.
  Iyo max/min inokurumidza kuzorodza mushure mekudzima kana kusanganiswa DML haina maitiro akafanana neakaisa-chete kesi. Inobvisa uye inodzokorodza iyo max/min kukosha kwemapoka akakanganisika. Iwe unofanirwa kuziva maitiro ayo ekuita.
• Maonero akagadzirwa ane mazita ekuona kana subqueries mu FROM clause inogona kukurumidza kudzoserwa kunze kwekunge maonero anogona kubatanidzwa zvachose. Kuti uwane ruzivo rwekuti ndeapi maonero achabatana, ona Oracle Database SQL Mutauro Reference.
• Kana pasina majoini ekunze, unogona kunge uine sarudzo dzakasarudzika uye kujoina mu WHERE chirevo.
• Materialized aggregate maonero ane ekunze majoini anokurumidza kudzoreredzwa mushure menguva dzose DML uye mitoro yakananga, chero tafura yekunze chete yakagadziridzwa. Zvakare, zvipingaidzo zvakasarudzika zvinofanirwa kunge zviripo pamakoramu ekujoinha emukati tafura yekujoinha. Kana paine majoini ekunze, majoini ese anofanira kubatanidzwa nawo ANDs uye inofanira kushandisa kuenzana (=) mushandisi.
• Zvekuona zvinhu ne CUBE, ROLLUP, seti dzemapoka, kana kubatanidzwa kwavo, zvinotemerwa zvinotevera zvinoshanda:
  • The SELECT list inofanira kuva nemusiyanisi wemapoka anogona kunge ari a GROUPING_ID shanda pane zvese GROUP BY matauriro kana GROUPING inoshanda imwe kune imwe neimwe GROUP BY kutaura. Somuenzaniso, kana GROUP BY chirevo chechimiro chechimiro ndeche "GROUP BY CUBE(a, b)", zvino SELECT list inofanira kuva ne"GROUPING_ID(a, b)»kana «GROUPING(a) AND GROUPING(b)»kuti maonero akaitwa zvinhu ave kukurumidza kuvandudzwa.
  • GROUP BY haifanire kuguma nemapoka akadzokorora. Semuyenzaniso, "GROUP BY a, ROLLUP(a, b)"haisi kukurumidza kudzoreredzwa nekuti inokonzeresa kuita duplicate mapoka"(a), (a, b), AND (a)".

5.3.8.7 Zvirambidzo paKumutsiridza Nekukurumidza paMaonero Akaitwa neUNION ALL

Materialized maonero pamwe UNION ALL set opareta tsigiro iyo REFRESH FAST sarudzo kana zvinotevera zvinogutsa:

• Mubvunzo wekutsanangura unofanirwa kuva ne UNION ALL opareta padanho repamusoro.

  The UNION ALL opareta haigone kuisirwa mukati me subquery, kunze kumwe chete: Iyo UNION ALL inogona kuva mune subquery mu FROM clause yakapa iyo yekutsanangura mubvunzo ndeye fomu SELECT * FROM (ona kana kubvunza ne UNION ALL) semumuenzaniso unotevera:

  GADZIRA VIEW view_with_unionall AS (SARUDZA c.rowid crid, c.cust_id, 2 umarker KUBVA kuvatengi c PAPI c.cust_last_name = 'Smith' UNION OSE SARUDZA c.rowid crid, c.cust_id, 3 umarker KUBVA kuvatengi c PAPI c.cust_last_ 'Jones'); GADZIRA MATERIALIZED VIEW unionall_inside_view_mv REFRESH FAST ON DEMAND AS SARUDZA * FROM view_with_unionall;
  

  Cherechedza kuti maonero view_with_unionall inogutsa zvinodiwa zvekukurumidza kuzorodza.

• Yese yemubvunzo block mu UNION ALL mubvunzo unofanirwa kugutsa zvinodikanwa zvekukurumidza kudzoreredzwa kwemaitiro ekuona ane aggregates kana inokurumidza kudzoreredzwa yenyama yekuona nemajoini.

  Iwo akakodzera akaitwa ekuona matanda anofanirwa kugadzirwa pamatafura sezvinodiwa kune inoenderana mhando yekukurumidza kudzoreredzwa yekuona zvinhu.
  Ziva kuti iyo Oracle Database zvakare inobvumira iyo yakakosha kesi yeimwe tafura inobatika maonero nemajoini akapihwa chete ROWID column yakabatanidzwa mu SELECT list uye mune yakaitwa yekuona log. Izvi zvinoratidzwa mukutsanangura mubvunzo wemaonero view_with_unionall.

• The SELECT rondedzero yemubvunzo wega wega inofanira kusanganisira a UNION ALL marker, uye UNION ALL mbiru inofanira kuva nenhamba yenhamba kana tambo yakakosha mune imwe neimwe UNION ALL bazi. Kupfuurirazve, iyo marker column inofanirwa kuoneka mune imwechete ordinal chinzvimbo mu SELECT runyoro rwemubvunzo wega wega block. Ona"UNION YESE Mucherechedzo uye Mubvunzo Nyorazve» Kuti uwane rumwe ruzivo nezve UNION ALL mamaki.
• Mamwe maficha akadai semajoini ekunze, kuisa-chete aggregate yakaitwa yekuona mibvunzo uye matafura ari kure haatsigirwe pakuona kwenyama UNION ALL. Ziva, zvisinei, kuti maonerwo enyama anoshandiswa mukudzokorodza, asina majoini kana akaunganidzwa, anogona kukurumidza kumutsiridzwa kana UNION ALL kana matafura ari kure anoshandiswa.
• Iyo yekuenderana yekutanga paramende inofanirwa kusetwa kune 9.2.0 kana kumusoro kuti igadzire inokurumidza kudzoreredza yenyama yekuona ne UNION ALL.

Ini handidi kugumbura vateveri veOracle, asi tichitarisa nerondedzero yavo yezvirambidzo, zvinoita sekunge iyi nzira yakanyorwa kwete mune yakajairika nyaya, vachishandisa imwe mhando yemuenzaniso, asi nezviuru zvevaIndia, uko munhu wese akapihwa mukana wekuita. nyora davi rawo, uye mumwe nomumwe wavo akaita sezvaaigona, uye akaita. Kushandisa nzira iyi yepfungwa chaiyo kwakafanana nekufamba munzvimbo ine zvimbambaira. Unogona kuwana mugodhi chero nguva nekurova chimwe chezvirambidzo zvisiri pachena. Mashandiro ayo zvakare mubvunzo wakasiyana, asi uri kunze kwechikamu chechinyorwa ichi.

Microsoft SQL Server

Zvimwe Zvinodiwa

Pamusoro peiyo SET sarudzo uye deterministic basa zvinodiwa, zvinotevera zvinodiwa zvinofanirwa kuzadzikiswa:

• Mushandisi anoshandisa CREATE INDEX anofanira kunge ari muridzi wemaonero.
• Paunogadzira index, iyo IGNORE_DUP_KEY sarudzo inofanirwa kusetwa ku OFF (iyo yekumisikidza yekumisikidza).
• Matafura anofanirwa kutaurwa nemazita ane zvikamu zviviri, schema.tablename mune tsanangudzo yekuona.
• Mushandisi-akatsanangurwa mabasa anotsanangurwa mumaonero anofanira kugadzirwa nekushandisa iyo WITH SCHEMABINDING kuita.
• Chero chero mushandisi-anotsanangurwa mabasa anotsanangurwa mumaonero anofanira kutaurwa nemazita ane-zvikamu zviviri, ..
• Iyo data yekuwana pfuma yemushandisi-inotsanangurwa basa inofanirwa kunge iri NO SQL, uye kunze kwekuwana pfuma inofanira kuva NO.
• Common language runtime (CLR) mabasa anogona kuoneka mune yakasarudzwa runyorwa rwemaonero, asi haigone kuve chikamu chetsananguro ye clustered index key. CLR mabasa haagone kuoneka muWHERE clause yemaonero kana ON clause yeJOIN mashandiro mukuona.
• CLR mabasa uye nzira dzeCLR dzakatsanangurwa mushandisi mhando dzinoshandiswa mutsanangudzo yekuona dzinofanirwa kunge dziine zvivakwa zvakaiswa sezvakaratidzwa mutafura inotevera.

  pfuma
  chitsamba

  DETERMINISTIC = CHOKWADI
  Inofanirwa kuziviswa zvakajeka sehunhu hweMicrosoft .NET Framework nzira.

  PRECISE = CHOKWADI
  Zvinofanira kuziviswa zvakajeka sechimiro che.NET Framework method.

  DATA ACCESS = HAPANA SQL
  Inotemerwa nekuseta DataAccess attribute kuDataAccessKind.None uye SystemDataAccess attribute kuSystemDataAccessKind.None.

  KUSVIKA KWEKUNZE = NO
  Ichi chivakwa chinokanganisa NO kune maitiro eCLR.

• Maonero anofanira kugadzirwa nekushandisa iyo WITH SCHEMABINDING kuita.
• Maonero acho anofanirwa kunongedzera chete base matafura ari mudhatabhesi rimwe chete semaonero. Maonero acho haakwanise kureva mamwe maonero.
• Chirevo che SELECT mune tsananguro yekuona haifanirwe kunge iine zvinotevera Transact-SQL zvinhu:

  COUNT
  ROWSET mabasa (OPENDATASOURCE, OPENQUERY, OPENROWSET, Uye OPENXML)
  OUTER akabatana (LEFT, RIGHT, kana FULL)

  Derived tafura (inotsanangurwa nekutsanangura a SELECT chirevo mu FROM clause)
  Kuzvibatanidza
  Kudoma mbiru nekushandisa SELECT * or SELECT <table_name>.*

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

  Float1, chinyorwa, ntext, mufananidzo, XML, kana filestream mbiru
  Subquery
  OVER clause, iyo inosanganisira chinzvimbo kana kubatanidza mahwindo mabasa

  Manyoro-akazara predicate (CONTAINS, FREETEXT)
  SUM basa rinonongedza chirevo chisingachinji
  ORDER BY

  CLR mushandisi-inotsanangurwa aggregate basa
  TOP
CUBE, ROLLUP, kana GROUPING SETS operators

  MIN, MAX
UNION, EXCEPT, kana INTERSECT operators
  TABLESAMPLE

  Tafura dzakasiyana
  OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

  Sparse column seti
  Inline (TVF) kana multi-statement tafura-yakakosha mabasa (MSTVF)
  OFFSET

  CHECKSUM_AGG

  1 Maonero ane indexed anogona kuva Float columns; zvisinei, makoramu akadaro haagone kuverengerwa mukiyi yakaunganidzwa.

• If GROUP BY iripo, iyo VIEW tsananguro inofanira kuva COUNT_BIG(*) uye haifaniri kuzara HAVING. Izvi GROUP BY zvirambidzo zvinoshanda chete kune iyo indexed yekuona tsananguro. Mubvunzo unogona kushandisa maonero ane indexed muurongwa hwayo hwekuita kunyange kana isingagutsi izvi GROUP BY kurambidzwa.
• Kana iyo yekuona tsananguro ine a GROUP BY clause, kiyi yeiyo yakasarudzika clustered index inogona kungoreva makoramu anotsanangurwa mu GROUP BY chirevo.

Zviri pachena pano kuti maIndia haana kubatanidzwa, sezvo vakasarudza kuzviita maererano nechirongwa "tichaita zvishoma, asi zvakanaka." Kureva kuti vane migodhi yakawanda pamunda, asi nzvimbo yavo iri pachena. Chinhu chinonyanya kuodza mwoyo ndeichi chinogumira:

Maonero acho anofanirwa kunongedzera chete base matafura ari mudhatabhesi rimwe chete semaonero. Maonero acho haakwanise kureva mamwe maonero.

Muchirevo chedu chechirevo, izvi zvinoreva kuti basa harigone kuwana rimwe basa rakagadzirwa. Izvi zvinoderedza ideology yese mubud.
Zvakare, ichi chinodzikisira (uyezve muzvinyorwa) chinoderedza zvakanyanya makesi ekushandisa:

Chirevo che SELECT mune tsananguro yekuona haifanirwe kunge iine zvinotevera Transact-SQL zvinhu:

COUNT
ROWSET mabasa (OPENDATASOURCE, OPENQUERY, OPENROWSET, Uye OPENXML)
OUTER akabatana (LEFT, RIGHT, kana FULL)

Derived tafura (inotsanangurwa nekutsanangura a SELECT chirevo mu FROM clause)
Kuzvibatanidza
Kudoma mbiru nekushandisa SELECT * or SELECT <table_name>.*

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

Float1, chinyorwa, ntext, mufananidzo, XML, kana filestream mbiru
Subquery
OVER clause, iyo inosanganisira chinzvimbo kana kubatanidza mahwindo mabasa

Manyoro-akazara predicate (CONTAINS, FREETEXT)
SUM basa rinonongedza chirevo chisingachinji
ORDER BY

CLR mushandisi-inotsanangurwa aggregate basa
TOP
CUBE, ROLLUP, kana GROUPING SETS operators

MIN, MAX
UNION, EXCEPT, kana INTERSECT operators
TABLESAMPLE

Tafura dzakasiyana
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Sparse column seti
Inline (TVF) kana multi-statement tafura-yakakosha mabasa (MSTVF)
OFFSET

CHECKSUM_AGG

OUTER JOINS, UNION, ORDER BY nezvimwe zvinorambidzwa. Zvingave zvakave nyore kutsanangura chaigona kushandiswa pane chaisagona kushandiswa. Ndaza yacho ingangove ipfupi zvikuru.

Kupfupisa: yakakura seti yezvirambidzo mune yega yega (ngaticherechedze yekutengesa) DBMS vs hapana (kunze kweimwe inonzwisisika, kwete tekinoroji) muLGPL tekinoroji. Nekudaro, zvinofanirwa kucherechedzwa kuti kuita iyi nzira mune yehukama logic kune imwe yakaoma kupfuura mune yakatsanangurwa inoshanda logic.

Kutevedzera

Inoshanda sei? PostgreSQL inoshandiswa se "virtual muchina". Iko kune yakaoma algorithm mukati inovaka mibvunzo. Here mabviro. Uye hapangori hombe seti yeheuristics ine boka re ifs. Saka, kana uine mwedzi mishoma yekudzidza, unogona kuedza kunzwisisa zvivakwa.

Inoshanda zvinobudirira here? Kunyatsoshanda. Zvinosuruvarisa, izvi zvakaoma kuratidza. Ndinogona kungotaura kuti kana iwe ukafunga nezve zviuru zvemibvunzo iripo mumashandisirwo makuru, saka paavhareji iwo anoshanda zvakanyanya kupfuura ayo emugadziri akanaka. Akanakisa SQL programmer anogona kunyora chero mubvunzo zvakanyanya, asi nechuru chemibvunzo haangove nekurudziro kana nguva yekuzviita. Chinhu chega chandinogona kutaura ikozvino sehumbowo hwekubudirira ndechekuti mapurojekiti akati wandei ari kushanda pachikuva chakavakirwa pane iyi DBMS. ERP masisitimu, iyo ine zviuru zvemabasa akasiyana MATERIALIZED, ane zviuru zvevashandisi uye terabyte dhatabhesi nemazana emamiriyoni emarekodhi anomhanya pane akajairwa maviri-processor server. Nekudaro, chero munhu anogona kutarisa / kuramba kushanda nekurodha platform uye PostgreSQL, kusanganisira kutema SQL mibvunzo uye kuyedza kushandura iyo logic uye data ipapo.

Muzvinyorwa zvinotevera, ini ndichataurawo nezve maitiro aungaita zvirambidzo pamabasa, kushanda nezvikamu zvekuchinja, nezvimwe zvakawanda.

Source: www.habr.com