Re: Please a parameter to disable undo, like _disable_logging

From: Connor McDonald <hamcdc@xxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Wed, 6 Oct 2004 11:24:21 +0100 (BST)

I can't see why the OP needs to disable anything to load 10,000,000 rows into a 
table.

This test just done on my old clunker laptop:

SQL> create table T tablespace users nologging
  2  as select rownum x from x$ksmmem
  3  where rownum <= 10000000;

Table created.

Elapsed: 00:00:07.69

7 seconds!  No hacking around with underscore parms etc, just a little 
nologging and the job's
done.  I would imagine that even the most mediocre of departmental style test 
servers could
deliver 10mill rows even faster than 7 seconds.

*Creating* great swathes of test data is never a performance problem - what to 
do with it once
you've created it is typically more the challenge.

hth
connor


 --- Tim Gorman <tim@xxxxxxxxxxxxx> wrote: 
> Direct-path or APPEND inserts do avoid undo, except for the space-management
> stuff in the data dictionary.
> 
> Direct-path loads data into TEMPORARY segments which, upon successful
> completion of the load (a.k.a. commit), are converted into DATA segments.
> Upon unsuccessful completion of the load (a.k.a. rollback), the TEMPORARY
> segments are simply dropped.
> 
> Look ma, no undo...
> 
> 
> 
> on 10/5/04 9:33 PM, John Clarke at jclarke@xxxxxxxxxxxxxxx wrote:
> 
> > /*+ append */ disables redo, not undo (as does =5Fdisable=5Flogging).
> > 
> > Array/bulk inserts will reduce the amount of undo you generate, so this =
> > method will help.  But you can't avoid undo altogether.
> > 
> > At least you're not updating or deleting the 10,000,000 rows.
> > 
> > John
> > 
> > ----- Original Message -----
> > From: Juan Carlos Reyes Pacheco <jreyes@xxxxxxxxxxxxxxxx>
> > To: oracle-l@xxxxxxxxxxxxx
> > Sent: Tue,  5 Oct 2004 20:23:01 -0400
> > Subject: RE: Please a parameter to disable undo, like =5Fdisable=5Floggi=
> > ng
> > 
> > 
> >> thanks
> >> =20
> >> but /*+ append */ too bypasses undo, and if you set =5Fdisable=5Floggi=
> > ng you run
> >> the same risk, if you have a problem.
> >> I think for a test database could be acceptable. For example I'm tryin=
> > g to
> >> create a 10 000 000 records in my home for testing
> >> purposes, so this will be greatly welcome.
> >> I bet there should be a way.
> >> =20
> >> =20
> >> Juan Carlos Reyes Pacheco
> >> OCP
> 
> --
> //www.freelists.org/webpage/oracle-l
>  

=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, 
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------


        
        
                
___________________________________________________________ALL-NEW Yahoo! 
Messenger - all new features - even more fun!  http://uk.messenger.yahoo.com
--
//www.freelists.org/webpage/oracle-l

References:
- Re: Please a parameter to disable undo, like _disable_logging
  - From: Tim Gorman

Re: Please a parameter to disable undo, like _disable_logging

Other related posts: