Ascending או Descending אינדקס, זה באמת משנה .. ?

ניתן להגדיר אינדקס כ 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 מומלץ בחום להכנס לפוסט הבא

פורסם בקטגוריה Tuning, עם התגים , , . אפשר להגיע לכאן עם קישור ישיר.