For UDB Version 8, AIX, are there any considerations, performance or otherwise on having nullable columns near the beginning of the table? In other words, when defining the table, are there performance considerations that dictate columns defined as nullable be put physically at the beggining or end of the table.

I believe I read something before about varchar fields should go near the end of the table, but that may have been for os/390 only.

Thanks,

Cliff.

You are correct that on OS/390 you should put varchar at the end of the row (unless that has been changed in recent releases).

On DB2 for Linux, Unix, and Windows, the varchar columns are put at the end of the table automatically when the table is created.

I have never heard anything about null vs not null columns. Nullable columns require one extra byte for the null indicator.

I feel confident in saying that nullable columns can be anywhere. In most storage formats there is usually a null-value indicator, either as an invisible first-byte of the column or as a bitmap stored with each column.

Generally, I don't recommend that you dwell too much on "efficiency" when you design your tables. That's really what the DBMS is supposed to be concerned with. Your concern is to be certain that the schema accurately represents what you need for it to be capable of storing.

Hi chimes1967,

I have read somewhere that the order of columns for performance reason is important.
The recomendation for order of columns in table was:
1. primary key columns,
2. not null columns,
3. null columns,
4. varchar columns.

I haven't tested this to confirm some performance benefits. I just write what I read...

Hope this helps,
Grofaty