Re: Can index be dropped and created automatically by the server?

"jameswan" <jameswan_at_email.msn.com> wrote in message news:uMvRqyuRBHA.1264_at_cpimsnntpa03...
> We have Oracle 8.0.4 server running on HP-UX. Every week, a cron job is
> running to update the database doing some updating, inserting tasks. The
> default optimization is rule-based optimization. Every Sunday, a cron job
is
> run with DBMS_Util.analyze("schema_name", "compute"). However, from week
> to week, the explain plan shows different path, and the index may be
created
> or dropped for the table. The performance of course varies from week to
> week. Because we do not know if some body has deliberately done this or
> not, I wonder if some body can confirm that the index could be
automatically
> created or dropped by the system.
>
> It is really troublesome, your help is appreciated.
>
> Thanks.
>
> -James W.
>
>
>
>

Unless you believe in ghosts, myths and fairy tales this is *NOT* possible. You or someone else must have setup code to do so. How do you know you are rule based optimization. If you are truly using RBO (by setting optimizer_mode in init.ora) analyzing the schema will not have *any* effect.

Oracle 8.0.4 has been desupported a long time ago, Oracle 8.0 will be desupported with 8.0.6 on September 30.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Sep 27 2001 - 14:15:08 CDT