ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Sib npaug sau thiab nyeem hauv cov ntaub ntawv

Sib npaug sau thiab nyeem hauv cov ntaub ntawv

Sib npaug sau thiab nyeem hauv cov ntaub ntawv
Hauv yav dhau los Tshooj Kuv tau piav qhia txog lub tswv yim thiab kev siv ntawm cov ntaub ntawv tsim los ntawm kev ua haujlwm, tsis yog cov rooj thiab cov teb xws li hauv cov ntaub ntawv sib raug zoo. Nws muab ntau yam piv txwv qhia qhov zoo ntawm qhov kev mus kom ze tshaj qhov classical. Ntau tus pom lawv tsis ntseeg txaus.

Hauv tsab xov xwm no, kuv yuav qhia tias lub tswv yim no tso cai rau koj kom sai thiab yooj yim sib npaug sau ntawv thiab nyeem rau hauv cov ntaub ntawv yam tsis muaj kev hloov pauv hauv kev ua haujlwm logic. Cov kev ua haujlwm zoo sib xws tau sim ua kom tau raws li kev lag luam niaj hnub DBMSs (tshwj xeeb, Oracle thiab Microsoft SQL Server). Thaum kawg ntawm tsab xov xwm kuv yuav qhia tias lawv tau ua dab tsi, muab nws me me, tsis ua haujlwm zoo heev.

piav qhia

Raws li ua ntej, kom nkag siab zoo dua kuv yuav pib cov lus piav qhia nrog cov piv txwv. Cia peb hais tias peb yuav tsum tau siv cov logic uas yuav rov qab tau cov npe ntawm cov chaw haujlwm nrog cov neeg ua haujlwm hauv lawv thiab lawv cov nyiaj hli tag nrho.

Nyob rau hauv ib tug functional database nws yuav zoo li no:

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

Qhov nyuaj ntawm kev ua cov lus nug no hauv ib qho DBMS yuav sib npaug O (tus naj npawb ntawm cov neeg ua haujlwm)vim tias qhov kev suav no yuav tsum tau tshuaj xyuas tag nrho cov lus ntawm cov neeg ua haujlwm thiab tom qab ntawd muab lawv los ntawm chav haujlwm. Tseem yuav muaj qee qhov me me (peb ntseeg tias muaj ntau tus neeg ua haujlwm ntau dua li cov tuam haujlwm) ntxiv nyob ntawm qhov kev npaj xaiv O (cov naj npawb ntawm cov neeg ua haujlwm) los yog O (tus naj npawb ntawm departments) rau pab pawg thiab lwm yam.

Nws yog qhov tseeb tias qhov kev ua haujlwm nyiaj siv ua haujlwm yuav txawv ntawm DBMSs sib txawv, tab sis qhov nyuaj yuav tsis hloov pauv txhua txoj hauv kev.

Nyob rau hauv qhov kev thov kom siv, lub functional DBMS yuav tsim ib tug subquery uas yuav xam cov nqi yuav tsum tau rau lub department, thiab ces ua ib tug JOIN nrog lub department rooj kom tau lub npe. Txawm li cas los xij, rau txhua txoj haujlwm, thaum tshaj tawm, nws tuaj yeem teeb tsa tus cim tshwj xeeb MATERIALIZED. Lub kaw lus yuav cia li tsim ib qho chaw sib raug rau txhua qhov haujlwm no. Thaum hloov tus nqi ntawm ib qho kev ua haujlwm, tus nqi ntawm daim teb kuj tseem yuav hloov pauv hauv tib qho kev hloov pauv. Thaum nkag mus rau qhov kev ua haujlwm no, daim teb ua ntej xam yuav nkag mus.

Tshwj xeeb, yog tias koj teeb MATERIALISED rau kev ua haujlwm suav cov neeg ua haujlwm ΠΈ nyiaj hli, ces ob daim teb yuav muab ntxiv rau lub rooj nrog cov npe ntawm cov tuam tsev, uas yuav khaws cov neeg ua haujlwm thiab lawv cov nyiaj hli tag nrho. Thaum twg muaj kev hloov pauv hauv cov neeg ua haujlwm, lawv cov nyiaj hli lossis kev koom tes hauv chav haujlwm, lub kaw lus yuav cia li hloov cov txiaj ntsig ntawm cov haujlwm no. Cov lus nug saum toj no yuav nkag mus rau cov teb ncaj qha thiab yuav raug tua hauv O (tus naj npawb ntawm departments).

Cov kev txwv yog dab tsi? Tsuas yog ib yam: xws li ib tug muaj nuj nqi yuav tsum muaj ib tug finite tus naj npawb ntawm input qhov tseem ceeb uas nws tus nqi yog txhais. Txwv tsis pub, nws yuav tsis yooj yim sua kom tsim ib lub rooj uas khaws tag nrho nws cov txiaj ntsig, vim tsis tuaj yeem muaj ib lub rooj nrog cov kab tsis kawg.

Piv Txwv:

employeesCount β€˜ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ ΡΠΎΡ‚Ρ€ΡƒΠ΄Π½ΠΈΠΊΠΎΠ² Ρ Π·Π°Ρ€ΠΏΠ»Π°Ρ‚ΠΎΠΉ > N’ (Department d, NUMERIC[10,2] N) = 
    GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;

Qhov no muaj nuj nqi yog txhais rau ib tug infinite tus nqi ntawm N (piv txwv li, tej yam tsis zoo nqi yog tsim nyog). Yog li ntawd, koj tsis tuaj yeem muab MATERIALISED rau nws. Yog li qhov no yog qhov kev txwv tsis pub muaj, tsis yog ib qho kev qhia (uas yog, tsis yog vim peb tsis tuaj yeem siv nws). Txwv tsis pub, tsis muaj kev txwv. Koj tuaj yeem siv pawg, txheeb xyuas, THIAB thiab LOSSIS, PARTITION, recursion, thiab lwm yam.

Piv txwv li, hauv qhov teeb meem 2.2 ntawm tsab xov xwm dhau los, koj tuaj yeem muab MATERIALIZED rau ob txoj haujlwm:

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;

Lub kaw lus nws tus kheej yuav tsim ib lub rooj nrog hom yuam sij Cov neeg yuav khoom, khoom ΠΈ KEV RAU HAUV, yuav ntxiv ob daim teb rau nws thiab yuav hloov kho cov txiaj ntsig ntawm thaj chaw hauv lawv nrog kev hloov pauv. Thaum hu xov tooj ntxiv rau cov haujlwm no, lawv yuav tsis suav, tab sis qhov tseem ceeb yuav raug nyeem los ntawm cov teb sib xws.

Siv cov txheej txheem no, koj tuaj yeem, piv txwv li, tshem tawm cov lus nug (CTE) hauv cov lus nug. Tshwj xeeb, xav txog cov pab pawg uas tsim ib tsob ntoo siv tus menyuam / niam txiv kev sib raug zoo (txhua pab pawg muaj qhov txuas rau nws niam txiv):

parent = DATA Group (Group);

Nyob rau hauv ib tug functional database, recursion logic yuav teev raws li nram no:

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;

Raws li rau kev ua haujlwm yogParent yog cim MATERIALIZED, ces ib lub rooj nrog ob tug yawm sij (paub pawg) yuav raug tsim rau nws, nyob rau hauv uas lub teb yogParent yuav muaj tseeb tsuas yog thawj tus yuam sij yog tus menyuam thib ob. Tus naj npawb ntawm kev nkag rau hauv cov lus no yuav muab sib npaug rau cov pab pawg sib npaug los ntawm qhov nruab nrab qhov tob ntawm tsob ntoo. Yog tias koj xav tau, piv txwv li, suav cov xeeb leej xeeb ntxwv ntawm ib pab pawg, koj tuaj yeem siv txoj haujlwm no:

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

Yuav tsis muaj CTE hauv cov lus nug SQL. Hloov chaw yuav muaj ib qho yooj yim GROUP BY.

Siv cov txheej txheem no, koj tuaj yeem yooj yim denormalize cov ntaub ntawv yog tias tsim nyog:

CLASS Order 'Π—Π°ΠΊΠ°Π·';
date 'Π”Π°Ρ‚Π°' = DATA DATE (Order);

CLASS OrderDetail 'Π‘Ρ‚Ρ€ΠΎΠΊΠ° Π·Π°ΠΊΠ°Π·Π°';
order 'Π—Π°ΠΊΠ°Π·' = DATA Order (OrderDetail);
date 'Π”Π°Ρ‚Π°' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;

Thaum hu ua haujlwm hnub rau txoj kab kev txiav txim, daim teb uas muaj qhov ntsuas yuav raug nyeem los ntawm lub rooj nrog cov kab xaj. Thaum qhov kev txiav txim hnub hloov pauv, lub kaw lus nws tus kheej yuav rov xam cov hnub denormalized hauv kab.

zoo

Qhov no tag nrho mechanism yog dab tsi? Hauv DBMSs classic, yam tsis muaj kev sau cov lus nug, tus tsim tawm lossis DBA tsuas tuaj yeem hloov pauv qhov ntsuas, txiav txim siab txheeb cais thiab qhia tus kws npaj cov lus nug yuav ua li cas rau lawv (thiab HINTs tsuas yog muaj nyob rau hauv kev lag luam DBMSs). Txawm hais tias lawv nyuaj npaum li cas los xij, lawv yuav tsis tuaj yeem ua tiav cov lus nug thawj zaug hauv kab lus hauv O (tus naj npawb ntawm department) yam tsis hloov cov lus nug lossis ntxiv cov txiaj ntsig. Hauv cov tswv yim npaj, ntawm theem kev txhim kho koj tsis tas yuav xav txog cov ntaub ntawv khaws cia thiab cov kev sib sau ua ke siv. Tag nrho cov no tuaj yeem hloov tau yooj yim ntawm ya, ncaj qha hauv kev ua haujlwm.

