Re: Oracle 11g - Invisible Indexes

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