ProHoster > Pūnaewele > Nā Administration > Kākau a heluhelu ʻo Balancing i kahi waihona

Kākau a heluhelu ʻo Balancing i kahi waihona

Kākau a heluhelu ʻo Balancing i kahi waihona
I ka mua 'ōlelo Ua wehewehe au i ka manaʻo a me ka hoʻokō ʻana i kahi waihona i kūkulu ʻia ma ke kumu o nā hana, ma mua o nā papa a me nā kahua e like me nā ʻikepili pili. Hāʻawi ia i nā hiʻohiʻona he nui e hōʻike ana i ka maikaʻi o kēia ala ma mua o ka mea kahiko. He nui ka poʻe i ʻike ʻaʻole lawa lākou.

Ma kēiaʻatikala, e hōʻike wau i keʻano o kēia manaʻo e hiki ai iāʻoe ke hoʻololi wikiwiki i nā kākau a heluhelu i ka waihona me ka hoʻololiʻole i ka loiloi hana. Ua ho'āʻo ʻia nā hana like e hoʻokō ʻia i nā DBMS kalepa hou (ʻo ia hoʻi, Oracle a me Microsoft SQL Server). Ma ka hopena o ka ʻatikala e hōʻike wau i ka mea a lākou i hana ai, me ka haʻahaʻa, ʻaʻole i hana maikaʻi loa.

hōʻikeʻano

E like me ka wā ma mua, no ka hoʻomaopopo maikaʻi ʻana e hoʻomaka wau i ka wehewehe me nā laʻana. E ʻōlelo mākou pono mākou e hoʻokō i ka loiloi e hoʻihoʻi i kahi papa inoa o nā keʻena me ka helu o nā limahana i loko o lākou a me kā lākou uku uku.

I loko o kahi waihona hana e like me kēia:

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

ʻO ka paʻakikī o ka hoʻokō ʻana i kēia nīnau ma kekahi DBMS e like me O(helu o na limahana)no ka mea, pono kēia helu ʻana e nānā i ka papa ʻaina holoʻokoʻa o nā limahana a laila e hui pū iā lākou e ka ʻoihana. E loaʻa pū kekahi mau mea liʻiliʻi (ke manaʻoʻiʻo mākou he nui aku nā limahana ma mua o nā keʻena) hoʻohui ma muli o ka hoʻolālā i koho ʻia O (helu helu o nā limahana) ai ole ia, O(helu o nā keʻena) no ka hui pū ʻana a pēlā aku.

Ua maopopo he ʻokoʻa paha ka hoʻokō ma luna o nā DBMS like ʻole, akā ʻaʻole e loli ka paʻakikī ma kekahi ʻano.

Ma ka hoʻokō i manaʻo ʻia, e hana ka DBMS hana i hoʻokahi subquery e helu i nā waiwai i koi ʻia no ka ʻoihana, a laila e hana i kahi JOIN me ka papa ʻaina e loaʻa ai ka inoa. Eia naʻe, no kēlā me kēia hana, i ka wā e haʻi aku ai, hiki ke hoʻonohonoho i kahi māka MATERIALIZED kūikawā. E hana ʻokoʻa ka ʻōnaehana i kahi kahua kūpono no kēlā me kēia hana. I ka hoʻololi ʻana i ka waiwai o kahi hana, e hoʻololi pū ka waiwai o ke kahua i ka hana like. Ke komo ʻana i kēia hana, e ʻike ʻia ke kahua i helu mua ʻia.

ʻO ka mea kūikawā, inā ʻoe e hoʻonohonoho i ka MATERIALIZED no nā hana helu i na limahana и salarySum, a laila e hoʻohui ʻia ʻelua mau māla i ka papa me ka papa inoa o nā keʻena, kahi e mālama ai i ka helu o nā limahana a me kā lākou uku uku. I kēlā me kēia manawa ke hoʻololi i nā limahana, kā lākou uku a i ʻole nā ​​hui ʻoihana, e hoʻololi koke ka ʻōnaehana i nā waiwai o kēia mau māla. E komo pololei ka nīnau ma luna i kēia mau kahua a e hoʻokō ʻia i loko O(helu o nā keʻena).

He aha nā kapu? Hoʻokahi wale nō mea: pono e loaʻa i kēlā ʻano hana kahi helu palena o nā waiwai hoʻokomo i wehewehe ʻia kona waiwai. A i ʻole, ʻaʻole hiki ke kūkulu i kahi pākaukau e mālama i kāna mau waiwai āpau, no ka mea ʻaʻole hiki ke loaʻa kahi papa me ka helu palena ʻole o nā lālani.

Pākuhi:

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

