Professional Web Applications Themes

Order of columns - IBM DB2

Regarding V8.1 for Red Hat, I was wondering if order of columns matters. For example, some of the columns are never (or almost never) modified, others are quite volatile. I'm especially concerned about VARCHAR volatile columns. Does NULL attribute matter? TIA...

  1. #1

    Default Order of columns

    Regarding V8.1 for Red Hat,

    I was wondering if order of columns matters. For example, some of the
    columns are never (or almost never) modified, others are quite
    volatile. I'm especially concerned about VARCHAR volatile columns.
    Does NULL attribute matter?

    TIA
    Ford Guest

  2. #2

    Default Re: Order of columns

    Depends what you're worried about. :-)

    Nullable columns contain 1 extra byte of data per column over not
    nullable. varchar data is on disk after all fixed var data (so modifying
    a varchar column doesn't cause all fixed len columns to shift).

    Hopefully this helps...

    Ford Desperado wrote:
     

    Sean Guest

  3. #3

    Default Re: Order of columns

    You might want to read this (see the section called The Fifth Column):

    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html

    Ford Desperado wrote:
     

    Blair Guest

  4. #4

    Default Re: Order of columns

    "Blair Adamache" <> wrote in message
    news:blc451$7ve$torolab.ibm.com... 
    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html 

    1. I believe there is a typo in this sentence from the above referenced
    article"

    "What gets written to the log is the first column in the row, and every row
    thereafter until DB2 reaches the final column that changed."

    Shouldn't that be "every column" instead of "every row"?

    2. After reading this from the same article: "If a table has variable length
    columns such as VARCHARs, these are still ordered in the order specified in
    the CREATE TABLE, but the variable data itself is at the end of the row:" I
    assume then that there is no advantage to putting frequently updated varchar
    columns at the end of the row, because DB2 puts it there anyway?

    Mark Farnsworth
    InfoStar Solutions, LLC
    info_star at domain qw_est.net
    (remove "domain" and all underscores)


    Mark Guest

  5. #5

    Default Re: Order of columns


    "Mark A" <net> wrote in message
    news:elieb.1814$uswest.net... 
    >[/ref]
    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html 
    >
    > 1. I believe there is a typo in this sentence from the above referenced
    > article"
    >
    > "What gets written to the log is the first column in the row, and every[/ref]
    row 
    length 
    in 

    varchar 

    You are correct on both counts.

    --
    Matt Emmerton


    Matthew Guest

  6. #6

    Default Re: Order of columns


    "Matthew Emmerton" <com> wrote in message
    news:y1reb.3149$bellglobal.com... 
    > >[/ref]
    >[/ref]
    http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html 
    > >
    > > 1. I believe there is a typo in this sentence from the above referenced
    > > article"
    > >
    > > "What gets written to the log is the first column in the row, and every[/ref]
    > row 
    > length 
    > in [/ref]
    row:" 
    > varchar 
    >
    > You are correct on both counts.
    >
    > --
    > Matt Emmerton
    >
    >[/ref]
    ..you Thank


    Mark Guest

  7. #7

    Default Re: Order of columns

    1. right
    2. right

    Mark A wrote:
     
    >
    > http://www7b.boulder.ibm.com/dmdd/library/techarticle/adamache/0621_adamache.html

    >
    >
    > 1. I believe there is a typo in this sentence from the above referenced
    > article"
    >
    > "What gets written to the log is the first column in the row, and every row
    > thereafter until DB2 reaches the final column that changed."
    >
    > Shouldn't that be "every column" instead of "every row"?
    >
    > 2. After reading this from the same article: "If a table has variable length
    > columns such as VARCHARs, these are still ordered in the order specified in
    > the CREATE TABLE, but the variable data itself is at the end of the row:" I
    > assume then that there is no advantage to putting frequently updated varchar
    > columns at the end of the row, because DB2 puts it there anyway?
    >
    > Mark Farnsworth
    > InfoStar Solutions, LLC
    > info_star at domain qw_est.net
    > (remove "domain" and all underscores)
    >
    >[/ref]

    Blair Guest

  8. #8

    Default Re: Order of columns

    Blair,

    that's a great article, thanks!
    When a VARCHAR inflates and causes a page to split, how is it logged?
    That should be more logging overhead, correct?
    Ford Guest

Similar Threads

  1. How to programatically order columns in anAdvancedDataGrid
    By Woestman in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 18th, 07:47 PM
  2. dataGrid - dynamically re-order columns
    By skinnyfingers in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 11th, 09:23 PM
  3. SELECT * / Order of Columns
    By dj shane in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 6th, 07:32 PM
  4. Column order and dynamically created columns
    By MattB in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: December 9th, 07:03 AM
  5. Selecting all columns, but in a different order in SELECT statement
    By Alex in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 12th, 07:38 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139