One of our applications will heavily make use of LOBs in the tables underneath. One of my users asked me if we should keep the LOB-heavy-tables in a separate tablespace.
I don't know what the advantages will be to keep them in a separate tablespace.
Currently they are in a tablespace that has other BIG non-LOB-tables as well and all the tables have been created with the uniform Extent_sizes.
Could anyone point out a few advantages of keeping LOB-tables in a separate tablespace ?
07-20-2001, 04:29 PM
We have LOB fileds in our table. We always keep LOB in separate tablespace than Table. Also, we use DISABLE
STORAGE IN ROW clause for LOB to make sure all contents
of LOB are stored outside table. We have OLTP and some queries do FTS on table. If first 4000 bytes are stored
in table, FTS will be costly.
Let me know if you need more info.
07-20-2001, 04:31 PM
The whole purpose is spilt the disk I/O into multi-disks,
if the datafile for this new tablespace locate on same disk
then there is no difference create sperate tablespace.
07-20-2001, 04:36 PM
the disk i/o thing is true for any objects not just for LOBs.
Could you explain what do you mean by --
"If first 4000 bytes are stored in table, FTS will be costly. "
07-20-2001, 04:45 PM
If you DON'T specify DISABLE STORAGE IN ROW for your LOB
clause, Oracle will put lob contents in row itself. If
length of LOB column becomes more than 4000 bytes, Oracle
will move contents out of row.
If you DISABLE STORAGE IN ROW CACHE, only LOB locator is
stored in table, which takes less space.
Having first 4000 bytes in Row itself will make Full Table
Scan costly for that table.
FTS-->Full Table Scan
07-20-2001, 05:23 PM
Hmmmm. I think it depends on your application. I agree that full table scans would be more costly if part of the information is stored in-line with the table and part of it is stored elsewhere via a LOB locator. But, who designs applications that do full table scans?
We store XML inside CLOBs with the first 4000 bytes in-line with the table and those CLOBs that are over 4000 in a separate tablespace (on a separate disk). We use InterMedia Text to index those CLOBs. So, searching for anything in the XML column results in a hit on the InterMedia index, not the table itself. Then, for most of the rows ids returned from the index data is returned from the table and in a few cases, where the CLOB is over 4000 bytes, a recursive call is made to get the data via the CLOB locator. Much faster.