Re: Oracle 11g - Invisible Indexes

On 8/12/07, Robert Freeman <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