Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003

    Unanswered: order of null versus not null columns

    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.



  2. #2
    Join Date
    May 2003
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2003
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

  4. #4
    Join Date
    Jan 2003
    Provided Answers: 1
    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts