Re: Oracle 11g - Invisible Indexes

Again, it seems quite easy to blame things on Optimizer bugs (and I will be the first to admit they do exist), etc but I think there is much more to this feature than that. To be overly critical of the Optimizer code, is to underestimate what it brings to the table. Don't take my word for it, ask any ISV that deals with multiple database vendors.

If you are a DBA and want to try to test an index in production, this is a prefect way to do so - it limits the risk exposure. This logic has also been applied to gathering stats in 11g. Gathering and publishing are now able to be done as individual tasks, thus again limiting the risk exposure. Same applies for SQL Plan Management. One has to be mindful it is nearly impossible to test code for every possible use case and features like this allow the user to take advantage of new code and as well as reduce the exposure to risk.

On 8/20/07, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
> This is the kind of feature where I really feel that Oracle marketing is
> doing a great job. Since indexes are maintained (with the associated
> cost) the only "benefit" is not seeing them used by the CBO. But why ,
> in the first place, would the CBO want to use them when, presumably,
> they hamper rather than help the query? Might it be (*gasp*) because of
> CBO bugs? Cough, cough. So much easier to pile up new features instead
> of fixing what exists ...
> That's shifting the work to the customer.
>
> Stephane Faroult
>
>
> Jared Still wrote:
> > On 8/12/07, *Robert Freeman* <robertgfreeman_at_yahoo.com
> > <mailto:robertgfreeman_at_yahoo.com>> wrote:
> >
> > On my blog now, 11g New Feature, Invisible Indexes.
> >
> > http://robertgfreeman.blogspot.com/
> >
> > Enjoy!!
> >
> >
> > Thinking of possible uses for this:
> >
> > make index visible
> > run long running job that needs this index
> > make index invisible.
> >
> > The assumption in this case is that the index is not desired for
> > normal activity.
> >
> > Still updated, but not considered by the CBO.
> >
> > That makes me wonder about the following however:
> >
> > long running job starts.
> > OLTP SQL ages out of cache
> > Someone/something runs the OLTP query while the index is visible.
> > Since the SQL had aged out of cache, it is re-costed, and future
> > executions get the plan with the index.
> >
> > Does making the index invisible invalidate current SQL with plans
> > using the index?
> >
> > --
> > Jared Still
> > Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l