ProHoster > Блог > Gudanarwa > Daidaita rubutu da karantawa a cikin ma'ajin bayanai

Daidaita rubutu da karantawa a cikin ma'ajin bayanai

Daidaita rubutu da karantawa a cikin ma'ajin bayanai
A baya labarin Na bayyana ra'ayi da aiwatar da bayanan da aka gina bisa tushen ayyuka, maimakon teburi da filayen kamar yadda suke cikin bayanan bayanai. Ya ba da misalai da yawa da ke nuna fa'idar wannan tsarin fiye da na gargajiya. Mutane da yawa sun same su ba su da tabbas sosai.

A cikin wannan labarin, zan nuna yadda wannan ra'ayi ya ba ku damar sauri da dacewa don daidaita rubuce-rubuce da karantawa zuwa bayanan bayanai ba tare da wani canji a cikin dabarun aiki ba. An yi ƙoƙarin aiwatar da irin wannan aikin a cikin DBMSs na kasuwanci na zamani (musamman, Oracle da Microsoft SQL Server). A ƙarshen labarin zan nuna cewa abin da suka yi, don sanya shi a hankali, bai yi aiki sosai ba.

Description

Kamar yadda ya gabata, don ƙarin fahimta zan fara bayanin da misalai. Bari mu ce muna buƙatar aiwatar da dabaru waɗanda za su dawo da jerin sassan da adadin ma’aikata a cikinsu da jimillar albashinsu.

A cikin rumbun adana bayanai mai aiki zai yi kama da haka:

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