Ua wehewehe ʻia kēia hana no ka helu palena ʻole o nā waiwai o N (no ka laʻana, kūpono kekahi waiwai maikaʻi ʻole). No laila, ʻaʻole hiki iā ʻoe ke kau i ka MATERIALIZED ma luna. No laila he palena kūpono kēia, ʻaʻole kahi ʻenehana (ʻo ia hoʻi, ʻaʻole no ka hiki ʻole iā mākou ke hoʻokō). A i ʻole, ʻaʻohe palena. Hiki iā ʻoe ke hoʻohana i nā hui pūʻulu, hoʻokaʻawale, AND a me OR, PARTITION, recursion, etc.

No ka laʻana, ma ka pilikia 2.2 o ka ʻatikala mua, hiki iā ʻoe ke kau i ka MATERIALIZED ma nā hana ʻelua:

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;

Na ka ʻōnaehana ponoʻī e hana i hoʻokahi papa me nā kī ʻano Customer, Product и INTEGER, e hoʻohui i ʻelua mau māla iā ia a e hōʻano hou i nā koina kahua i loko o lākou me nā loli. Ke hana hou ʻia nā kelepona i kēia mau hana, ʻaʻole lākou e helu ʻia, akā e heluhelu ʻia nā waiwai mai nā kahua pili.

Ke hoʻohana nei i kēia mīkini, hiki iā ʻoe, no ka laʻana, ke kāpae i nā recursions (CTE) i nā nīnau. E noʻonoʻo pono i nā hui e hana ana i kumu lāʻau me ka hoʻohana ʻana i ka pilina keiki/makua (he loulou ko kēlā me kēia hui i kona makua):

parent = DATA Group (Group);

I loko o kahi waihona hana, hiki ke kuhikuhi ʻia ka loiloi recursion penei:

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;

Mai no ka hana isParent ua kaha ʻia ʻo MATERIALIZED, a laila e hana ʻia kahi pākaukau me ʻelua mau kī (hui) nona, kahi e hoʻokumu ʻia ai ke kahua. isParent ʻoiaʻiʻo wale nō inā he keiki ke kī mua a ka lua. ʻO ka helu o nā mea i hoʻokomo ʻia ma kēia pākaukau e like me ka helu o nā pūʻulu i hoʻonui ʻia me ka hohonu awelika o ka lāʻau. Inā makemake ʻoe, no ka laʻana, e helu i ka helu o nā mamo o kekahi hui, hiki iā ʻoe ke hoʻohana i kēia hana:

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

ʻAʻohe CTE ma ka nīnau SQL. Ma kahi e loaʻa kahi GROUP BY maʻalahi.

Ke hoʻohana nei i kēia mīkini, hiki iā ʻoe ke hoʻololi maʻalahi i ka waihona inā pono:

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

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

I ke kāhea ʻana i kahi hana la no ka laina kauoha, e heluhelu ʻia ke kahua e loaʻa ai kahi kuhikuhi mai ka papa ʻaina me nā laina kauoha. Ke hoʻololi ka lā kauoha, e helu hou ka ʻōnaehana ponoʻī i ka lā denormalized i ka laina.

pono

No ke aha kēia hana holoʻokoʻa? Ma nā DBMS maʻamau, me ka kākau ʻole ʻana i nā nīnau, hiki i ka mea hoʻomohala a i ʻole DBA ke hoʻololi i nā kuhikuhi, e hoʻoholo i nā helu a haʻi i ka mea hoʻolālā nīnau pehea e hoʻokō ai (a loaʻa wale nā ​​HINT i nā DBMS kalepa). ʻAʻole hiki iā lākou ke hoʻopau i ka nīnau mua ma ka ʻatikala ma O (helu o nā keʻena) me ka hoʻololi ʻole ʻana i nā nīnau a i ʻole ka hoʻohui ʻana i nā mea hoʻomaka. Ma ka papahana i manaʻo ʻia, ma ka pae hoʻomohala ʻaʻole pono ʻoe e noʻonoʻo e pili ana i ka ʻōnaehana mālama ʻikepili a me nā hui e hoʻohana ai. Hiki ke hoʻololi maʻalahi kēia mau mea a pau ma ka lele, pololei i ka hana.

