Invisible indexes by example

Preamble

There is a new interesting 11gR2 feature, not widely use in company where I work: invisible indexes.

I initially thought that the main added value was to test indexes creation but, even if written black on white in Official documentation, I missed the testing of index removal before doing it which is clearly more interesting in my opinion.

Invisible indexes does not mean no work for Oracle in the background. Keep in mind that even if invisible those type of indexes are maintained in the background by database engine like other types of indexes.

This post has been written using Red Hat Enterprise Linux Server release 5.5 (Tikanga) and Oracle 11.2.0.3.

Invisible indexes implementation

I’m going to take the example we have seen when testing Automatic SQL Tuning task post i.e.:

SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"  WHERE "MEMBER_ID_CREATED" =:1;

Where one of the recommendation was and index creation with:

CREATE INDEX DATALOAD.IDX$$_0001000D ON
DATALOAD.MN_HIST_TASK("MEMBER_ID_CREATED");

If I display the explain plan:

SQL> SET lines 200 pages 0
SQL> COLUMN plan_plus_exp ON format a110
SQL> EXPLAIN PLAN FOR
     SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"
     WHERE "MEMBER_ID_CREATED" = :1;
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display('plan_table',NULL,'all'));
PLAN hash VALUE: 1159508356
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | ROWS  | Bytes | COST (%CPU)| TIME     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    13 | 16465   (1)| 00:03:18 |
|   1 |  SORT AGGREGATE    |              |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| MN_HIST_TASK |     1 |    13 | 16465   (1)| 00:03:18 |
-----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / MN_HIST_TASK@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - filter("MEMBER_ID_CREATED"=TO_NUMBER(:1))
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(*)[22]
 
25 ROWS selected.

Which is exactly what we have seen in Automatic SQL Tuning task report.

If I perform two selects to see response time:

SQL> SET TIMING ON
SQL> SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"  WHERE "MEMBER_ID_CREATED" =1;
 
  COUNT(1)
----------
         0
 
Elapsed: 00:00:12.63
SQL> SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"  WHERE "MEMBER_ID_CREATED" =1611;
 
  COUNT(1)
----------
         0
 
Elapsed: 00:00:05.21

Now let’s create the index as suggested by Oracle but invisible:

SQL> CREATE INDEX DATALOAD.IDX$$_0001000D ON
DATALOAD.MN_HIST_TASK("MEMBER_ID_CREATED") invisible;
 
INDEX created.

If I re-display explain plan nothing has changed as you must change the default value of optimizer_use_invisible_indexes initialization parameter (it’s possible to change it at system level):

SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
 
SESSION altered.

Once optimizer parameter changed:

SQL> SET lines 200 pages 0
SQL> COLUMN plan_plus_exp ON format a110
SQL> EXPLAIN PLAN FOR
     SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"
     WHERE "MEMBER_ID_CREATED" = :1;
 
Explained.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display('plan_table',NULL,'all'));
PLAN hash VALUE: 3895781544
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | ROWS  | Bytes | COST (%CPU)| TIME     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX$$_0001000D |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   2 - SEL$1 / MN_HIST_TASK@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("MEMBER_ID_CREATED"=TO_NUMBER(:1))
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT(*)[22]
 
25 ROWS selected.

Now if I perform the same two selects again:

SQL> SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"  WHERE "MEMBER_ID_CREATED" =1;
 
  COUNT(1)
----------
         0
 
Elapsed: 00:00:00.00
SQL> SELECT /*+ all_rows */ COUNT(1) FROM "DATALOAD"."MN_HIST_TASK"  WHERE "MEMBER_ID_CREATED" =1611;
 
  COUNT(1)
----------
         0
 
Elapsed: 00:00:00.00

Remark:
Please note the above is true for all sessions that have changed value of optimizer_use_invisible_indexes, it is not only for your session !

If you are happy with the index you can make it “normal” with:

ALTER INDEX DATALOAD.IDX$$_0001000D visible;

That can be changed back again with:

ALTER INDEX DATALOAD.IDX$$_0001000D invisible;

But remember that an invisible index is maintained by Oracle so if you do not plan to use better to drop it:

DROP INDEX DATALOAD.IDX$$_0001000D;

About Post Author

This entry was posted in Oracle and tagged . Bookmark the permalink.

2 thoughts on “Invisible indexes by example

  1. Hi,

    Excellent article. My question, tho – why would you have to ALTER the index back to INVISIBLE after the final test?
    You nver made it VISIBLE in your example.
    Do you mean to say that the optimizer_use_invisible_indexes parameter needs to be set back to FALSE?

    Thanks for your help and time.

    • Hi,

      Thanks for nice comment ! You right, I mess up a bit at the end of the article. I have tried to correct it to better reflect what I meant, hope it is clear now…

      Thanks, Yannick.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>