הפונקציה COALESCE - ilDBA Portal

הפונקציה COALESCE

09/06/2014 | פורסם על ידי

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף אחד לא יודע איך באמת מבטאים את השם שלה 🙂

נתחיל בקווי הדמיון בין הפונקציות האלה:

מה?
לשתי הפונקציות תפקיד דומה – להחזיר את האיבר הראשון שאיננו NULL מתוך רשימה של איברים. אם כל האיברים ברשימה הם NULL, גם התוצאה תהיה NULL.

איפה?
לשתי הפונקציות אפשר לקרוא גם מ-SQL וגם מ-PL/SQL.

לגבי ההבדלים בין הפונקציות – יש שני הבדלים עיקריים ומשמעותיים:

כמה?
ההבדל הראשון הוא בכמות האיברים שהפונקציות מקבלות.

NVL תומכת בשני איברים בדיוק. הביטוי

NVL(a,b)

יחזיר את הערך של a אם הוא שונה מ-NULL, ואת b אם a שווה ל-NULL.
הפונקציה COALESCE תומכת בשני איברים או יותר.
נסתכל על הביטוי

COALESCE(X1, X2, X3,..., Xn)

אם X1 שונה מ-NULL הערך שלו יחזור
אחרת, אם X2 שונה מ-NULL הערך שלו יחזור
אחרת, אם X3 שונה מ-NULL הערך שלו יחזור
וכך הלאה
אז אם יוצא לכם לכתוב לפעמים משהו כמו:

NVL(a,NVL(b,c))

או לחילופין:

CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END
אתם יכולים להחליף את זה ב:
COALESCE(a,b,c)

מתי?
ההבדל השני פחות בולט לעין, אבל מאוד משמעותי במקרים מסויימים.
במשך שנים רבות הנחתי שאם אני כותב

NVL(expression1,expression2)

אז expression2 יחושב אך ורק אם expression1 הוא NULL. זה פשוט נראה הגיוני, לא? אבל לא כך הדבר. ב-NVL שני הביטויים מחושבים תמיד. ב-COALESCE, לעומת זאת, הביטויים מחושבים רק לפי הצורך (וכך גם ב-DECODE וב-CASE, דרך אגב).

נראה דוגמה בה התנהגות זו של NVL בעייתית:
בשאילתה הבאה רוצים להחזיר את הערך של העמודה DESCRIPTION אם הוא קיים. אם העמודה ריקה, רוצים להחזיר את התוצאה של פונקציה כלשהי – get_description – שמקבלת כקלט את העמודות A_ID ו-B_ID.

SELECT NVL(DESCRIPTION, get_description(A_ID,B_ID))
FROM TEST_TABLE;

לא חשוב מה בדיוק עושה הפונקציה get_description. מה שבטוח זה שכל קריאה לפונקציה כאשר העמודה DESCRIPTION מכילה ערך מיותרת, אבל זה בדיוק מה שקורה כאן, והפגיעה בביצועים יכולה להיות משמעותית.
אבל אם נשתמש ב-COALESCE במקום ב-NVL, נשיג את אותה תוצאה מבחינה פונקציונלית, והקריאות המיותרות לא יתבצעו. get_description תיקרא רק עבור הרשומות בהן העמודה DESCRIPTION ריקה.

SELECT COALESCE(DESCRIPTION, get_description(A_ID,B_ID))
FROM TEST_TABLE;

יתרון אחד שיש ל-NVL על פני COALESCE הוא כמות התווים שצריך להקליד.
אז אם יש לנו בדיוק שני ביטויים, ושניהם כבר מחושבים, אפשר להמשיך להשתמש ב-NVL. בשאר המקרים, כדאי לעבור ל-COALESCE.

בהצלחה,
אורן נקדימון

The following two tabs change content below.
אורן נקדימון (oren@db-oriented.com) הוא יועץ עצמאי, מומחה למסד הנתונים של אורקל, בעל למעלה מ-20 שנות נסיון עשיר ונרחב עם מערכות מבוססות אורקל – כאיש פיתוח, DBA, מנהל ומרצה. אורן צבר במרוצת השנים ידע רב על אורקל, והוא שמח לחלוק אותו עם אחרים. אורן מאמין בשיתוף פעולה בין אנשי הפיתוח וה-DBAs, ובעבודתו מנסה לגשר על הפערים המסורתיים בין שני העולמות האלה. לפרטים נוספים: www.db-oriented.com.

Latest posts by אורן נקדימון (see all)

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2017 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: