Ask The Real Tom

June 26, 2008



column position and trailing nulls

Filed under: oracle — Tags: , , — Tom Turbo @ 8:48 pm

Trailing Nulls

First we want to see how much space we can save if we place columns that frequently contain nulls at the end of the row.
Remember, if the column length is more than 250 bytes, the row requires 3 bytes per column to store the information about the length. If the column length is smaller, then it requires 1 byte.
For trailing nulls no column length data ist stored in the row. The shorter the row, the more you benefit from trailing nulls.

We create a table with the following columns.

col1 varchar2(1 byte)
col2 varchar2(1 byte)
col3 varchar2(1 byte)
col4 varchar2(1 byte)
col5 varchar2(1 byte)

In col4 and col5 we only have nulls, no data.
I have chosen 1 byte column length to keep the rowlength very short.

First we create a table (table1) with the columns in the following order:
col1, col4, col2, col5, col3
In this case we have no trailing nulls

In the second case we create the table (table2) with the following column order:
col1, col2, col3, col4, col5

Now we have trailing nulls in col4 and col5

OK lets create the tables:

create table stewie.table1 (
col1 varchar2(1 byte),
col4 varchar2(1 byte),
col2 varchar2(1 byte),
col5 varchar2(1 byte),
col3 varchar2(1 byte));

create table stewie.table2 (
col1 varchar2(1 byte),
col2 varchar2(1 byte),
col3 varchar2(1 byte),
col4 varchar2(1 byte),
col5 varchar2(1 byte));

I populated both of the tables with 1 million rows and in both tables the columns col4 and col5 have no values.

OK let’s calculate how many MBs table2 would be smaller:
There are 1 million rows and we save 2×3 bytes each row, makes 5MB in total.

SQL> select sum(bytes)/1024/1024 MB from dba_segments where owner='STEWIE' and segment_name='TABLE1';
MB
----------
15

SQL> select sum(bytes)/1024/1024 MB from dba_segments where owner='STEWIE' and segment_name='TABLE2';
MB
----------
13

Oh, only 2MB saved. This is because there is an info in the row, that says that the last 2 columns in the previous row containing trailing nulls.

Ok in this case we saved 13% of the space, but this amount would decrease the larger the rowlength is.

Column position

I will write about that tomorrow.

Powered by WordPress