How to write to a LOB column with high performance in Oracle?

GurcanK
GurcanK used Ask the Experts™
on
Dear Experts,

We have a large table with a BLOB field. A scheduled software is writing data to that BLOB field daily. (Everyday single record is written to the table) The size of Data is about 8 GB. How can we achieve fast writes to BLOB field, is it possible to write parallel to single BLOB field ?

BR
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I don't think you can do a parallel write to a LOB.

Can you provide some additional information?

Are you saying that every day you APPEND something to the BLOB?  Check out DBMS_LOB.WRITEAPPEND.

I assume you cannot change the design to insert a single row daily then possibly a view that returns the ENTIRE set of LOB data?  Maybe even a Materialized View that refreshes after the daily update?

Author

Commented:
We create a record (row) everyday in the table. The table has a BLOB field, there is no append to an existing BLOB field of a row.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Is the row you are inserting 8 Gig or the entire tablesize is 8 Gig?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Just the row (its BLOB field) is about 8 GB.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I still to not understand:
One row is 8 Gig or all rows total up to be 8 Gig?

In other words are you inserting one 8 Gig row every day?

Author

Commented:
Yes, we are inserting one 8 GB row everyday.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
How are you currently loading it?
Are you doing it un chunks or all at once?
Is the file on the database server or streamed across the network or something else?

I have to assume you have already looked at the table definition/storage parameters/etc... and have made sure the LOB pieces are on your fastest disks (and not RAID5).

Author

Commented:
These are good questions, I will talk to our software engineer who wrote the program.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial