Oracle Database Discussions

John,

It is my understanding that NULL values in the last columns (non-null values appear only in the first set of columns defined for the table) in of a row do not consume space, while NULL values in the first set of columns, if they are followed by columns containing non-NULL values will consume space. At this time, I am having trouble locating a reference which backs up my understanding of how NULLs affect storage space.

A little test to possibly help - needs to be expanded to fully test the above:
Oracle 10.2.0.4, 8KB block size, ASSM tablespace with auto extent size.

Create a table containing various types of columns:
CREATE TABLE T1 (
  C1 VARCHAR2(300),
  C2 NUMBER(22,4),
  C3 DATE,
  C4 BLOB,
  C5 CLOB);

Gather statistics on the table:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
         0          0            0           0                   0

SELECT
  EXTENT_ID,
  BYTES,
  BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8

Insert 1,000,000 rows containing NULL values in all columns:
INSERT INTO T1 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO T1 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

COMMIT;

Gather statistics on the table, note that Oracle's estimated row count is a little off:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
    984619       1630            0           1                   0

SELECT
  EXTENT_ID,
  BYTES,
  BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128
        22    1048576        128
        23    1048576        128
        24    1048576        128
        25    1048576        128
        26    1048576        128
        27    1048576        128

SELECT
  SUM(BYTES) BYTES,
  SUM(BLOCKS) BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

     BYTES     BLOCKS
---------- ----------
  13631488       1664

The above shows that the table is using 1630 blocks to store the rows with the NULL values, there are 1664 blocks allocated to extents used by the table,
and 13,631,488 bytes used by the extents.
NULLs consuming space?

Experimentation with ROWID pseudocolumn: 
SELECT
  SUBSTR(ROWID,1,6) OBJECT_ID,
  SUBSTR(ROWID,7,3) FILE_ID,
  SUBSTR(ROWID,10,6) BLOCK_ID,
  SUBSTR(ROWID,16,3) ROW_ID,
  LENGTHB(ROWID) LEN,
  COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN,
  SUM(LENGTH(ROWID)) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES
FROM
  T1
WHERE
  ROWNUM<=1400;

OBJECT_ID FILE_ID BLOCK_ID ROW_ID	 LEN	   ROWN      BYTES
========= ======= ======== ====== ========== ========== ==========
AAALn4	  AAE	  AAE6Wc   AAA		  18	      1 	18
AAALn4	  AAE	  AAE6Wc   AAB		  18	      2 	36
AAALn4	  AAE	  AAE6Wc   AAC		  18	      3 	54
AAALn4	  AAE	  AAE6Wc   AAD		  18	      4 	72
AAALn4	  AAE	  AAE6Wc   AAE		  18	      5 	90
AAALn4	  AAE	  AAE6Wc   AAF		  18	      6        108
AAALn4	  AAE	  AAE6Wc   AAG		  18	      7        126
AAALn4	  AAE	  AAE6Wc   AAH		  18	      8        144
AAALn4	  AAE	  AAE6Wc   AAI		  18	      9        162
AAALn4	  AAE	  AAE6Wc   AAJ		  18	     10        180
AAALn4	  AAE	  AAE6Wc   AAK		  18	     11        198
AAALn4	  AAE	  AAE6Wc   AAL		  18	     12        216
AAALn4	  AAE	  AAE6Wc   AAM		  18	     13        234
AAALn4	  AAE	  AAE6Wc   AAN		  18	     14        252
AAALn4	  AAE	  AAE6Wc   AAO		  18	     15        270
...
AAALn4	  AAE	  AAE6Wc   AKQ		  18	    657      11826
AAALn4	  AAE	  AAE6Wc   AKR		  18	    658      11844
AAALn4	  AAE	  AAE6Wc   AKS		  18	    659      11862
AAALn4	  AAE	  AAE6Wc   AKT		  18	    660      11880
AAALn4	  AAE	  AAE6Wd   AAA		  18	      1 	18
AAALn4	  AAE	  AAE6Wd   AAB		  18	      2 	36
AAALn4	  AAE	  AAE6Wd   AAC		  18	      3 	54
AAALn4	  AAE	  AAE6Wd   AAD		  18	      4 	72
AAALn4	  AAE	  AAE6Wd   AAE		  18	      5 	90
...
AAALn4	  AAE	  AAE6Wd   AKP		  18	    656      11808
AAALn4	  AAE	  AAE6Wd   AKQ		  18	    657      11826
AAALn4	  AAE	  AAE6Wd   AKR		  18	    658      11844
AAALn4	  AAE	  AAE6Wd   AKS		  18	    659      11862
AAALn4	  AAE	  AAE6Wd   AKT		  18	    660      11880
AAALn4	  AAE	  AAE6We   AAA		  18	      1 	18
AAALn4	  AAE	  AAE6We   AAB		  18	      2 	36
AAALn4	  AAE	  AAE6We   AAC		  18	      3 	54
AAALn4	  AAE	  AAE6We   AAD		  18	      4 	72
AAALn4	  AAE	  AAE6We   AAE		  18	      5 	90
...

The above shows that Oracle was packing 660 of these completely NULL rows into each 8KB block with a default PCT_FREE of 10%, leaving about 7370 bytes of space for storing the 660 rows per block, indicating that each row was consuming about 11 bytes.

Comparison test:

CREATE TABLE T2 (
  C1 VARCHAR2(1));

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2');

INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2');

SELECT
  SUM(BYTES) BYTES,
  SUM(BLOCKS) BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T2'
ORDER BY
  EXTENT_ID;

     BYTES     BLOCKS
---------- ----------
  13631488       1664

The 13,631,488 bytes and 1,664 blocks looks to be the same as in the first test. Feel free to continue testing different set ups.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricatiing, Inc.

Edited by: Charles Hooper on Jan 31, 2009 11:42 AM
Changed the second "tables" to "columns" at the start of the test script. VSIZE - I was intending to include an example of that in the script, but the specific function name could not be remembered prior to the post, thanks for the reminder Sybrand.