CODEWITHSUNDEEP
oracle-databasedatabase-partitioninglob
thesquaregroot
thesquaregroot

Reputation: 1452

Is partitioning necessary for LOB columns?

My boss has a concern that creating LOB columns in our database tables could result in effectively permanent storage overhead if we do not partition the table and drop the partitions over time (as opposed to just deleting the records). He thinks that data for the LOB will be stored separately when the record is created, which seems to be correct, but that Oracle may not delete the data for the LOB column when the row is deleted.

Is this correct or even possible? I find it hard to believe that the DBMS would handle object storage so poorly. We are using Oracle 11g if that changes anything.

Upvotes: 0

Views: 1934

Answers (1)

thesquaregroot
thesquaregroot

Reputation: 1452

I eventually found this ask tom question which covers the same topic.

When the database creates an extent to store the LOBs it is associated directly with the table. When rows containing those LOBs are deleted, the LOBs are removed from the extent which remaining the same size (it does not shrink just because a LOB was removed). However, the table is able to reuse the space from the removed LOB as one would expect.

So while the database at large is not able to reuse the free space in an extent created for a table's LOB storage, the space IS free for use by the table. The extent will exist (and therefore take up space that would otherwise be completely available to the database) until table is dropped or truncated, or the extent is explictly deallocated.

In the end, unless the extent(s) remaining allocated to the table is a concern (or there are other concerns that may be addressed through partition), creating partitions solely for this purpose is unnecessary.

Upvotes: 0

Related Questions