Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Lindsay.

Asked: July 05, 2000 - 12:24 pm UTC

Last updated: July 05, 2000 - 12:24 pm UTC

Version: 8i

Viewed 1000+ times

You Asked

Is it important to have LOBs located in a different tablespace than the rest of the data?

What are the performance and/or space issues?

Thanks

and Tom said...

It is not "important". It is an option, a choice to be made, but it is not important to separate them.

A LOB consists of three pieces (talking about non-trivial lobs, lobs > 4k, which are stored out of line). There is

o the lob locator. always stored in the row (in the table). Its a pointer.

o the lob index. organizes all of the chunks of a lob. Its in its own segment separate from the table. that means a full scan of the table will not read this data (only when you dereference the lob will it read this data).

o the lob data itself. these are all of the chunks pointed to by the lob index. These are stored in their own segment.

The lob index and lob segment may reside in their own tablespace separate from the data. You might do this to track storage, review IO hits (we can record IO by file or tablespace -- if you want to know how many reads/writes to a lob segment are happening you would have to put it into its own tablespace). You might do this to spread IO out. You might do this because you like to use uniformally sized extents in tablespaces but would like to use different sized extents for your lob segment vs your table segment. You might do this to spread IO out across more devices.

So, there are reasons you might want to but its not "do or die"


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