RE: Oracle 11g - Invisible Indexes

Yes, but the _use_nosegment_indexes parameter is not supported, of course, and the clincher is that it's not dynamic. I'm suggesting that at some point, we might have Oracle dynamically creating indexes for us in the background, and if we didn't look, we would never know it happened, ALA automatic SQL tuning in 11g where we get profiles dynamically created on the fly. I'm suggesting that this is the first step towards that end.

Cheers!!

RF

Robert G. Freeman
Oracle Consultant/DBA/Author
Principal Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press)
Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com! BLOG: http://robertgfreeman.blogspot.com/ Sig V1.2

-----Original Message-----
From: K Gopalakrishnan [mailto:kaygopal_at_gmail.com] Sent: Monday, August 20, 2007 6:33 PM
To: robertgfreeman_at_yahoo.com
Cc: sfaroult_at_roughsea.com; jkstill_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Oracle 11g - Invisible Indexes

Robert,

I got your point. However this same feature is already available in the form of NOSEGMENT indexes where you can create an index (with NOSEGMENT) and try the index using a little known undocumented parameter (_use_nosegment_indexes=true) at session level. We do that all the time before trying an index.

I just see this as an extension of nosegment indexes..

-Gopal

On 8/20/07, Robert Freeman <robertgfreeman_at_yahoo.com> wrote:
> I rather suspect where Oracle is going with the
> invisible index is associated with the automated SQL
> tuning. Say, for example, Oracle reviews SQL that has
> run and determines that an index would benefit the
> execution plan. Why not create it as an invisible
> index (thus, you don't impact other execution plans),
> test it and quantify the performance and then, if you
> get say 3 or 4x performance, you make it visible.

--
Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

Author: Oracle Database 10g RAC Handbook, Oracle Press 2006
http://www.amazon.com/gp/product/007146509X/

--
http://www.freelists.org/webpage/oracle-l