Hauv kev xyaum nws zoo li no. Qee tus neeg tsim cov logic ncaj qha raws li txoj haujlwm ntawm tes. Lawv tsis nkag siab algorithms thiab lawv cov complexity, los yog cov kev npaj ua, los yog hom kev koom tes, los yog lwm yam technical components. Cov neeg no yog cov kws tshuaj ntsuam xyuas kev lag luam ntau dua li cov neeg tsim khoom. Tom qab ntawd, tag nrho cov no mus rau hauv kev sim lossis kev ua haujlwm. Enables loging ntawm cov lus nug ntev. Thaum tshawb pom cov lus nug ntev, tom qab ntawd lwm tus neeg (ntau dua kev - qhov tseem ceeb DBA) txiav txim siab los ua kom MATERIALIZED ntawm qee qhov haujlwm nruab nrab. Qhov no slows cia cov ntaubntawv povthawj siv me ntsis (vim nws yuav tsum tau muab kho dua ib tug ntxiv teb nyob rau hauv kev sib pauv). Txawm li cas los xij, tsis yog tsuas yog cov lus nug no tau nrawm nrawm, tab sis kuj tag nrho lwm tus uas siv cov haujlwm no. Nyob rau tib lub sijhawm, kev txiav txim siab uas ua haujlwm rau materialize yog qhov yooj yim. Ob qhov tsis tseem ceeb: tus naj npawb ntawm qhov muaj peev xwm nkag tau (qhov no yog pes tsawg cov ntaub ntawv yuav nyob rau hauv lub rooj sib tham), thiab ntau npaum li cas nws siv hauv lwm yam haujlwm.

Analogs

Kev lag luam niaj hnub DBMSs muaj cov txheej txheem zoo sib xws: MATERIALIZED VIEW nrog FAST REFRESH (Oracle) thiab INDEXED VIEW (Microsoft SQL Server). Hauv PostgreSQL, MATERIALIZED VIEW tsis tuaj yeem hloov kho hauv kev hloov pauv, tab sis tsuas yog thov (thiab txawm tias muaj kev txwv nruj heev), yog li peb tsis xav txog nws. Tab sis lawv muaj ntau yam teeb meem uas txwv tsis pub lawv siv.

Ua ntej, koj tsuas tuaj yeem ua kom cov khoom siv tau yog tias koj twb tau tsim VIEW tsis tu ncua. Txwv tsis pub, koj yuav tau rov sau cov lus thov ntxiv kom nkag mus rau qhov kev pom tshiab tsim los siv cov khoom siv no. Los yog tawm txhua yam raws li nws yog, tab sis nws yuav tsawg kawg yog tsis muaj txiaj ntsig yog tias muaj qee cov ntaub ntawv tau suav ua ntej, tab sis ntau cov lus nug tsis tas yuav siv nws, tab sis rov suav dua.

Qhov thib ob, lawv muaj ntau yam kev txwv:

Oracle

5.3.8.4 Kev txwv dav dav ntawm Fast Refresh

Lub ntsiab lus nug ntawm materialized saib yog txwv raws li hauv qab no:

  • Cov ntaub ntawv pom zoo yuav tsum tsis muaj cov ntaub ntawv hais txog cov lus tsis rov hais dua li SYSDATE thiab ROWNUM.
  • Cov ntaub ntawv pom yuav tsum tsis muaj cov ntaub ntawv xa mus RAW or LONG RAW hom ntaub ntawv.
  • Nws tsis tuaj yeem muaj a SELECT npe subquery.
  • Nws tsis tuaj yeem muaj analytical functions (piv txwv li, RANK) hauv lub SELECT clause.
  • Nws tsis tuaj yeem siv lub rooj ntawm qhov twg XMLIndex index yog txhais.
  • Nws tsis tuaj yeem muaj a MODEL clause.
  • Nws tsis tuaj yeem muaj a HAVING clause nrog subquery.
  • Nws tsis tuaj yeem muaj cov lus nug nested uas muaj ANY, ALL, los yog NOT EXISTS.
  • Nws tsis tuaj yeem muaj a [START WITH …] CONNECT BY clause.
  • Nws tsis tuaj yeem muaj ntau lub rooj nthuav dav ntawm ntau qhov chaw.
  • ON COMMIT materialized views tsis tuaj yeem muaj cov lus qhia txog tej thaj chaw deb.
  • Nested materialized views yuav tsum muaj kev koom los yog aggregate.
  • Materialized koom views thiab materialized aggregate views nrog a GROUP BY clause tsis tuaj yeem xaiv los ntawm lub rooj teeb tsa kev teeb tsa.

