| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Ever popular LOB question
Thanks Jack. However, Oracle's docs state the opposite - even for lobs > 4000 bytes. I was trying to find this - I knew I had read it before but couldn't find it until 5 minutes ago.
"The default, ENABLE STORAGE IN ROW, is usually the best choice for the following reasons:
Small LOBs: If the LOB is small (< 4000 bytes), then the whole LOB can be read while reading the row without extra disk I/O. Large LOBs: If the LOB is big (> 4000 bytes), then the control information is still stored in the row if ENABLE STORAGE IN ROW is set, even after moving the LOB data out of the row. This control information could enable us to read the out-of-line LOB data faster."
What control information is Oracle storing with enable storage in row that isn't present when the row is explicitely stored out of line? There's still the pointer but is there something more? Seems strange that the preferred way would be "inline" even if it's going to be moved out of line due to size.
Thanks - Brian
JApplewhite_at_austinisd.org wrote:
Explicitly store 'em out-of-line from the get-go. 1. You can manage the LOB segments more directly and sensibly. 2. Queries that just need "regular", non-LOB, data don't have to read as many blocks.
Worked for me with 13 million CLOB documents. Also, almost essential if you ever use interMedia / Oracle Text to index the docs (which we did).
Jack C. Applewhite - Database Administrator Austin (Texas) Independent School District 512.414.9715 (wk) / 512.935.5929 (pager)
The devil made me do it the first time,
The second time I done it on my own.
- Billy Joe Shaver, "Black Rose"
Brian Wisniewski
yahoo.com> cc:
Sent by: Subject: Ever popular LOB question
oracle-l-bounce_at_fr
eelists.org
08/18/2005 12:27
PM
Please respond to
brian_wisniewski
RHAS 3.0/Oracle 9.2.0.6
Is there any benefit to explicitely stating 'out of line' on lobs outside
of rowsize/possible caching issues. I'm going to be storing ~100K lobs -
chunksize defaulted to the blocksize - 8k so obviously these will be out of
line because of their size in this particular table. Once the lobs are
inserted into the database they will not be updated.
I'm not sure of how Oracle will recognize the entire size of the lob when it gets it from the application. Will Oracle start storing it in line w/ associated redo until it reaches ~4k and then move it out of line for every one of these lobs? That would seem like an expensive operation but I'm not really sure of the behind the sceens actions of what Oracle is doing.
I'm trying to set up some tests with our apps folks but was wondering if anyone had any insight regarding explicit out-of-line vs in-line migrated to out-of-line because of size.
When we were dealing with smaller lobs we experienced decrease performance when we had explicitely used out-of-line. But those lobs at the time would have been a mix of in-line and out-of-line so I don't consider that valid given the difference in lob sizes.
Test, test, test - I know - just having 'challenges' with developers. I also want to test different chunksizes but time is critical right now.
Any thoughts/experiences you'd like to share regarding lobs?
Thanks - Brian
--
http://www.freelists.org/webpage/oracle-l
 |
 |