RE: LOB's -- How Many Out of Line?

  • From: "Larry Elkins" <elkinsl@xxxxxxxxx>
  • To: "Brian Wisniewski" <brian_wisniewski@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Jan 2005 19:44:10 -0600

I'm unclear on whether or not an out of line LOB does (or should) get
registered in CHAIN_CNT. It seems like I remember coming across some
documents that were contradictory (and I need to do some more research on
Metalink). But take a look at the following example, executed on
and (I will repeat on an and an DB when I get back
to work).
SQL> create table chain_clob (x number, y clob) ;

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   v varchar2(32000) := rpad('X',32000,'X');
  3  begin
  4  insert into chain_clob values (1,v);
  5* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> analyze table chain_clob compute statistics;

Table analyzed.

SQL> select chain_cnt from dba_tables where table_name = 'CHAIN_CLOB';


SQL> select dbms_lob.getlength(y) from chain_clob;


SQL> select in_row from dba_lobs where table_name = 'CHAIN_CLOB';


You might get different results on other versions. I wonder how this plays
on other versions.

Regardless, I really appreciate your taking the time to respond and offer

Larry G. Elkins

  -----Original Message-----
  From: Brian Wisniewski [mailto:brian_wisniewski@xxxxxxxxx]
  Sent: Wednesday, January 26, 2005 6:58 PM
  To: elkinsl@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
  Subject: RE: LOB's -- How Many Out of Line?

  If you're looking for a rough estimate as to how many are out of line you
can check the chained row count, assuming your rows aren't migrating due to
other reasons.    However, with in-line lobs you can easily migrate your
rows if you're storing a ~3K lob so it's not really reliable but it's a lot
faster than doing a count(*) on your table where dbms_lob.getlength > 3964.

  - Brian


Other related posts: