DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Position of NOT NULL cols in table ...

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Is still valid to say they should be at start of table definition? My developer has scripts with NOT NULLS at start and end of large tables and columns of 1000 and 500 chars in between.
    What are the storage implications?

    Also - precision with NUMBER columns. What's the implications of using NUMBER over NUMBER(8)?

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    Quote
    ---------
    Also - precision with NUMBER columns. What's the implications of using NUMBER over NUMBER(8)?
    ----------

    there is no difference between number and number(8) as far as storage is concerned .

    by mentioning number(8), u are just restricting the the maximum number of digits for this column. adding a constraint .
    oracle will shrink the storage usage based on the value itself .

    u can check the number of bytes used using the following command .

    SQL> select vsize ( 9999) from dual ;

    VSIZE(9999)
    -----------
    3

    SQL> select vsize ( -99 ) from dual ;

    VSIZE(-99)
    ----------
    3

    SQL> select vsize ( 9.9 ) from dual ;

    VSIZE(9.9)
    ----------
    3

    SQL> select vsize ( 99 ) from dual ;

    VSIZE(99)
    ----------
    2

    for every two digits it takes 1 byte .
    and extra byte for sign and extra byte for precision .




    siva prakash
    DBA

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    pk first
    fk second
    not null third
    null, large (..) last


    F.

  4. #4
    Join Date
    Dec 2000
    Posts
    126

    Exclamation

    quote from Mnemonical

    pk first
    fk second
    not null third
    null, large (..) last


    shouldn't it be


    pk first
    fk second
    not null third
    large (..)
    null last << since null takes 1 byte if precedes non-null column

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Mnemonical

    pk first
    fk second
    not null third
    null, large (..) last
    Hm, what does position of FK have to do with space saving? What if FK column is NULLable? Would you still position it immediately after the PK and in front of other NOT NULL columns?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    So -I assume it's good practice to save nullable columns til the end of the table definitions, but WHY - explicitly? In words that a non-Oracle person might understand (the project manager, for instance).


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It's quite simple. If row contains null value in a particular column, this column consumes 0 or 1 byte of space on disk. If a null-value column is the last one in a table or if all columns that follow it contain null values, then it consumes no space at all (0 bytes). But if any following columns contain non-null value, then oracle uses 1 byte to store that null value.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    So -I assume it's good practice to save nullable columns til the end of the table definitions, but WHY - explicitly? In words that a non-Oracle person might understand (the project manager, for instance).


  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It's quite simple. If row contains null value in a particular column, this column consumes 0 or 1 byte of space on disk. If a null-value column is the last one in a table or if all columns that follow it contain null values, then it consumes no space at all (0 bytes). But if any following columns contain non-null value, then oracle uses 1 byte to store that null value.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    To follow on to jmodic:

    If you follow the NOT_NULL->NULLABLE pattern (PK's are not nullable by definition, a unique can be null but a PK can't and a foreign key only has to be unique in the parent table), then a given row in Oracle will take less space because Oracle doesn't store anything for null fields at the end of a row. This is because Oracle stores a 'map' of how many columns are stored for each row in a block. If a null is followed by a field with data, then the map is updated to show the columns.

    This means less disk usage. It can also mean better performance because more rows are stored in a given block, meaning more data is returned in a given read (or written in a given write) and given rows can be found faster in memory than on disk.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

Posting Permissions

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


Click Here to Expand Forum to Full Width