במקרים מסויימים הלוגיקה של משפט ה WHERE מונעת את השימוש באינדקס, זוהי מלכודת נפוצה אשר מפתחים רבים נופלים בה. חשוב להכיר את מקרים אלו כדי לדעת כיצד לתכנן את הקוד בצורה יעילה יותר.
בפוסט זה נכיר את הסיטואציות העיקריות בהן אינדקסים לא יהיו שמישים ונראה כיצד ניתן להתגבר על חלקן.
NOT EQUAL Operators ( <> , =! )
אינדקסים לא יהיו שמישים כאשר ננסה לאתר מידע בטבלאות אשר לא שווה לערך מסויים.
RAM >SELECT * FROM hr.employees WHERE employee_id = 100 ; Execution Plan ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 73 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 73 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- RAM >SELECT * FROM hr.employees WHERE employee_id <> 100 ; Execution Plan ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 106 | 7738 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 106 | 7738 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------
נקשה ונאמר – יכול להיות שהאופרטור <> הניב במקרה האחרון Full Table Scan מכיוון שכמעט כל השורות חזרו, ולכן היה יותר הגיוני להשתמש ב Access Path הזה ולא ב Index Unique Scan .
כדי לענות על שאלה זו נייצר טבלה נוספת אשר מורכבת מ 108 עובדים – עבור 107 העובדים הראשונים מספר העובד שווה ל 1, עבור העובד ה 108 מספר העובד שווה ל 100
CREATE TABLE new_emps
AS SELECT 1 AS ID , last_name , first_name , salary
FROM hr.employees ;
INSERT INTO new_emps
SELECT employee_id , last_name , first_name , salary
FROM hr.employees
WHERE employee_id = 100 ;
RAM >SELECT id , COUNT(*) AS "HOW_MANY"
2 FROM new_emps
3 GROUP BY id;
ID HOW_MANY
---------- ----------
1 107
100 1
CREATE INDEX new_emps_ix ON new_emps(id) ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER , 'NEW_EMPS') ;
שוב, נבקש את כל הנתונים – פעם אחת עבור עובד מס' 100 (שורה אחת) ופעם שניה עבור כל אלה שמספרם אינו שווה ל 1 (שורה אחת גם כן). שימו לב לתוכניות הפעולה :
RAM >SELECT * FROM new_emps WHERE id = 100 ; Execution Plan ------------------------------------------------------------------------------------------ Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| NEW_EMPS | 1 | 21 | 2 (0)| 00:00:01 | * 2 | INDEX RANGE SCAN | NEW_EMPS_IX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ RAM >SELECT * FROM new_emps WHERE id <> 1 ; Execution Plan ----------------------------------------------------------------------------- Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 | * 1 | TABLE ACCESS FULL| NEW_EMPS | 1 | 21 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------
שימו לב כי ב Full Index Scan וב Fast Full Index Scan האופרטורים <>, =! עדיין תופסים.
RAM >SELECT id FROM new_emps WHERE id <> 1 ; Execution Plan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX FULL SCAN | NEW_EMPS_IX | 1 | 3 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------
Using IS NULL Operator
מלכודת נוספת היא השימוש באופרטור IS NULL במשפט ה WHERE – מכיוון שערכי Null לא מאונדקסים (BTREE), השימוש בפקודה זו יניב תמיד Full Table Scan.
נייצר טבלה עם עמודה אחת אשר תכיל ערכי Null, ונייצר על אותה עמודה אינדקס –
HR >CREATE TABLE emp_test 2 AS SELECT employee_id , last_name , salary , 3 commission_pct AS btree_commission 4 FROM employees ; Table created. HR >CREATE INDEX emp_com_btree ON emp_test (btree_commission); Index created. HR >EXEC dbms_stats.gather_table_stats( USER , 'EMP_TEST', CASCADE => TRUE ) PL/SQL procedure successfully completed.
פעם ראשונה ננסה לגשת לאינדקס באמצעות IS NOT NULL (ניתן לגשת)
HR >SELECT * FROM emp_test WHERE btree_commission IS NOT NULL ; Execution Plan --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 35 | 595 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 35 | 595 | 2 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN | EMP_COM_BTREE | 35 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
פעם שניה ננסה לגשת לאינדקס באמצעות IS NULL (לא ניתן לגשת)
נעשה שימוש ב Hint כדי להוכיח שגם אם ננסה "בכח" להכריח אותו להשתמש באינדקס הוא לא ישתמש
HR >SELECT /*+ INDEX(emp_test(btree_commission)) */ 2 * FROM emp_test WHERE btree_commission IS NULL ; Execution Plan ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 72 | 1224 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP_TEST | 72 | 1224 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------
פתרון אפשרי הוא שימוש ב Function Based Index
HR >CREATE INDEX emp_com_btree ON emp_test (btree_commission , 0); Index created. HR >SELECT /*+ INDEX(emp_test(btree_commission)) */ 2 * FROM emp_test WHERE btree_commission IS NULL ; Execution Plan --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72 | 1224 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 72 | 1224 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMP_COM_BTREE | 72 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
שימוש בפונקציות
כאשר בתנאי ה WHERE אנו מבצעים סינון עפ"י ערך החוזר כתוצאה מפונקציה על עמודה מאונדקסת – לא יהיה שימוש באינדקס.
HR >CREATE TABLE hr.emps AS SELECT * FROM hr.employees ; Table created. HR >CREATE INDEX hr.emps_lname ON hr.emps (last_name) ; Index created.
פעם ראשונה אנו עושים חיפוש עפ"י ה last_name – החיפוש ישתמש באינדקס
HR >SELECT * FROM hr.emps WHERE last_name = 'King'; Execution Plan ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 266 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPS | 2 | 266 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMPS_LNAME | 2 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
ניתן לפתור זאת בשני דרכים
דרך ראשונה – להחיל את הפונקציה על הערך להשוואה ולא על העמודה עצמה
HR >SELECT * FROM hr.emps WHERE last_name = INITCAP('KING') ;
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 266 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPS | 2 | 266 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPS_LNAME | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
דרך שניה – לייצר Function Based Index
HR >DROP INDEX hr.emps_lname ; Index dropped. HR >CREATE INDEX hr.emps_lname ON hr.emps (UPPER(last_name)) ; Index created.
בדרך זו חיפושים על הערך החוזר כתוצאה מהשימוש בפונקציה יוכלו להשתמש באינדקס
HR >SELECT * FROM hr.emps WHERE UPPER(last_name) = 'KING'; Execution Plan ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 147 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPS | 1 | 147 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMPS_LNAME | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
אך כעת חיפושים על הערך עצמו יניבו Full Table Scan …
HR >SELECT * FROM hr.emps WHERE last_name = 'King'; Execution Plan -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 266 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPS | 2 | 266 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
