Re: Oracle 11g - Invisible Indexes

From: Robert Freeman <robertgfreeman_at_yahoo.com>
Date: Mon, 20 Aug 2007 12:09:30 -0700 (PDT)
Message-ID: <382029.16249.qm@web38909.mail.mud.yahoo.com>

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.

Automatic index tuning, perhaps a new feature of Oracle 11gR2. It just seems a natural outgrowth of automatic SQL tuning in 11g.

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:09:30 CDT