Oracle order columns by data type interferes in performace

Question

A job partner said: when we create a table, the order of fields data type, in oracle database matters for performance. Example: if I have varchar columns first and date columns last I would have performance issues, but in my search I didn't find anything about it. It is true?

Answer 1

The order of columns can have a slight impact on performance, but it has nothing to do with the data types.

Some examples of where column order matters:

1. If you put more frequently queried columns first, you'll save Oracle time looking through fetched rows field by field. (Oracle starts at the beginning -- so if you query the first column in your table, it finds it and stops. If you query the last column in your table, it looks through all of them).
2. If you put more frequently queried columns first, you'll sometimes avoid a second I/O if a row was split into two blocks (i.e., chained). The earlier columns will more likely be in the original block, the later columns more likely in the chained block.
3. If you put all your usually NULL columns last, Oracle can save some space by not storing placeholders for the trailing NULL columns. It can, instead, just write the end-of-row marker and it's assumed that all the later columns are NULL. If you put a NOT NULL column last, Oracle cannot do that.

That all said... the performance impacts are usually negligible and usually dwarfed by performance problems caused by poor SQL, poor indexes, bad clustering, not using bind variables, etc.

Put your columns in your table in the order that they make sense to you.

If you later find a demonstrable performance impact (which will be rare!) you can restructure it later.

There are a million other considerations when designing for performance. This isn't one of them.