Matsalolin aiwatar da wannan tambayar a kowace DBMS zai yi daidai da O (yawan ma'aikata)saboda wannan lissafin yana buƙatar bincika dukkan teburin ma'aikata sannan a haɗa su ta hanyar sashe. Hakanan za a sami wasu ƙananan (mun yi imanin cewa akwai ƙarin ma'aikata fiye da sassan) kari dangane da shirin da aka zaɓa O (lambar ma'aikata) ko O (yawan sassan) domin hada kai da sauransu.

A bayyane yake cewa kisa na iya bambanta a cikin DBMS daban-daban, amma rikitarwa ba zai canza ta kowace hanya ba.

A cikin aiwatar da aiwatarwa, DBMS mai aiki za ta samar da ƙaramin bincike guda ɗaya wanda zai ƙididdige ƙimar da ake buƙata don sashin, sannan yin JOIN tare da teburin sashen don samun sunan. Koyaya, ga kowane aiki, lokacin bayyanawa, yana yiwuwa a saita alamar MATERIALIZED na musamman. Tsarin zai ƙirƙiri filin daidai ta atomatik don kowane irin wannan aikin. Lokacin canza ƙimar aiki, ƙimar filin kuma za ta canza a cikin ma'amala iri ɗaya. Lokacin samun damar wannan aikin, za a shiga filin da aka riga aka ƙidaya.

Musamman, idan kun saita MATERIALIZED don ayyuka ma'aikata и albashiSum, sa'an nan kuma za a kara filayen biyu a kan tebur tare da jerin sassan, wanda zai adana adadin ma'aikata da adadin albashin su. A duk lokacin da aka samu sauyi a ma’aikata, albashinsu ko ma’aikatan sashe, tsarin zai canza dabi’un wadannan fagagen kai tsaye. Tambayar da ke sama za ta shiga waɗannan filayen kai tsaye kuma za a aiwatar da su a ciki O (yawan sassan).

Menene hani? Abu ɗaya kawai: irin wannan aikin dole ne ya kasance yana da iyakataccen adadin ƙimar shigarwa wanda aka ayyana ƙimarsa. In ba haka ba, ba zai yuwu a gina teburin da ke adana duk ƙimarsa ba, tunda ba za a iya samun tebur tare da layuka marasa iyaka ba.

Alal misali:

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

An ayyana wannan aikin don ƙima mara iyaka na N (misali, kowane ƙimar mara kyau ya dace). Don haka, ba za ku iya sanya MATERIALIZED akansa ba. Don haka wannan iyakacin hankali ne, ba na fasaha ba (wato, ba don mun kasa aiwatar da shi ba). In ba haka ba, babu ƙuntatawa. Kuna iya amfani da ƙungiyoyi, rarrabawa, AND da OR, PARTITION, maimaitawa, da sauransu.

Misali, a cikin matsala 2.2 na labarin da ya gabata, zaku iya sanya MATERIALIZED akan ayyukan biyu:

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;

Tsarin da kansa zai ƙirƙiri tebur ɗaya tare da maɓallan nau'in Abokin ciniki, Samfur и INTEGER, zai ƙara filayen biyu zuwa gare shi kuma zai sabunta ƙimar filin a cikinsu tare da kowane canje-canje. Lokacin da aka ƙara yin kira zuwa waɗannan ayyuka, ba za a ƙididdige su ba, amma za a karanta ƙimar daga filayen da suka dace.

Yin amfani da wannan tsarin, zaku iya, alal misali, kawar da sake dawowa (CTE) a cikin tambayoyin. Musamman, la'akari da ƙungiyoyin da suka samar da bishiya ta yin amfani da dangantakar yara/iyaye (kowace ƙungiya tana da hanyar haɗi zuwa iyayenta):

parent = DATA Group (Group);

A cikin ma'auni mai aiki, za'a iya ayyana ma'anar maimaitawa kamar haka:

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;

Tunda don aikin Iyaye ne an yi masa alamar MATERIALIZED, sannan za a samar da tebur mai maɓalli guda biyu (ƙungiyoyi) wanda filin. Iyaye ne zai zama gaskiya ne kawai idan maɓallin farko shine ɗan na biyu. Adadin shigarwar a cikin wannan tebur zai kasance daidai da adadin ƙungiyoyi waɗanda aka ninka ta matsakaicin zurfin bishiyar. Idan kuna buƙatar, misali, don ƙidaya adadin zuriyar wata ƙungiya, zaku iya amfani da wannan aikin:

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

Ba za a sami CTE a cikin tambayar SQL ba. A maimakon haka za a sami sauƙi GROUP BY.

Yin amfani da wannan tsarin, zaku iya rage ma'anar bayanai cikin sauƙi idan ya cancanta:

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

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

Lokacin kiran aiki date don layin oda, filin da akwai maƙasudinsa za a karanta daga tebur tare da layin oda. Lokacin da kwanan watan oda ya canza, tsarin da kansa zai sake ƙididdige kwanan wata a cikin layi ta atomatik.

Amfanin

Menene wannan duka tsarin don? A cikin DBMS na yau da kullun, ba tare da sake rubuta tambayoyin ba, mai haɓakawa ko DBA na iya canza fihirisa kawai, tantance ƙididdiga da gaya wa mai tsara shirin yadda ake aiwatar da su (kuma HINTs suna nan a cikin DBMS na kasuwanci kawai). Ko ta yaya suka yi ƙoƙari, ba za su iya kammala tambaya ta farko a cikin labarin a ciki ba O (yawan sassan) ba tare da canza tambaya ko ƙara abubuwan da ke jawo hankali ba. A cikin tsarin da aka tsara, a matakin ci gaba ba dole ba ne ka yi tunani game da tsarin ajiyar bayanai da kuma waɗanne tarawa don amfani. Duk wannan za a iya sauƙi canza a kan gardama, kai tsaye a cikin aiki.

A aikace yana kama da wannan. Wasu mutane suna haɓaka tunani kai tsaye bisa aikin da ke hannunsu. Ba su fahimci algorithms da sarkar su ba, ko tsare-tsaren aiwatarwa, ko nau'ikan haɗin gwiwa, ko kowane ɓangaren fasaha. Waɗannan mutane sun fi masu nazarin harkokin kasuwanci fiye da masu haɓakawa. Bayan haka, duk wannan yana shiga gwaji ko aiki. Yana ba da damar shigar da tambayoyin da ake dadewa. Lokacin da aka gano doguwar tambaya, to, wasu mutane (ƙarin fasaha - ainihin DBA) sun yanke shawarar ba da damar MATERIALIZED akan wani matsakaicin aiki. Wannan yana rage saurin yin rikodin kaɗan (tunda yana buƙatar sabunta ƙarin filin a cikin ma'amala). Duk da haka, ba wai kawai wannan tambayar tana da sauri sosai ba, har ma da duk sauran waɗanda ke amfani da wannan aikin. A lokaci guda, yanke shawarar wane aikin da zai zama abu ne mai sauƙi. Biyu manyan sigogi: yawan yiwu shigar da dabi'u (wannan shi ne nawa records za su kasance a cikin m tebur), da kuma sau nawa ana amfani da shi a wasu ayyuka.

Analogs

DBMBS kasuwanci na zamani suna da irin wannan hanyoyin: yanayin yanayi tare da kayan aiki mai sauri (oracle) da kuma nuna alama (Microsoft SQL Server (Microsoft SQL Servied). A cikin PostgreSQL, MATERIALIZED VIEW ba za a iya sabunta shi a cikin ma'amala ba, amma akan buƙata kawai (har ma tare da tsauraran ƙuntatawa), don haka ba ma la'akari da shi. Amma suna da matsaloli da yawa waɗanda ke iyakance amfani da su sosai.

Da fari dai, za ku iya kunna kayan aiki kawai idan kun riga kun ƙirƙiri VIEW na yau da kullun. In ba haka ba, dole ne ku sake rubuta sauran buƙatun don samun damar sabon ra'ayi don amfani da wannan kayan aikin. Ko barin komai kamar yadda yake, amma zai zama aƙalla mara amfani idan akwai wasu bayanan da aka riga aka ƙididdige su, amma yawancin tambayoyin ba koyaushe suke amfani da su ba, amma suna sake ƙididdige su.

Na biyu, suna da hani mai yawa:

Oracle

5.3.8.4 Gabaɗaya Ƙuntatawa akan Sassauta Saurin

An taƙaita ma'anar tambayar abin da aka ƙirƙira kamar haka:

  • Dole ne ra'ayi na zahiri ya ƙunshi nassoshi zuwa maganganun da ba a maimaita su ba kamar SYSDATE da kuma ROWNUM.
  • Dole ne ra'ayi na zahiri bai ƙunshi nassoshi ba RAW or LONG RAW nau'ikan bayanai.
  • Ba zai iya ƙunsar a SELECT jerin subquery.
  • Ba zai iya ƙunsar ayyukan nazari ba (misali, RANK) a cikin SELECT fassarar.
  • Ba zai iya yin la'akari da tebur a kansa ba XMLIndex index aka bayyana.
  • Ba zai iya ƙunsar a MODEL fassarar.
  • Ba zai iya ƙunsar a HAVING jumla tare da subquery.
  • Ba zai iya ƙunsar tambayoyin gida waɗanda ke da ANY, ALL, ko NOT EXISTS.
  • Ba zai iya ƙunsar a [START WITH …] CONNECT BY fassarar.
  • Ba zai iya ƙunsar allunan dalla-dalla da yawa a shafuka daban-daban ba.
  • ON COMMIT ra'ayi na zahiri ba zai iya samun tebur daki-daki mai nisa ba.
  • Matsakaicin ra'ayi na zahiri dole ne ya sami haɗin kai ko tarawa.
  • Materialized haɗa ra'ayoyi da zahirin jimlar ra'ayoyi tare da a GROUP BY magana ba zai iya zaɓar daga tebur mai ƙididdigewa ba.

5.3.8.5 Ƙuntatawa akan Wartsakarwa da sauri akan Ra'ayoyin Material tare da haɗin gwiwa kawai

Ƙayyadaddun tambayoyin don ra'ayi na zahiri tare da haɗin kai kawai kuma babu tarawa da ke da hani masu zuwa kan wartsakarwa da sauri:

  • Duk ƙuntatawa daga «Ƙuntatawa Gabaɗaya akan Sassauta Saurin".
  • Ba za su iya samu ba GROUP BY clauses ko aggregates.
  • Rowids na duk tebur a cikin FROM dole lissafin ya bayyana a cikin SELECT jerin tambayoyin.
  • Dole ne rajistan ayyukan gani na kayan aiki su kasance tare da layuka don duk teburin tushe a cikin FROM jerin tambayoyin.
  • Ba za ku iya ƙirƙirar ra'ayi mai saurin wartsakewa ba daga teburi da yawa tare da haɗin kai masu sauƙi waɗanda suka haɗa da ginshiƙi nau'in abu a cikin SELECT sanarwa.

Hakanan, hanyar wartsakewa da kuka zaɓa ba za ta yi inganci ba idan:

  • Tambayar ma'anar tana amfani da haɗin waje wanda ke aiki kamar haɗin ciki. Idan ma'anar tambayar ta ƙunshi irin wannan haɗin, la'akari da sake rubuta ma'anar tambayar don ƙunshi haɗin ciki.
  • The SELECT jerin ra'ayi na zahiri ya ƙunshi maganganu akan ginshiƙai daga teburi da yawa.

5.3.8.6 Ƙuntatawa akan Wartsakarwa da sauri akan Ra'ayoyin Material tare da Tari

Ƙayyadaddun tambayoyin don ra'ayi na zahiri tare da tarawa ko haɗin kai yana da hani masu zuwa akan sabuntawa da sauri:

Ana goyan bayan wartsakarwa da sauri ga duka biyun ON COMMIT da kuma ON DEMAND ra'ayi na zahiri, duk da haka akwai ƙuntatawa masu zuwa:

  • Duk teburin da ke cikin ra'ayi na zahiri dole ne su kasance da rajistan ayyukan gani na zahiri, kuma rajistan ayyukan gani na zahiri dole ne:
    • Ya ƙunshi duk ginshiƙai daga teburin da aka yi magana a cikin sigar zahiri.
    • Saka da ROWID da kuma INCLUDING NEW VALUES.
    • Saka da SEQUENCE juzu'i idan ana sa ran tebur ɗin ya sami haɗaɗɗun abubuwan sakawa/ lodi kai tsaye, sharewa, da sabuntawa.

  • Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN da kuma MAX ana tallafawa don sabuntawa da sauri.
  • COUNT(*) dole ne a kayyade.
  • Ayyukan tarawa dole ne su faru kawai a matsayin mafi girman ɓangaren magana. Wato aggregates irin su AVG(AVG(x)) or AVG(x)+ AVG(x) ba a yarda.
  • Ga kowane tara kamar AVG(expr), wanda ya dace COUNT(expr) dole ne ya kasance. Oracle ya ba da shawarar hakan SUM(expr) a ƙayyade.
  • If VARIANCE(expr) or STDDEV(expr) an kayyade, COUNT(expr) da kuma SUM(expr) dole ne a kayyade. Oracle ya ba da shawarar hakan SUM(expr *expr) a ƙayyade.
  • The SELECT shafi a cikin ma'anar tambaya ba zai iya zama hadadden magana mai ginshiƙai daga teburi masu yawa ba. Matsala mai yuwuwa ga wannan ita ce amfani da ra'ayi na zahiri.
  • The SELECT dole lissafin ya ƙunshi duka GROUP BY ginshikan.
  • Ra'ayin zahiri bai dogara ne akan tebur ɗaya ko fiye da nisa ba.
  • Idan kayi amfani da CHAR Nau'in bayanai a cikin ginshiƙan tacewa na log ɗin duba abin duniya, saitin halayen babban rukunin yanar gizon da yanayin zahiri dole su kasance iri ɗaya.
  • Idan ra'ayi na zahiri yana da ɗaya daga cikin masu biyowa, to ana samun goyan bayan wartsakewa cikin sauri akan abubuwan da aka saka na DML na al'ada da lodi kai tsaye.
    • Ra'ayi na kayan abu tare da MIN or MAX tarawa
    • Materialized views waxanda suke da SUM(expr) amma babu COUNT(expr)
    • Materialized views ba tare da COUNT(*)

    Irin wannan ra'ayi na zahiri ana kiransa abin da aka saka kawai.

  • Duban zahiri tare da MAX or MIN yana da saurin wartsakewa bayan gogewa ko gauraye kalamai na DML idan bashi da a WHERE fassarar.
    Max/min wartsakarwa da sauri bayan gogewa ko gauraye DML bashi da hali iri ɗaya da abin saka kawai. Yana sharewa kuma yana ƙididdige ƙimar max/min don ƙungiyoyin da abin ya shafa. Kuna buƙatar sanin tasirin aikin sa.
  • Abubuwan da aka ƙirƙira tare da ra'ayoyi masu suna ko ƙararraki a cikin FROM Za a iya wartsake magana da sauri muddin ra'ayoyin za a iya haɗa su gaba ɗaya. Don bayani kan waɗanne ra'ayoyi za su haɗu, duba Bayanin Harshe na Oracle SQL.
  • Idan babu haɗin kai na waje, kuna iya samun zaɓi na sabani da shiga cikin WHERE fassarar.
  • Abubuwan da aka haɗa tare da haɗin kai na waje suna da saurin wartsakewa bayan DML na al'ada da lodi kai tsaye, muddin kawai an gyara tebur na waje. Har ila yau, ƙayyadaddun ƙuntatawa dole ne su kasance a kan ginshiƙan haɗin haɗin tebur na ciki. Idan akwai haɗin waje, duk haɗin dole ne a haɗa su ta ANDs kuma dole ne a yi amfani da daidaito (=) mai aiki.
  • Don ra'ayi na zahiri tare da CUBE, ROLLUP, ƙungiyoyin saiti, ko haɗa su, waɗannan ƙuntatawa suna aiki:
    • The SELECT lissafin yakamata ya ƙunshi rarrabuwa na rukuni wanda zai iya zama ko dai a GROUPING_ID aiki akan duka GROUP BY maganganu ko GROUPING ayyuka daya ga kowane GROUP BY magana. Misali, idan GROUP BY juzu'in ra'ayi na zahiri shine "GROUP BY CUBE(a, b)", sannan kuma SELECT ya kamata lissafin ya ƙunshi ko ɗaya"GROUPING_ID(a, b)» ko «GROUPING(a) AND GROUPING(b)» don ganin abin da aka sanya a zahiri ya zama mai saurin wartsakewa.
    • GROUP BY bai kamata ya haifar da rukunonin kwafi ba. Misali, "GROUP BY a, ROLLUP(a, b)"ba shi da saurin wartsakewa saboda yana haifar da ƙungiyoyin kwafi"(a), (a, b), AND (a)".

5.3.8.7 Ƙuntatawa akan Wartsakarwa da sauri akan Ra'ayoyin Material tare da UNION ALL

Materialized views tare da UNION ALL saita mai aiki da goyan bayan REFRESH FAST zaɓi idan waɗannan sharuɗɗan sun cika:

  • Tambayar ma'anar dole ne ta sami UNION ALL mai aiki a matakin sama.

    The UNION ALL Ba za a iya shigar da ma'aikaci a cikin wani yanki ba, ban da guda ɗaya: The UNION ALL iya zama a cikin subquery a cikin FROM juzu'i idan har ana buƙatar ma'anar siffan SELECT * FROM (duba ko yin rajista tare da UNION ALL) kamar yadda a cikin misali mai zuwa:

    CREATE VIEW view_with_unionall AS (Zabi c.rowid crid, c.cust_id, 2 umarker DAGA abokan ciniki c INA c.cust_last_name = 'Smith' UNION DUK SELECT c.rowid crid, c.cust_id, 3 umarker DAGA abokan ciniki c = INA c.cust_last_name 'Jones'); Ƙirƙiri KYAUTA KYAUTA duk_inside_view_mv SANARWA DA AZUMI AKAN BUKATA KAMAR ZABI * DAGA gani_with_unionall;

    Lura cewa ra'ayi view_with_unionall ya gamsar da buƙatun don wartsakewa da sauri.

  • Kowane toshewar tambaya a cikin UNION ALL Dole ne tambaya ta gamsar da buƙatun hangen nesa mai wartsakewa cikin sauri tare da tarawa ko saurin wartsakewar gani mai kayatarwa tare da haɗin gwiwa.

    Dole ne a ƙirƙira madaidaitan rajistan ayyukan gani na kayan aiki akan teburin kamar yadda ake buƙata don daidaitaccen nau'in gani mai wartsakewa cikin sauri.
    Lura cewa Oracle Database kuma yana ba da damar yanayi na musamman na tebur guda ɗaya da aka ƙirƙira ra'ayi tare da haɗin kai kawai da aka bayar ROWID An haɗa shafi a cikin SELECT jeri kuma a cikin log ɗin duba kayan aiki. Ana nuna wannan a cikin ma'anar tambayar kallo view_with_unionall.

  • The SELECT jerin kowace tambaya dole ne ya ƙunshi a UNION ALL alama, da UNION ALL ginshiƙi dole ne ya sami takamaiman lamba ko ƙimar kirtani a kowane UNION ALL reshe. Bugu da ari, ginshiƙi mai alamar dole ne ya bayyana a cikin matsayi iri ɗaya a cikin SELECT jerin kowane tubalan tambaya. Duba"UNION DUK Alamar da Tambayoyi Sake rubutawa» don ƙarin bayani game da UNION ALL alamomi
  • Wasu fasalulluka kamar haɗe-haɗe na waje, saka-kawai tara tarin abubuwan duba tambayoyin gani da teburi masu nisa ba su da tallafi don ra'ayi na zahiri tare da UNION ALL. Lura, duk da haka, cewa ra'ayoyin zahiri da aka yi amfani da su wajen kwafi, waɗanda ba su ƙunshi haɗaɗɗiya ko tarawa ba, za a iya wartsakewa da sauri idan UNION ALL ko kuma ana amfani da tebur mai nisa.
  • Dole ne a saita ma'aunin ƙaddamar da dacewa zuwa 9.2.0 ko sama don ƙirƙirar ra'ayi mai saurin wartsakewa tare da UNION ALL.

Ba na so in cutar da magoya bayan Oracle, amma yin la'akari da jerin abubuwan da suka hana, da alama ba a rubuta wannan tsarin ba a cikin al'amuran gaba ɗaya, ta yin amfani da wani nau'i na samfurin, amma dubban Indiyawan, inda aka ba kowa damar yin amfani da su. Ya rubuta reshensa, kowannensu ya yi abin da zai iya, ya yi. Yin amfani da wannan hanyar don tunani na gaske kamar tafiya ta wurin nakiyoyi ne. Kuna iya samun ma'adinai a kowane lokaci ta hanyar buga ɗaya daga cikin ƙuntatawa mara kyau. Yadda yake aiki kuma tambaya ce daban, amma ta wuce iyakar wannan labarin.

Microsoft SQL Server

Requarin buƙatun

Baya ga zaɓuɓɓukan SET da ƙayyadaddun ayyukan aiki, dole ne a cika buƙatun masu zuwa:

  • Mai amfani da ke aiwatarwa CREATE INDEX dole ne ya zama mai ra'ayi.
  • Lokacin da ka ƙirƙiri fihirisar, da IGNORE_DUP_KEY dole ne a saita zaɓi zuwa KASHE (Tsoffin saitin).
  • Dole ne a yi nuni da Tables da sunaye kashi biyu, tsarin.sunan tebur a cikin view definition.
  • Ayyukan da aka ayyana mai amfani da aka ambata a cikin ra'ayi dole ne a ƙirƙira su ta amfani da WITH SCHEMABINDING zaɓi.
  • Duk wani aikin da aka ayyana mai amfani da aka ambata a cikin ra'ayi dole ne a yi nuni da sunaye kashi biyu, ..
  • Dole ne kadarorin samun damar bayanai na aikin da aka ayyana mai amfani ya kasance NO SQL, kuma dole ne kadarorin shiga waje ya kasance NO.
  • Ayyukan lokaci na gama-gari na yare (CLR) na iya bayyana a cikin zaɓin jerin ra'ayi, amma ba za su iya zama ɓangare na ma'anar maɓalli mai tari ba. Ayyukan CLR ba za su iya bayyana a cikin INA juzu'in ra'ayi ba ko ON juzu'in aikin JOIN a cikin ra'ayi.
  • Ayyukan CLR da hanyoyin nau'ikan fayyace nau'ikan CLR da aka yi amfani da su a cikin ma'anar ra'ayi dole ne su sami kaddarorin da aka saita kamar yadda aka nuna a tebur mai zuwa.

    Property
    Note

    HUKUNCI = GASKIYA
    Dole ne a bayyana a sarari azaman sifa ta hanyar Microsoft .NET Framework.

    GASKIYA = GASKIYA
    Dole ne a bayyana a sarari azaman sifa ta hanyar .NET Framework.

    SAMUN DATA = BABU SQL
    An ƙaddara ta hanyar saita sifa DataAccess zuwa DataAccessKind.Babu da SystemDataAccess sifa zuwa SystemDataAccessKind.Babu.

    HANYAR WUTA = A'A
    Wannan kadarar ta gaza zuwa NO don ayyukan yau da kullun na CLR.

  • Dole ne a ƙirƙira ra'ayi ta amfani da WITH SCHEMABINDING zaɓi.
  • Dole ne ra'ayi ya yi la'akari da tebur na tushe kawai waɗanda ke cikin ma'ajin bayanai iri ɗaya da ra'ayi. Duban ba zai iya yin nuni da wasu ra'ayoyi ba.
  • Bayanin SELECT a cikin ma'anar ra'ayi dole ne ya ƙunshi abubuwan Transact-SQL masu zuwa:

    COUNT
    Ayyukan ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)
    OUTER shiga(LEFT, RIGHT, ko FULL)

    Teburin da aka samo (an bayyana ta hanyar ƙayyadaddun a SELECT sanarwa a cikin FROM magana)
    Haɗin kai
    Ƙayyadaddun ginshiƙai ta amfani SELECT * or SELECT <table_name>.*

    DISTINCT
    STDEV, STDEVP, VAR, VARP, ko AVG
    Maganar tebur gama gari (CTE)

    taso kan ruwa1, rubutu, rubutu, image, XML, ko tsarin fayil ginshikan
    Subquery
    OVER juzu'i, wanda ya haɗa da matsayi ko tara ayyukan taga

    Bayanin cikakken rubutu (CONTAINS, FREETEXT)
    SUM aikin da ke nuni da magana maras tushe
    ORDER BY

    Ƙayyadaddun aikin mai amfani na CLR
    TOP
    CUBE, ROLLUP, ko GROUPING SETS aiki

    MIN, MAX
    UNION, EXCEPT, ko INTERSECT aiki
    TABLESAMPLE

    Matsalolin tebur
    OUTER APPLY or CROSS APPLY
    PIVOT, UNPIVOT

    Saitunan ginshiƙan ƙima
    Layin layi (TVF) ko ayyuka masu kimar tebur mai yawan magana (MSTVF)
    OFFSET

    CHECKSUM_AGG

    1 Ra'ayin da aka lissafta zai iya ƙunsar taso kan ruwa ginshiƙai; duk da haka, irin waɗannan ginshiƙai ba za a iya haɗa su cikin maɓalli mai tari ba.

  • If GROUP BY yana nan, dole ne ma'anar VIEW ta ƙunshi COUNT_BIG(*) kuma dole ne ba ya ƙunshi HAVING. wadannan GROUP BY ƙuntatawa suna aiki ne kawai ga ma'anar duban fihirisa. Tambaya na iya amfani da ra'ayi da aka lissafta a cikin shirinta na aiwatarwa ko da bai gamsar da waɗannan ba GROUP BY ƙuntatawa.
  • Idan ma'anar kallon ta ƙunshi a GROUP BY juzu'i, maɓallin keɓaɓɓen fihirisar tari na iya yin la'akari da ginshiƙan da aka ƙayyade a cikin GROUP BY fassarar.

