Tuesday 30 June 2009

The Char: Myth busted or confirmed?

There are many myths on the Oracle database. Many of those myths were true statements versions ago, but became myths because the Oracle database developers outsmarted the limitations. There was a myth that said that you should put non-null columns in the beginning of the table and null columns at the end. This was because in earlier versions a null column in a row did not take space until there was a filled column after it. In that case the database preallocated space for the null column to save on re-allocation efforts. But I learned that the storing-techniques of the database were improved in a way that this statement became a myth. Although it is still a good practice to have a standard on organizing your tables.

I know quite a lot of the Oracle database. But most of it is from using the modern ones and carrying the lugage of courses in the older ones. I know how a car and it's engine theoretically work, but I have no up-to-date knowlegde of the internals of the modern car. In the same way I have to rely on what I hear from other experts and co-workers about statements as above. So I keep my ears open and carefull about spreading myths.

This week a valued co-worker came with a DDL-script to create a table with Char-columns. My first reaction was that he should replace the CHARs with VARCHAR2s. Since a Varchar, as suggested by it's name, should occupy space related to it's fillings, while a CHAR occupies space according to it's maximum size. Though this was true, I was not sure if this statement was passed over by the reality of the modern database's storage techniques.
But according to Tom Kyte, the statement still holds (article started in 2001, but is updated recently). However, browsing to the end of the article's list of comments, I read: if a char or a varchar2 column is null, than it does not take more room then is needed to hold the null-value.

So myth confirmed...

No comments :