Re: very poor SYS view performance (8i) / fragmented data dictionary

gg.3.ukuechle_at_spamgourmet.com (Uwe Kuechler) wrote in message news:<beab228e.0312100801.7f069977_at_posting.google.com>...
> "Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message news:<3fd6f6f7$0$1025$afc38c87_at_news.optusnet.com.au>...
> >
> > Queries on a fragmented dictionary tend to
> > produce some weird plans. I'd suggest you
> > help the optimiser a bit by re-working your
> > query to depend less on ALL_CONSTRAINTS.
> >
>
> Noons,
> Thanks for your suggestions, but as I stated earlier, the said queries
> are generated by third-party software. Modification of those queries
> is therefore unfortunately NOT an option.
> What do you exactly mean with "fragmented dictionary"? Physically
> fragmented? I already thought about re-creating the whole DB, but is
> this really the only option I've got? Can be quite annoying with a
> terabyte-size DB...
>
> > select
> > to_char(null), "what the heck is this for?"
>
> Nice sense of humour ;-)
> Well, that's what you often get to see when tools generate their SQL
> dynamically.
>
> Regards,
> Uwe

I have had to code a to_char or to_number on a column that was returning null values in views with unions to get Oracle to properly type the null to the column datatype otherwise Oracle spit out an error. If this select is part of a union then the vendor may be doing the same thing. In embedded code it is possible that the vendor just wanted a character place holder for use with some generic code routines that takes a certain number of parameters or to make the code portable. Oracle does not require the use of to_char on the null but another db supported by the vendor might. Then again maybe it is just stupid code?

IMHO -- Mark D Powell -- Received on Wed Dec 10 2003 - 20:48:24 CST