Re: Informix limitations, should we be using Oracle?

As a clarification ..:-)

CHAR is a fixed length data type and internally we store it as a fixed length ..I guess what Andrew mentioned was when we select the char column we ignore the trailing blanks (I am not so sure about that .. maybe will need to do some testing). So, if you define a column as CHAR(10) the engine will occupy 10 bytes internally as well regardless of what's the length of the actual data within the column.

VARCHAR is a variable length data type and internally we store it as 1 byte (to store the length of the data) and then the length of the data. So, if you define a column as VARCHAR(10,0) we will have 1 byte for the length and if there's nothing in the column 1 byte for the null. The overhead of frequently updating a VARCHAR field is that if the new row cannot fit into the page a REMAINDER page is created to store the row with a pointer put in the original row to point to the REMAINDER PAGE. But, if you update a VARCHAR field and the size remains the same the engine will just do the checking and keep the row in the same page..so the overhead of "checking" is always there for a VARCHAR field regardless of the fact whether the row will fit or not within the page.

HTH Thanx much,

Rajib Sarkar
Advisory Support Engineer (Wells Fargo Bank) IBM Data Management Group
Ph : (602)-217-2100
Fax: (602)-217-2100

Thought is never complete unless it finds expression in action and action limits your thought -- Mahatma Gandhi

                                                                                                                                        
                      "Tim Schaefer"                                                                                                    
                      <tim_at_datad.com>           To:      informix-list_at_iiug.org                                                         
                      Sent by:                  cc:                                                                                     
                      owner-informix-li         Subject: Re: Informix limitations, should we be using Oracle?                           
                      st_at_iiug.org                                                                                                       
                                                                                                                                        
                                                                                                                                        
                      10/17/2002 04:36                                                                                                  
                      AM                                                                                                                
                      Please respond to                                                                                                 
                      "Tim Schaefer"                                                                                                    
                                                                                                                                        
                                                                                                                                        

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)
> >

>

>