PostgreSQL యాంటీప్యాటర్న్‌లు: SQLలో పరిస్థితులను మూల్యాంకనం చేయడం

SQL అనేది C++ కాదు మరియు జావాస్క్రిప్ట్ కాదు. అందువల్ల, తార్కిక వ్యక్తీకరణల గణన భిన్నంగా జరుగుతుంది మరియు ఇది ఒకే విషయం కాదు:

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 యాంటీప్యాటర్న్‌లు: ఒక అరుదైన రికార్డ్ JOIN మధ్యలో చేరుకుంటుంది.

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