Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Raivis.

Asked: October 23, 2019 - 8:16 pm UTC

Last updated: October 24, 2019 - 7:11 am UTC

Version: Oracle 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

We have a big table where we store LOB documents.
Table size ~9TB
Users have a possibility to insert new records, update existing LOBS, delete old ones.

I would like to know the best practices for LOB storage in the table, where performance would be ok and users would be allowed to do frequent DML operations with those LOBs.

Currently, table store LOBS in the old way (BASIC LOB, not SECURE FILES)

Are there maybe some good examples of how we should store LOBS in big tables?

BR,
Raivis

and Connor said...

Two things I would be looking at

1) partitioning. Big tables (whether they have lobs or not) are harder to manage. Managing multiple smaller partitions is often a lot easier. If you are new to partitioning, start here

https://asktom.oracle.com/partitioning-for-developers.htm

2) definitely look at securefile. It is the best way moving forward for lobs. Some information of how to migrate you can get from my blog:

https://connor-mcdonald.com/?s=securefile

and also, you can see this post for a mechanism of doing it piecemeal

https://asktom.oracle.com/pls/apex/asktom.search?tag=move-basic-lob-to-securefile-lob


For the existing setup, the only things you really want to be concerned about are enable/disable storage in row and the retention settings. This depends on the average size of your lobs, your database blocksize and other things.

Rating

  (1 rating)

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

Comments

Raivis Voitkuns, October 24, 2019 - 7:37 am UTC

Thanks for fast response, definitely will check links.

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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database