Storage of null values in Oracle Database

In Oracle Database, the storage of null values mainly verifies the storage of null values of the number and varchar2 data types. First, we will introduce the structure of row piece, A rowpiece (not including cluster table and chain row) includes row header and column data www.2cto.com. In other cases, it will be released in future studies. BBED> dump/vFile:/u01/apps/oracle/oradata/david/users01.dbf (4) Block: 531 Offsets: 8173to 8191 Dba: 0x01000213 ------------------------------------------------------- 3c020302 c1020353 59530444 55414c01 l <... á .. SYS. DUAL.06fbd4 l. bytes <16 bytes per line> www.2cto.com For example: 3c0203, the first three bytes of A rowpiece, indicates the flag1 byte lb (itl slot) of 1 byte) 1-byte columncount. In this example, 3c = flag = 00111100 = -- HDFL -- = header + delete + first + last02 = lb itl s Lot 0x0203 = column count column value information includes: column length, for example, 02c102 indicates that the column value occupies 2 bytec102 to indicate the specific value www.2cto.com dump. You can use UTL_RAW.CAST_TO_xxxx for translation (thanks to itpub iori809 for its guidance) next we will start to verify that the environment is 11.1.0.6 linux x64 [SQL] dex @ FAKE> desc ts Name Null? Type direction -------- ------------------------------------------ id number name VARCHAR2 (20) count number dex @ FAKE> select t. *, 2 dbms_rowid.rowid_relative_fno (t. rowid) as "FNO #", 3 dbms_rowid.rowid_block_number (t. rowid) as "BLK #", 4 dbms_rowid.rowid_row_number (t. rowid) as "ROW #" 5 from dex. ts t www.2cto.com 6; id name count fno # BLK # RO W # ---------- -------------------- ---------- ------------ 1 dd 4 31 0 2 2 4 31 1 2 3 4 31 2 4 4 4 31 3 Create a table statement: dex @ FAKE> create table ts (id number, name varchar2 (20), count number); because the long column is not used in the table, therefore, the order of the columns in the table is the same as that of the columns declared in the table. BBED> set dba 16777247 DBA 0x000001f (,) BBED> map www.2cto.com File:/u01/apps/oracle/oradata/fake/users01.dbf (4) Block: 31 Dba: 0x000001f rjktb Data Block (Table/Cluster) struct kcbh, 20 bytes @ 0 struct ktbbh, 72 bytes @ 20 struct kdbh, 14 bytes @ 100 struct kdbt [1], 4 bytes @ 114 sb2 kdbr [3] @ 118 ub1 freespace [8036] @ 124 ub1 rowdata [28] @ 8160 ub4 tailchk @ 8188 first, it seems that the row of the first row id = 1 # = 0 rowpieceBBED> p * kdbr [0] rowdata [19] ----------- ub1 rowdata [19] www.2cto.com BBED> set offset 8179 OFFSET 8179 BBED> dump/v count 128 File: /u01/apps/oracle/oradata/fake/users01.dbf (4) Block: 31 Offsets: 8179 to 8191 Dba: 0x01_1f 1272c010202 c1020264 64010669 9f l ,... Á .. dd .. i. <16 bytes per li Ne> 2c010202 c1020264 these are the values in the table corresponding to the first row of rowpiece: dex @ FAKE> select * from ts where id = 1; id name count ---------- ------------------ ---------- 1 dd you can see the row # = 0 of id = 1. The value of COUNT (number type) in the last column is null and is not stored in the block, and count number is identified as having 2 columns of www.2cto.com. Let's look at row pieceBBED> p * kdbr [1] rowdata [9] ---------- ub1 rowdata [9] @ 8169 0x2cBBED> dump/v offset 8169 count 128 File: /u01/apps/oracle/orad Ata/fake/users01.dbf (4) Block: 31 Offsets: 8169 to 8191 Dba: 0x000001f 20172c010302 c103ff02 c1032c01 0202c102 l ,... á... Á .,... á. 02646401 06699f l. dd .. i. <16 bytes per line> 2c010302 c103ff02 c103 the value of rowpiece in the second row is id name count ---------- -------------------- ---------- 2 2 www.2cto.com the second column name (varchar2 type) if it is null, how is it stored here? Because it is not the last column in row piece, we can see that ff is used to indicate the length is 0. Then we can see row pieceBBED> p * kdbr [2] rowdata [0] ---------- ub1 rowdata [0] @ 8160 0x2c BBED> dump/v offset 8160 count 128 File: /u01/apps/oracle/oradata/fake/users01.dbf (4) Block: 31 Offsets: 8160 to 8191 Dba: 0x000001f 20172c0103ff 013202c1 042c0103 02c103ff l ,.... 2. Á .,... Á .. 02c1032c 010202c1 0202 6464 0106699f l. Á .,... Á .. dd .. i. <16 bytes per line> www.2cto.com rowpiece is 2c0103ff 013202c1 04. The value dex @ FAKE> select * from ts where id is null; id name count ---------- -------------------- ---------- 2 3 you can see that the first column id (number type) is null, which is expressed by ff. Finally, let's take a look at the row piece BBED> dump/v offset 8154 count 128 File of row id = 4 row # = 3: /u01/apps/oracle/oradata/fake/users01.dbf (4) Block: 31 Offsets: 8154 to 8191 Dba: 0x000001f 20172c020102 c1052c01 03ff0132 02c00002c l ,... Á .,.... 2. á ., 010302c1 03ff02c1 032c0102 02c10202 l... á... Á .,... Á .. 64640206 b2a1 l dd .. ² bytes www.2cto.com <16 bytes per line> rowpiece is 2c 020102 the value in the table corresponding to c105 is dex @ FAKE> select * from ts where id = 4; id name count ---------- ------------------ ---------- 4 You Can See That row piece contains only one column, because the name and count values of the last two columns are empty, so ff is not used. Www.2cto.com is also quite reasonable. If ff is not used, there is no way to ensure the column order. When the values of the following columns are null, one byte of ff can be saved. So far, verification has ended.