size of numeric data

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Frank Swarbrick
    #1

    size of numeric data

    My DBA says that a column defined, for instance, as DECIMAL(11,2) and
    containing a value of 1.00 takes up no more space on the database disk than
    a column defined as DECIMAL(7,2) and containing a value of 1.00.

    I am honestly having a hard time believing this, but I don't know where I
    can find the answer.
    Help?

    Thanks,
    Frank

    Tags: None
    • Dave Hughes
      #2
      Re: size of numeric data

      Frank Swarbrick wrote:
      My DBA says that a column defined, for instance, as DECIMAL(11,2) and
      containing a value of 1.00 takes up no more space on the database
      disk than a column defined as DECIMAL(7,2) and containing a value of
      1.00.
      >
      I am honestly having a hard time believing this, but I don't know
      where I can find the answer.
      Help?
      >
      Thanks,
      Frank
      Your DBA is indeed mistaken (assuming he's referring to DB2 :-). From
      the CREATE TABLE reference in the DB2 9.5 InfoCenter [1] (search for
      the "Storage Byte Counts" heading):

      "Storage Byte Counts: The following table contains the storage byte
      counts of columns by data type for data values. The byte counts depend
      on whether or not VALUE COMPRESSION is active. When VALUE COMPRESSION
      is not active, the byte counts also depend on whether or not the column
      is nullable. The values in the table represent the amount of storage
      (in bytes) that is used to store the value.

      ....

      DECIMAL (when VALUE COMPRESSION is not active and the column is not
      nullable): The integral part of (p/2)+1, where p is the precision"

      So a DECIMAL(11,2) NOT NULL column without VALUE COMPRESSION requires 6
      bytes, while a DECIMAL(7,2) column with the same attributes requires 4
      bytes per row, regardless of the value stored within the row.

      [1]http://publib.boulder. ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db
      2.luw.sql.ref.d oc/doc/r0000927.html


      Cheers,

      Dave.

        Comment

        • Mark A
          #3
          Re: size of numeric data

          "Frank Swarbrick" <Frank.Swarbric k@efirstbank.co mwrote in message
          news:48297F5F.6 F0F.0085.0@efir stbank.com...
          My DBA says that a column defined, for instance, as DECIMAL(11,2) and
          containing a value of 1.00 takes up no more space on the database disk
          than
          a column defined as DECIMAL(7,2) and containing a value of 1.00.
          >
          I am honestly having a hard time believing this, but I don't know where I
          can find the answer.
          Help?
          >
          Thanks,
          Frank
          That is true with some databases, but not with DB2 unless you are
          specifically using compression.


            Comment

            • Frank Swarbrick
              #4
              Re: size of numeric data

              >>On 5/13/2008 at 12:25 PM, in message
              <laSdnXAOn6mJQL TVnZ2dnUVZ8gidn Z2d@posted.plus net>, Dave
              Hughes<dave@wav eform.plus.comw rote:
              Frank Swarbrick wrote:
              >
              >My DBA says that a column defined, for instance, as DECIMAL(11,2) and
              >containing a value of 1.00 takes up no more space on the database
              >disk than a column defined as DECIMAL(7,2) and containing a value of
              >1.00.
              >>
              >I am honestly having a hard time believing this, but I don't know
              >where I can find the answer.
              >Help?
              >>
              >Thanks,
              >Frank
              >
              Your DBA is indeed mistaken (assuming he's referring to DB2 :-). From
              the CREATE TABLE reference in the DB2 9.5 InfoCenter [1] (search for
              the "Storage Byte Counts" heading):
              >
              "Storage Byte Counts: The following table contains the storage byte
              counts of columns by data type for data values. The byte counts depend
              on whether or not VALUE COMPRESSION is active. When VALUE COMPRESSION
              is not active, the byte counts also depend on whether or not the column
              is nullable. The values in the table represent the amount of storage
              (in bytes) that is used to store the value.
              >
              ...
              >
              DECIMAL (when VALUE COMPRESSION is not active and the column is not
              nullable): The integral part of (p/2)+1, where p is the precision"
              >
              So a DECIMAL(11,2) NOT NULL column without VALUE COMPRESSION requires 6
              bytes, while a DECIMAL(7,2) column with the same attributes requires 4
              bytes per row, regardless of the value stored within the row.
              >
              [1]http://publib.boulder. ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db
              2.luw.sql.ref.d oc/doc/r0000927.html
              Thanks for the link. Looks like exactly what I need.

              Frank

                Comment

                • Frank Swarbrick
                  #5
                  Re: size of numeric data

                  >>On 5/13/2008 at 5:24 PM, in message
                  <7qpWj.134289$E r2.127202@bigne ws6.bellsouth.n et>, Mark
                  A<nobody@nowher e.comwrote:
                  "Frank Swarbrick" <Frank.Swarbric k@efirstbank.co mwrote in message
                  news:48297F5F.6 F0F.0085.0@efir stbank.com...
                  >My DBA says that a column defined, for instance, as DECIMAL(11,2) and
                  >containing a value of 1.00 takes up no more space on the database disk
                  >than
                  >a column defined as DECIMAL(7,2) and containing a value of 1.00.
                  >>
                  >I am honestly having a hard time believing this, but I don't know where
                  I
                  >can find the answer.
                  >Help?
                  >
                  That is true with some databases, but not with DB2 unless you are
                  specifically using compression.
                  Do you by chance know if Oracle is one of the "some databases"? The DBA is
                  an Oracle guy who we also have working on DB2 now.

                  Thanks,
                  Frank

                    Comment

                    • Mark A
                      #6
                      Re: size of numeric data

                      "Frank Swarbrick" <Frank.Swarbric k@efirstbank.co mwrote in message
                      news:4829D0ED.6 F0F.0085.0@efir stbank.com...
                      Do you by chance know if Oracle is one of the "some databases"? The DBA
                      is
                      an Oracle guy who we also have working on DB2 now.
                      >
                      Thanks,
                      Frank
                      I believe that Oracle is one of the databases that automatically compresses
                      some data types.


                        Comment

                        • Serge Rielau
                          #7
                          Re: size of numeric data

                          Frank Swarbrick wrote:
                          >>>On 5/13/2008 at 5:24 PM, in message
                          <7qpWj.134289$E r2.127202@bigne ws6.bellsouth.n et>, Mark
                          A<nobody@nowher e.comwrote:
                          >"Frank Swarbrick" <Frank.Swarbric k@efirstbank.co mwrote in message
                          >news:48297F5F. 6F0F.0085.0@efi rstbank.com...
                          >>My DBA says that a column defined, for instance, as DECIMAL(11,2) and
                          >>containing a value of 1.00 takes up no more space on the database disk
                          >>than
                          >>a column defined as DECIMAL(7,2) and containing a value of 1.00.
                          >>>
                          >>I am honestly having a hard time believing this, but I don't know where
                          >I
                          >>can find the answer.
                          >>Help?
                          >That is true with some databases, but not with DB2 unless you are
                          >specifically using compression.
                          >
                          Do you by chance know if Oracle is one of the "some databases"? The DBA is
                          an Oracle guy who we also have working on DB2 now.
                          NUMBER is stored similar to a "VARCHAR". Call it VARDECIMAL if you wish.
                          That is the storage depends on the number of digits, where trailing 0's
                          are removed.

                          --
                          Serge Rielau
                          DB2 Solutions Development
                          IBM Toronto Lab

                            Comment

                            • Frank Swarbrick
                              #8
                              Re: size of numeric data

                              >>On 5/13/2008 at 7:48 PM, in message
                              <68uunbF2vk8ueU 1@mid.individua l.net>,
                              Serge Rielau<srielau@ ca.ibm.comwrote :
                              Frank Swarbrick wrote:
                              >>>>On 5/13/2008 at 5:24 PM, in message
                              ><7qpWj.134289$ Er2.127202@bign ews6.bellsouth. net>, Mark
                              >A<nobody@nowhe re.comwrote:
                              >>"Frank Swarbrick" <Frank.Swarbric k@efirstbank.co mwrote in message
                              >>news:48297F5F .6F0F.0085.0@ef irstbank.com...
                              >>>My DBA says that a column defined, for instance, as DECIMAL(11,2) and
                              >>>containing a value of 1.00 takes up no more space on the database disk
                              >>>than
                              >>>a column defined as DECIMAL(7,2) and containing a value of 1.00.
                              >>>>
                              >>>I am honestly having a hard time believing this, but I don't know where
                              >>I
                              >>>can find the answer.
                              >>>Help?
                              >>That is true with some databases, but not with DB2 unless you are
                              >>specificall y using compression.
                              >>
                              >Do you by chance know if Oracle is one of the "some databases"? The DBA
                              is
                              >an Oracle guy who we also have working on DB2 now.
                              NUMBER is stored similar to a "VARCHAR". Call it VARDECIMAL if you wish.
                              Really! Interesting.
                              That is the storage depends on the number of digits, where trailing 0's
                              are removed.
                              Leading zeroes as well?

                              Any idea where I can find more information on this? I want to know all I
                              can before bring it do my DBA.

                              Thanks!
                              Frank

                                Comment

                                • Frank Swarbrick
                                  #9
                                  Re: size of numeric data

                                  >>On 5/14/2008 at 4:54 PM, in message
                                  <482B194E.6F0F. 0085.0@efirstba nk.com>,
                                  Frank Swarbrick<Frank .Swarbrick@efir stbank.comwrote :
                                  >>>On 5/13/2008 at 7:48 PM, in message
                                  <68uunbF2vk8ueU 1@mid.individua l.net>,
                                  Serge Rielau<srielau@ ca.ibm.comwrote :
                                  >NUMBER is stored similar to a "VARCHAR". Call it VARDECIMAL if you wish.
                                  >
                                  >
                                  Really! Interesting.
                                  >
                                  >That is the storage depends on the number of digits, where trailing 0's
                                  >are removed.
                                  >
                                  Leading zeroes as well?
                                  >
                                  Any idea where I can find more information on this? I want to know all
                                  I
                                  can before bring it do my DBA.
                                  Found what I was looking for:

                                  Oracle Data Types
                                  http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#

                                  i16209

                                  "Internal Numeric Format
                                  Oracle Database stores numeric data in variable-length format. Each value is
                                  stored in scientific notation, with 1 byte used to store the exponent and up
                                  to 20 bytes to store the mantissa. The resulting value is limited to 38
                                  digits of precision. Oracle Database does not store leading and trailing
                                  zeros. For example, the number 412 is stored in a format similar to 4.12 x
                                  102, with 1 byte used to store the exponent(2) and 2 bytes used to store the
                                  three significant digits of the mantissa(4,1,2) . Negative numbers include
                                  the sign in their length.

                                  Taking this into account, the column size in bytes for a particular numeric
                                  data value NUMBER(p), where p is the precision of a given value, can be
                                  calculated using the following formula:

                                  ROUND((length(p )+s)/2))+1
                                  where s equals zero if the number is positive, and s equals 1 if the number
                                  is negative.

                                  Zero and positive and negative infinity (only generated on import from
                                  Oracle Database, Version 5) are stored using unique representations . Zero
                                  and negative infinity each require 1 byte; positive infinity requires 2
                                  bytes."

                                  The DB2 way is more familiar to me, being a Cobol programmer. Cobol stores
                                  numeric data in a very similar fashion.

                                  I am sure there are both good and bad points to both the DB2 way and the
                                  Oracle way. The one thing that seems nice about the Oracle way is you don't
                                  really have to worry ahead of time about the maximum value that would ever
                                  need to be stored in a particular column. As long as it's not more that 38
                                  digits of precision you're good to go!

                                  We had a problem just the other day where we had a Cobol field defined as
                                  PIC S9(9)V99 (which can hold a maximum value of 999,999,999.99) and we ended
                                  up generating (or trying to generate, I should say) a transaction for over a
                                  billion dollars. Well the most significant digit was truncated, so we were
                                  'out of balance' by $1 billion. Don't worry! No customer was affected!!
                                  <g Still, not good. Obviously whoever designed the data store at the time
                                  couldn't imagine a single transaction of this type that would ever be this
                                  large.

                                  Frank

                                    Comment

                                    Previous template Next
                                    Working...