5.3.8.5 Kev txwv ceev ceev rov qab rau cov ntaub ntawv pom nrog koom nrog nkaus xwb

Kev txhais cov lus nug rau cov ntsiab lus pom nrog kev koom ua ke nkaus xwb thiab tsis muaj kev sib sau ua ke muaj cov kev txwv hauv qab no ntawm kev hloov tshiab sai:

  • Tag nrho cov kev txwv los ntawm Β«Cov kev txwv dav dav ntawm Fast Refresh".
  • Lawv tsis tuaj yeem muaj GROUP BY clauses los yog aggregates.
  • Rowis ntawm tag nrho cov rooj nyob rau hauv lub FROM daim ntawv yuav tsum tshwm nyob rau hauv lub SELECT lis query.
  • Materialized saib cav yuav tsum muaj nyob nrog rowids rau tag nrho cov hauv paus ntxhuav hauv lub FROM lis query.
  • Koj tsis tuaj yeem tsim kom muaj kev hloov kho sai sai los ntawm ntau lub rooj nrog kev sib koom ua ke yooj yim uas suav nrog cov khoom hom kab hauv SELECT daim ntawv qhia.

Tsis tas li ntawd, txoj kev kho tshiab koj xaiv yuav tsis zoo yog tias:

  • Lub ntsiab lus nug siv ib qho kev koom nrog sab nraud uas coj zoo li kev koom nrog sab hauv. Yog tias cov lus nug txhais tau muaj xws li kev koom ua ke, xav txog rov sau cov lus nug kom muaj kev koom nrog sab hauv.
  • cov SELECT daim ntawv teev cov khoom siv pom muaj cov kab lus ntawm kab los ntawm ntau lub rooj.

5.3.8.6 Kev txwv ceev ceev rov qab rau cov ntaub ntawv pom zoo nrog kev sib sau

Kev txhais cov lus nug rau cov ntsiab lus pom nrog kev sib sau los yog koom nrog muaj cov kev txwv hauv qab no rau kev hloov kho sai:

