Ahh, a chance to have a wildly exaggerated example, excellent....


ops$tkyte@ORA9IR2> declare
2 l_stmt long := 'c1 int';
3 begin
4 for i in 2 .. 999
5 loop
6 l_stmt := l_stmt || ', c' || i || ' int';
7 end loop;
8
9 execute immediate 'create table t1 ( x int, ' || l_stmt || ')';
10 execute immediate 'create table t2 ( ' || l_stmt || ', x int )';
11 end;
12 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 (x) select object_id from all_objects;

27812 rows created.

ops$tkyte@ORA9IR2> insert into t2 (x) select x from t1;

27812 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec show_space( 'T1' );
Free Blocks............................. 2
Total Blocks............................ 48
Total Bytes............................. 393,216
Total MBytes............................ 0
Unused Blocks........................... 3
Unused Bytes............................ 24,576
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 489
Last Used Block......................... 5

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec show_space( 'T2' );
Free Blocks............................. 5
Total Blocks............................ 4,224
Total Bytes............................. 34,603,008
Total MBytes............................ 33
Unused Blocks........................... 98
Unused Bytes............................ 802,816
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 5,129
Last Used Block......................... 30

PL/SQL procedure successfully completed.


wow, 400k, versus 34MEG


So, what happened -- well, Oracle will store "trailing null columns" in a table using zero bytes. If the last column in the table is NULL, nothing, if the last and second to last are NULL -- two nothings and so on...

So, here we have 1,000 columns (most you are going to have). In case 1 -- one not null column followed by 999 "nothings"

In case 2 -- 999 nothings followed by one column -- in this case, each of the nothings took a byte (null flag -- to say "column 42 isn't here, it is null")


Take a couple thousand rows with 999 bytes -- versus the same without them and it adds up.


So, yes -- but remember -- this was total exaggeration here. But if you have 1,000,000 rows and know that the "last column or two" are mostly null -- you can save a meg or two (not much).


Now you know how it works though, it is true that

o putting columns you frequently access "near the front"
o putting columns that are mostly NULL last
o putting columns you infrequently access nearer to the end of the table

will derive mostly marginal improvements in speed and space usage.