Ma ka hana, ua like me keia. Hoʻomohala pololei kekahi poʻe i ka loiloi ma muli o ka hana ma ka lima. ʻAʻole maopopo iā lākou ka algorithm a me ko lākou paʻakikī, ʻaʻole hoʻolālā hoʻokō, ʻaʻole ʻano hui, ʻaʻole kekahi mea ʻenehana ʻē aʻe. ʻOi aku kēia poʻe i nā loiloi ʻoihana ma mua o nā mea hoʻomohala. A laila, hele kēia mau mea i ka hoʻāʻo a i ʻole ka hana. Hiki ke hoʻopaʻa inoa i nā nīnau lōʻihi. Ke ʻike ʻia kahi nīnau lōʻihi, a laila hoʻoholo nā poʻe ʻē aʻe (ʻoi aku ka ʻenehana - ʻoi aku ka DBA) e hiki iā MATERIALIZED ma kekahi hana waena. Hoʻolohi iki kēia i ka hoʻopaʻa ʻana (no ka mea e pono ana e hoʻonui i kahi kahua hou i ke kālepa). Eia naʻe, ʻaʻole wale kēia nīnau i wikiwiki nui ʻia, akā ʻo nā mea ʻē aʻe a pau e hoʻohana nei i kēia hana. I ka manawa like, maʻalahi ka hoʻoholo ʻana i ka hana e materialize. ʻElua mau ʻāpana nui: ʻo ka helu o nā koina komo (ʻo ia ka nui o nā moʻolelo i loko o ka papa e pili ana), a pehea ka manawa e hoʻohana ʻia ai i nā hana ʻē aʻe.

Analogues

Loaʻa i nā DBMS pāʻoihana hou nā hana like: MATERIALIZED VIEW me FAST REFRESH (Oracle) a me INDEXED VIEW (Microsoft SQL Server). Ma PostgreSQL, ʻaʻole hiki ke hōʻano hou ʻia ka MATERIALIZED VIEW i kahi kālepa, akā ma ke noi wale nō (a me nā palena paʻa loa), no laila ʻaʻole mākou e noʻonoʻo. Akā, loaʻa iā lākou kekahi mau pilikia e kaupalena nui i kā lākou hoʻohana.

ʻO ka mea mua, hiki iā ʻoe ke ʻae i ka materialization inā ua hana mua ʻoe i kahi VIEW maʻamau. A i ʻole, pono ʻoe e kākau hou i nā noi i koe e kiʻi i ka ʻike hou i hana ʻia e hoʻohana i kēia materialization. A i ʻole e waiho i nā mea āpau e like me ia, akā ʻaʻole ia e hana inā aia kekahi mau ʻikepili i helu mua ʻia, akā ʻaʻole hoʻohana mau nā nīnau he nui, akā e helu hou.

ʻO ka lua, he nui nā palena:

kahi e'ōlelo ai

5.3.8.4 Nā Kāohi Nui no ka Hoʻohou wikiwiki

Ua kaupalena ʻia ka nīnau wehewehe o ka ʻike materialized penei:

  • ʻAʻole pono e loaʻa i ka ʻike i hoʻohālikelike ʻia nā kuhikuhi ʻana i nā ʻōlelo hou ʻole e like me SYSDATE a ROWNUM.
  • ʻAʻole pono e loaʻa nā kuhikuhi i ka manaʻo materialized RAW or LONG RAW ʻano ʻikepili.
  • ʻAʻole hiki ke loaʻa a SELECT papa inoa subquery.
  • ʻAʻole hiki ke loaʻa nā hana analytical (no ka laʻana, RANK) i ka SELECT māhele.
  • ʻAʻole hiki iā ia ke kuhikuhi i kahi papa ʻaina kahi XMLIndex wehewehe ʻia ka index.
  • ʻAʻole hiki ke loaʻa a MODEL māhele.
  • ʻAʻole hiki ke loaʻa a HAVING pauku me ka subquery.
  • ʻAʻole hiki ke loaʻa nā nīnau pūnana i loaʻa ANY, ALL, a NOT EXISTS.
  • ʻAʻole hiki ke loaʻa a [START WITH …] CONNECT BY māhele.
  • ʻAʻole hiki ke loaʻa nā papa kikoʻī he nui ma nā wahi like ʻole.
  • ON COMMIT ʻAʻole hiki ke loaʻa nā papa kikoʻī mamao.
  • Pono e hui pū ʻia nā manaʻo i hoʻohuihui ʻia.
  • Materialized hui ʻana i nā manaʻo a materialized aggregate manaʻo me ka GROUP BY ʻAʻole hiki i ka paukū ke koho mai kahi papa kuhikuhi-i hoʻonohonoho ʻia.

5.3.8.5 Kaohi ʻana i ka hoʻihoʻi wikiwiki ʻana i nā manaʻo i hana ʻia me nā hui wale nō

ʻO ka wehewehe ʻana i nā nīnau no nā manaʻo i hoʻohālikelike ʻia me ka hui pū wale ʻana a ʻaʻohe hui i loaʻa i kēia mau palena no ka hōʻoluʻolu wikiwiki:

  • Nā kapu a pau mai «ʻO nā kapu maʻamau no ka hoʻohou wikiwiki".
  • ʻAʻole hiki iā lākou ke loaʻa GROUP BY nā māhele a i ʻole nā ​​hui.
  • Rowids o nā papa ʻaina a pau i ka FROM pono e puka i ka papa inoa ma ka SELECT papa inoa o ka ninau.
  • Pono e loaʻa nā lāʻau nānā materialized me nā rowids no nā papa kumu a pau i ka FROM papa inoa o ka ninau.
  • ʻAʻole hiki iā ʻoe ke hana i kahi hiʻohiʻona hoʻomaʻamaʻa wikiwiki mai nā papa he nui me nā hui maʻalahi e komo pū me kahi kolamu ʻano mea i loko o ka SELECT olelo.

Eia kekahi, ʻaʻole ʻoi aku ka maikaʻi o ke ala hoʻomaha āu e koho ai inā:

  • Hoʻohana ka nīnau wehewehe i kahi hui waho e like me kahi hui i loko. Inā loaʻa i ka nīnau wehewehe kahi hui like, e noʻonoʻo e kākau hou i ka nīnau wehewehe i loaʻa kahi hui i loko.
  • ka SELECT Loaʻa i ka papa inoa o ka ʻike i hoʻohālikelike ʻia i nā kolamu mai nā papa he nui.

5.3.8.6 Kaohi ʻana i ka hōʻano hou wikiwiki ʻana i nā manaʻo i hoʻohālikelike ʻia me nā hui

ʻO ka wehewehe ʻana i nā nīnau no nā manaʻo i hoʻohālikelike ʻia me nā hōʻuluʻulu a i ʻole nā ​​hui pū ʻana e loaʻa i kēia mau mea kaohi no ka hōʻoluʻolu wikiwiki:

Kākoʻo ʻia ka hōʻano hou wikiwiki no nā mea ʻelua ON COMMIT a ON DEMAND nā manaʻo i hoʻokō ʻia, akā pili kēia mau mea:

  • Pono nā papa ʻaina a pau i ka ʻike i hoʻopaʻa ʻia e loaʻa i nā log view materialized, a ʻo nā log view materialized pono:
    • Loaʻa i nā kolamu a pau mai ka papa ʻaina i kuhikuhi ʻia ma ka ʻike maoli.
    • E wehewehe me ROWID a INCLUDING NEW VALUES.
    • Ho'āka i ka SEQUENCE pauku inā manaʻo ʻia ka papaʻaina e hui pū ʻia me nā mea hoʻokomo/direct-loads, holoi, a me nā mea hou.

  • wale no SUM, COUNT, AVG, STDDEV, VARIANCE, MIN a MAX kākoʻo ʻia no ka hōʻoluʻolu wikiwiki.
  • COUNT(*) pono e hoakakaia.
  • Pono e hana ʻia nā hana hui ma ke ʻano he ʻaoʻao waho loa o ka ʻōlelo. ʻO ia hoʻi, nā hui e like me AVG(AVG(x)) or AVG(x)+ AVG(x) ʻaʻole ʻae ʻia.
  • No kēlā me kēia hui e like me AVG(expr), ka mea pili COUNT(expr) pono e noho. Manaʻo ʻo Oracle i kēlā SUM(expr) e hoakakaia.
  • If VARIANCE(expr) or STDDEV(expr) ua kuhikuhi ʻia, COUNT(expr) a SUM(expr) pono e hoakakaia. Manaʻo ʻo Oracle i kēlā SUM(expr *expr) e hoakakaia.
  • ka SELECT ʻAʻole hiki i ke kolamu i ka nīnau wehewehe ke ʻōlelo paʻakikī me nā kolamu mai nā papa kumu he nui. ʻO kahi workaround hiki i kēia ke hoʻohana i kahi hiʻohiʻona nested materialized.
  • ka SELECT Pono nā papa inoa a pau GROUP BY kolamu.
  • ʻAʻole hoʻokumu ʻia ka hiʻohiʻona ma luna o hoʻokahi a ʻoi aku paha nā papa mamao.
  • Inā ʻoe e hoʻohana i CHAR ʻano ʻikepili i loko o nā kolamu kānana o kahi log view materialized, pono e like nā pūʻulu ʻano o ka pae kumu a me ka nānā ʻana i ka materialized.
  • Inā loaʻa kekahi o nā hiʻohiʻona i hoʻohālikelike ʻia, a laila kākoʻo ʻia ka hōʻoluʻolu wikiwiki ma nā hoʻokomo DML maʻamau a me nā ukana pololei.
    • Manaʻo materialized me MIN or MAX hōʻuluʻulu manaʻo
    • Nā manaʻo materialized i loaʻa SUM(expr) akā ʻaʻole COUNT(expr)
    • Manaʻo materialized me ka ʻole COUNT(*)

    Ua kapa ʻia kēlā ʻano hiʻohiʻona he insert-only materialized view.

  • He manaʻo materialized me MAX or MIN Hiki ke hoʻomaha hou ma hope o ka holoiʻana a iʻole ka hui pūʻana i nā'ōlelo DML ināʻaʻole he WHERE māhele.
    ʻAʻole like ke ʻano o ka max/min ma hope o ka holoi ʻana a i ʻole DML hui pū me ka hihia hoʻokomo wale nō. Holoi a helu hou i nā koina max / min no nā hui i hoʻopilikia ʻia. Pono ʻoe e ʻike i ka hopena o kāna hana.
  • Nā manaʻo waiwai me nā manaʻo i kapa ʻia a i ʻole subqueries ma ka FROM Hiki ke hoʻohou koke ʻia ka paukū inā hiki ke hoʻohui ʻia nā manaʻo. No ka ʻike e hui ai nā manaʻo, e ʻike ʻO Oracle Database SQL Language Reference.
  • Inā ʻaʻohe hui waho, loaʻa paha iā ʻoe nā koho koho a hui pū ʻia i loko o ka WHERE māhele.
  • Hiki ke hoʻomaʻamaʻa wikiwiki ʻia nā hiʻohiʻona i hoʻohui ʻia me nā hui waho ma hope o ka DML maʻamau a me nā ukana pololei, ke hoʻololi wale ʻia ka papa ʻaina waho. Eia nō hoʻi, pono e loaʻa nā kaohi kū hoʻokahi ma nā kolamu hui o ka papa hoʻohui i loko. Inā loaʻa nā hui waho, pono e hoʻopili ʻia nā hui āpau e ANDs a pono e hoʻohana i ke kaulike (=) mea hoʻohana.
  • No nā manaʻo materialized me CUBE, ROLLUP, nā pūʻulu hui, a i ʻole ka hui pū ʻana o ia mau mea, pili kēia mau kapu:
    • ka SELECT Pono e loaʻa i ka papa inoa ka wehewehe ʻana i hiki ke a GROUPING_ID hana ma na mea a pau GROUP BY nā ʻōlelo a i ʻole GROUPING hana hoʻokahi no kēlā me kēia GROUP BY hōʻike. No ka laʻana, inā ka GROUP BY ʻO ka paukū o ka manaʻo materialized "GROUP BY CUBE(a, b)", a laila ka SELECT i loko o ka papa inoa "GROUPING_ID(a, b)»a «GROUPING(a) AND GROUPING(b)»no ka nānā ʻana i mea maoli e hiki ke hoʻomaha hou.
    • GROUP BY ʻaʻole pono e loaʻa nā hui pālua. ʻo kahi laʻana, "GROUP BY a, ROLLUP(a, b)"ʻAʻole hiki ke hoʻoulu hou ʻia no ka mea e hopena i nā hui pālua "(a), (a, b), AND (a)".

