Find percentage of NULL storage in Oracle table

oracle

I have a table in Oracle 11g Standard One Edition:

table1

col1 col2 col3 col4 col5 col6 col7 col8       col9 col10 col11
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    NULL 2    3    4    5    NULL 1 Jan 2009 19   21    22
1    NULL 2    3    4    5    NULL NULL       19   21    22
1    9    2    3    4    5    A    NULL       19   21    22
1    NULL 2    3    4    5    B    NULL       19   21    22

The table desc is:

Name                 Null Type          
-------------------- ---- ------------- 
COL1                      NUMBER        
COL2                      NUMBER        
COL3                      NUMBER        
COL4                      NUMBER       
COL5                      NUMBER        
COL6                      NUMBER        
COL7                      VARCHAR2(255) 
COL8                      DATE          
COL9                      DATE  
COL10                     DATE        
COL11                     VARCHAR2(255)

I need to find out what is the percentage of storage a table consumes with values NULL?

Example: table1 storage consumed is 1 GB, and NULLs inside it consumes 100MB, so, NULL takes up 10% of the storage.

Also, are there alternate representations of NULL in ORACLE?

Best Answer

NULL value

Absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero.

There are no alternate definitions of NULL in Oracle. A column can't take less space than NULL unless the column doesn't exist at all. Also, if a null value is in the last column of the row, the row will take less space than the same null column as an earlier column. Here is the wording in the documentation:

In general, try to place columns that frequently contain nulls last so that rows take less space. Note, though, that if the table you are creating includes a LONG column as well, then the benefits of placing frequently null columns last are lost.

Since in your example data, none of the rows with nulls have them last, we can ignore this space optimization. We can also not compare the lack of a column to a column with nulls as that would be the true "space" of a null, which according to some block dumps I did is one byte per null per row.

In this case we are actually comparing the space of a null vs. the space of a value. Because any value will by definition use more space than a null, my answer is that, a null value will decrease not increase the amount of space consumed, therefore creating a percentage compared to the size of the table is meaningless.

You could do a block dump of a row containing a value and then set the value to null and dump the block again to see how much additional space is available, but that number and the resulting ratio you could create with it and the size of the table would be a measure of space used by values rather than space used by NULLs.

Related Question