Fast refresh yog txaus siab rau ob qho tib si ON COMMIT thiab ON DEMAND materialized views, tab sis cov kev txwv hauv qab no siv:

  • Tag nrho cov rooj nyob rau hauv materialized saib yuav tsum muaj materialized saib cav, thiab cov materialized saib cav yuav tsum:
    • Muab tag nrho cov kab los ntawm cov lus hais hauv cov khoom pom.
    • Qhia nrog ROWID thiab INCLUDING NEW VALUES.
    • Qhia kom meej SEQUENCE clause yog tias lub rooj yuav tsum muaj kev sib xyaw ua ke / ncaj qha-loads, tshem tawm, thiab hloov tshiab.

  • tsuas yog SUM, COUNT, AVG, STDDEV, VARIANCE, MIN thiab MAX tau txais kev txhawb nqa ceev ceev.
  • COUNT(*) yuav tsum tau teev tseg.
  • Cov haujlwm sib sau ua ke yuav tsum tshwm sim tsuas yog nyob rau sab nraud ntawm qhov kev qhia. Ntawd yog, aggregates xws li AVG(AVG(x)) or AVG(x)+ AVG(x) tsis pub.
  • Rau txhua aggregate xws li AVG(expr), qhov sib thooj COUNT(expr) yuav tsum muaj. Oracle pom zoo tias SUM(expr) tau teev tseg.
  • If VARIANCE(expr) or STDDEV(expr) tau teev tseg, COUNT(expr) thiab SUM(expr) yuav tsum tau teev tseg. Oracle pom zoo tias SUM(expr *expr) tau teev tseg.
  • cov SELECT kem nyob rau hauv lub ntsiab lus nug yuav tsis yog ib tug complex qhia nrog kab los ntawm ntau lub hauv paus ntxhuav. Ib txoj hauv kev ua tau zoo rau qhov no yog siv cov khoom siv zes pom.
  • cov SELECT daim ntawv yuav tsum muaj tag nrho GROUP BY txhua ntawv.
  • Kev pom cov khoom siv tsis yog nyob ntawm ib lossis ntau lub rooj nyob deb.
  • Yog tias koj siv a CHAR hom ntaub ntawv nyob rau hauv lub lim kab ntawm ib materialized saib cav, cov cim teev ntawm tus tswv site thiab cov materialized saib yuav tsum yog tib yam.
  • Yog hais tias cov khoom pom muaj ib qho ntawm cov hauv qab no, ces ceev refresh yog txaus siab tsuas yog rau cov pa DML inserts thiab ncaj qha loads.
    • Materialized views nrog MIN or MAX sib sau ua ke
    • Materialized views uas muaj SUM(expr) tab sis tsis muaj COUNT(expr)
    • Materialized views tsis muaj COUNT(*)

    Xws li ib tug materialized saib yog hu ua ib tug insert-tsuas materialized saib.

  • materialized saib nrog MAX or MIN yog ceev refreshable tom qab rho tawm lossis sib xyaw DML nqe lus yog tias nws tsis muaj WHERE clause.
    Qhov max / min ceev refresh tom qab rho tawm lossis sib xyaw DML tsis muaj tus cwj pwm zoo ib yam li cov ntaub ntawv ntxig nkaus xwb. Nws deletes thiab recomputes max / min qhov tseem ceeb rau cov pab pawg cuam tshuam. Koj yuav tsum paub txog nws qhov kev ua tau zoo.
  • Materialized views nrog lub npe views los yog subqueries nyob rau hauv lub FROM clause tuaj yeem hloov kho sai sai yog qhov kev pom tuaj yeem sib xyaw ua ke. Yog xav paub ntxiv txog qhov twg views yuav sib koom ua ke, saib Oracle Database SQL Language Reference.
  • Yog tias tsis muaj kev koom nrog sab nraud, koj tuaj yeem muaj kev xaiv xaiv thiab koom nrog WHERE clause.
  • Materialized aggregate views nrog cov koom nrog sab nrauv yog ceev refreshable tom qab cov pa DML thiab cov khoom ncaj qha, tsuas yog lub rooj sab nrauv tau hloov kho. Tsis tas li ntawd, cov kev txwv tshwj xeeb yuav tsum muaj nyob rau ntawm cov kab sib koom ntawm lub rooj koom sab hauv. Yog tias muaj kev sib koom ua ke sab nraud, tag nrho cov koom nrog yuav tsum tau txuas nrog ANDs thiab yuav tsum siv qhov sib npaug (=) tus neeg ua haujlwm.
  • Rau materialized views nrog CUBE, ROLLUP, pawg pawg, lossis kev sib txuas ntawm lawv, cov kev txwv hauv qab no siv tau:
    • cov SELECT daim ntawv teev npe yuav tsum muaj pab pawg sib txawv uas tuaj yeem yog a GROUPING_ID ua haujlwm rau txhua tus GROUP BY lus los yog GROUPING ua haujlwm ib qho rau txhua tus GROUP BY kev qhia. Piv txwv li, yog tias cov GROUP BY clause ntawm materialized saib yog "GROUP BY CUBE(a, b)", tiam sis SELECT daim ntawv yuav tsum muaj xws li "GROUPING_ID(a, b)Β» los yog Β«GROUPING(a) AND GROUPING(b)Β» rau qhov materialized saib kom ceev refreshable.
    • GROUP BY yuav tsum tsis txhob ua rau muaj kev sib tw ua pab pawg. Piv txwv li, "GROUP BY a, ROLLUP(a, b)"Nws tsis tuaj yeem hloov kho sai vim tias nws ua rau cov pab pawg sib npaug"(a), (a, b), AND (a)".

5.3.8.7 Kev txwv ceev ceev rov qab rau cov ntaub ntawv pom zoo nrog UNION ALL

