(small) LOB Design GuideLine for Performance?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: (small) LOB Design GuideLine for Performance?

  1. #1
    Join Date
    Aug 2003
    Posts
    2

    (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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    6. data and lob are accessed separately( ie. data is read first, and SOMETIMES the lob is read)
    ===
    Can you define "SOMETIMES" in terms percentage?

    If the percentage is around 5 to 10, then I would opt out-of-line LOB storage.

    If data is going to be around 4K, you do not need LOB column, you can very well use varchar2.

    Also, you can consider larger block size to store more rows in one block.

    Tamil

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Tamil

    less than 5% of the times the LOB is accessed together with data. Most of the time, it's data first, and then LOB later in a separate query (if at all).

    VARCHAR is no good b/c the LOB can be
    a small GIF file or JPG file. Basically
    it contains binary data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width