पोस्टग्रेएसक्यूएल अँटीपॅटर्न: एसक्यूएलमधील परिस्थितींचे मूल्यांकन करणे

SQL C++ नाही, आणि JavaScript नाही. म्हणून, तार्किक अभिव्यक्तींची गणना वेगळ्या प्रकारे होते आणि हे सर्व समान नाही:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL क्वेरी अंमलबजावणी योजना ऑप्टिमाइझ करण्याच्या प्रक्रियेत स्वैरपणे समतुल्य परिस्थिती "पुनर्रचना" करू शकते, त्यातील काही वैयक्तिक नोंदींसाठी मोजू नका, त्यांना लागू निर्देशांकाच्या अटींशी संबंधित करा... थोडक्यात, सर्वात सोपा मार्ग म्हणजे तुम्ही नियंत्रित करू शकत नाही ते कोणत्या क्रमाने करतील (आणि त्यांची गणना केली जाईल की नाही) समान परिस्थिती.

म्हणून, जर तुम्हाला अजूनही प्राधान्य व्यवस्थापित करायचे असेल, तर तुम्हाला त्याची रचना करणे आवश्यक आहे या अटी असमान करा सशर्त वापरणे अभिव्यक्ती и ऑपरेटर.

डेटा आणि त्यांच्यासोबत काम करणे हा आधार आहे आमचे VLSI कॉम्प्लेक्स, म्हणूनच आमच्यासाठी हे खूप महत्वाचे आहे की त्यांच्यावर ऑपरेशन्स केवळ योग्यरित्याच नव्हे तर कार्यक्षमतेने देखील केल्या जातात. चला विशिष्ट उदाहरणे पाहू या जेथे अभिव्यक्ती मोजण्यात चुका होऊ शकतात आणि त्यांची कार्यक्षमता सुधारणे योग्य आहे.

#0: RTFM

सुरू होत आहे दस्तऐवजीकरणातील उदाहरण:

जेव्हा मूल्यमापनाचा क्रम महत्त्वाचा असतो, तेव्हा ते रचना वापरून कॅप्चर केले जाऊ शकते CASE. उदाहरणार्थ, वाक्यात शून्याने भागाकार टाळण्याचा हा एक मार्ग आहे WHERE अविश्वसनीय:

SELECT ... WHERE x > 0 AND y/x > 1.5;

सुरक्षित पर्याय:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

अशा प्रकारे डिझाइन वापरले CASE अभिव्यक्ती ऑप्टिमायझेशनपासून संरक्षित करते, म्हणून ते आवश्यक तेव्हाच वापरले जावे.

#1: ट्रिगर स्थिती

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

सर्व काही छान दिसते, पण... गुंतवणुकीचे आश्वासन कोणी देत ​​नाही SELECT पहिली अट खोटी असल्यास अंमलात आणली जाणार नाही. यासह त्याचे निराकरण करूया घरटे IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

आता काळजीपूर्वक पाहू - ट्रिगर फंक्शनचा संपूर्ण भाग "गुंडाळलेला" आहे IF. याचा अर्थ असा आहे की वापरून प्रक्रियेतून ही स्थिती काढून टाकण्यापासून काहीही प्रतिबंधित करत नाही WHEN-परिस्थिती:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

जेव्हा स्थिती चुकीची असते तेव्हा सर्व्हर संसाधने जतन करण्यासाठी हा दृष्टिकोन हमी देतो.

#2: किंवा/आणि साखळी

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

अन्यथा, आपण दोन्ही सह समाप्त करू शकता EXISTS "सत्य" असेल, पण दोन्ही पूर्ण होतील.

परंतु जर आपल्याला निश्चितपणे माहित असेल की त्यापैकी एक "सत्य" आहे (किंवा "खोटे" - साठी AND-चेन्स) - कसा तरी "त्याचा प्राधान्यक्रम वाढवणे" शक्य आहे जेणेकरुन दुसरा पुन्हा अंमलात आणला जाणार नाही?

असे दिसून आले की हे शक्य आहे - अल्गोरिदमिक दृष्टीकोन लेखाच्या विषयाच्या जवळ आहे PostgreSQL अँटीपॅटर्न: एक दुर्मिळ रेकॉर्ड जॉइनच्या मध्यभागी पोहोचेल.

चला या दोन्ही अटी फक्त CASE अंतर्गत "ढकून टाका":

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

या प्रकरणात आम्ही परिभाषित केले नाही ELSE-मूल्य, म्हणजे, दोन्ही अटी असत्य असल्यास CASE परत येईल NULL, ज्याचा अर्थ असा केला जातो FALSE в WHERE-परिस्थिती.

हे उदाहरण इतर मार्गांनी एकत्र केले जाऊ शकते - चव आणि रंगावर अवलंबून:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: अटी कशा लिहायच्या

या ट्रिगरच्या "विचित्र" ऑपरेशनच्या कारणांचे विश्लेषण करण्यासाठी आम्ही दोन दिवस घालवले - ते का ते पाहूया.

स्रोत:

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

समस्या #1: असमानता NULL चा आदर करत नाही

चला कल्पना करूया की सर्वकाही OLD-क्षेत्रांना अर्थ होता NULL. काय होईल?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

आणि परिस्थितीनुसार काम करण्याच्या दृष्टिकोनातून NULL समतुल्य FALSE, वर नमूद केल्याप्रमाणे.

निर्णय: ऑपरेटर वापरा IS DISTINCT FROM पासून ROW-ऑपरेटर, एकाच वेळी संपूर्ण रेकॉर्डची तुलना करणे:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

समस्या #2: समान कार्यक्षमतेची भिन्न अंमलबजावणी

तुलना कराः

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

येथे अतिरिक्त गुंतवणूक का आहे? SELECT? एक कार्य to_regclass? ते वेगळे का आहे?..

चला निराकरण करूया:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

समस्या #3: बूल ऑपरेशन्सची प्राथमिकता

चला स्त्रोत स्वरूपित करू:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

अरेरे... खरं तर, असे दिसून आले की जर पहिल्या दोन अटींपैकी कोणतीही सत्य असेल तर, संपूर्ण स्थिती बदलते TRUE, खात्यात असमानता न घेता. आणि हे आम्हाला हवे होते असे अजिबात नाही.

चला निराकरण करूया:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

समस्या #4 (लहान): जटिल किंवा एका फील्डसाठी स्थिती

वास्तविक, आम्हाला क्र. 3 मध्ये समस्या होत्या कारण तीन अटी होत्या. परंतु त्याऐवजी आपण यंत्रणा वापरून एकासह मिळवू शकता coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

तर आम्ही NULL "आम्ही पकडू", आणि अवघड OR कंसात कुंपण घालण्याची गरज नाही.

एकूण

आम्हाला काय मिळाले ते रेकॉर्ड करूया:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

आणि जर आपण विचार केला की हे ट्रिगर फंक्शन फक्त मध्ये वापरले जाऊ शकते UPDATE-उपलब्धतेमुळे ट्रिगर OLD/NEW वरच्या-स्तरीय स्थितीत, नंतर ही स्थिती साधारणपणे ठेवली जाऊ शकते WHEN- स्थिती, # 1 मध्ये दर्शविल्याप्रमाणे...

स्त्रोत: www.habr.com