אינדקסים – חלק 4

בחלקים הקודמים דיברנו על נושאים שונים בעולם האינדקסים. עכשיו, כשאנחנו מבינים את הרעיון נדבר על נקודה שהרבה אנשים לא מכירים או לא מבינים לעומק, וזה ה- Clustering Factor.

תזכורת של המאמרים הקודמים: חלק 1, חלק 2, חלק 3

מהו Clustering Factor

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

1. הגישה תהיה דרך אינדקס
2. לאינדקס 2 רמות

כדי להביא את הרשומות, אורקל ילך לשורש של האינדקס, ממנו יעבור לעלה ומהעלה יביא את כל חמשת ה- ROWID הרלוונטים. עד כאן עשינו 2 פעולות IO. השאלה הגדולה היא מה קורה עכשיו. יש לנו חמישה ROWID ואנחנו צריכים להביא את הרשומות עצמן. במקרה הטוב ביותר (ציור מספר 1 למטה), כל הרשומות יהיו באותו בלוק בטבלה ונצטרך לעשות רק עוד פעולת IO אחת. במקרה הרע ביותר (ציור מספר 2 למטה), כל רשומה נמצאת בבלוק אחר בטבלה ונצטרך לעשות חמש פעולות IO כדי להביא את הרשומות. דבר זה משפיע מאד על הביצועים וגם על החלטת ה- optimizer האם לעשות full table scan או index scan.

אז כמו שיכולתם לנחש, Clustering Factor הוא המספר שמייצג את הסטטיסטיקה הזאת. אני אנסה לנסח את זה במשפט אחד פשוט (וזה לא קל): Clustering Factor טוב מתאר מצב שבו רשומות שנמצאות אחת ליד השניה בעלים של האינדקס, נמצאות אחת ליד השניה גם בבלוקים של הטבלה.

כלומר, ככל שה- Clustering Factor נמוך יותר, אז כאשר אנחנו שולפים 2 רשומות צמודות מהאינדקס, יש סיכוי גבוה שהן באותו בלוק בטבלה. ואם ה- Clustering Factor היה גרוע, סיכוי סביר שהרשומות נמצאות בבלוקים אחרים בטבלה. במקרה כזה, על מנת להביא את הרשומות, היה צורך בשתי גישות לדיסק כדי להביא אותן.

טווח הערכים האפשריים של ה- Clustering Factor נע ממספר הבלוקים בטבלה למספר הרשומות בטבלה.

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

איך משפרים את ה- Clustering Factor

כאן יש מיס-קונספציה גדולה, הרבה פעמים אני שומע מאנשים שכדי לשפר את ה- Clustering Factor צריך לבצע rebuild לאינדקס. אם הבנתם את המאמר עד כאן, אתם גם אמורים להבין ש- rebuild לאינדקס הוא לגמרי חסר משמעות כאן. ביצוע rebuild לאינדקס יבנה את האינדקס מחדש, אבל בגלל שהאינדקס ממוין, אותן רשומות יהיו אחת ליד השניה באינדקס גם אחרי ה- rebuild. בגלל ש- rebuild לאינדקס לא נוגע כלל בטבלה, אז רשומות עוקבות באינדקס עדיין יהיו באותו בלוק או בבלוקים נפרדים בטבלה, ללא קשר ל- rebuild.

אז איך משפרים את ה- Clustering Factor? התשובה היחידה היא: בונים את הטבלה מחדש.
היתרון בבניה מחדש הוא שאפשר למיין את הרשומות תוך כדי הבניה מחדש. אם הרשומות ממוינות לפי שדה מסויים, אז האינדקס לפי השדה הזה עכשיו יהיה בעל Clustering Factor מצוין. רשומות שנמצאות אחת ליד השניה באינדקס יהיו אחת ליד השניה גם בטבלה (כי היא ממוינת).

אני בטוח יש אנשים שקוראים את זה ואומרים "אבל רגע, אם אני בונה מחדש את הטבלה ממוינת לפי שדה מסוים, אז זה בעצם פוגע ב- Clustering Factor של אינדקסים אחרים".
זה נכון, בניה מחדש היא לא רק קשה וכבדה, היא גם פוגעת באינדקסים אחרים, ואין מה לעשות עם זה. לכן, חשוב לקחת בחשבון את כל השיקולים ולהחליט בצורה מושכלת האם כדאי לבנות מחדש את הטבלה ולפי איזה שדה.

