Oracle 11g is released and Invisible Indexes!!

I'm a bit tardy with a blog entry about this since it happened last week, but Oracle Database 11g 11.1.0.6 for Linux has been released!! I've got it up and running on my Linux VMWare machine right now!

For those of you interested in yet another new feature, how about invisible indexes? No, invisible indexes are not a character in a Wes Craven slasher picture, but rather a way to kind of turn on/off an index from the point of view of the optimizer. For example, we have a table called TEST with an index called ix_test. Look at how the execution plan changes if I make the ix_test index invisible:

-- first the index is visible
SQL> alter index ix_test visible;
Index altered.

SQL> select * from test where id=1;

ID
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 416078300

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_TEST | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

-- Now, make it invisible
SQL> alter index ix_test invisible;

Index altered.

SQL> select * from test where id=1;

ID
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 24 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 24 (5)| 00:00:01 |
--------------------------------------------------------------------------

One thing I noticed on the production cut is that you can not use a hint and have the index be used. So something like this does not seem to work:

select /*+ index(test ix_test) */ * from test where id=1;

The doc's I've seen so far seem to indicate this should work, but it might warrant some additional investigation before I call it a bug.

[Edited addition]
In looking at the production documentation now, all references to hints making the invisible index accessible by the optimizer are gone. So, it appears that hints, indeed, do not impact the optimizers ability to see an invisible index.

[/edit]