imp with/without precreated tables,which one faster?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: imp with/without precreated tables,which one faster?

  1. #1
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75

    imp with/without precreated tables,which one faster?

    Hi, all,
    I need to reorg my database on sun platform with oracle9.2.0.3,
    I could do
    1, exp and imp
    2, exp and create tables first,then imp the data with IGNORE=Y
    I'd like to know which one is faster, or the same? Thanks for help.

    D.F.
    Still learning

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Of course the import with pre-created tables will be faster.
    At the same time, its not true with pre-created indexes. In fact, its the other way round for indexes.

    But, pre-creating tables will be more attractive for other reasons like, you will have control over the process of they getting created.
    svk

  3. #3
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75
    Thanks very much.
    Still learning

  4. #4
    Join Date
    Feb 2001
    Posts
    295
    Well, in most cases the time to create table structures is not significant when compared with the data loading itself. If you consider the time spent to create tables manually (even if you just run a script, it is a manual intervention) there is no difference between these approaches.

    However, as svk said, indexes on the table may slow down the import process if present. If you use the import to create the tables, the indexes will only be created after the data load, speeding up the process.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What kind of reorg are you doing that requires exp/imp? What about moving the tables and rebuilding the indexes?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75
    Actually, we are migrating to productions from QA,
    I am going to exp the schema(A), and import into another schema(B), then migrate the changes to B. B would have all the production data plus new deloyments. my imp run 3 and half hours. I want to know if I can shorten the time by doing something else such as pre_creating tables in advance.
    Thanks for all your inputs.

    DF
    Still learning

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    You may want to read this, I think I got it from this website many moons ago.

    http://www.oracledba.co.uk/tips/import_speed.htm

    MH
    I remember when this place was cool.

  8. #8
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Friend,
    If your creating the PRODUCTION afresh, why dont u create LMT and import the tables, this would solve you problem of deframentaion.

    regards
    anandkl
    anandkl

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