Oracle SQL की बोली: उदाहरण में मॉडल। भाग २

यह SELECT कथन एक्सटेंशन - मॉडल निर्माण का उपयोग करने के बारे में लेख की एक निरंतरता है। पहले भाग से, आपके पास पहले से ही उद्देश्य और इसके आवेदन की कुछ विशेषताओं के बारे में एक विचार है, और आधे सिंटैक्स से भी परिचित हैं। अगला, कई जटिल उदाहरणों का विश्लेषण किया जाएगा, साथ ही साथ गुंजाइश और प्रदर्शन का विश्लेषण भी किया जाएगा।

MODEL निर्माण का उपयोग अन्य चयनित ब्लॉकों के अंदर कुल कार्यों के उपयोग पर प्रतिबंध लगाता है (सामान्य रूप से, सभी चयन योग्य कॉलम MODEL में उल्लिखित उन लोगों से प्राप्त किए जाने चाहिए) - इसके बजाय, इन कार्यों को पार्टीशन, BY, आयाम BY या ME के ​​माध्यम से घोषित करें। उदाहरण के लिए, निम्नलिखित सरल क्वेरी।

SELECT employee_id, sum(amount)
FROM sales
GROUP BY employee_id;

के बराबर होगा

SELECT employee_id, amt
FROM sales
GROUP BY employee_id
MODEL PARTITION BY (employee_id)
DIMENSION BY (0 dummy)
MEASURES (sum(amount) amt)
RULES ();

नियमों द्वारा किए गए परिवर्तनों को नियंत्रित करने के लिए तीन शब्दार्थ हैं: UPDATE, UPSERT, UPSERT ALL। अद्यतन केवल आपको आइटम अपडेट करने की अनुमति देता है, यूपीएसईआरटी (जो डिफ़ॉल्ट रूप से काम करता है) को स्थितीय लिंक का उपयोग करके बदला और जोड़ा जा सकता है, और यूपीएसईआरटी सभी आपको प्रतीकात्मक लिंक का उपयोग करके आइटम बनाने की अनुमति देता है। शब्दार्थ दोनों को एक व्यक्तिगत नियम के लिए बदला जा सकता है (इस मामले में, निर्देश इसके पहले लिखा गया है), और पूरे ब्लॉक के स्तर पर (फिर निर्देश को नियम के बाद निर्दिष्ट किया जाना चाहिए)।

यह जानना महत्वपूर्ण है कि UPSERT ALL फॉर लूप की तरह काम नहीं करता है। इस मामले में नई वस्तुओं का निर्माण चार चरणों में होता है।

1. प्रतीकात्मक लिंक से मेल खाने वाले सभी तत्व पाए जाते हैं।
2. प्रत्येक आयाम के लिए, अद्वितीय सूचकांक मूल्यों का एक सेट है।
3. इन सेटों के कार्टेशियन उत्पाद की गणना की जाती है।
4. काम में मौजूद सभी गैर-मौजूद तत्व बनाए जाते हैं।

जटिल उदाहरण

अंत में, कुछ उदाहरणों से पता चलता है कि MODEL का उपयोग करके विभिन्न कार्यों को कैसे हल किया जा सकता है। शुरू करने के लिए, हम प्रत्येक दिन एक लाइन में पीने वाले कॉफी की सूची देंगे:

SELECT day, substr(type, 2) listing
FROM coffee
MODEL RETURN UPDATED ROWS
PARTITION BY (day)
DIMENSION BY (row_number() OVER ( PARTITION BY day ORDER BY type) position)
MEASURES (type, cnt)
RULES ITERATE (100500) UNTIL (presentv(type[iteration_number + 2], 1, 0) = 0) (
type[0] = type[0] || ', ' || cnt[iteration_number + 1] || ' x ' || type[iteration_number + 1]
)
ORDER BY day;

DAY LISTING
---------- ----------------------------------------
1 1 x espresso, 1 x turkish
2 1 x black, 1 x espresso, 2 x turkish
3 3 x latte
4 2 x black, 1 x ice

