(small) LOB Design GuideLine for Performance?
Most articles that i have read dealt with LOB (large) and discuss the pros and cons of Storage In Row vs Out of Row.
While it all makes sense but my question is regarding storage of small lob
Oracle suggests that small ( 4000 bytes or less) be stored in row but the penalty is larger row size and is not ideal if
1. the lob isn't read at the same time as the data
2. range scan is used to search for data on the table.
But moving those small LOBs out of line requires that the LOBs stored as chunk (== 1 block size). In essence on a 8K block size db, a 10 bytes BLOB will still be stored as 8K.
(i've attempted this and my 2GIG in row table turns into 9GIG out of row LOB)
So i am curious as to how one would design a table with lob under the following constraint.
1. minimize storage space
2. table is range scan often
3. table has 1 lob column and will have upto 20 Million rows
3. 95% of the lobs are mostly 1-3K in size , the remaining 5% (but not often) lobs can be 100K - 3Mb or NULL
4. 9i R2 Oracle on Win2K , 8K Block size
5. Lobs are mostly write once, readonly after
6. data and lob are accessed separately( ie. data is read first, and SOMETIMES the lob is read)
I've looked into using multiple block size option in 9i but not sure how that
will help
My last resort is to split the table int o 2 tables where the LOB is stored inline in the 2nd table.
This way it solves the range scan issue
as well as the space issue. Will this will introduce 1 extra I/O per insert compared to the single table scheme ?
One for FOO and one for FOOLOB ?
e.g. Old table
create table FOO (
FOOPKEY NUMBER
col 1 ,
..
col n ,
lobColumn BLOB
)
New table
create table FOO (
FOOPKEY NUMBER
col 1 ,
..
col n
)
create table FOOLOB (
FOOPKEY NUMBER <<- fkey to FOO
lobColumn BLOB
)
Thanks and sorry for the long post.
Tom
