Hi,
I've read your text about putting NULL columns at the end of the table located here:
https://asktom.oracle.com/pls/apex/asktom.search?tag=null-columns-200503 My questions are:
Question no 1:
Please confirm that NULL columns at the end of the row allocate exactly 0 Bytes
Question no 2:
Does this situation with no storage consumption also apply to others TYPEs in particular DATE?
Darek
I saw your comment, you're going to have to share your complete test case to show what you did.
As the docs say:
Nulls are stored in the database if they fall between columns with data values. In these cases, they require 1 byte to store the length of the column (zero). Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, then no data is stored for these columns. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/tables-and-table-clusters.html#GUID-FF28EE12-E161-4DAD-A0A2-027D72366EE6 Tweaking Tom's example slightly to include other data types I get similar results; the table with nulls at the end (T1) is notably smaller than the one without (T2):
declare
l_stmt long := 'c1 int';
begin
for i in 2 .. 999 loop
l_stmt := l_stmt || ', c' || i ||
case mod ( i, 3 )
when 0 then ' int'
when 1 then ' varchar2(100)'
when 2 then ' date'
end ;
end loop;
execute immediate 'create table t1 ( x int, ' || l_stmt || ')';
execute immediate 'create table t2 ( ' || l_stmt || ', x int )';
end;
/
insert into t1 (x) select object_id from all_objects;
insert into t2 (x) select x from t1;
commit;
select segment_name, bytes from user_segments
where segment_name in ( 'T1', 'T2' );
-- results from 19c
SEGMENT_NAME BYTES
T1 917504
T2 88080384
-- results from 11.2
SEGMENT_NAME BYTES
T1 2097152
T2 109051904 Sooooo....
Show us your SQL!