Re: Extracting 2.5 million rows

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
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.com
Received on Thu Jan 07 2010 - 12:38:12 CST

Original text of this message