Re: Extracting 2.5 million rows
Date: Thu, 7 Jan 2010 18:38:12 -0000
Message-ID: <1KOdnReIv_GMt9vWnZ2dnUVZ8vydnZ2d_at_bt.com>
"Mladen Gogala" <no_at_email.here.invalid> wrote in message news:pan.2010.01.07.16.58.25_at_email.here.invalid...
> On Thu, 07 Jan 2010 10:37:08 +0000, Jonathan Lewis wrote:
>
>> how about:
>> create audit_clone nologging
>> as
>> select (required columns) from audit_table_at_remote ?
>
> That's going to do wonders on the remote rollback segments.
>
>
>
> --
> http://mgogala.byethost5.com
It shouldn't generate any more undo and redo on "remote" than the export; and if you're worried about that you should also be worried about the changes of the export failing with ORA-01555 anyway.
True, the remote select will acquire a transaction table slot,
and stop one undo segment from wrapping until the CTAS
is complete - and that can have unpleasant side effects (like
the example I blogged about
http://jonathanlewis.wordpress.com/2009/10/07/undone/ ),
so it will be worth testing how fast the CTAS can run with
a "rownum" limit to get an estimate of how long the entire
operation might take.
Bear in mind, the CTAS may operate faster than the select used by exp (and that's something worth testing), and it's always possible that the analysis of the audit_table doesn't need all the columns - and the CTAS can be selective on columns, unlike the export which has to select all columns.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Thu Jan 07 2010 - 12:38:12 CST