בגרסת 11g לאינדקסים התווסף מאפיין חדש – Invisible, אשר באמצעותו ניתן להגדיר אותם כבלתי נראים.
Invisible Index הוא אינדקס רגיל לכל דבר (תחזוקה, איסוף סטטיסטיקות וכד') פרט לעניין אחד – באופן דיפולטיבי, האופטימייזר לא מתחשב בו בעת חישוב תוכנית הפעולה.
לאינדקס זה שימושים רבים, לדוגמא ניתן לבחון תוכניות פעולה ללא אינדקס מסויים מבלי למחוק אותו, לבדוק בסשן ספציפי השפעת אינדקס חדש בזמן ששאר המערכת לא מודעת לקיומו, "לכבות" באופן זמני אינדקס בעייתי, ועוד.
נייצר טבלה ונאכלס אותה בנתונים לפי הסינטקס הבא
RAM >> CREATE TABLE test_tbl (id NUMBER ); Table created. RAM >> RAM >> BEGIN 2 FOR i IN 1 .. 10000 LOOP 3 INSERT INTO test_tbl VALUES (i); 4 END LOOP; 5 COMMIT; 6 END; 7 / PL/SQL procedure successfully completed.
נייצר את ה Invisible Index על עמודת id
RAM >> CREATE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE; Index created.
ניתן לראות את מאפיין ה Invisibility באמצעות user_indexes
RAM >> RAM >> SELECT table_name , index_type, index_name, leaf_blocks, visibility 2 FROM user_indexes 3 WHERE index_name='TEST_TBL_IX'; TABLE_NAME INDEX_TYPE INDEX_NAME LEAF_BLOCKS VISIBILITY ---------- ---------- --------------- ----------- ---------- TEST_TBL NORMAL TEST_TBL_IX 21 INVISIBLE
ניתן לראות כי איסוף הסטטיסטיקות על האינדקס אפשרי
RAM >> RAM >> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_TBL', cascade=>> TRUE); PL/SQL procedure successfully completed. RAM >> RAM >> EXEC DBMS_STATS.gather_index_stats(USER, 'TEST_TBL_IX'); PL/SQL procedure successfully completed.
עם זאת, האופטימייזר לא יבחר להשתמש באינדקס כל עוד לא נציין לו בצורה מפורשת באמצעות הפרטמר OPTIMIZER_USE_INVISIBLE_INDEXES
RAM >> SET AUTOTRACE TRACEONLY EXPLAIN RAM >> RAM >> SELECT * FROM test_tbl WHERE id = 9999; Execution Plan ---------------------------------------------------------- Plan hash value: 602094504 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 4 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=9999) RAM >> RAM >> SET AUTOTRACE OFF RAM >> RAM >> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE RAM >> RAM >> SET AUTOTRACE TRACEONLY EXPLAIN RAM >> RAM >> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE; Session altered. RAM >> RAM >> SELECT * FROM test_tbl WHERE id = 9999; Execution Plan ---------------------------------------------------------- Plan hash value: 86670887 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TEST_TBL_IX | 1 | 4 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID"=9999) RAM >> RAM >> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE; Session altered. RAM >> RAM >> SELECT * FROM test_tbl WHERE id = 9999; Execution Plan ---------------------------------------------------------- Plan hash value: 602094504 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 4 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=9999)
באמצעות פקודות ALTER INDEX ניתן להחליט להעביר את האינדקס ממצב Invisible ל Visible ולהפך.
RAM >> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE; Session altered. RAM >> ALTER INDEX test_tbl_ix VISIBLE; Index altered. RAM >> RAM >> SELECT * FROM test_tbl WHERE id = 9999; Execution Plan ---------------------------------------------------------- Plan hash value: 86670887 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TEST_TBL_IX | 1 | 4 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID"=9999) RAM >> RAM >> ALTER INDEX test_tbl_ix INVISIBLE; Index altered. RAM >> RAM >> SELECT * FROM test_tbl WHERE id = 9999; Execution Plan ---------------------------------------------------------- Plan hash value: 602094504 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_TBL | 1 | 4 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=9999) RAM >>
מתי האופטימייזר יתייחס לאינדקס הבלתי נראה
למרות שהאופטימייזר לא משתמש באינדקס הבלתי נראה לחישוב תוכניות פעולה, במקרים מסויימים הוא כן משתמש בו עבור סטטיסטיקות לחישוב קרדינליות נכונה, Richard Foote מסביר ומדגים את הנושא בצורה מאוד יפה.
אינדקס בלתי נראה ופעולות DML
פרט לעניין הנראות, אינדקס בלתי נראה הוא אינדקס לכל דבר ופעולות DML משפיעות עליו ומושפעות ממנו, לדוגמא Invisible Unique Index עדיין ימנע הכנסה של נתונים לא יחודיים לעמודה
RAM >> CREATE UNIQUE INDEX test_tbl_ix ON test_tbl(id) INVISIBLE; Index created. RAM >> RAM >> INSERT INTO test_tbl VALUES (9999) ; INSERT INTO test_tbl VALUES (9999) * ERROR at line 1: ORA-00001: unique constraint (RAM.TEST_TBL_IX) violated RAM >> RAM >> DROP INDEX test_tbl_ix ; Index dropped. RAM >> RAM >> INSERT INTO test_tbl VALUES (9999) ; 1 row created. RAM >>
נכתב ע"י רם קדם
