Add New Skills!

Udemy
Showing posts with label Oracle 11g Invisible Indexes. Show all posts
Showing posts with label Oracle 11g Invisible Indexes. Show all posts

Tuesday, November 29, 2011

Oracle 11g Invisible Indexes

What would you do when you want to test the effect of not using an index without actually dropping it? An invisible index is an alternative to making an index unusable. When you make an index invisible, you essentially hide it from the optimizer so that it is not used by any queries hitting the underlying table. Under the hood, invisible indexes are real, maintained by DML but cannot be used by the optimizer. Good way to test performance of queries without dropping the table right away. Alternatively, you could drop an index and do the same test. For large tables, dropping and creating indexes can be a time consuming overhead all of which can be avoided with invisible indexes.

SQL> CREATE INDEX IDX_EMPL_ID ON EMPLOYEES2(EMPLOYEE_ID);

Index created.

SQL> SELECT * FROM EMPLOYEES2
  2  WHERE EMPLOYEE_ID=108;

image

SQL> explain plan for SELECT * FROM EMPLOYEES2
  2  WHERE EMPLOYEE_ID=108;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4209916482

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    72 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES2  |     1 |    72 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMPL_ID |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - access("EMPLOYEE_ID"=108)

14 rows selected.

SQL> ALTER INDEX IDX_EMPL_ID INVISIBLE;

Index altered.

Once set to invisible, the optimizer is unaware and can’t use the index:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2513133951

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    72 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES2 |     1 |    72 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("EMPLOYEE_ID"=108)

13 rows selected.