Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Thirumalaisamy.

Asked: June 07, 2023 - 7:37 am UTC

Last updated: June 07, 2023 - 1:33 pm UTC

Version: 12.2.0.1.0 - 64bit

Viewed 1000+ times

You Asked

Hi Tom,
Is it advisable to have more BLOB fields in a table? (Planning to have 20), Each BLOB field will be stored with small amount of data per row.
table may have 1.5 million rows.
what will be disadvantages (if any) if we have more BLOB fields in a table.


and Chris said...

If you have 20 different binary attributes to store, you have to put them somewhere. Assuming they're all required for every row, start with the 20 columns and test to see if it meets your performance requirements. If it doesn't come back with your findings and we can discuss alternatives.

If you're sure the binary data will be "small" you could use RAW instead. This has an upper limit of 2,000 bytes (32,767 if you have enabled extended data types).

That said, this is unusual. Are you sure you need 20 columns? Could you create a child table with a row/binary attribute? e.g. something like:

create table t ( id int primary key );

create table t_binary ( 
  id    references t,
  attr# varchar2(10),
  bdata blob,
  primary key ( id, attr# )
);


This is likely more appropriate - particularly if the BLOB attributes are optional and will be mostly null for most rows. It really comes down to what you're storing and why.

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

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.