Space requirements for INITIAL Fields

[Former Member]

Hello DB experts,

many of my clients are always scared of adding new fields to large database tables, due to additional table space requirements. In my line of business it's quiet common that some fields would only be used by certain processes / lines of business.

So let's assume i have a table with 100,000,000 records - the client processes data from 4 different lines of business (25mio records per LoB). So if i do add a 30 CHARACTER field which is only used by a process specific to 1 LoB (that means value is INITIAL/SPACE for the others), does this really take up the full table space for all the records? I would have assumed that modern databases support compression algorythms, where all the "empty" fields would only take up 1byte, rather then the entire 30 (Assuming ASCII encoding).

Space requirements would be 75,000,000 x 1 + 25,000,000 x 30 = 825MB => only 75MB for the INITIAL fields. I would assume that this kind of additional space requirement would be negligible compared to the table space that an additional table would require (including indexes) and especially the potentially additional processing time required by creating complex table joins & reads to find all the required information. 

I understand creating a new field (usually with initial value/NULL)  in a table that already has 100 mio records does not actually require additional table space, however when i insert new records through ABAP / standard RFC's these fields will always be SPACE and not NULL. I also understand that this may depend on DB release & configuration - So let's assume one of the latest oracle releases.

I hope you can support my theory and if possible it would be great if you could post a link to an article from someone who is more credible in the DB space than me.

Thanks & Regards,

Jan

[Former Member · Accepted solution]

Hi Jan,

LOBs are designed for storing large objects such as image, pdf, mail or big files. If you are planning to store the transactional data in the LOB fields, I recommend you to use cluster tables in the SAP environment. From space allocation point of view, both on transparent and cluster tables, unused space does not allocate over the database. Support the scenario with an example. Assume that table ZTEST1 has a field FLD1(CHAR100) type. When a new record inserted into the table with command below, the table size will be 4 (inital)+1=5;

insert into sapsr3.ztest (fld1) values ('A');

Then, analyze the table and calculate the current size;

analyze table sapsr3.ztest compute statistics;

select avg_row_len*num_rows from dba_tables where table_name='ZTEST';

After commit the transaction, add a new record, with the statements below and repeat the analyze. So the size will be 10 bytes;

insert into sapsr3.ztest (fld1) values ('B');

Regarding the scenario, the database does not allocate empty chars, physically. As a result of it do not scare to add fields into the table. If you are planning to create big tables such as BSEG, I suggest you to use cluster tables. For the further information, you can check the document, below;

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f083446011d189700000e8322d00/content.htm

As an additional information, if the system is running on Oracle 11g, it is able to use table compression to reduce physical database size.

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Thanks... I meant something different with LOB. Question was only about adding simple text or number fields. Anyways the rest of the answer was very helpful to confirm my theory.

Thanks & Regards.,

Jan

[Former Member · Accepted solution]

Hi Jan,

In short, of course you can insert text, numbers and so on into the LOB field.

Best regards,

Orkun Gedik