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?)

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