Re: Database Design Efficiency Question

In article <1992Jan27.183813.25986_at_cbnewsd.att.com>, elel_at_cbnewsd.att.com (eric.edberg) writes:

|>Only some of the fields (appx 10%) in any tabel may
|>contain data at any time; The rest will not be
|>populated == (char *NULL) or a LONG INT that contains no
|>value.
|>
|>My question relates to space efficiency and how to design a
|>table so that many fields can be created, but the fields that
|>are not populated during run-time applications do NOT use space
|>in the database.

Oracle stores data of type char by storing a variable length string and its length. Null values are stored through use of a zero field length. Thus if you declared a field a char(25) and inserted a string 25 characters long it would take oracle 26 bytes to store the string. 25 bytes for the string and 1 byte to store its length, inserting a 7 character string would take 8 bytes, a null value would take 1.

Numbers also take up a variable amount of space in the database. Each digit takes one half byte, the exponent takes another byte, and another byte is used if the number is negative.

In other words Oracle is already doing what you want. Nota Bene that there is additional overhead for each row and block of the table which must be taken into account when calculating the amount of space to allocate for the object. Also your attention is invited to page 6-2 of the ORACLE RDBMS Database Administrators Guide version 6.0 (revised October 1990) for a description of the upcoming differences between char and varchar. In the future char will be fixed length (blank-padded) and varchar will be variable length.

                                  
                   Ian MacGregor [Stanford Linear Accelerator Center]