ונתחיל בתשובה – זו לא אשמתו, זו אשמת הטבלה 🙂

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

Clustering Factor מעולה ישאף למספר הבלוקים בטבלה, Clustering Factor  זוועתי ישאף למספר השורות בטבלה.  

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

נסביר את הדיאגרמה, קיימות 2 טבלאות עם אותם נתונים – עמודה אחת עם הערכים 1 עד 9.  בטבלה העליונה (A) הנתונים מפוזרים לחלוטין בין הבלוקים השונים, בטבלה התחתונה (B) הנתונים מסודרים באופן רציף בין הבלוקים השונים.

כאשר אנו נרצה לגשת לכל אותם נתונים באמצעות האינדקס :

·         עבור הטבלה העליונה נצטרך לבצע 9 Block Switches (מספר 1 יושב בבלוק 1 – סוויץ' ראשון, מספר 2 יושב בבלוק 2 – סוויץ' שני, מספר 3 יושב בבלוק 3 – סוויץ' שלישי וכן הלאה…)

·         עבור הטבלה התחתונה נצטרך לבצע 3 Block Switches בלבד.

 

פחות פעולות Switch = פחות פעולות I/O = שאילתה מהירה יותר.

Clustering Factor

נייצר טבלה לפי הסינטקס הבא :

RAM >CREATE TABLE emps

  2  AS SELECT object_id AS employee_id ,

  3            object_name AS last_name ,

  4            status AS gender

  5  FROM all_objects

  6  ORDER BY object_name ;

 

Table created.

באמצעות השאילתות הבאות ניתן לראות מעט מידע אודותיה

RAM >SELECT COUNT(*) FROM emps ;

 

  COUNT(*)

———-

     71666

שימו לב כי מספרי העובדים לא מסודרים באף צורה …

RAM >SELECT * FROM emps WHERE rownum <= 10 ;

 

EMPLOYEE_ID LAST_NAME                      GENDER

———– —————————— ——-

      34042 /1000323d_DelegateInvocationHa VALID

      34043 /1000323d_DelegateInvocationHa VALID

      44844 /1000e8d1_LinkedHashMapValueIt VALID

      44845 /1000e8d1_LinkedHashMapValueIt VALID

     …

      19738 /10076b23_OraCustomDatumClosur VALID

      45461 /100c1606_StandardMidiFileRead VALID

   

10 rows selected.

נייצר אינדקס על העמודה המבולגנת ונאסוף סטטיסטיקות –

RAM >CREATE INDEX emps_id_ix ON emps (employee_id) ;

Index created.

RAM >EXEC dbms_stats.gather_table_stats ( USER , 'EMPS')

PL/SQL procedure successfully completed.

 

באמצעות טבלאות המערכת הבאות נראה את מספר הבלוקים ומספר השורות של הטבלה וכן את ה Clustering Factor של האינדקס

RAM >SELECT table_name, blocks, num_rows

  2  FROM user_tables

  3  WHERE table_name = 'EMPS';

TABLE_NAME                         BLOCKS   NUM_ROWS
—————————— ———- ———-
EMPS                                  401      71879

 

RAM >SELECT index_name, clustering_factor

  2  FROM user_indexes

  3  WHERE table_name = 'EMPS';

INDEX_NAME                     CLUSTERING_FACTOR
—————————— —————–
EMPS_ID_IX                                 35841

 

ניתן לראות כי ערך ה Clustering Factor  יכול להיות יותר גרוע  (לא שווה ל 71879) אבל גם לא הכי טוב (לא שווה ל 401)

שימו לב למספר פעולות ה I/O  (43179) כאשר נכריח את השאילתה הבאה להשתמש ב Index Range Scan –

 

RAM >SELECT /*+ INDEX(emps emps_id_ix)  */ *
FROM emps
WHERE employee_id >= 1  ;

 

Execution Plan

—————————————————————————————- 

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT            |            | 71666 |  2449K| 35717   (1)| 00:12:47 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       | 71666 |  2449K| 35717   (1)| 00:12:47 |

|*  2 |   INDEX RANGE SCAN          | EMPS_ID_IX | 71666 |       |   160   (0)| 00:00:04 |

——————————————————————————————

…

Statistics

———————————————————-

          1  recursive calls

          0  db block gets

       43179consistent gets

Fixing the Clustering Factor

שימו לב לצורה בה אנו עושים את התיקון – בנייה מחדש לטבלה כאשר שורותיה מסודרות לפי העמודה המאונדקסת

RAM >CREATE TABLE new_emps

  2  AS

  3  SELECT * FROM emps

  4  ORDER BY employee_id ;

Table created.

RAM >DROP TABLE emps ;

Table dropped.

RAM >ALTER TABLE new_emps RENAME TO emps ;

Table altered.

 

RAM >CREATE INDEX emps_id_ix ON emps (employee_id) ;

Index created.

 

RAM >EXEC dbms_stats.gather_table_stats ( USER , 'EMPS')

PL/SQL procedure successfully completed.

 

RAM >SELECT table_name, blocks, num_rows

  2  FROM user_tables

  3  WHERE table_name = 'EMPS';

 

TABLE_NAME                         BLOCKS   NUM_ROWS

—————————— ———- ———-
EMPS                                  401      71879

 

RAM >SELECT index_name, clustering_factor

  2  FROM user_indexes

  3  WHERE table_name = 'EMPS';

 

INDEX_NAME                     CLUSTERING_FACTOR
—————————— —————–

EMPS_ID_IX                                   401

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

שימו לב כיצד ממספר פעולות ה I/O  (10105) יורד באופן משמעותי כאשר נריץ שוב את אותה שאילתה מהדוגמא הקודמת

RAM >SELECT /*+ INDEX(emps emps_id_ix)  */ *
FROM emps
WHERE employee_id >= 1  ;

 

Execution Plan

—————————————————————————————- 

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

——————————————————————————————

|   0 | SELECT STATEMENT            |            | 71666 |  2449K| 35717   (1)| 00:12:47 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       | 71666 |  2449K| 35717   (1)| 00:12:47 |

|*  2 |   INDEX RANGE SCAN          | EMPS_ID_IX | 71666 |       |   160   (0)| 00:00:04 |

——————————————————————————————

…

Statistics

———————————————————-

          1  recursive calls

          0  db block gets

       10105 consistent gets

Clustering Factor & table with more than one index

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

נייצר טבלה עם שתי עמודות – מספר עובד ושכר, נדאג כי הערכים בעמודת מספר העובד יהיו מסודרים והערכים בעמודת שכר יהיו רנדומליים

 

RAM >Create table cf_tab (empno number(10), sal number(10));

Table created.

 

RAM >Begin

  2  For i in 1..100000

  3  Loop

  4     Insert into cf_tab

  5     values(i, ROUND(dbms_random.value(1000,10000)));

  6  End loop;

  7  commit;

  8  End;

  9  /

 

PL/SQL procedure successfully completed.

 

RAM >SELECT * FROM cf_tab

  2  WHERE rownum < 11;

 

     EMPNO        SAL

———- ———-

         1       4156

         2       6283

         3       7368

         4       1428

         5       2879

         6       5430

         7       5023

         8       3288

         9       8958

        10       3330

 

10 rows selected.

נייצר אינדקס על כל אחת מהעמודות ונאסוף סטטיסטיקות –

RAM >CREATE INDEX cf_empno ON cf_tab(empno);

Index created.

 

RAM >CREATE INDEX cf_sal ON cf_tab(sal);

Index created.

 

RAM >exec dbms_stats.gather_table_stats(USER,'CF_TAB',CASCADE => TRUE)

PL/SQL procedure successfully completed.

 

כעת שימו לב ל Clustering Factor – עבור האינדקס של עמודת מס' עובד ה CF אופטימלי, עבור האינדקס של  עמודת שכר ה CF רחוק מלהיות אופטימלי

 

RAM >SELECT table_name, blocks, num_rows

  2  FROM user_tables

  3  WHERE table_name = 'CF_TAB';

 

TABLE_NAME                         BLOCKS   NUM_ROWS

—————————— ———- ———-

CF_TAB                                192     100000

 

RAM >SELECT index_name, clustering_factor

  2  FROM user_indexes

  3  WHERE table_name = 'CF_TAB';

 

INDEX_NAME                     CLUSTERING_FACTOR

—————————— —————–

CF_SAL                                     97191

CF_EMPNO                                     192

 

נתקן את ה Clustering Factor של האינדקס על עמודת שכר, שימו לב מה יקרה ל CF של האינדקס על עמודת מספר עובד.

RAM >CREATE TABLE new_cf_tab

  2  AS

  3  SELECT * FROM cf_tab

  4  ORDER BY sal;

 

Table created.

 

RAM >DROP TABLE cf_tab ;

 

Table dropped.

 

RAM >ALTER TABLE new_cf_tab RENAME TO cf_tab ;

 

Table altered.

 

RAM >SELECT * FROM cf_tab

  2  WHERE rownum < 11;

 

     EMPNO        SAL

———- ———-

      6376       1000

      6606       1000

     34733       1000

     38237       1000

     40991       1000

     58603       1000

     60461       1000

     62141       1000

     67444       1000

     72557       1000

 

10 rows selected.

 

RAM >CREATE INDEX cf_empno ON cf_tab(empno);

 

Index created.

 

RAM >CREATE INDEX cf_sal ON cf_tab(sal);

 

Index created.

 

RAM >exec dbms_stats.gather_table_stats(USER,'CF_TAB',CASCADE => TRUE)

 

PL/SQL procedure successfully completed.

 

RAM >SELECT table_name, blocks, num_rows

  2  FROM user_tables

  3  WHERE table_name = 'CF_TAB';

 

TABLE_NAME                         BLOCKS   NUM_ROWS

—————————— ———- ———-

CF_TAB                                192     100000

 

RAM >SELECT index_name, clustering_factor

  2  FROM user_indexes

  3  WHERE table_name = 'CF_TAB';

 

INDEX_NAME                     CLUSTERING_FACTOR

—————————— —————–

CF_SAL                                       192

CF_EMPNO                                   99456

 

מתי ה Clustering Factor  רלוונטי

המקרה העיקרי בו ה Clustering Factor  רלוונטי הוא כאשר אנו עושים Index Range Scan, במקרים אחרים ה Clustering Factor  לא ממש משנה, לדוגמא :  

·         כאשר אנו עושים Full Table Scan – אין מלכתחילה גישה לאינדקס

·         כאשר אנו עושים Index Unique Scan – הגישה היא לערך בודד ואז הנושא של ה Block Switches פחות רלוונטי

·         כאשר אנו עושים Index Full Scan הגישה היא לאינדקס בלבד

 

לכן זה בסדר שה Clustering Factor  של כל האינדקסים על הטבלה לא יהיה אופטימלי כל עוד שה CF על האינדקס העיקרי שבו אנו משתמשים לסריקות יהיה תקין.

נכתב ע"י רם קדם