To copy a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: To copy a table

  1. #1
    Join Date
    Sep 2000
    Posts
    103
    Can you tell me which is the fastest method to copy a table ?

    Can I just do a create table as select * from table; will this generate any redo info. The table I'm trying to copy has around 6millions rows.
    Or do I do an export ? or do I use a copy command?

    Which do you think will be ideal. This is to copy a table within the same database.


    Thanks a lot

    pst.

  2. #2
    Join Date
    Aug 2000
    Posts
    143
    you may be able to use the nologging option to stop redo being produced.

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi,

    You could use direct load insert.

    Create table new_table
    (col1 varchar1 ....... .. . .... ...)

    Insert /*+ append */' into new_table
    select * from old table

    you can stop redo generation by issuing

    alter table table name nologging; before direct load insert.

    Direct load insert bypasses buffer cache and goes straight to datafile so is faster.

    You can also do a parallel direct load insert, spawning multiple server processes to speed up the task further.

    Suresh




    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Oct 2001
    Posts
    122

    Thumbs up

    ..that's a clean solution.

    Try parallel load it's lot faster. Make sure to understand the
    concept and settings first otherwise it could go other way around too.

    Have a fun.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width