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

From: Randolf Geist <>
Date: Wed, 29 Jul 2009 02:32:44 -0700 (PDT)
Message-ID: <>

> 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 :).

Do you think that accessing 3 million rows out of 70 million rows (1/24th of the table) via index is faster than doing a full table scan? I would doubt that in most cases... What is the clustering factor of that index used? You might be faster with a simple FTS.

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

What do you mean by "subsequent inserts"? As part of the conversion, of after the conversion when using the new table instead of the old? Please elaborate and specify more details.

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

So you mean to say the converted table segment has 300G size. What about the LOB segment, what's the size of the associated LOB segment?

300k extents is definitely a lot. If you were going for an AUTOALLOCATE tablespace, you would end up with a lot less number of extents, something about less than 5,000 I think given the allocation rules used by AUTOALLOCATE. Most of your extents were then 64M. Sometimes it's mentioned that it might even use 256M extents, but I've never seen them myself.


Oracle related stuff blog:

SQLTools++ for Oracle (Open source Oracle GUI for Windows): Received on Wed Jul 29 2009 - 04:32:44 CDT

Original text of this message