ונתחיל בתשובה – זו לא אשמתו, זו אשמת הטבלה 🙂
באופן עקרוני – ה 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 על האינדקס העיקרי שבו אנו משתמשים לסריקות יהיה תקין.
נכתב ע"י רם קדם