RE: Oracle 11g - Invisible Indexes
- From: "Robert Freeman" <robertgfreeman@xxxxxxxxx>
- To: "K Gopalakrishnan" <kaygopal@xxxxxxxxx>
- Date: Mon, 20 Aug 2007 22:53:46 -0600
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@xxxxxxxxx]
Sent: Monday, August 20, 2007 6:33 PM
To: robertgfreeman@xxxxxxxxx
Cc: sfaroult@xxxxxxxxxxxx; jkstill@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