A bayyane yake a nan cewa Indiyawan ba su da hannu, tunda sun yanke shawarar yin hakan bisa ga tsarin "za mu yi kadan, amma da kyau." Wato suna da ma'adinan da yawa a filin, amma wurin da suke a bayyane yake. Abu mafi ban takaici shine wannan iyakancewa:

Dole ne ra'ayi ya yi la'akari da tebur na tushe kawai waɗanda ke cikin ma'ajin bayanai iri ɗaya da ra'ayi. Duban ba zai iya yin nuni da wasu ra'ayoyi ba.

A cikin kalmomin mu, wannan yana nufin cewa aiki ba zai iya samun damar wani aiki na zahiri ba. Wannan yana yanke duk akida a cikin toho.
Hakanan, wannan iyakance (da ƙari a cikin rubutu) yana rage yawan lokuta masu amfani:

Bayanin SELECT a cikin ma'anar ra'ayi dole ne ya ƙunshi abubuwan Transact-SQL masu zuwa:

COUNT
Ayyukan ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)
OUTER shiga(LEFT, RIGHT, ko FULL)

Teburin da aka samo (an bayyana ta hanyar ƙayyadaddun a SELECT sanarwa a cikin FROM magana)
Haɗin kai
Ƙayyadaddun ginshiƙai ta amfani SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP, ko AVG
Maganar tebur gama gari (CTE)

taso kan ruwa1, rubutu, rubutu, image, XML, ko tsarin fayil ginshikan
Subquery
OVER juzu'i, wanda ya haɗa da matsayi ko tara ayyukan taga

Bayanin cikakken rubutu (CONTAINS, FREETEXT)
SUM aikin da ke nuni da magana maras tushe
ORDER BY

Ƙayyadaddun aikin mai amfani na CLR
TOP
CUBE, ROLLUP, ko GROUPING SETS aiki

MIN, MAX
UNION, EXCEPT, ko INTERSECT aiki
TABLESAMPLE

Matsalolin tebur
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Saitunan ginshiƙan ƙima
Layin layi (TVF) ko ayyuka masu kimar tebur mai yawan magana (MSTVF)
OFFSET

CHECKSUM_AGG

Haɗin kai, UNION, ORDER BY da sauransu an haramta su. Zai iya zama da sauƙi a ƙayyade abin da za a iya amfani da shi maimakon abin da ba za a iya amfani da shi ba. Wataƙila lissafin zai fi guntu sosai.

Don taƙaitawa: ƙaƙƙarfan tsari na ƙuntatawa a cikin kowane (bari mu lura da kasuwanci) DBMS vs babu ɗaya (ban da ma'ana ɗaya, ba fasaha ba) a cikin fasahar LGPL. Duk da haka, ya kamata a lura cewa aiwatar da wannan tsari a cikin tunani na dangantaka yana da ɗan wahala fiye da yadda aka kwatanta aikin dabaru.

Aiwatarwa

Ta yaya yake aiki? Ana amfani da PostgreSQL azaman "na'ura ta zahiri". Akwai hadadden algorithm a ciki wanda ke gina tambayoyi. nan tushe. Kuma ba kawai babban saiti na heuristics tare da gungu na ifs ba. Don haka, idan kuna da watanni biyu don yin karatu, kuna iya ƙoƙarin fahimtar gine-gine.

Yana aiki yadda ya kamata? Yayi tasiri sosai. Abin takaici, wannan yana da wuyar tabbatarwa. Zan iya cewa kawai idan kun yi la'akari da dubban tambayoyin da ke wanzu a cikin manyan aikace-aikace, to, a matsakaici sun fi dacewa fiye da na mai haɓaka mai kyau. Kyakkyawan mai tsara shirye-shiryen SQL na iya rubuta kowace tambaya da inganci, amma tare da tambayoyi dubu kawai ba zai sami kwarin gwiwa ko lokacin yin ta ba. Abin da kawai zan iya ambata a matsayin tabbacin tasiri shine cewa ayyuka da yawa suna aiki akan dandalin da aka gina akan wannan DBMS Tsarin ERP, wanda ke da dubban ayyuka daban-daban na MATERIALIZED, tare da dubban masu amfani da bayanan terabyte tare da daruruwan miliyoyin bayanan da ke gudana akan uwar garken mai sarrafawa guda biyu na yau da kullum. Koyaya, kowa zai iya bincika / karyata tasiri ta hanyar saukewa dandamali da PostgreSQL, kunnawa shigar da tambayoyin SQL da ƙoƙarin canza dabaru da bayanai a wurin.

A cikin labarai masu zuwa, zan kuma yi magana game da yadda zaku iya saita hani akan ayyuka, aiki tare da lokutan canji, da ƙari mai yawa.

source: www.habr.com

Add a comment