שימו לב לעוד דבר חשוב, אם הטבלה ממשיכה להשתנות ורשומות נמחקות ובמקומן נכנסות רשומות חדשות, עם הזמן הערך של ה- Clustering Factor ישתנה לרעה. ככה שבניה מחדש של טבלה, שהיא פעולה כבדה, עושים בדך כלל על טבלה שלא משתנה (כמו טבלת היסטוריה).

מתי Clustering Factor לא מעניין אותנו

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

אז מתי Clustering Factor כן חשוב לנו

ה- Clustering Factor חשוב לנו במקרים בהם אנחנו צריכים לשלוף די הרבה נתונים מהטבלה ואנחנו רוצים להימנע מפעולות IO רבות ומיותרות. אני ממליץ להתייחס לערך של ה- Clustering Factor בעיקר בטבלאות גדולות שצריכים לשלוף מהן הרבה מידע.
במקרים כאלה (בהנחה ש- full table scan הוא מאד יקר כי הטבלה ענקית), כדאי למיין את הטבלה (בדרך כלל את ה- partition הרלוונטי) לפי האינדקס הנפוץ (למשל תאריך) כדי לשפר את ה- Clustering Factor ולשפר ביצועים בגישה דרך אינדקס.

דוגמאות

אחרי שדיברנו על כל התאוריה, בואו נדגים את הנושא.

נכין את הטבלאות:

SQL> create table clustering (id number, name varchar2(100));
Table created.

SQL> begin
  2     for i in 1..1000000
  3     loop
  4             insert into clustering values(i,’name ‘||i);
  5     end loop;
  6     commit;
  7  end;
  8  /
PL/SQL procedure successfully completed.

SQL> create table clustering_nosort as
  2  select * from clustering order by dbms_random.value;
Table created.

SQL> create table clustering_sort as
  2  select * from clustering order by id;
Table created.

עכשיו נבנה את האינדקסים ונבדוק את ה- Clustering Factor

SQL> create index nosort_index on clustering_nosort(id);
Index created.

SQL> create index sort_index on clustering_sort(id);
Index created.

SQL> select index_name,clustering_factor from user_indexes where table_name like 'CLUSTERING%';
SQL> select index_name,clustering_factor from user_indexes where table_name like 'CLUSTERING%';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
SORT_INDEX                                  3024
NOSORT_INDEX                              999654

ושימו לב להבדלים בכמות הגישות לבלוקים בשאילתות הבאות:

SQL> set autotrace traceonly stat
SQL> select * from clustering_nosort where id between 100 and 110;
11 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        829  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

 SQL> select * from clustering_sort where id between 100 and 110;
11 rows selected.
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        829  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

סיכום

כאן סיימתי את סדרת המאמרים על אינדקסים. ראינו איך אינדקס בנוי ומנוהל ולמה הוא טוב, חישוב גדלים, rebuild ועכשיו דיברנו גם על Clustering Factor.

אגב, למי שרוצה עוד להעמיק בעולם האינדקסים, יש בחור באורקל אוסטרליה שנקרא Richard Foote. אינדקסים זה סוג של תחביב אצלו, אז הנה כמה לינקים לאתר שלו:

עמוד הבית: http://richardfoote.wordpress.com/

לינק לעמוד מצגות: http://richardfoote.wordpress.com/articles-demos/

לינק למצגת על index internals (זהירות, מצגת מעמיקה מאוד, רק לאנשים שבאמת מוכנים לזה): http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth-ii.pdf

מקווה שנהניתם לקרוא, אשמח לתגובות, הערות והצעות.
לירון אמיצי.

ניתן ליצור קשר עם לירון דרך עמוד האודות שלו: אודות.

The following two tabs change content below.

• Bio
• Latest Posts

לירון אמיצי

ירון אמיצי הוא סמנכ"ל שירותי מומחה בחברת בריליקס ו-DBA בכיר בעל נסיון של למעלה מ- 15 שנים. ללירון תואר Oracle Ace ומתמחה בנושאי ביצועים, תשתיות, פתרונות זמינות גבוהה, גיבויים ושחזורים. ללירון יש גם בלוג עצמאי בכתובת: https://amitzil.wordpress.com

Latest posts by לירון אמיצי (see all)

• ה-SQL Performance Analyzer ב-Real Application Testing - 18/12/2014
• עד כמה דינאמי יכול להיות SQL דינאמי? - 02/10/2014
• מבוא ל- Real Application Testing - 23/07/2014