Skip to Main Content
  • Questions

  • Fastest way to write binary data onto oracle database.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nitin.

Asked: October 22, 2007 - 5:55 pm UTC

Last updated: October 23, 2007 - 1:28 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Tom,

What would be the datatype of choice in 10g/11g for writing binary data (SAE-J2735 about 900 bytes per message) onto an oracle database with write speed being the main consideration. Expected data collection over the first month is estimated to be 80 GB of raw binary data.
I have the underlying disks configured in a RAID-10 array. Any pointers to tweaking the database parameters for such a load operation would be welcome.

Nitin

and Tom said...

if the amount of raw data does not exceed 2000 bytes per record, the RAW type would be the way to go.

If the amount of raw data exceeds 2000 bytes, then a BLOB is going to be the way to go - and allow for INLINE storage of the blob and consider "caching" of the out of line blobs (if you have access to my book Expert Oracle Database Architecture, I go into the options in great detail)

A raw(2000) will be dealt with just like a varchar2(4000) would be - DBWR writes the data out in the background, you do not wait for it.

A Blob - stored inline up to 4000 bytes - will be the same pretty much as a raw would be - stored on the block and cached, written out in the background.

Unless you do direct path loads of course and 80gig will go in so fast (it is a rather smallish amount of data to load today) that you probably won't find a significant bottleneck here. To "speed" it, you would be looking at parallelizing the operation.

There will not be much in the way of a database parameter here in as much as the proper datatype (use raw if you can, blob otherwise)

Rating

  (1 rating)

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

Comments

Retrieval

Nitin, October 23, 2007 - 11:13 am UTC

Thank You Tom for your valuable opinion.
Would the retrieval speed of the stored data be affected by the datatype at all, RAW vs BLOB?
Tom Kyte
October 23, 2007 - 1:28 pm UTC 

if (you can use raw)
then
   you should use raw
else (implies YOU CANNOT AND ALL DISCUSSION OF RAW IS MOOT)
   you should use blob
end if;


it is that simple. If you can use raw, do it. Else, you cannot use raw, so......


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here