5.3.8.7 Kaohi ʻana i ka hoʻihoʻi wikiwiki ʻana i nā manaʻo i hoʻohālikelike ʻia me UNION ALL

Manaʻo materialized me ka UNION ALL hoʻonohonoho i ke kākoʻo o ka mea hoʻohana i ka REFRESH FAST koho inā hoʻokō ʻia nā kūlana i lalo:

  • Pono ka nīnau wehewehe UNION ALL mea hoʻohana ma ka pae kiʻekiʻe.

    ka UNION ALL ʻAʻole hiki ke hoʻokomo ʻia ka mea hoʻohana i loko o kahi subquery, me kahi ʻokoʻa: The UNION ALL hiki i ka subquery ma ka FROM pauku i hāʻawi ʻia i ke ʻano o ka nīnau wehewehe SELECT * FROM (nānā a i ʻole nā ​​nīnau me UNION ALL) e like me kēia laʻana:

    E HOʻOHANA i ka view_with_unionall AS (koho i c.rowid crid, c.cust_id, 2 mau mea kūʻai mai c WHERE c.cust_last_name = 'Smith' UNION E KOHO A pau c.rowid crid, c.cust_id, 3 mea kūʻai mai c WHERE c.cust_last_name 'Jones'); HANA I KA MATERIALIZED VIEW unionall_inside_view_mv REFRESH FAST ON DEMAND AS SELECT * FROM view_with_unionall;

    E hoʻomaopopo i ka ʻike view_with_unionall ʻoluʻolu i nā koi no ka hoʻomaha wikiwiki.

  • ʻO kēlā me kēia poloka nīnau ma ka UNION ALL Pono e hoʻokō ka nīnau i nā koi o ka nānā ʻana i ka mea hoʻomaʻamaʻa wikiwiki me nā aggregates a i ʻole kahi hiʻohiʻona hoʻomaʻamaʻa wikiwiki me nā hui.

    Pono e hana ʻia nā moʻolelo ʻike materialized kūpono ma nā papa e like me ka mea e pono ai no ke ʻano like o ka ʻike maka.
    E hoʻomaopopo hoʻi e ʻae ʻia ka Oracle Database i ka hihia kūikawā o kahi papaʻaina i hoʻohālikelike ʻia me ka hui pū ʻana i hāʻawi ʻia i ka ROWID ua komo ke kolamu ma ka SELECT papa inoa a i loko o ka mooolelo ike. Hōʻike ʻia kēia ma ka nīnau wehewehe o ka ʻike view_with_unionall.

  • ka SELECT Pono e komo i ka papa inoa o kēlā me kēia nīnau a UNION ALL hoailona, ​​a me ka UNION ALL Pono e loaʻa i ke kolamu ka waiwai helu mau a i ʻole ke kaula i kēlā me kēia UNION ALL lālā. Eia hou, pono ke kolamu marker ma ke kūlana ordinal like ma ka SELECT papa inoa o kēlā me kēia poloka nīnau. E ʻike "UNION ALL Marker and Query Rewrite»no ka 'ike hou aku e pili ana UNION ALL nā māka.
  • ʻAʻole kākoʻo ʻia kekahi mau hiʻohiʻona e like me ka hui ʻana o waho, hoʻokomo wale ʻia nā nīnau ʻike i hoʻohālikelike ʻia a me nā papa mamao no nā manaʻo waiwai me UNION ALL. E hoʻomaopopo naʻe, ʻo nā manaʻo i hoʻohana ʻia i ka hana hou ʻana, ʻaʻole i loaʻa nā hui a i ʻole nā ​​​​huihui, hiki ke hōʻoluʻolu wikiwiki ʻia i ka wā UNION ALL a i ʻole nā ​​papa ʻaina mamao.
  • Pono e hoʻonoho ʻia ka ʻāpana hoʻomaka hoʻohālikelike i ka 9.2.0 a i ʻole ke kiʻekiʻe aʻe e hana i kahi hiʻohiʻona hoʻomaʻamaʻa wikiwiki me ka UNION ALL.