Materialized views nrog lub UNION ALL teeb tus neeg teb xov tooj txhawb nqa lub REFRESH FAST kev xaiv yog tias cov xwm txheej hauv qab no txaus siab:

  • Lub ntsiab lus nug yuav tsum muaj UNION ALL tus neeg teb xov tooj nyob rau theem sab saum toj.

    cov UNION ALL tus neeg teb xov tooj tsis tuaj yeem muab tso rau hauv ib qho lus nug, nrog rau ib qho kev zam: Cov UNION ALL tuaj yeem muaj nyob rau hauv subquery hauv lub FROM clause muab lub ntsiab lus nug yog ntawm daim ntawv SELECT * FROM (saib los yog subquery nrog UNION ALL) raws li hauv qab no piv txwv:

    CREATE VIEW view_with_unionall AS (SELECT c.rowid crid, c.cust_id, 2 umarker NTAWM cov neeg muas zaub c.C. C.cust_last_name = 'Smith' UNION TAG NRHO SELECT c.rowid crid, c.cust_id, 3 umarker NTAWM cov neeg muas zaub c.WHERE c.cust 'Jones'); CREATE MATERIALIZED VIEW unionall_inside_view_mv REFRESH NTAWM QHOV TSEEB AS SELECT * LOS NTAWM view_with_unionall;

    Nco ntsoov tias qhov kev pom view_with_unionall txaus siab rau qhov yuav tsum tau ua kom nrawm nrawm.

  • Txhua lo lus nug thaiv hauv lub UNION ALL Cov lus nug yuav tsum ua kom tau raws li qhov yuav tsum tau ua ntawm kev pom sai refreshable materialized saib nrog aggregates lossis ceev refreshable materialized saib nrog koom.

    Cov ntaub ntawv pom zoo tsim nyog yuav tsum tau tsim rau ntawm cov ntxhuav raws li qhov xav tau rau hom sib txuas ntawm cov khoom siv ceev ceev refreshable pom.
    Nco ntsoov tias Oracle Database kuj tso cai rau cov ntaub ntawv tshwj xeeb ntawm ib lub rooj pom zoo nrog koom nrog tsuas yog muab rau ROWID kem tau suav nrog hauv SELECT daim ntawv teev thiab nyob rau hauv cov materialized saib log. Qhov no yog qhia nyob rau hauv lub ntsiab lus nug ntawm qhov pom view_with_unionall.

  • cov SELECT daim ntawv teev cov lus nug yuav tsum muaj a UNION ALL marker, thiab UNION ALL kem yuav tsum muaj tus lej tsis tu ncua los yog hlua tus nqi hauv txhua qhov UNION ALL ceg. Tsis tas li ntawd, lub cim kab yuav tsum tshwm nyob rau hauv tib txoj hauj lwm nyob rau hauv lub SELECT cov npe ntawm txhua qhov kev nug block. Saib "UNION ALL Marker thiab Query RewriteΒ» Yog xav paub ntxiv txog UNION ALL tej cim.
  • Qee qhov nta xws li kev sib koom ua ke sab nrauv, ntxig-tsuas yog cov ntaub ntawv pom cov lus nug thiab cov rooj sib tham tej thaj chaw deb tsis txaus siab rau cov khoom pom zoo nrog UNION ALL. Nco ntsoov, txawm li cas los xij, cov ntsiab lus pom tau siv hauv kev rov ua dua, uas tsis muaj kev sib koom lossis sib sau ua ke, tuaj yeem hloov kho sai sai thaum UNION ALL los yog siv tej thaj chaw deb rooj.
  • Qhov kev sib haum xeeb pib qhov ntsuas yuav tsum tau teeb tsa rau 9.2.0 lossis siab dua los tsim kom muaj qhov pom tau sai dua tshiab nrog UNION ALL.

Kuv tsis xav ua phem rau Oracle cov kiv cua, tab sis txiav txim los ntawm lawv cov npe ntawm cov kev txwv, nws zoo li tias cov txheej txheem no tau sau tsis yog nyob rau hauv cov ntaub ntawv dav dav, siv qee hom qauv, tab sis los ntawm ntau txhiab tus neeg Khab, qhov twg txhua tus tau muab lub sijhawm rau sau lawv tus kheej ceg, thiab lawv txhua tus ua raws li nws ua tau. Siv cov txheej txheem no rau kev xav tiag tiag zoo li taug kev los ntawm minefield. Koj tuaj yeem tau txais kuv lub sijhawm twg los tau los ntawm kev ntaus ib qho ntawm cov kev txwv tsis meej. Yuav ua li cas nws ua hauj lwm kuj yog ib lo lus nug cais, tab sis nws yog dhau ntawm cov kab lus no.

Microsoft SQL neeg rau zaub mov

Lwm Yam Ntxiv

