Re: Informix limitations, should we be using Oracle?

I was under the impression that CHAR data types were a fixed allocation of space and varchars were variablspaced,  thus the naming of them. I've also learned ( from Informix training no less ) that when you create a varchar to alway allocate an even number for it, i.e. varchar(20), varchar(10), instead of varchar(19) or varchar(9), because of the way they use space. My memory is foggy on the "why" but it has a lot to do with page allocation, etc being wasted on odd-sized varchars. It is also unwise of course to use varchars on fields that will be updated a lot, as it creates "broken" data if the new data in the field exceeds the original length of data used in that space.

As to the trailing blanks statement, I would be very quick to ask someone from an Informix or Oracle technical background if indeed trailing blanks are "optimized" in the database for plain CHAR. On several versions of older Informix this was not the case as I recall but again my memory is foggy on this.

"Andrew Hamm" <ahamm_at_mail.com> wrote in message news:3dae1472_1_at_news.iprimus.com.au...
> Obnoxio The Clown wrote:
> >
> > 255 byte limit on varchar fields -- still true (I think)
> >
>
> One point that should be shared with Oraclers regarding varchars on
> Informix...
>
> I've noticed that Oraclers are obsessed with using varchars of one kind or
> another, and from what I read about the page allocation strategies, it is
> indeed worthwhile to use them in Oracle.
>
> However, with informix, plain old CHAR types are highly optimised (in the
> engine and the way they are handled, I mean), and they also behave very
> varcharish anyway. Trailing blanks are disregarded amongst other things, and
> Informix varchars miss out on some high-performance optimisations. In a
> typical database, the wasted space is far less than you might imagine... So
> if you ever dabble with Informix, it really is OK to abandon varchars for
> CHARs. Just a small cultural difference to adapt to.
>
>
Received on Thu Oct 17 2002 - 13:36:44 CEST