ʻAʻole wau makemake e hōʻeha i nā poʻe Oracle, akā i ka hoʻoholo ʻana ma kā lākou papa inoa o nā mea kapu, me he mea lā ʻaʻole i kākau ʻia kēia ʻano hana ma ka hihia maʻamau, me ka hoʻohana ʻana i kekahi ʻano hiʻohiʻona, akā e nā tausani o nā India, kahi i hāʻawi ʻia i kēlā me kēia kanaka ka manawa e kakau iho i ko lakou lala iho, a hana kela mea keia mea i kona hiki, a hana iho la. ʻO ka hoʻohana ʻana i kēia mīkini no ka loiloi maoli e like me ka hele ʻana ma waena o kahi minefield. Hiki iā ʻoe ke loaʻa i ka mine i kēlā me kēia manawa ma ke kī ʻana i kekahi o nā mea ʻike ʻole. He nīnau kaʻawale nō hoʻi ke ʻano o ka hana ʻana, akā aia ma waho o ke kiko o kēia ʻatikala.

Pūnaewele SQL Microsoft

Nā Mana'ē aʻe

Ma waho aʻe o nā koho SET a me nā koi hana deterministic, pono e hoʻokō ʻia nā koi aʻe:

  • ʻO ka mea hoʻohana e hoʻokō CREATE INDEX pono ka mea nona ka ike.
  • Ke hana ʻoe i ka index, ka IGNORE_DUP_KEY Pono e hoʻonoho ʻia ke koho i OFF (ka hoʻonohonoho paʻamau).
  • Pono e kuhikuhi ʻia nā papa e nā inoa ʻelua ʻāpana, nā wikiwiki.papa inoa i ka wehewehe ʻike.
  • Pono e hana ʻia nā hana i wehewehe ʻia e ka mea hoʻohana ma ka hoʻohana ʻana i ka WITH SCHEMABINDING koho.
  • Pono e kuhikuhi ʻia nā hana i wehewehe ʻia e ka mea hoʻohana e nā inoa ʻelua ʻāpana, ..
  • Pono ka waiwai komo ʻikepili o kahi hana i wehewehe ʻia e ka mea hoʻohana NO SQL, a me ka waiwai komo waho pono e NO.
  • Hiki ke ʻike ʻia nā hana manawa holo ʻōlelo maʻamau (CLR) ma ka papa inoa koho o ka nānā, akā ʻaʻole hiki ke lilo i ʻāpana o ka wehewehe ʻana o ke kī kuhikuhi clustered. ʻAʻole hiki ke ʻike ʻia nā hana CLR ma ka māhele WHERE o ka ʻike a i ʻole ka paukū ON o kahi hana JOIN ma ka ʻike.
  • Pono nā hana CLR a me nā ʻano o nā ʻano i wehewehe ʻia e ka mea hoʻohana i hoʻohana ʻia i ka wehewehe ʻana i nā waiwai i hoʻonohonoho ʻia e like me ka mea i hōʻike ʻia ma ka papa ma lalo.

    waiwai
    palapala aie

    HOOMAIKAI = OIAIO
    Pono e haʻi ʻia ma ke ʻano he ʻano o ke ʻano Microsoft .NET Framework.

    PRECISE = OIAIO
    Pono e haʻi ʻia ma ke ʻano he ʻano o ke ʻano .NET Framework.

    HANA KAHIKI = NO SQL
    Hoʻoholo ʻia ma ka hoʻonohonoho ʻana i ke ʻano DataAccess iā DataAccessKind.None a me SystemDataAccess hiʻohiʻona iā SystemDataAccessKind.None.

    HANA KUWAHO = NO
    ʻAʻole kēia waiwai no nā hana maʻamau CLR.

  • Pono e hana ʻia ka ʻike me ka hoʻohana ʻana i ka WITH SCHEMABINDING koho.
  • Pono ka ʻike e kuhikuhi i nā papa kumu wale nō i loko o ka waihona like me ka ʻike. ʻAʻole hiki i ka ʻike ke kuhikuhi i nā manaʻo ʻē aʻe.
  • ʻAʻole pono e loaʻa i ka ʻōlelo SELECT ma ka wehewehe ʻana i nā mea Transact-SQL e pili nei:

    COUNT
    Nā hana ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, A ME OPENXML)
    OUTER hui (LEFT, RIGHT, a FULL)

    Pākaukau i loaʻa (i wehewehe ʻia e ka wehewehe ʻana a SELECT ʻōlelo ma ka FROM māhele)
    Hoʻohui ponoʻī
    Ka wehewehe ʻana i nā kolamu ma ka hoʻohana ʻana SELECT * or SELECT <table_name>.*

    DISTINCT
    STDEV, STDEVP, VAR, VARP, a AVG
    Hōʻike papaʻaina maʻamau (CTE)

    ke lana1, kikokikona, ntext, kiʻi, 'ōkuene, a filestream kolamu
    Subquery
    OVER pauku, e komo pū ana me nā hana puka makani hoʻonohonoho

    Predicates kikokikona piha (CONTAINS, FREETEXT)
    SUM hana e kuhikuhi ana i ka olelo nullable
    ORDER BY

    ʻO ka hana aggregate i wehewehe ʻia e ka mea hoʻohana
    TOP
    CUBE, ROLLUP, a GROUPING SETS ana

    MIN, MAX
    UNION, EXCEPT, a INTERSECT ana
    TABLESAMPLE

    Nā hoʻololi papa
    OUTER APPLY or CROSS APPLY
    PIVOT, UNPIVOT

    Hoʻonohonoho kolamu kaʻawale
    Inline (TVF) a i ʻole nā ​​hana i helu ʻia i ka papa ʻōlelo nui (MSTVF)
    OFFSET

    CHECKSUM_AGG

    1 Hiki ke loaʻa i ka ʻike kuhikuhi ke lana kolamu; akā naʻe, ʻaʻole hiki ke hoʻokomo ʻia ia mau kolamu i ke kī kuhikuhi puʻupuʻu.

  • If GROUP BY aia, pono e komo ka wehewehena VIEW COUNT_BIG(*) ʻaʻole pono e komo HAVING. ʻO kēia GROUP BY pili wale nā ​​kapu i ka wehewehe ʻike kuhikuhi. Hiki i kahi nīnau ke hoʻohana i ka ʻike kuhikuhi i loko o kāna hoʻolālā hoʻokō inā ʻaʻole ia e hoʻokō i kēia mau mea GROUP BY mea ea.
  • Inā loaʻa i ka wehewehe ʻike a GROUP BY pauku, hiki i ke ki o ka index clustered ke kuhikuhi wale i na kolamu i hoakakaia ma ka GROUP BY māhele.

