Skip to Main Content
  • Questions

  • Column Object Storage in Relational Tables

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, titi.

Asked: November 22, 2025 - 2:51 pm UTC

Last updated: December 08, 2025 - 5:36 am UTC

Version: 23ai

Viewed 1000+ times

You Asked

Refer to object-relational-developers-guide / Column Object Storage in Relational Tables
If
“The null indicators for an object and its embedded object attributes occupy one bit each. Thus, an object with n embedded object attributes (including objects at all levels of nesting) has a storage overhead of CEIL(n/8) bytes.”
And
“Since the null indicator is one byte in size, the overhead of null information for each row of the relational table is one for each object column of relational table.”
Then
where can I check that null indicator length is changed for an object column(in an relational table) with more than 8 attributes or where storage is changed ?because I didn’t see any changes in data length column for object column through all_tab_columns.

and Connor said...

I'm not fully convinced of the accuracy of the statement. However, we can look at some examples to see exactly the usage - I wrote a little plsql block to generate types of varying attribute counts

SQL> set serverout on
SQL> declare
  2    s varchar2(4000);
  3    procedure ddl(m varchar2) is
  4    begin
  5      dbms_output.put_line(m);
  6      execute immediate m;
  7    end;
  8  begin
  9  for i in 5 .. 15 loop
 10    begin
 11      ddl('drop table t'||i);
 12    exception
 13      when others then null;
 14    end;
 15
 16    begin
 17      ddl('drop type mytype'||i);
 18    exception
 19      when others then null;
 20    end;
 21    s := 'create type mytype'||i||' as object (c1 int';
 22    for j in 2 .. i loop
 23      s := s || ',c'||j||' int';
 24    end loop;
 25    ddl(s||');');
 26
 27    s := 'create table t'||i||'(c mytype'||i||')';
 28    ddl(s);
 29
 30    s := 'insert into t'||i||' values (mytype'||i||'(null';
 31    for j in 2 .. i-1 loop
 32      s := s || ',null';
 33    end loop;
 34    ddl(s||',10))');
 35  end loop;
 36
 37  for i in 5 .. 15 loop
 38  dbms_output.put_line('select dump(c) from t'||i||';');
 39  end loop;
 40  end;
 41  /
drop table t5
drop type mytype5
create type mytype5 as object (c1 int,c2 int,c3 int,c4 int,c5 int);
create table t5(c mytype5)
insert into t5 values (mytype5(null,null,null,null,10))
drop table t6
drop type mytype6
create type mytype6 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int);
create table t6(c mytype6)
insert into t6 values (mytype6(null,null,null,null,null,10))
drop table t7
drop type mytype7
create type mytype7 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int);
create table t7(c mytype7)
insert into t7 values (mytype7(null,null,null,null,null,null,10))
drop table t8
drop type mytype8
create type mytype8 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int);
create table t8(c mytype8)
insert into t8 values (mytype8(null,null,null,null,null,null,null,10))
drop table t9
drop type mytype9
create type mytype9 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int);
create table t9(c mytype9)
insert into t9 values (mytype9(null,null,null,null,null,null,null,null,10))
drop table t10
drop type mytype10
create type mytype10 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int);
create table t10(c mytype10)
insert into t10 values (mytype10(null,null,null,null,null,null,null,null,null,10))
drop table t11
drop type mytype11
create type mytype11 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int,c11 int);
create table t11(c mytype11)
insert into t11 values (mytype11(null,null,null,null,null,null,null,null,null,null,10))
drop table t12
drop type mytype12
create type mytype12 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int,c11 int,c12 int);
create table t12(c mytype12)
insert into t12 values (mytype12(null,null,null,null,null,null,null,null,null,null,null,10))
drop table t13
drop type mytype13
create type mytype13 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int,c11 int,c12 int,c13 int);
create table t13(c mytype13)
insert into t13 values (mytype13(null,null,null,null,null,null,null,null,null,null,null,null,10))
drop table t14
drop type mytype14
create type mytype14 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int,c11 int,c12 int,c13 int,c14 int);
create table t14(c mytype14)
insert into t14 values (mytype14(null,null,null,null,null,null,null,null,null,null,null,null,null,10))
drop table t15
drop type mytype15
create type mytype15 as object (c1 int,c2 int,c3 int,c4 int,c5 int,c6 int,c7 int,c8 int,c9 int,c10 int,c11 int,c12 int,c13 int,c14 int,c15 int);
create table t15(c mytype15)
insert into t15 values (mytype15(null,null,null,null,null,null,null,null,null,null,null,null,null,null,10))
select dump(c) from t5;
select dump(c) from t6;
select dump(c) from t7;
select dump(c) from t8;
select dump(c) from t9;
select dump(c) from t10;
select dump(c) from t11;
select dump(c) from t12;
select dump(c) from t13;
select dump(c) from t14;
select dump(c) from t15;

PL/SQL procedure successfully completed.


Now we can run the final select's

SQL> select dump(c) from t5;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=10: 132,1,10,255,255,255,255,2,193,11

SQL> select dump(c) from t6;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=11: 132,1,11,255,255,255,255,255,2,193,11

SQL> select dump(c) from t7;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=12: 132,1,12,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t8;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=13: 132,1,13,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t9;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=14: 132,1,14,255,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t10;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=19: 132,1,254,0,0,0,19,255,255,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t11;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=20: 132,1,254,0,0,0,20,255,255,255,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t12;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=21: 132,1,254,0,0,0,21,255,255,255,255,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t13;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=22: 132,1,254,0,0,0,22,255,255,255,255,255,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t14;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=23: 132,1,254,0,0,0,23,255,255,255,255,255,255,255,255,255,255,255,255,255,2,193,11

SQL> select dump(c) from t15;

DUMP(C)
--------------------------------------------------------------------------------------------------------------
-------
Typ=121 Len=24: 132,1,254,0,0,0,24,255,255,255,255,255,255,255,255,255,255,255,255,255,255,2,193,11


The pattern is predictable until t10, where we take a 5 byte jump. You can expand this to whatever number you want.

Of course, there are plenty of permutations here - objects of objects etc etc...

I don't think its anything to lose sleep over.

Rating

  (2 ratings)

Comments

how LEN is calculated?

titi, December 07, 2025 - 8:05 pm UTC

thank you for your comprehensive explanations, would you please let me know for following insert :
insert into t10 values (mytype10(1,2,3,4,5,NULL,7,8,NULL,99))
Typ=121 Len=33: 132,1,254,0,0,0,33,2,193,2,2,193,3,2,193,4,2,193,5,2,193,6,255,2,193,8,2,193,9,255,2,193,100
1- Len=33 calculation is right?
If for each value 1 byte is considered then seventh value is len and is calculated based on => 1,1,1,1,1,1,(1+32),1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
32=sum(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)
2- how combination: 132,1,254,0,0,0 and 2,193 (before each value) are calculated?

Connor McDonald
December 08, 2025 - 5:36 am UTC

Length is not stored as such, its simply an output of DUMP.

Values in a row are stored (in general) as:

column data, "end of column indicator", column data, "end of column indicator"

and so forth.

Hence, in this case, we simply read 33 bytes until we hit the delimiter, hence DUMP says "Oh, this column used 33bytes"

when number of attributes=170

titi, December 10, 2025 - 3:55 pm UTC

Refer to https://docs.oracle.com/en/database/oracle/oracle-database/26/adobj/object-relational-developers-guide.pdf / Column Object Storage in Relational Tables, I expected to see storage increment when number of attributes is more than 8(to have null indicator more than 1 byte because by default is 1 byte and logically is n/8 bytes, n=number of embedded attributes ) and the first place that it could be happened is where number of attributes=9 but we see changes where number of attributes=10 (increment is 254,0,0,0 which is caused adding 4 bytes to length ), does it mean that one of 9 attributes should not be null? if yes so this is right that first place where increment is happened is number of attributes=10... so why oracle says that sentence? and
for all select dump(c) from t10,…,select dump(c) from t169 , outputs are similar in combination as follows:
- insert into t10 values (mytype10(null,…,null,100929))
- insert into t69 values (mytype169(null,… ,null,100929))
- select dump(c) from t10;
Typ=121 Len=21: 132,1,254,0,0,0,21,255,255,255,255,255,255,255,255,255,4,195,11,10,30
- select dump(c) from t169;
Typ=121 Len=180: 132,1,254,0,0,0,180,255,…,255,4,195,11,10,30
I know output for 1 is 2,193,2 or for 150 is 3,194,2,51 =(2+1),(193+1),(1+1),(50+1) so output for 100929 is 4,195,11,10,30 which can be calculated based on (2+2),(193+2),(10+1),(09+1),(29+1)
but for number of attributes=170 , output format is changed , please see and let me know how can i find nulls and value in output?
- insert into tt_170 values (mytype170(null,…,null,100929))
Typ=121 Len=181: 32,52,247,127,251,127,0,0,32,101,97,26,108,2,0,0,64,119,243,22,108,2,0,0,40,2,243,22,108,2,0,0,0,202,154,59,160,15,0,0,160,15,0,0,1,0,4,0,212,228,38,253,247,127,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library