Sunday, August 12, 2007

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]

5 comments:

Gary Myers said...

Looks like there is an OPTIMIZER_USE_INVISIBLE_INDEXES parameter that defaults to false but when true "Invisible indexes are treated as visible (normal) indexes."
I suppose that corresponds with using a parameter to ignore/fail on unusable indexes.

Robert Freeman said...

Indeed, that is the parameter!

yas said...

I had a post about this too. You can check that post about using invisible indexes and gathering stats on them.

http://oracletoday.blogspot.com/2007/08/invisible-indexes-in-11g.html

Hemant K Chitale said...

Yuri van Buren shows a Hint (and not optimizer_use_invisible_indexes) being used at
http://www.bloggingaboutoracle.org/archives/blogging-about-11g-part-4-invisible-indexes

Robert Freeman said...

I suspect that this was done on the beta code, which did support hints. Hints do not work in production 11g for invisible indexes.

 
Subscribe in a reader