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