Re: CLOB

Ah, I forgot one important issue about LOB performance -> if you select a varchar or long, the data in those is returned to client instantly, but if you select a LOB, then only a pointer (locator) is returned and it's up to client whether it sends a reading request to server to get actual contents of LOB. This means additional sqlnet roundtrips for each LOB item.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, September 24, 2003 5:04 AM

> Hi!
>
> > Basically making a column type as Clob - instead of varchar2(4000) - how
> > much extra overhead (as far as bytes/size) will it add to the table
size?
>
> When creating table with clob columns, you can specify whether you can
allow
> storing of small lobs inline with the actual data row. The clob data
maximum
> inline limit is 3964 bytes (4000 bytes - 20 bytes for lob locator -16
bytes
> for lob inode structure), anything larger will go to external lob segment.
> Note that for varying-width characterset clobs, Oracle converts data to
> 2-byte fixed character set, so with UTF-8, a letter 'X' will be stored as
> hex value 00 58 or 58 00 to the datablock, depending on which platform you
> are. If you store your 'X' into normal varchar field, it'll be stored just
> as hex value 58, thus in one byte.
> So, with variable-width charsets you can actually store only 1982
characters
> inline with lob column. Note that nclobs are a bit different, they can
have
> even longer "byte-length". Also they always seem to be stored in little
> endian format, even on Intel platform (that means 00 58 for 'X' not 58 00
> like with regular clob).
>
> Note that there's a difference between "enable storage in row" lob which
is
> forced to store its lob item out-of-line (because it's size >3964 bytes)
and
> regular "disable storage in row" lob. In first case, more space is used in
a
> row because inode structure, which points directly to physical lob
location.
> But native out-of-line lobs created with "disable storage in row" do have
> only lob locator inline, thus needing additional sequential IOs for
scanning
> lob index which directs them to lob item physical location. In other
words,
> inline lobs don't normally need lob index scanning.
>
> >
> > I have a table with 6000 rows, and about 500 of them have 1 column with
> more
> > that 4000 chars, so they need to go to a clob column. now I just want to
> > know if I make a clob column and put the bigger column into clob for all
> the
> > 6000 rows, how much disk space would I waste by doing this, or would
> Oracle
> > use the space efficient enough on the clobs so that I shouldn't worry
> about
>
> With so small table you don't actually have to worry about size at all.
> Anyway, depending on your data and character set, your table might grow
> because lob locator&inode structures are bigger than varchar2 control
> structure (only 1 or 3 bytes stating it's length) and if using
> variable-width charsets, your inline data will probably grow. On the other
> hand, large lobs will be stored in lob segment. But: space in lob segments
> is allocated in chunks, which are multiplies of Oracle blocks. Thus if you
> have 8kB chunk size for lobs, then every out-of-line lob item will take at
> least 8k. If lobs size is 9000 bytes, then it takes 16k and so on
(situation
> changes a bit with big lobs).
>
> So, overall size of your data will grow.
>
> > size ? Other question is it true that Clobs are generally slower even
the
> > data in them are small ?
>
> Querying inline clobs shouldn't be much slower, if you are using indexed
> access to your table. But since you have very big rows, your buffer cache
> might get saturated, since in normal envrionment you'd have tens to
hundreds
> of rows in a block, now you got only few. Full table scans and analyzing
> will of course be slower, since table is much bigger.
>
> Sorry for answering a simple question with too long answer, I got carried
> away...
> Tanel.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).