Ntxiv nrog rau SET cov kev xaiv thiab kev txiav txim siab ua haujlwm, cov kev cai hauv qab no yuav tsum tau ua raws li:

  • Tus neeg siv ua haujlwm CREATE INDEX yuav tsum yog tus tswv ntawm qhov kev pom.
  • Thaum koj tsim qhov index, tus IGNORE_DUP_KEY kev xaiv yuav tsum tau teem rau OFF (lub neej ntawd teeb tsa).
  • Cov lus yuav tsum tau hais los ntawm ob feem npe, schema.npe table nyob rau hauv kev pom txhais.
  • Tus neeg siv-txhais ua haujlwm hais txog hauv qhov kev pom yuav tsum tau tsim los ntawm kev siv lub WITH SCHEMABINDING kev xaiv.
  • Txhua tus neeg siv lub luag haujlwm uas tau hais tseg hauv qhov kev pom yuav tsum tau hais los ntawm ob lub npe, ..
  • Cov ntaub ntawv nkag mus rau cov cuab yeej ntawm tus neeg siv kev ua haujlwm yuav tsum yog NO SQL, thiab cov khoom siv sab nraud yuav tsum yog NO.
  • Hom lus runtime (CLR) kev ua haujlwm tuaj yeem tshwm sim hauv cov npe xaiv ntawm qhov pom, tab sis tsis tuaj yeem yog ib feem ntawm lub ntsiab lus ntawm pawg ntsuas qhov tseem ceeb. CLR cov haujlwm tsis tuaj yeem tshwm sim nyob rau hauv qhov chaw ntawm qhov pom lossis ON clause ntawm JOIN ua haujlwm hauv qhov pom.
  • CLR kev ua haujlwm thiab cov txheej txheem ntawm CLR cov neeg siv-txhais hom siv hauv kev pom txhais yuav tsum muaj cov khoom teeb tsa raws li qhia hauv cov lus hauv qab no.

    Khoom
    Ceeb toom

    DETERMINISTIC = TRUE
    Yuav tsum tau tshaj tawm meej meej raws li tus cwj pwm ntawm Microsoft .NET Framework txoj kev.

    PRECISE = TRUE
    Yuav tsum tau tshaj tawm meej meej raws li tus cwj pwm ntawm .NET Framework txoj kev.

    DATA ACCESS = NO SQL
    Txiav txim siab los ntawm kev teeb tsa DataAccess attribute rau DataAccessKind.None thiab SystemDataAccess attribute rau SystemDataAccessKind.None.

    EXTERNAL ACCESS = TSIS MUAJ
    Cov cuab yeej no ua rau tsis muaj rau CLR cov txheej txheem.

  • Kev pom yuav tsum tau tsim los ntawm kev siv lub WITH SCHEMABINDING kev xaiv.
  • Qhov kev pom yuav tsum siv tsuas yog cov rooj hauv paus uas nyob hauv tib lub database raws li qhov pom. Qhov kev pom tsis tuaj yeem hais txog lwm qhov kev xav.
  • Cov nqe lus SELECT hauv cov ntsiab lus saib yuav tsum tsis muaj cov hauv qab no Transact-SQL cov ntsiab lus:

    COUNT
    ROWSET cov haujlwm (OPENDATASOURCE, OPENQUERY, OPENROWSET, THIAB OPENXML)
    OUTER koom nrog(LEFT, RIGHT, los yog FULL)

    Derived table (txhais los ntawm kev qhia a SELECT nqe lus nyob rau hauv FROM clause)
    Kev koom nrog tus kheej
    Qhia cov kab ke los ntawm kev siv SELECT * or SELECT <table_name>.*

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

    ntab1, ntawv nyeem, ntext, duab, XML, los yog filestream kab
    Subquery
    OVER clause, uas muaj xws li qeb duas lossis aggregate qhov rais ua haujlwm

    Cov ntawv nyeem tag nrho (CONTAINS, FREETEXT)
    SUM muaj nuj nqi uas hais txog qhov hais tsis tau
    ORDER BY

    CLR tus neeg siv-txhais kev sib sau ua ke
    TOP
    CUBE, ROLLUP, los yog GROUPING SETS tswv

    MIN, MAX
    UNION, EXCEPT, los yog INTERSECT tswv
    TABLESAMPLE

    Table variables
    OUTER APPLY or CROSS APPLY
    PIVOT, UNPIVOT

    Sparse kab teeb
    Inline (TVF) lossis multi-statement table-valued functions (MSTVF)
    OFFSET

    CHECKSUM_AGG

    1 Cov kev pom indexed tuaj yeem muaj ntab kab; Txawm li cas los xij, cov kab ntawv no tsis tuaj yeem suav nrog hauv pawg ntsuas qhov tseem ceeb.

  • If GROUP BY tam sim no, VIEW txhais yuav tsum muaj COUNT_BIG(*) thiab yuav tsum tsis muaj HAVING. Cov no GROUP BY Cov kev txwv tsuas yog siv tau rau cov ntsiab lus indexed saib. Cov lus nug tuaj yeem siv qhov ntsuas ntsuas hauv nws txoj kev npaj ua tiav txawm tias nws tsis txaus siab rau cov no GROUP BY txwv.
  • Yog hais tias saib txhais muaj a GROUP BY clause, tus yuam sij ntawm qhov sib txawv ntawm qhov ntsuas qhov sib txawv tuaj yeem siv tsuas yog cov kab uas tau teev tseg hauv GROUP BY clause.

Nws yog qhov tseeb ntawm no tias cov neeg Khab tsis koom nrog, txij li lawv tau txiav txim siab ua raws li lub tswv yim "peb yuav ua me me, tab sis zoo." Ntawd yog, lawv muaj cov mines ntau dua ntawm thaj teb, tab sis lawv qhov chaw yog pob tshab dua. Qhov kev poob siab tshaj plaws yog qhov kev txwv no:

Qhov kev pom yuav tsum siv tsuas yog cov rooj hauv paus uas nyob hauv tib lub database raws li qhov pom. Qhov kev pom tsis tuaj yeem hais txog lwm qhov kev xav.

