Skip to Main Content
  • Questions
  • How much overhead is caused by adding a mostly empty BLOB column to a table?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Morten.

Asked: January 23, 2018 - 7:19 am UTC

Last updated: January 24, 2018 - 12:19 am UTC

Version: 11g XE

Viewed 1000+ times

You Asked

Let's say I have a large-ish (a few hundred thousand rows) table that contains file names, file sizes and other meta-information for files, but not the actual files themselves.

I then decide to add a BLOB column to the table to make it possible to store actual files in the table. The existing rows will not have BLOB data added (the column will be NULL) and not all new insert will have BLOB data either.

Question is, how much overhead is incurred by adding this BLOB column to the table and leaving it mostly empty for the majority (more than 95%) of the rows? Do the empty columns add any storage overhead? What about "select *" queries that now include the BLOB column? Any other considerations? (Am I better off creating a separate child table that stores the BLOB data with a foreign key back to the original table?)

and Connor said...

The storage cost of adding a new column is zero.

SQL> create table tx
  2  as select rownum x from dual
  3  connect by level <= 1000000;

Table created.

SQL>
SQL> select bytes from dba_segments where segment_name = 'TX';

     BYTES
----------
  13631488

SQL>
SQL> alter table tx add b blob;

Table altered.

SQL>
SQL> select bytes from dba_segments where segment_name = 'TX';

     BYTES
----------
  13631488


It is only when you start populating that data that you will consume space.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database