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.
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.
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.