Re: Column priority in table definition?

Jonathan Lewis wrote:
>
> 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 data-
> packing. 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
> >

(Although this is nitpicking...)

Could you argue that if you have some very long columns (larger than a block) then you would want the columns that most often appeared in where clauses to be toward the 'front' to avoid the chained access ?

Cheers
C

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse