Does Oracle reserve space for a VARCHAR2() datatype; even if it does not contain a data value? If it does, how much and how does it compare to a CHAR() datatype, i.e., blank padding, etc.?
If the VARCHAR2 column does not contain any value, i.e., it contains NULL, then there is no space being used in the data block. We'll look at how the data exists in the block. I've got a simple test table which holds the following information:
ORA9I SQL> select * from test; ID VAL1 VAL2 VAL3 ---------- ---------- ---------- ---------- 1 first second third 1 first third
You can see that in the VAL2 column of my table, I have a NULL value. Let's do a dump of the block containing these two rows of data to see how it actually physically lives in the datafile. Looking at the block dump, I get the following information:
tl: 25 fb: --H-FL-- lb: 0x1 cc: 4 col 0: [ 2] c1 02 col 1: [ 5] 66 69 72 73 74 col 2: [ 6] 73 65 63 6f 6e 64 col 3: [ 5] 74 68 69 72 64 tab 0, row 1, @0x1f74 tl: 19 fb: --H-FL-- lb: 0x1 cc: 4 col 0: [ 2] c1 02 col 1: [ 5] 66 69 72 73 74 col 2: *NULL* col 3: [ 5] 74 68 69 72 64
Now this information can be daunting, but I'll talk you through the parts relevant to our discussion. This dump shows the two rows of information. For the VARCHAR2 data, the value for the column is the ASCII code of the characters, in hexadecimal notation. For instance "66 69 72 73 74" is ASCII Hex for "first".
Notice 'col 2' in the dump. This is the same column as the VAL2 column in our test table. Also notice that for the first row of data, there is a value there and for the second row, there is "*NULL*". This is exactly as we'd expect it to be.
Now for each row, look at the 'tl' value. This is the "total length" of that row. For the first row, there is a total length of 25 bytes. For the second row, the row length is 19 bytes. This is a difference of 6 bytes of data.
Next, notice the number in brackets for each column. This is the number of bytes to hold that column's value. For our first row of data, 'col 2' is six bytes in length. This makes sense, six characters take six bytes. For our second row, there is no value.
Since six bytes is the difference in the two row lengths, and the two rows contain the exactly same data, differing in only the VAL2 column, and that the length of the VAL2 column for the first row is 6 bytes, I can safely conclude that the NULL in the VAL2 column of the second row does not require any space, i.e. it uses zero bytes.
The only way to really answer the question was to take a look at how Oracle is actually storing the data in the block. That, and a little scientific data arrived at the conclusion that Oracle does not use any space to store NULL values.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.