Ua maopopo ma aneʻi ʻaʻole i komo ka poʻe India, ʻoiai ua hoʻoholo lākou e hana e like me ke kumumanaʻo "e hana liʻiliʻi mākou, akā maikaʻi." ʻO ia hoʻi, ʻoi aku ka nui o nā mines ma ke kahua, akā ʻoi aku ka maopopo o ko lākou wahi. ʻO ka mea pōʻino loa kēia palena:

Pono ka ʻike e kuhikuhi i nā papa kumu wale nō i loko o ka waihona like me ka ʻike. ʻAʻole hiki i ka ʻike ke kuhikuhi i nā manaʻo ʻē aʻe.

I kā mākou huaʻōlelo, ʻo ia ke ʻano ʻaʻole hiki i kahi hana ke komo i kahi hana materialized ʻē aʻe. Hoʻopau kēia i nā manaʻo a pau i ka pua.
Eia kekahi, ʻo kēia palena (a ʻoi aku ma ka kikokikona) e hōʻemi nui i nā hihia hoʻohana:

ʻAʻole pono e loaʻa i ka ʻōlelo SELECT ma ka wehewehe ʻana i nā mea Transact-SQL e pili nei:

COUNT
Nā hana ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, A ME OPENXML)
OUTER hui (LEFT, RIGHT, a FULL)

Pākaukau i loaʻa (i wehewehe ʻia e ka wehewehe ʻana a SELECT ʻōlelo ma ka FROM māhele)
Hoʻohui ponoʻī
Ka wehewehe ʻana i nā kolamu ma ka hoʻohana ʻana SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP, a AVG
Hōʻike papaʻaina maʻamau (CTE)

ke lana1, kikokikona, ntext, kiʻi, 'ōkuene, a filestream kolamu
Subquery
OVER pauku, e komo pū ana me nā hana puka makani hoʻonohonoho

Predicates kikokikona piha (CONTAINS, FREETEXT)
SUM hana e kuhikuhi ana i ka olelo nullable
ORDER BY

ʻO ka hana aggregate i wehewehe ʻia e ka mea hoʻohana
TOP
CUBE, ROLLUP, a GROUPING SETS ana

MIN, MAX
UNION, EXCEPT, a INTERSECT ana
TABLESAMPLE

Nā hoʻololi papa
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

Hoʻonohonoho kolamu kaʻawale
Inline (TVF) a i ʻole nā ​​hana i helu ʻia i ka papa ʻōlelo nui (MSTVF)
OFFSET

CHECKSUM_AGG

OUTER JOINS, UNION, ORDER BY a me nā mea ʻē aʻe ua pāpā ʻia. Ua maʻalahi paha ka wehewehe ʻana i ka mea hiki ke hoʻohana ʻia ma mua o ka mea hiki ʻole ke hoʻohana. E ʻoi aku ka pōkole o ka papa inoa.

No ka hōʻuluʻulu manaʻo: kahi hoʻonohonoho nui o nā kaohi i kēlā me kēia (e nānā i ka pāʻoihana) DBMS vs ʻaʻohe (koe wale kekahi logical, ʻaʻole loea) i ka ʻenehana LGPL. Eia nō naʻe, pono e hoʻomaopopo ʻia ʻo ka hoʻokō ʻana i kēia ʻano hana i loko o ka loina relational ʻoi aku ka paʻakikī ma mua o ka loiloi hana i wehewehe ʻia.

Ka hoʻokō

Pehea ia hana? Hoʻohana ʻia ʻo PostgreSQL ma ke ʻano he "mīkini virtual". Aia kahi algorithm paʻakikī i loko e kūkulu i nā nīnau. Eia kumuwaiwai. A ʻaʻole wale kahi pūʻulu nui o nā heuristics me kahi hui o nā ifs. No laila, inā he mau mahina kāu e aʻo ai, hiki iā ʻoe ke hoʻāʻo e hoʻomaopopo i ka hoʻolālā.

He hana pono anei? Pono maikaʻi. ʻO ka mea pōʻino, paʻakikī kēia e hōʻoia. Hiki iaʻu ke ʻōlelo inā inā ʻoe e noʻonoʻo i nā tausani o nā nīnau i loaʻa i nā noi nui, a laila ma ka awelika ʻoi aku ka maikaʻi ma mua o nā mea hoʻomohala maikaʻi. Hiki i ka mea hoʻolālā SQL maikaʻi ke kākau i kekahi nīnau me ka ʻoi aku ka maikaʻi, akā me hoʻokahi kaukani nīnau ʻaʻole loaʻa iā ia ka manaʻo a i ʻole ka manawa e hana ai. ʻO ka mea wale nō e hiki iaʻu ke haʻi i kēia manawa he hōʻoia o ka maikaʻi ʻo ia ka hana ʻana o kekahi mau papahana ma luna o ke kahua i kūkulu ʻia ma kēia DBMS Pūnaehana ERP, he mau kaukani o nā hana MATERIALIZED like ʻole, me nā tausani o nā mea hoʻohana a me nā waihona terabyte me nā haneli miliona o nā moʻolelo e holo ana ma kahi kikowaena ʻelua-processor maʻamau. Eia naʻe, hiki i kekahi ke nānā a hōʻole i ka pono ma ka hoʻoiho ʻana anuu a me PostgreSQL, huli ana hoʻopaʻa inoa i nā nīnau SQL a ho'āʻo e hoʻololi i ka loina a me ka ʻikepili ma laila.

Ma nā ʻatikala aʻe, e kamaʻilio pū wau e pili ana i ke ʻano e hiki ai iā ʻoe ke hoʻonohonoho i nā mea paʻa i nā hana, hana me nā kau hoʻololi, a me nā mea hou aku.

Source: www.habr.com

Pākuʻi i ka manaʻo hoʻopuka