החל מגרסת 8i ניתן לייצר אינדקסים אשר מבוססים על פונקציה. באמצעותם ניתן לגשת לערכים בעמודה כאשר בתנאי החיפוש מוגדר פונקציה, זהו השימוש הבסיסי והמוכר ביותר.
בפוסט זה נראה כיצד בפועל מיושם ה Function Based Index ובנוסף נכיר שתי טכניקות מתקדמות בעבודה מול אינדקסים מסוג זה.
Function Based Index – Basic Usage
נתחיל בהסבר השימוש הבסיסי – בדוגמא הבאה ננסה לשלוף את שמות הלקוחות עבור אלו אשר שמם מתחיל באות "L", נשתמש בפונקצית ה SUBSTR על מנת לבצע את הסינון.
בפעם הראשונה שנריץ את השאילתה – האופטימייזר ישתמש ב Full Table Scan כי פשוט לא קיים אף אינדקס על העמודה.
נייצר אינדקס על העמודה ונריץ בפעם השניה את השאילתה – האופטימייזר עדיין ישתמש ב Full Table Scan מכיוון שאינדקסים רגילים לא יכולים לשרת חיפוש עפ”י פונקציה.
נייצר אינדקס אשר מבוסס על הפונקציה בתנאי החיפוש ונריץ בפעם השלישית את השאילה – הפעם האופטימייזר ישתמש באינדקס שנוצר, שימו לב ל Cost שירד בצורה ניכרת.
SH > SET AUTOTRACE TRACEONLY EXPLAIN SH > SH > SELECT cust_first_name FROM customers WHERE substr(cust_last_name,1,1) = 'L'; Execution Plan ---------------------------------------------------------- Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 555 | 8325 | 407 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 555 | 8325 | 407 (1)| 00:00:05 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SUBSTR("CUST_LAST_NAME",1,1)='L') SH > SH > --Instead of regular btree index SH > SH > CREATE INDEX ix_customers_cust_ln ON customers(cust_last_name); Index created. SH > SH > -- Same execution plan .. SH > SELECT cust_first_name FROM customers WHERE substr(cust_last_name,1,1) = 'L'; Execution Plan ---------------------------------------------------------- Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 555 | 8325 | 407 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 555 | 8325 | 407 (1)| 00:00:05 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SUBSTR("CUST_LAST_NAME",1,1)='L') SH > SH > SH > -- Consider using a function based index SH > SH > CREATE INDEX fbi_customers_cust_ln_init ON customers (SUBSTR(cust_last_name,1,1)); Index created. SH > SH > -- Different and less expensive execution plan .. SH > SELECT cust_first_name FROM customers WHERE substr(cust_last_name,1,1) = 'L'; Execution Plan ---------------------------------------------------------- Plan hash value: 2038893127 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 555 | 4995 | 32 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 555 | 4995 | 32 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | FBI_CUSTOMERS_CUST_LN_INIT | 222 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(SUBSTR("CUST_LAST_NAME",1,1)='L') SH >
Index Specific Values
ניתן להשתמש ב Function Based Index כדי לאנדקס רק חלק מהערכים בעמודה.
לדוגמא – בטבלת לקוחות קיימים שני סוגי לקוחות : "A" ו – "I", כאשר הלקוחות סוג הלקוחות "A" מהווה כחמישית מהטבלה :
SH > SELECT DISTINCT cust_valid FROM sh.customers ;
C
-
I
A
SH >
SH > SELECT COUNT(*) FROM customers ;
COUNT(*)
----------
55500
SH >
SH > SELECT COUNT(*) FROM customers WHERE cust_valid = 'A' ;
COUNT(*)
----------
10621
ניתן להשתמש בפקודת Case פשוטה כדי לייצר עמודה המחזירה חווי על סוג הלקוח – ללקוח "A" היא תחזיר "A" ללקוח "I" היא תחזיר NULL
SH > SH > SELECT cust_valid, 2 CASE cust_valid WHEN 'A' THEN 'A' 3 ELSE NULL 4 END 5 FROM customers 6 WHERE rownum <= 10 ; C C - - I A A I I I A A I A A I I 10 rows selected.
כדי לאנדקס רק את הלקוחות מסוג "A" ניתן להשתמש באותה פקודת Case כחלק מה Function Based Index – כאשר קיים לקוח מסוג "A" יאונדקס "A", כאשר קיים לקוח מסוג אחר לא יאונדקס כלום (BTEEE לא מאנדקסים NULL).
כדי שאינדקס זה יפעל יש צורך להשתמש בתנאי החיפוש בכל הפונקציה שנכתבה, אם ננסה לסנן עפ"י WHERE cust_valid = 'A' לא יהיה שימוש באינדקס.
SH > SH > CREATE INDEX cust_valid_idx 2 ON customers ( CASE cust_valid WHEN 'A' THEN 'A' ELSE NULL END ); Index created. SH > SH > SET AUTOTRACE TRACEONLY EXPLAIN SH > SH > SELECT * FROM customers WHERE cust_valid = 'A' ; Execution Plan ---------------------------------------------------------- Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27750 | 4905K| 406 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 27750 | 4905K| 406 (1)| 00:00:05 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CUST_VALID"='A') SH > SH > SELECT * FROM customers WHERE CASE cust_valid WHEN 'A' THEN 'A' ELSE NULL END = 'A' ; Execution Plan ---------------------------------------------------------- Plan hash value: 2540774236 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 555 | 98K| 51 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 555 | 98K| 51 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | CUST_VALID_IDX | 222 | | 20 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "CUST_VALID" WHEN 'A' THEN 'A' ELSE NULL END ='A')
כדי שלא נצטרך לכתוב את השאילה כל פעם מחדש ניתן להכניס אותה לתוך View
SH > SH > CREATE VIEW customers_a 2 AS 3 SELECT * 4 FROM customers 5 WHERE CASE cust_valid WHEN 'A' THEN 'A' ELSE NULL END = 'A' ; View created. SH > SET AUTOTRACE TRACEONLY EXPLAIN SH > SH > SELECT * FROM customers_a ; Execution Plan ---------------------------------------------------------- Plan hash value: 2540774236 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 555 | 98K| 51 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 555 | 98K| 51 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | CUST_VALID_IDX | 222 | | 20 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "CUST_VALID" WHEN 'A' THEN 'A' ELSE NULL END ='A')
Enforcing data integrity
נייצר טבלה עבור סוגי בגדים שונים.
RAM > CREATE TABLE clothes 2 (category varchar2(25), 3 subcategory number(3)) ; Table created.
הבגדים בטבלה זו נחלקים לקטגוריה ולתת קטגוריה, לדוגמא קטגורית BOOTS עם תתי הקטגוריות : 1,2,3,4,5.
אנו רוצים לאפשר לקטגוריה מסויימת להכיל פעמיים את אותה תת קטגוריה, לדוגמא קטגורית TSHIRTS עם תתי קטגוריות : 1,2,2,4,5 כל עוד הקטגוריה היא לא JEANS, בקטגוריה זו אסור לייצר שתי תתי קטגוריות מאותו מספר .
כדי ליישם זאת נשתמש ב Unique Function Based Index הבא, שימו לב לתוצאות פעולת ה Insert על הטבלה.
RAM >
RAM > CREATE UNIQUE INDEX test_tbl_fbi ON clothes (CASE WHEN category='JEANS' THEN subcategory ELSE NULL END) ;
Index created.
RAM >
RAM >
RAM > INSERT INTO clothes VALUES ('TSHIRTS' , 1) ;
1 row created.
RAM > INSERT INTO clothes VALUES ('TSHIRTS' , 2) ;
1 row created.
RAM > INSERT INTO clothes VALUES ('TSHIRTS' , 1) ;
1 row created.
RAM >
RAM > INSERT INTO clothes VALUES ('JEANS' , 1) ;
1 row created.
RAM > INSERT INTO clothes VALUES ('JEANS' , 2) ;
1 row created.
RAM > INSERT INTO clothes VALUES ('JEANS' , 1) ;
INSERT INTO clothes VALUES ('JEANS' , 1)
*
ERROR at line 1:
ORA-00001: unique constraint (RAM.TEST_TBL_FBI) violated
RAM >
