Re: By default, LOB store in line or out of line?
Date: Sun, 22 Apr 2012 18:08:08 +0300
Message-ID: <CAMHX9JKVumLLhM7rZsJAuuEjerJEN3qYQtqGGOAw3p92dHkZVA_at_mail.gmail.com>
If you create the LOB column with DISABLE STORAGE IN ROW, then the LOB data is always stored out-of-line. LOB index is always used. Only the LOB ID is stored inline, and the ID is looked up in LOB index, where you'll get the pointers to actual LOB chunk blocks.
If you create the LOB column with ENABLE STORAGE IN ROW, then the LOB data may be stored in-line or out-of-line.
If the total LOB data + overhead <= 4000 bytes, then the LOB item will be stored in-line. No LOB index is used, even if you modify the LOB later on as everything is stored in-line with the row and versioning/rollback is achieved with undo data.
If the total LOB data + overhead > 4000 bytes, then the LOB item will be stored out-of-line. If the LOB fits into 12 x LOB_chunk_size, then no LOB index entries are created, because the in-line LOB locator can store up to 12 pointers to the LOB chunk blocks for each lob item. So if your LOB chunk size is 8kB, you can store LOB items up to 96kB in size without inserting anything to LOB index. However if the LOB item is bigger, then no pointers are stored in-row and all pointers will be put to the LOB index.
Note that once you modify an existing LOB item (which is bigger than 4000 bytes with its overhead), but smaller than 12 x chunk_size, then LOB index will still be used after the first LOB change operation as pointers to the old LOB chunk versions have to be stored in it (LOB segments don't rely on undo for rollback & consistency, but just use LOB chunk versioning managed by LOB index).
The "overhead" of an in-line LOB item is 36 bytes, so the actual LOB data must be 4000 - 36 = 3964 bytes or less in order to fully fit in-row.
-- Tanel Poder Enkitec Europe http://www.enkitec.com/ Advanced Oracle Troubleshooting v2.0 Seminars in May/June 2012! http://blog.tanelpoder.com/seminar/ On Sun, Apr 22, 2012 at 12:21 PM, Leyi Kamus Zhang <kamusis_at_gmail.com>wrote:Received on Sun Apr 22 2012 - 10:08:08 CDT
> Hi Louis
> Yes, you're correct, I used dbms_lob.getlenghth to get the CLOB column
> length, and my database character set is ZHS16GBK, so the length 2080 I
> mentioned in original mail should require 4160 bytes, it will store out of
> line.
>
> It seemed the 1st paragraph in the doc I mentioned is wrong? By default,
> LOB will store in line, except it's length large than 4000 bytes or
> explicitly using DISABLE STORAGE IN ROW.
>
> I'll check the Tanel's presentation. Thanks for your info.
>
> --
> Kamus <kamusis_at_gmail.com>
>
> Visit my blog for more : http://www.dbform.com
> Join ACOUG: http://www.acoug.org
>
>
> 2012/4/22 louis <ylouis83_at_gmail.com>
>
> > Data in CLOB columns is stored in a format that is compatible with UCS-2
> > when the database character set is multibyte, such as UTF8 or AL32UTF8.
> > This means that the storage space required for an English document
> doubles
> > when the data is converted. Storage for an Asian language document in a
> > CLOB column requires less storage space than the same document in a LONG
> column
> > using UTF8, typically around 30% less, depending on the contents of the
> > document.
> >
> > 在 2012年4月21日 下午11:34，Leyi Kamus Zhang <kamusis_at_gmail.com>写道：
> >
> >> Hi Lists
> >> According to the Doc as below:
> >> *Oracle® Database Application Developer's Guide - Large Objects
> >> 10g Release 2 (10.2)*
> >>
> >> Part Number B14249-01
> >>
> >> In Chapter 4 there are 2 graph：
> >> 1. LOB values are stored out-of-line when any of the following
> situations
> >> apply:
> >>
> >> *By default. That is, if you do not specify a LOB parameter for the LOB
> >> storage clause when you create the table.*
> >>
> >>
> >> 2. ENABLE or DISABLE STORAGE IN ROW Clause
> >>
> >> You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether
> the
> >> LOB should be stored inline (in the row) or out-of-line.
> >>
> >> Note:
> >> You may not alter this specification once you have made it: if you
> ENABLE
> >> STORAGE IN ROW, then you cannot alter it to DISABLE STORAGE IN ROW and
> >> vice
> >> versa.
> >> *The default is ENABLE STORAGE IN ROW.*
> >>
> >>
> >> Document error? Which one is the TRUTH? or I misunderstanding something?
> >>
> >> Based on our test, even every LOB has only 2080bytes(less than
> 4000bytes),
> >> LOB is stored OUT of line, but dba_lobs.in_row=YES.
> >>
> >> --
> >> Kamus <kamusis_at_gmail.com>
> >>
> >> Visit my blog for more : http://www.dbform.com
> >> Join ACOUG: http://www.acoug.org
> >>
> >> --
> >> http://www.freelists.org/webpage/oracle-l
> >>
> >>
> >>
> >
> >
> > --
> > Phone: +86 13918046970
> > Email & Gtalk: ylouis83_at_gmail.com
> > Personal Blog: http://www.vmcd.org
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l