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

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 8.1.7.0.0 and 9.2.0.1.0 (I will repeat on an 8.1.7.3 and an 9.2.0.5 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';

 CHAIN_CNT

         0

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

DBMS_LOB.GETLENGTH(Y)

                32000

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

IN_
---

YES 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 suggestions.
Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

  -----Original Message-----
  From: Brian Wisniewski [mailto:brian_wisniewski_at_yahoo.com]   Sent: Wednesday, January 26, 2005 6:58 PM   To: elkinsl_at_flash.net; oracle-l_at_freelists.org   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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 26 2005 - 20:39:45 CST