Re: CTAS for tables with big lob segment (out of line) very slow

I have workaround the issue with:

alter table move subpartition xxx lob ()... parallel 4 nologging;

Using this approach, moving the lob table is taking 10-15 minutes, and with CTAS/exchange, the CTAS is using 2-3 hours. I did not do the actual table move, I moved the test table which I get from CTAS from subpartition of the original table.

We did not use ASSM tablespace. We use traditional freelist management within LMT tablespace;

I am still trying to figure out why CTAS and move has such huge performance difference;

PCTVERSION can help reduce the snapshot too old issue for LOB, but it comes with more space usage.

And My question is not how to resolve the snapshot too old, but knowing, whether it is non-lob data causing snapshot too old, or the LOB segmetn causing snapshot too old. I have not figured it out yet.

Insert /*+ append**/ provide the same performance as CTAS, my colleague did the test with similar performance as CTAS.

On 8/7/06, Nuno Souto <dbvision_at_iinet.net.au> wrote:
>
> Hemant K Chitale wrote,on my timestamp of 6/08/2006 2:53 PM:
>
>
> Cheers
> Nuno Souto
> in sunny Sydney, Australia
> dbvision_at_iinet.net.au
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards
Zhu Chao
www.cnoug.org

--
http://www.freelists.org/webpage/oracle-l