Re: Execution Plan Tuning - A cautionary tale

"Mark J. Bobak" <mark_at_bobak.net> wrote in message news:fe9b0e1b.0207190854.13a76b84_at_posting.google.com...
> Just remember to use the '/*+ IMAGINARY_INDEX */' hint next time! ;-)
>
> Yeah, I've done that before too, Niall. ;-) I'm sure we all have, at
some
> point in the past. Made me feel about as silly as the time I did:
> select to_char(sysdate,'HH:MM:SS') from dual;
> "Hey look, Oracle is broken! The minutes aren't changing!!" ;-)
>
> -Mark

huge grin! done something very similar myself.

Actually I got to checking what indexes were missing and why (this one was because id copied the table across from another system using CTAS across a dblink not exp/imp). The reason I *knew* it was there was this app doesn't use primary keys but all columns are defined as not null and all have a unique index named in a particular way. this was the primary key substitute so it likely was going astray in an upgrade (unlike the 12 I have created). It does however turn out that 11 such primary key substitutes have disappeared (only about a 2% failure rate) for no good reason. I feel a support ticket coming on. I also can't help but feel appropo another thread that were the db responsible for RI not the app this would be unlikely to have happened.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************