I learn something new every day...

Ok, time for a geek (eg: having a smidge of technical content) post.  What I learned new today about Oracle that I don't think I knew about before.  Notice that I said "that I don't think I knew", there is a chance I did - but forgot.

In a nutshell - LOBS are always out of line in an index organized table unless you have an overflow segment defined.  And that fact may have a massive impact on your performance during inserts, retrievals and the total amount of storage used.

ops$tkyte%ORA10GR2> create table t
  2  ( x int primary key,
  3    y clob
  4  )
  5  organization index
  6  lob (y) store as
  7  ( enable storage in row )
  8  /
create table t
*
ERROR at line 1:
ORA-22853: invalid LOB storage option specification

So, we cannot special enable storage in row here at all - not without an OVERFLOW clause:  

ops$tkyte%ORA10GR2> create table t
  2  ( x int primary key,
  3    y clob
  4  )
  5  organization index
  6  lob (y) store as
  7  ( enable storage in row )
  8  overflow
  9  /
Table created.

ops$tkyte%ORA10GR2> insert into t
  2  select object_id,
  3         object_name
  4    from all_objects;
50047 rows created.
Elapsed: 00:00:03.68

ops$tkyte%ORA10GR2> select segment_name,
  2         bytes
  3    from user_segments;

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_LOB0000065089C00002$$           65536
SYS_IOT_TOP_65089                 4194304
SYS_IL0000065089C00002$$            65536
SYS_IOT_OVER_65089                  65536

About 3 and 1/2 seconds, about 4 MB of data - however, if we just "default" - don't specify the overflow: 

ops$tkyte%ORA10GR2> drop table t purge;
Table dropped.

ops$tkyte%ORA10GR2> create table t
  2  ( x int primary key,
  3    y clob
  4  )
  5  organization index
  6  lob (y) store as
  7  ( disable storage in row )
  8  /
Table created.

ops$tkyte%ORA10GR2> insert into t
  2  select object_id,
  3         object_name
  4    from all_objects;
50046 rows created.
Elapsed: 00:02:13.66

ops$tkyte%ORA10GR2> select segment_name, bytes
  2    from user_segments;

SEGMENT_NAME                        BYTES
------------------------------ ----------
SYS_IL0000065094C00002$$          3145728
SYS_IOT_TOP_65094                 2097152
SYS_LOB0000065094C00002$$       419430400

As you can see - when we skip the overflow clause - the LOB will be stored out of line - regardless of size.  That means each row will touch the lob index and maintain it and each row will allocate at least a chunk of storage (even though we only need 30 characters at most in this example).  So, we go from 3 and 1/2 seconds roughly using inline LOBS to 2 and 1/4 minutes when the LOB is out of line.  Additionally - we go from 4 MB total storage (all of it in the IOT) to over 400 MB (due in part to my chunk size, 8k in this case - as small as possible) spread across the IOT, the lob segment and the lob index.

All of this came about due to a customer saying "sqlldr is really slow against tables with LOBS".  Slowly peel away the onion to find it was IOT's with LOBS (not just tables), peeling further back to recognize the default (and only) storage for a LOB in and IOT without an overflow is "disable storage in row" - and there you go.  "The reason".

Lots of layers on some onions.