כיצד לאנדקס טבלה עבור שליפה הכוללת תנאי על טווח (In, Between) ומיון

28/02/2014

תגיות: ,
אין תגובות

נתונה טבלה MyTbl ואנחנו רוצים לשלוף ממנה נתונים העונים לתנאי על טווח של ערכים ולמיין את הפלט.
ה-SQL די ברור במקרה זה:

Select *

From   MyTbl

Where  Col1 In (..)   

Order By Col2;

והשאלה היא כיצד לאנדקס את הטבלה.
מקובל שמאנדקסים טבלה קודם כל לפי העמודות שב-Where כדי שישלפו רק השורות המבוקשות,
ולאחר מכן העמודות שב-Order By כדי שמה שנשלף יהיה ממויין,
אלא שזה נכון לגבי תנאים על ערכים בודדים (הכוללים סימן =) ואילו במקרה זה אם ניצור אינדקס לפי Col1 ואחריו Col2 – נקבל סט ממויין לפי הערכים שב-Col1 (בניגוד למקרה הקודם בו יש רק ערך אחד ב-Col1 ולכן אין משמעות למיון), והמערכת תצטרך למיין אותו. לעומת זאת, אם נהפוך את סדר העמודות ו-Col2 תהיה ראשונה – המערכת תצטרך לבצע Scan מלא על הטבלה כדי למצוא את השורות העונות על התנאי, אם כי יחסך המיון שכן התוצאות ישלפו ממויינות כבר..
טוב מראה עיניים מדיבורים מיותרים:

If Object_ID('T1','U') Is Not Null Drop Table T1; 

Select    *

Into    T1

From    sys.messages;

Alter Table T1 Add Constraint PK_T1 Primary Key Clustered(language_id,message_id);

 

If Object_ID('T2','U') Is Not Null Drop Table T2; 

Select    *

Into    T2

From    sys.messages;

Alter Table T2 Add Constraint PK_T2 Primary Key Clustered(message_id,language_id);

 

Select    *

From    T1

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

 

Select    *

From    T2

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

image

מה אמרתי לך? (ציטוט מהורי כשלא שמעתי מוסר אבי ונטשתי תורת אמי) – נכון שאפשרות אחת טובה פי 16מזולתה במקרה זה, אלא שהיה עלינו לבחור בין שתי רעות: מיון במקרה הראשון ו-Clustered Index Scan במקרה השני.. אי אפשר Clustered Index Seek ללא Sort?.. (שאלה רטורית שהתשובה עליה היא "לא").
באופן כללי- ככל שיהיו פחות ערכים בסט כך תהיה עדיפות לאופציה הראשונה הכוללת מיון (יהיה צורך למיין מספר קטן יחסית של שורות),
וככל שיהיו יותר ערכים (כמו במקרה זה) – תהיה עדיפות לביצוע Scan.
איו להסיק מדוגמה זו שאינדקס כמו בטבלה הראשונה תמיד עדיף: אם נריץ את השאילתות עם שני ערכים בתנאי המגמה תתהפך.

אינדקס שהיה פותר את הבעייה, אך נקודתית למקרה זה- הוא שימוש ב-Filtered Index שיכלול רק את הרשומות המפולטרות:

If Object_ID('T3','U') Is Not Null Drop Table T3;

Select    *

Into    T3

From    sys.messages;

Create Index Idx_T3 On T3(message_id)  Include(language_id,severity,is_event_logged,text) Where language_id In (1028,1033,1033,1046);

 

Select    *

From    T1

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

 

Select    *

From    T2

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

 

Select    *

From    T3

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

image

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

דרך אחרת למצוא פתרון טוב יותר בצורה "לא הוגנת" היא להפוך את השליפה לשליפת Union All (למעשה כל תנאי Where הכולל Or או מספר אפשרויות – ניתן להמרה ל-Union All):

Select    *

From    T1

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

 

Select    *

From    T2

Where    language_id In (1028,1033,1035,1046)

Order By message_id;

 

Select    *

From    T1

Where    language_id=1028

Union All

Select    *

From    T1

Where    language_id=1033

Union All

Select    *

From    T1

Where    language_id=1035

Union All

Select    *

From    T1

Where    language_id=1046

Order By message_id;

image

(לא רואים את מלוא ה-Execution Plan אך יש 4 Clustered Index Seeks כנגד 4 הערכים ב-In)
כאן מדובר בניסוח שונה של השליפה ללא יצירת אינדקס מיוחד (השתמשתי בטבלה T1), הביצועים במקרה זה גם טובים משמעותית משתי האופציות הקודמות (אם כי פחות טוביםמשל Filtered Index).
המערכת שולפת את הנתונים עבור כל ערך, ומכיוון שהיא יודעת שהם כבר ממויינים – על ידי ביצוע Merge מקבלים סט מאוחד ממויין אף הוא.

לסיכום: אין פילטר שמתאים כללית לבעיות כאלו,
אבל ניתן לאלתר פתרונות יצירתיים למקרים ספציפיים.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *