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

From: joel garry <joel-garry_at_home.com>
Date: Mon, 27 Jul 2009 13:17:44 -0700 (PDT)
Message-ID: <de4f7282-f5d4-4c48-8d59-fea3992c2d56_at_12g2000pri.googlegroups.com>

On Jul 27, 12:16 pm, Grzegorz <grzegor..._at_interia.pl> wrote:
> Mark D Powell pisze:
>
>
>
> > On Jul 27, 12:19 pm, Grzegorz <grzegor..._at_interia.pl> wrote:
> >> Hi,
> >>         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
>
> > LOB data is forward recoverable just like any other data.  The LOB
> > inserts are being logged.
>
> Even with NOLOGGING hint ?
> How to minimize redo size in that case ?
> Regards.
> Grzegorz

Check to be sure force logging hasn't been set for the database or tablespace. The cache option implicitly overides nologging - did you define your table with it in the tablespace clause? A transported tablespace will switch from nologging to logging. If you use inline LOBs, that will use redo even with nologging, see http://www.oracle.com/technology/products/database/application_development/pdf/lob_performance_guidelines.pdf

jg

--
_at_home.com is bogus.
http://www3.signonsandiego.com/stories/2009/jul/25/microsoft-yields-eu-allows-web-browser-choice/?uniontrib

Received on Mon Jul 27 2009 - 15:17:44 CDT