לפניכם מאמר כללי בDB, המתייחס לנושא הכאוב של הכפלת רשומות: כיצד ניתן לאתר, תוך מספר שניות, את הטבלה או הטבלאות אשר גורמות להכפלת רשומות בשאילתא.
המאמר מאת: תמיר בר-נצר. תמיר מועסק כעצמאי בארגונים בניתוח,פיתוח, יישום והטמעה של מערכות Business Objects, מערכות DWH וBI. לתמיר נסיון של כ 13 שנה בנושאים אלו.
כל אחד העוסק בעיבוד נתונים נתקל מדי פעם במצב של הכפלת רשומות. כלומר , ערך מספרי אשר הוכפל מספר פעמים בשאילתת sql. לדוגמא, נסתכל על הסכמה הבאה:
לפנינו טבלת סוכנים עם מכירות לסוכן:
בטבלה זאת, שדה agent_id הוא המפתח ושדה agent_sales_num הוא הנתון המספרי. השדות האחרים מתחברים לטבלאות אחרות אשר מפענחות אותם.
כמובן שאם נבצע שאילתא הסוכמת את agent_sales_num, ברמה של agent_id, נקבל תוצאות תקינות (agent_id הוא המפתח):
SELECT agent_id, SUM(agent_sales_num) AS agent_sales_num
FROM agents
GROUP BY agent_id
והתוצאה תהיה:
אם נשכלל את השאילתא ונחבר את שתי הטבלאות הנוספות: טבלת ערים (cities)
וטבלת מחלקות (divisions)
כלומר בשאילתא הבאה:
SELECT agents.agent_id, SUM(agents.agent_sales_num) AS agent_sales_num
FROM agents ,
cities ,
divisions
where agents.agent_city_id = cities.city_id
and agents.agent_division_id = divisions.division_id
GROUP BY agents.agent_id
שוב, התוצאה תהיה תקינה:
הבעיה מתחילה שנרצה להוסיף טבלה נוספת בעלת יחס של אחד לרבים לטבלת הagents, לדוגמא טבלת כתובות (addresses):
אם תשימו לב, לסוכן 1 ישנן שתי כתובות, לכן אם נוסיף טבלה זאת לשאילתא:
SELECT agents.agent_id, SUM(agents.agent_sales_num) AS agent_sales_num
FROM agents ,
cities ,
divisions,
addresses
where agents.agent_city_id = cities.city_id
and agents.agent_division_id = divisions.division_id
and agents.agent_id=addresses.agent_id
GROUP BY agents.agent_id
אם נתבונן היטב בתוצאות,נראה שקיבלנו הכפלת רשומות בסוכן מספר 1:
הסיבה היא כמובן שבטבלת הכתובות מוזנות שתי כתובות לסוכן מספר 1, מכאן ה100 המקורי הוכפל במספר רשומות הכתובת הקיימות לכל סוכן. אגב, אותו הדין לסוכן מספר 2, אלא שלו מוזנת רק כתובת אחת לכן לא נראה הכפלת רשומות.
מצב זה הוא כמובן לא תקין ונשאלת השאלה: כיצד ניתן לאתר שהבעיה היא בטבלת addresses?
הדרך הטרווילית שבה מנסים לאתר את הטבלאות הסוררות היא באמצעות הסרה של כל טבלה מהשאילתא , הרצת השאילתא ובדיקת התוצאות וכך מסיקים מסקנות לגבי הטבלה הבעייתית. שיטה זאת אולי טובה ויפה כשמדובר בשאילתא של 3טבלאות היוצאות מטבלה אחת מרכזית,כמתואר במקרה זה, אך מה קורה כשמדובר בשאילתא בת 50 טבלאות הקשורות אחת לשנייה? או שאילתות שנוצרו על ידי כלים אוטומטים, כגון bo או קוגנוס. כמובן ששיטה זאת תהיה מסורבלת מאד ותדרוש הרבה זמן והבנה טובה של הסכמה.
למזלנו, ישנה שיטה הרבה יותר טובה ומהירה. כדי לאתר הטבלה הבעיתית, נבצע את הפעולות הבאות:
1. נבחר ערך אחד של המפתח של הטבלה שאנו יודעים שהרשומות בו הוכפלו , במקרה זה סוכן 1. שימו לב: שלב זה הוא רק למטרת נוחות כדי שלא נצטרך לעבוד עם כמות גדולה של רשומות.
2. ניקח את השאילתא המקורית ונבצע עליה את הפעולות הבאות:
2.1 נוסיף לחלק הwhere את הערך אחד של המפתח שבחרנו (במקרה זה, סוכן 1)
2.2 בחלק הselect נחליף את כל מה שרשום ל * .
2.3 נסיר את הgroup by מהשאילתא
בקיצור, נהפוך את השאילתא מ:
SELECT agents.agent_id, SUM(agents.agent_sales_num) AS agent_sales_num
FROM agents ,
cities ,
divisions,
addresses
where agents.agent_city_id = cities.city_id
and agents.agent_division_id = divisions.division_id
and agents.agent_id=addresses.agent_id
GROUP BY agents.agent_id
לשאילתא:
SELECT *
FROM agents ,
cities ,
divisions,
addresses
where agents.agent_city_id = cities.city_id
and agents.agent_division_id = divisions.division_id
and agents.agent_id=addresses.agent_id
and agents.agent_id=1
נריץ את השאילתא, אנו אמורים לקבל מספר רשומות כגודל ההכפלה (במקרה הזה 2) אשר זהות כמעט לחלוטין. ההבדל בין הרשומות הוא הדבר שיוצר את ההכפלה.
במקרה שלנו, נריץ את השאילתא:
החלק המסומן הוא התוצאה הנכונה מוכפלת במספר הרשומות (2).
שימו לב שרשומה 1 ו2 זהות לחלוטין, אך כשנמשיך לגלול ימינה, נראה את ההבדל ברשומות:
ההבדל בין השורות הוא הדבר שגורם להכפלת הרשומות. לכן ברור לנו שההכפלה נגרמת מטבלת הכתובות. מצאנו את הבעיה במהירות!
הערות: