Re: Oracle 11g - Invisible Indexes

Another issue with the NOSEGMENT indexes is that you cannot try the query: the index is not there so you just see that CBO will use it in an explain plan, but you're not sure that the expected performance gains are real.

rgds

On 8/21/07, Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl> wrote:
>
> At least the invisible index will consume all the resources needed to
> maintain it, which the NOSEGMENT index won't do.
> That might look as a waste of time, OTOH, it also allows to measure its
> resource consumption connected to all the DML, without hampering execution
> plans for important queries.
>
> Whatever, it seems some maturation has to be waited for.
> It's sounds like a solution, looking for a problem to solve;-)
>
> Best regards,
>
> Carel-Jan Engel
>
> ===
> If you think education is expensive, try ignorance. (Derek Bok)
> ===
>
> On Mon, 2007-08-20 at 19:33 -0500, K Gopalakrishnan wrote:
>
> Robert,
> I got your point. However this same feature is already available inthe form of NOSEGMENT indexes where you can create an index (withNOSEGMENT) and try the index using a little known undocumentedparameter (_use_nosegment_indexes=true) at session level. We do thatall the time before trying an index.
> I just see this as an extension of nosegment indexes..
> -Gopal
> On 8/20/07, Robert Freeman <robertgfreeman_at_yahoo.com> wrote:> 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.
>
>
>
>

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