Re: Column priority in table definition?

The only type of update that doesn't move a row around a data block is a single column update that doesn't change the length of the row. The argument you have been given for picking a column priority it therefore spurious.

There is, however, an argument for being a little fussy in extreme cases.

Columns which are null take no space at all if they are at the end of a row, but one byte per column if there are any non-null columns following them.

Imagine then that you have 15 optional columns at the end of the row and that they all start out as null. If you then update the row so that the 15th column is populated, the preceding 14 columns change from taking no bytes each to taking one byte each, which, perhaps an unexpected overhead on the update.

There is, therefore, a small case of putting optional columns at the end of the row, and arranging them in the order 'most-frequently used to least-frequently used'. For occasional special cases, this can improve the datapacking.   In most cases (and unless the table is very large) the benefit is usually negligible.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Douglas Scott wrote in message <3900FC81.F5BD72C2_at_ev1.net>...


>I recently heard that when you create a table you should order the

>columns in the table in the following order to increase database

>efficiency.  The order should be something like PK,  mandatory fixed

>length, mandatory variable length, optional fixed length, optional

>variable length.  PK columns should not be updated so they get to go to

>the head of the line.  Fixed length, and mandatory columns get next

>priority because as they get updated they have the least effect on

>expanding and shrinking the row in which the data is written, so writing

>and future reads take less time.  I really prefer to see columns

>logically grouped myself.  Is the increase in efficiency enough to

>warrant this?

>

>Douglas Scott

>