ניתן להגדיר אינדקס כ Ascending או כ Descending, כאשר ב Ascending Index – האינדקס "הרגיל", המיון של הערכים בתוך האינדקס נעשה מהערך הקטן לערך הגדול, וב Descending Index המיון של הערכים נעשה מהערך הגדול לערך הקטן.
מתי זה משנה ? ובאילו מקרים נעדיף צורה אחת על פני האחרת ?
נערוך ניסוי קטן –
RAM >CREATE TABLE emps (employee_id number, last_name varchar2(40)) ; Table created. RAM >CREATE INDEX emp_empid_asc_ix ON emps(employee_id) ; Index created. RAM >CREATE INDEX emp_empid_desc_ix ON emps(employee_id desc) ; Index created. RAM >INSERT INTO emps 2 SELECT rownum, object_name 3 FROM all_objects ; 71672 rows created. RAM >COMMIT ; Commit complete.
ונבדוק את גדלי האינדקסים –
RAM >ANALYZE INDEX emp_empid_asc_ix VALIDATE STRUCTURE ; Index analyzed. RAM >SELECT lf_rows, lf_blks, pct_used FROM index_stats; LF_ROWS LF_BLKS PCT_USED ---------- ---------- ---------- 71672 143 99 RAM >ANALYZE INDEX emp_empid_desc_ix VALIDATE STRUCTURE ; Index analyzed. RAM >SELECT lf_rows, lf_blks, pct_used FROM index_stats; LF_ROWS LF_BLKS PCT_USED ---------- ---------- ---------- 71672 305 50
ניתן לראות בבירור כי ה Descending Index תופס פי 2 מקום וה PCT_USED שלו נמוך (בכל בלוק 50% הוא מקום ריק) – הסיבה לכך נובעת מה Splits שהאינדקס עובר כאשר הוא נבנה. בעוד ה Ascending עבר ספליטים של 90/10 בלבד מכיוון שערכיו נבנו בצורה סדרתית עולה ה Descending עבר ספליטים של 50/50 מכיוון שבנייתו נעשתה מלמעלה – למטה.
נערוך ניסוי נוסף :
RAM >DROP INDEX emp_empid_desc_ix ;
Index dropped.
RAM >SET AUTOTRACE TRACEONLY EXPLAIN
RAM >SELECT *
FROM emps
WHERE employee_id BETWEEN 200 and 700
ORDER BY employee_id ASC ;
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 502 | 14558 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPS | 502 | 14558 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMPID_ASC_IX | 502 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
RAM >SELECT *
FROM emps
WHERE employee_id BETWEEN 200 and 700
ORDER BY employee_id DESC ;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 502 | 14558 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPS | 502 | 14558 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| EMP_EMPID_ASC_IX | 502 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
ניתן לראות כי באמצעות הגישה ל Ascending Index ניתן לבצע מיון עולה (Ascending) וכן ניתן לבצע מיון יורד (Descending) , אותו דבר נכון גם ל Descending Index.
אז אם נרצה לסכם – עד כה ראינו כי ב Descending Index גדלים הסיכויים לפרגמנטציה ואנו לא באמת זקוקים לו כדי לבצע מיון בסדר יורד, אם כן מתי נרצה להשתמש בו ? הפתרון בהדגמה הבאה:
נייצר טבלה ונבנה עליה אינדקס המורכב מ 2 עמודות ( (Concatenated Index
HR >CREATE TABLE hr_test AS SELECT * FROM employees ; Table created. HR >CREATE INDEX hr_test_ix ON hr_test (employee_id , department_id) ; Index created.
כעת נריץ שאילתה אשר ממיינת את הטבלה לפי אותן שתי עמודות
HR >SELECT * 2 FROM hr_test 3 WHERE employee_id BETWEEN 190 AND 200 4 ORDER BY employee_id , department_id ; Execution Plan ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 11 | 1463 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| HR_TEST | 11 | 1463 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | HR_TEST_IX | 11 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
ניתן לראות כי נעשה שימוש באינדקס.
אך מה יקרה אם ננסה למיין את הטבלה בצורה הבאה :
HR >SELECT * 2 FROM hr_test 3 WHERE employee_id BETWEEN 190 AND 200 4 ORDER BY employee_id DESC , department_id ASC ; Execution Plan ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 1463 | 3 (34)| 00:00:01 | | 1 | SORT ORDER BY | | 11 | 1463 | 3 (34)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| HR_TEST | 11 | 1463 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | HR_TEST_IX | 11 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------
הפעם פעולת המיון לא הסתיימה בסריקת האינדקס אלא נאלצה לבצע פעולת מיון נוספת. במקרה מסוג זה אם נייצר Descending Index נוכל לחסוך את פעולת המיון :
HR >DROP INDEX hr_test_ix ; Index dropped. HR >CREATE INDEX hr_test_ix 2 ON hr_test (employee_id DESC , department_id ASC ) ; Index created. HR >SELECT * 2 FROM hr_test 3 WHERE employee_id BETWEEN 190 AND 200 4 ORDER BY employee_id DESC , department_id ASC; Execution Plan ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 11 | 1463 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| HR_TEST | 11 | 1463 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | HR_TEST_IX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
לאינדקסים תפקיד חשוב במניעת פעולות מיון, ובפוסט זה ראינו את מקומו של ה Descending Inde בתהליך. ראוי לציין כי לא כל פעולת מיון אמורה להיות מוחלפת באינדקס, ה PGA הוא מקומם הטבעי של פעולות המיון, Descending Index הוא צורת גישה אלטרנטיבית שאנו צריכים לבחון בעת הצורך.
למידע נוסף אודות ה Descending Index מומלץ בחום להכנס לפוסט הבא
