title: Oracle 10g small Blob or Clob not being stored inline?
body: <p>According to the documents I've read, the default storage for a CLOB or BLOB is inline, which means that if it is less than approx 4k in size then it will be held in the table.</p>
  
  <p>But when I test this on a dummy table in Oracle (10.2.0.1.0) the performance and response from Oracle Monitor (by Allround Automations) suggest that it is being held outwith the table.</p>
  
  <p>Here's my test scenario ...  </p>
  
  <pre><code>create table clobtest ( x int primary key, y clob, z varchar(100) )  
  ;
  insert into clobtest 
     select object_id, object_name, object_name  
     from all_objects where rownum &lt; 10001  
  ;
  select COLUMN_NAME, IN_ROW 
  from user_lobs 
  where table_name = 'CLOBTEST'  
  ;
  </code></pre>
  
  <p>This shows: Y  YES  (suggesting that Oracle will store the clob in the row)</p>
  
  <pre><code>select x, y from CLOBTEST where ROWNUM &lt; 1001 -- 8.49 seconds  
  select x, z from CLOBTEST where ROWNUM &lt; 1001 -- 0.298 seconds  
  </code></pre>
  
  <p>So in this case, the CLOB values will have a maximum length of 30 characters, so should always be inline.  If I run Oracle Monitor, it shows a LOB.Length followed by a LOB.Read() for each row returned, again suggesting that the clob values are held outwith the table.  </p>
  
  <p>I also tried creating the table like this  </p>
  
  <pre><code>create table clobtest 
      ( x int primary key, y clob, z varchar(100) ) 
      LOB (y) STORE AS     (ENABLE STORAGE IN ROW)  
  </code></pre>
  
  <p>but got exactly the same results.  </p>
  
  <p>Does anyone have any suggestions how I can force (persuade, encourage) Oracle to store the clob value in-line in the table?  (I'm hoping to achieve similar response times to reading the varchar2 column z)  </p>
  
  <p>UPDATE: If I run this SQL  </p>
  
  <pre><code>select COLUMN_NAME, IN_ROW, l.SEGMENT_NAME, SEGMENT_TYPE, BYTES, BLOCKS, EXTENTS 
  from user_lobs l 
        JOIN USER_SEGMENTS s
         on (l.Segment_Name = s. segment_name )
  where table_name = 'CLOBTEST'  
  </code></pre>
  
  <p>then I get the following results ...  </p>
  
  <pre><code>Y   YES SYS_LOB0000398621C00002$$   LOBSEGMENT  65536   8   1  
  </code></pre>