CHAR vs VARCHAR (and VARCHAR2)

A friend just asked me:

I have a DB, informix actually but I think its unimportant. A column is a char(100). I have a string of text in a row in that col. the string is 4 characters long. When I select the char(100) column I get a space padded string of 100 characters with my string at the front. Have you ever seen this?

Yep that's normal. A char(100) column is exactly that: 100 characters, no more, no less. So if you put too few characters in the field, rather than giving an error, the db pads the value with spaces.

The "very advanced" datatype varchar (or varchar2 in oracle) remembers how many characters you put in the field. (I shudder to think what oracle's varchar datatype must have been like. These days it's an alias to varchar2).

There are basically no reasons to use a char. We used e.g. char(2) to store a user's country in uboot ("at", "de" etc) but in fact we even had some problems with that, so we decided to always use varchar thenceforth.

In MySQL, if you define a table where it can pre-compute the byte-width of a row in advance, i.e. it's only composed of chars, ints, etc, no varchars, then if you delete rows and then re-insert rows, you never lose any space. As it can slot new rows exactly into the space taken up by old rows.

But if you use any varchars, then it can't. And in that case, it states there is no advantage to having any chars. So if you have a table with some chars and some varchars, then it converts those chars to varchars.

Incidentally, my book on Oracle (which serves also as an advertisement for Oracle) says that Informix, if you do an insert, then the page where the row gets written gets locked for the duration of the transaction, i.e. page lock not row lock. So if you do another insert from another transaction, even though it's an independent row, it's probably going to be written to the same page, so it must wait on the first transaction. So there's a lot of waiting going on. Not being able to do two inserts on the same table from two different transactions simultaneously would seem to be exeedingly rubbish. So I'm curious if it's really true or just Oracle marketing. Or maybe it was the case in Informix 1.0. But let's not forget that Oracle 1.0 probably didn't have varchar2.

Update: my friend writes: spoke to our informix guy, he says that was true in informix 7.10 from '97.