Skip to Main Content
  • Questions
  • Oracle row compared to Mongo document


Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: May 20, 2024 - 3:34 pm UTC

Last updated: May 23, 2024 - 2:23 am UTC

Version: 21c

Viewed 1000+ times

You Asked

Good Morning,

In the last year, I've started to support Mongo databases. Mongo stores data in BSON which is the binary form of JSON. JSON is just the field name followed by a value. This doesn't seem so different from Oracle since Oracle also its data in a series of columns with values.

I'm curious to know how an Oracle row looks like. If a table has the following columns:

-Fname string
-Lname string
-notes string

If row has say, Fname='John' and Lname='Doe', does Oracle add the field names Fname and Lname to each row? Does the row look like this on disk:

Fname='John', Lname='Doe', notes null
or does it look like this:
'John','Doe', null

My guess is that it looks like option 1.

It would be nice if you could also provide what an Oracle row looks like on disk.

Thank you


and Chris said...

Fun fact - if you haven't got Transparent Data Encryption enabled, you can open up data files and view data stored in your tables! So you can see for yourself what a row looks like.

For example:

create table find_this ( 
  col_name1 varchar2(10), col_name2 int, col_name3 varchar2(10), 
  col_name4 int, col_name5 int
insert into find_this values ( 'Find me 1', null, 'and this 1', null, null );
insert into find_this values ( 'Find me 2', null, 'and this 2', null, null );
insert into find_this values ( 'Find me 3', null, 'and this 3', null, null );
alter system checkpoint;

Search the data file for the tablespace the table is in for "Find me" and you'll see something like:

,^A^C       Find me 3<FF>
and this 3,^A^C Find me 2<FF>
and this 2,^A^C Find me 1<FF>
and this 1^A^F

Side note - this is why TDE is important; if someone gets hold of your unencrypted data files they can see the data by opening it up in any editor.

So no, it's not repeating the column names for each row. There's no need to - these are fixed and stored in the data dictionary. Also notice that the last two null values are ignored.

The Concepts Guide has information on block and row structures if you want to know more:


  (1 rating)


John Cantu, May 21, 2024 - 10:04 pm UTC

Thanks, Chris.

It seems like NoSQL seems to seems to overhype "JSON" which is basic field:value. What does Oracle call how it stores it's column and values?
Connor McDonald
May 23, 2024 - 2:23 am UTC

What does Oracle call how it stores it's column and values?

Proprietary :-)

If you want go digging in the weeds, here's a detailed guide