אינדקס התומך בשילוב של שיוויון ואי שיוויון (או מיון)

התבוננו בדוגמה הבאה:

Select Col1,Col2

From   MyTbl

Where  Col3='aaa'

              And Col4>=150;

האינדקס המתאים יהיה במקרה זה -

Create Index Idx_Col3_Col4

On MyTbl(Col3,Col4);

אני מתעלם מאופציות ה-Include וה-With שעליהן נהוג לומר it depends..

בעזרת האינדקס המערכת תמצא את כל השורות בהן 'Col3='aaa , והן יהיו ממויינות לפי Col4 וכך ניתן יהיה למצוא את הראשון ששווה 150 ולשלוף את כל מה שממנו ואילך.

אינדקס זה (Idx_Col3_Col4) תומך בשתי שאילתות נוספות:

Select Col1,Col2

From   dbo.MyTbl

Where  Col3='aaa'

Order By Col4;

Select Col1,Col2,

              Row_Number() Over(Partition By Col3 Order By Col4) N

From   dbo.MyTbl;

בשתיהן אנחנו צריכים חלוקה לפי Col3 ומיון פנימי בכל קבוצה לפי Col4.

מה קורה במקרים מסובכים יותר, למשל:

--1

Select Col1,Col2

From   dbo.MyTbl

Where  Col3='aaa'

              And Col5='20230315'

              And Col4>=150;

--2

Select Col1,Col2

From   dbo.MyTbl

Where  Col3='aaa'

              And Col5='20230315'

Order By Col4;

--3

Select Col1,Col2

From   dbo.MyTbl

Where  Col3='aaa'

Order By Col5,Col4;

--4

Select Col1,Col2

From   dbo.MyTbl

Where  Col3='aaa'

Order By Col5,Col4;

--5

Select Col1,Col2,

              Row_Number() Over(Partition By Col3,Col5 Order By Col4) N

From   dbo.MyTbl;

--6

Select Col1,Col2,

              Row_Number() Over(Partition By Col3 Order By Col5,Col4) N

From   dbo.MyTbl;

בכל המקרים האלו - המערכת תנצל באופו אופטימלי את האינדקס המקובץ לפי Col3, עם קיבוץ פנימי לפי Col5, ומיון פנימי לפי Col4.

מה יקרה במקרה מורכב יותר עם 4 או 5 או יותר עמודות עם תנאים ומיונים?

כל עוד מדובר בתנאי שיוויון + מיון (ובהתאם בפונקצייה Row_Number) - ניתן להוסיף עמודות (כמובן בכפוף למגבלות היכולת של האינדקס).

מתי לא?

כאשר יש שני אי שיוויונים ב-Where או כשיש אי שיוויון ב-Where ו-Order By (כולל ב-Row_Number). הסיבה היא שכל עוד מדובר בשיוויונים - כל תנאי מצמצם את השורות לערך אחד.

למשל - 'Where  Col3='aaa מחזיר שורת בכולן Col3 זהה; ולעומת זאת Col4>=150 מחזיר שורות בהן יש ערכים שונים של Col4 ואם יש עוד אי שיוויון או אולי Order By יהיה צורך לחפש כל ערך בנפרד (במקרה של אי שיוויון) או למיין את כל הערכים המקרה של Order By.

מה עושים אם כך? צריך לשקול איזו אופצייה עדיפה, למשל במקרה של השאילתה הזו:

Select Col1,Col2

From   dbo.MyTbl

Where  Col3='aaa'

              And Col5='20230315'

              And Col4>=150

Order By Col6;

שתי העמודות הראשונות באינדקס יהיו Col3 & Col5 (כרגע לא משנה הסדר), אבל לגבי העמודה השלישית נתלבט בין Col4 ל- Col6:

• אם Col4 אזי נקבל את כל השורות הרלוונטיות, אבל נצטרך למיין אותן לפי Col6.

• אם Col6 אזי נקל את כל השורות ממויינות, אבל נצטרך לבצע עליהן Scan ולפלטר את אלו העונות על התנאי של Col4.
  

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

את העמודה שוויתרנו עליה נשים ב-Include כי אין טעם להוסיף אותה לאינדקס עצמו.

אותה התלבטות תהיה אם יהיו שני אי שיוויונים ב-Where או אי שיוויון ב-Where ו-Row_Numberעם Order By. לעומת זאת, אם יהיו שתי עמודות ב-Order By אזי לא תהיה כל בעיה.