फ़ंक्शन presentv (रेफरी, arg1, arg2) से , यहाँ नया, यह arg1 रिटर्न अगर रेफरी संदर्भ तत्व के साथ एक मौजूदा तत्व के लिए नहीं NULL मान और arg2 अन्यथा। लाइन नंबरिंग और नाम के आधार पर छांटने के लिए, विश्लेषणात्मक फ़ंक्शन (row_number () OVER (पार्टिशन बाय दिन ORDER BY प्रकार) का उपयोग किया जाता है। मॉडल और विश्लेषणात्मक फ़ंक्शन दोनों में विभाजन (विभाजन बाय) द्वारा प्रत्येक दिन के लिए अलग से प्रसंस्करण प्रदान किया जाता है।

और, आखिरकार, एक ज्यामितीय उदाहरण। अन्तर्विभाजक खंडों का एक सेट [p1, P2] सीधी रेखा पर दिया गया है; आपको पूरी तरह से खंडों से आच्छादित श्रेणियों की क्रमबद्ध सूची प्रदर्शित करने की आवश्यकता है:

SELECT * FROM lines;

P1 P2
---------- ----------
1 6
5 7
2 4
8 20
11 28
30 32

यह माना जाता है कि पी 2> = पी 1, बेतरतीब ढंग से संग्रहीत खंडों के समाधान को इच्छुक लोगों को गर्म करने के लिए छोड़ दिया जाता है। तो उदाहरण कोड:

SELECT p1, max(p2) p2
FROM (
SELECT p1, p2 FROM lines
MODEL DIMENSION BY (row_number() OVER ( ORDER BY p1, p2) rn)
MEASURES (p1, p2)
RULES AUTOMATIC ORDER (
p1[rn > 1] ORDER BY rn = CASE sign(p2[cv(rn) - 1] - p1[cv(rn)])
WHEN 1 THEN p1[cv(rn) - 1]
ELSE p1[cv(rn)]
END ,
p2[rn > 1] ORDER BY rn = greatest(p2[cv(rn) - 1], p2[cv(rn)])
)
)
GROUP BY p1
ORDER BY p1;

P1 P2
---------- ----------
1 7
8 28
30 32

मॉडल के सेगमेंट को पहले बाएं बिंदुओं से क्रमबद्ध किया जाता है और क्रमांकित (row_number () OVER (ORDER BY p1, P2)) द्वारा क्रमबद्ध किया जाता है, और फिर ऊपर से नीचे की ओर संख्या (ORDER BY rn directive) पर देखा जाता है। ध्यान दें कि पहली बार, समानता के बाईं ओर एक प्रतीकात्मक लिंक का उपयोग किया जाता है, यही कारण है कि आपको उस आदेश को निर्दिष्ट करना होगा जिसमें तत्वों को संसाधित किया जाएगा। यदि देखे गए खंड का बिंदु p1 पिछले खंड का है, तो उसे पिछले खंड के p1 से बदल दिया जाता है। P2 को वर्तमान और पिछले सेगमेंट में सबसे सही P2 द्वारा प्रतिस्थापित किया गया है)। इस प्रकार, नीचे जाकर, हम खंडों द्वारा कवर की गई सीमा का विस्तार करते हैं। स्वचालित आदेश निर्देश का उपयोग किया जाता है ताकि पी 1 और पी 2 को वर्तमान खंड के बदले में पढ़ा जाए। यदि आप निर्देश निकालते हैं, तो सभी लाइनों के लिए पहला नियम पहले निष्पादित किया जाएगा, और उसके बाद ही दूसरा। सामान्य स्थिति में, यह निर्देश नियमों को संसाधित करते समय तत्वों के बीच निर्भरता को ध्यान में रखता है और उनकी परिधि के आदेश का उल्लंघन कर सकता है (जैसा कि ऊपर वर्णित है)।

व्यावहारिक अनुप्रयोग और प्रदर्शन

सामान्य तौर पर, मॉडल का उपयोग अविश्वसनीय रूप से शायद ही कभी किया जाता है। ऐसा इसलिए होता है क्योंकि वास्तव में जटिल चीजों का 90% विश्लेषणात्मक कार्यों द्वारा हल किया जाता है, और जो 10% MODEL के लिए बने रहते हैं, उन्हें परंपरागत रूप से डेटाबेस स्तर से निकाल दिया जाता है। इसके बावजूद, यहां मुख्य एप्लिकेशन परिदृश्य हैं:

• यह वास्तव में जटिल रिपोर्ट उत्पन्न करने के लिए आवश्यक है (पठनीयता और रखरखाव में आसानी प्राथमिकता है);
• नमूना विश्लेषणात्मक कार्यों का उपयोग करने के लिए भी संभव नहीं है;
• अनुरोध उच्च स्तर की भाषा में गतिशील रूप से उत्पन्न होता है (एनालिटिक्स की तुलना में MODEL में नियम बनाना आसान है);
• जब मॉडल अभी भी पर्याप्त है, तो संसाधन-गहन पुनरावृत्त गणना का उपयोग किया जाता है, और PL / SQL संक्रमण प्रदर्शन में कमी की ओर जाता है;
• शैक्षणिक रुचि (प्रक्रियात्मक के बजाय घोषणात्मक दृष्टिकोण)।

प्रदर्शन सरल है। अधिकांश मामलों में मॉडल समान PL / SQL कोड की तुलना में तेज है, लेकिन विश्लेषणात्मक कार्यों का उपयोग करने की तुलना में धीमा है। EXPLAIN PLAN से, आप अनुरोध प्रसंस्करण के बारे में कुछ विवरण पा सकते हैं।

• SQL मॉडल की घोषणा [तेजी] - इसका मतलब है कि नियमों को क्वेरी में निर्दिष्ट अनुक्रम में संसाधित किया जाता है (डिफ़ॉल्ट रूप से);
• SQL मॉडल ACCCLIC [तेजी] - सेल निर्भरता स्वचालित रूप से गणना कर रहे हैं;
• SQL मॉडल CYCLIC कोशिकाओं के बीच जटिल संबंधों के साथ सबसे धीमा विकल्प है।

फास्ट एनोटेशन होने का मतलब है कि सब कुछ ठीक है। यह इस शर्त के तहत होता है कि समानता के सभी बाएं हिस्से स्थितीय लिंक हैं, या बाईं ओर प्रतीकात्मक लिंक हैं, लेकिन एक ही समय में सरल अंकगणितीय समग्र कार्य (राशि, एवीजी, आदि) दाईं ओर हैं। तदनुसार, गति के संदर्भ में, MODEL FAST विश्लेषणात्मक कार्यों का उपयोग करने के करीब है, जबकि MODEL CYCLIC PL / SQL कार्यान्वयन खेल सकता है।

साहित्य

1. Oracle® Database Data Warehousing Guide 11g रिलीज़ 2 - अध्याय 22 "मॉडलिंग के लिए SQL"।
2. Oracle डाटाबेस 10g का SQL मॉडल क्लॉज।