Re: Oracle 11g - Invisible Indexes

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Mon, 20 Aug 2007 12:55:39 -0700 (PDT)
Message-ID: <290430.34306.qm@web38912.mail.mud.yahoo.com>

What I want to know is what good is an invisible index if you can no longer hint that it should be used? In my mind the benefit would be creation of the index, testing of a query to determine it's effect on that query while not impacting other queries.

Now that I can't force the use of an invisible index with a hint, what good is the feature?

RF
--- 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
>
>
>

Robert G. Freeman
Author:
Now Available for Pre-Sales on Amazon.com!!!!

Oracle Database 11g New Features (Oracle Press)

Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
Blog: http://robertgfreeman.blogspot.com (Oracle Press)

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

Received on Mon Aug 20 2007 - 14:55:39 CDT