Re: long to clob conversion redo size and new table size

>>         I've got 300GB table t1 with 2 columns:
>>
>> id number primary key,
>> some_data long
>>
>> currently 70 mln rows.
>> When I'm doing to_lob conversion via:
>>
>> insert /*+ APPEND NOLOGGING */ into t2 select id, to_lob(some_data)
>> some_data from t1 where id between 1 and 2222222;
>>
>> table t2 clob is defined as 8kb chunk in MSSM tablespace (uniform size 1M)
>>
>> a 63MB redolog is switched every 18 seconds.
>> My question is what is in redo ?
>> The only think which comes to my mind is lob index maintenance but
>> is that true ? Database is 9.2.0.8 and NOARCHOVELPG mode.
>> Second question :
>> How to minimize ne wtable (t2) size (table is insert/select only).
>>
>> Regards.
>> Grzegorz

>
> 2. LOBs are a different matter though:
>
> You haven't said how you've created the LOB:
> - ENABLE/DISABLE STORAGE IN ROW: Affects significantly how the LOB
> will generate UNDO/REDO
> - (NO)CACHE (NO)LOGGING: Affects significantly how the LOB will
> generate REDO (and affects caching & I/O as well)
>
> If you enabled storage in row, then all LOBs that are less than 3964
> bytes will be stored in-row and the table's attributes regarding UNDO/
> REDO generation apply (the other LOB-specific attributes don't apply
> in this case)

Thank You for great explanation. I've figured out that lob segment needs nocache nologging combo for minimizing redo size and Im using in rows storage.
Now the redo is minimal and I'm experiencing direct write/read waits only . I've got range partitioned table with clob and number (primary key) columns and doing insert from select /*+ index(hint) */ id, to_lob(dane) from long_table where pk between range1 and range2 (I've defined 24 ranges so 24 separate inserts with index hint on select) . When I am done with inserts a primary key will be added and few renames close the case :).

Few more questions come to my mind:
Subsequent inserts take longer .
I've observed about 10% overtime is that normal.

Table after conversion got about 300k 1M extents in uniform sized MSSM tablespace is that ok from performance point of view ?

Regards.
Grzegorz Received on Tue Jul 28 2009 - 13:25:19 CDT