Hauv peb lub ntsiab lus, qhov no txhais tau hais tias ib qho haujlwm tsis tuaj yeem nkag mus rau lwm yam khoom siv tau. Qhov no txiav down tag nrho ideology nyob rau hauv lub bud.
Tsis tas li ntawd, qhov kev txwv no (thiab ntxiv rau hauv cov ntawv nyeem) zoo heev txo cov neeg siv:

Cov nqe lus SELECT hauv cov ntsiab lus saib yuav tsum tsis muaj cov hauv qab no Transact-SQL cov ntsiab lus:

COUNT
ROWSET cov haujlwm (OPENDATASOURCE, OPENQUERY, OPENROWSET, THIAB OPENXML)
OUTER koom nrog(LEFT, RIGHT, los yog FULL)

Derived table (txhais los ntawm kev qhia a SELECT nqe lus nyob rau hauv FROM clause)
Kev koom nrog tus kheej
Qhia cov kab ke los ntawm kev siv SELECT * or SELECT <table_name>.*

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

ntab1, ntawv nyeem, ntext, duab, XML, los yog filestream kab
Subquery
OVER clause, uas muaj xws li qeb duas lossis aggregate qhov rais ua haujlwm

Cov ntawv nyeem tag nrho (CONTAINS, FREETEXT)
SUM muaj nuj nqi uas hais txog qhov hais tsis tau
ORDER BY

CLR tus neeg siv-txhais kev sib sau ua ke
TOP
CUBE, ROLLUP, los yog GROUPING SETS tswv

MIN, MAX
UNION, EXCEPT, los yog INTERSECT tswv
TABLESAMPLE

Table variables
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Sparse kab teeb
Inline (TVF) lossis multi-statement table-valued functions (MSTVF)
OFFSET

CHECKSUM_AGG

Kev Koom Tes Sab Nraud, UNION, ORDER BY thiab lwm yam raug txwv. Tej zaum nws yuav yooj yim dua los piav qhia yam twg tuaj yeem siv dua li qhov tsis tuaj yeem siv. Cov npe tej zaum yuav luv dua.

Los xaus: ib qho kev txwv loj loj hauv txhua qhov (cia peb nco txog kev lag luam) DBMS vs tsis muaj (nrog rau kev zam ntawm ib qho laj thawj, tsis yog kev kawm) hauv LGPL thev naus laus zis. Txawm li cas los xij, nws yuav tsum raug sau tseg tias kev siv cov txheej txheem no hauv kev sib raug zoo logic yog qhov nyuaj dua li hauv cov lus piav qhia ua haujlwm.

Kev siv

Nws ua haujlwm li cas? PostgreSQL yog siv los ua "lub tshuab virtual". Muaj ib tug complex algorithm nyob rau hauv uas tsim queries. Ntawm no lub hauv paus. Thiab tsis yog ib pawg loj ntawm heuristics nrog ib pawg ntawm ifs. Yog li, yog tias koj muaj ob peb lub hlis los kawm, koj tuaj yeem sim nkag siab txog architecture.

Nws puas ua haujlwm zoo? Zoo heev. Hmoov tsis, qhov no nyuaj los ua pov thawj. Kuv tsuas tuaj yeem hais tias yog tias koj xav txog ntau txhiab qhov kev thov uas muaj nyob rau hauv daim ntawv thov loj, ces qhov nruab nrab lawv muaj txiaj ntsig zoo dua li cov neeg tsim khoom zoo. Ib tus programmer SQL zoo tuaj yeem sau cov lus nug tau zoo dua, tab sis nrog ib txhiab lus nug nws tsuas yog tsis muaj kev txhawb siab lossis sijhawm ua nws. Qhov tsuas yog qhov kuv tuaj yeem hais tam sim no ua pov thawj ntawm kev ua tau zoo yog tias ntau qhov haujlwm ua haujlwm ntawm lub platform tsim ntawm DBMS no ERP systems, uas muaj ntau txhiab qhov sib txawv MATERIALIZED ua haujlwm, nrog ntau txhiab tus neeg siv thiab cov ntaub ntawv terabyte nrog ntau pua lab cov ntaub ntawv khiav ntawm ob tus neeg ua haujlwm tsis tu ncua. Txawm li cas los xij, leej twg tuaj yeem tshawb xyuas / tsis lees paub qhov ua tau zoo los ntawm kev rub tawm platform thiab PostgreSQL, tig rau logging SQL queries thiab sim hloov cov logic thiab cov ntaub ntawv muaj.

Hauv cov ntawv hauv qab no, kuv tseem yuav tham txog yuav ua li cas koj tuaj yeem teeb tsa kev txwv ntawm kev ua haujlwm, ua haujlwm nrog kev hloov pauv, thiab ntau ntxiv.

Tau qhov twg los: www.hab.com

Ntxiv ib saib