Import takes long time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Import takes long time

  1. #1
    Join Date
    Mar 2008
    Posts
    140

    Import takes long time

    hi, why does import takes longer time compared to export?


    thanks,

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    have you thought about what it is actually doing compared to export

  3. #3
    Join Date
    Mar 2008
    Posts
    140

    Import takes long time

    Hi, In my opinion import takes long time due to following factors:

    1. enable constraints
    2. creates tables
    3. creates indexes
    4. executes procedures, functions.

    Iam I right ? Please confirm.


    thanks,

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    It does not executes procedures, functions.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2008
    Posts
    5
    Quote Originally Posted by iris
    hi, why does import takes longer time compared to export?


    thanks,

    Hi,


    It takes longer time because of indexes.
    So Import the database with parameter indexes=no
    import will be fast after that import the indexes.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by syedfarukhali
    ...after that import the indexes.
    huh? Did you mean "build" the indexes after?

    Also, be sure you do not import constraints. Import would try to validate them so is going to be faster if you create constraints after import is complete including the NOVALIDATE parameter.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by syedfarukhali
    Hi,


    It takes longer time because of indexes.
    So Import the database with parameter indexes=no
    import will be fast after that import the indexes.

    Import will anyway build indexes after dataload.. then what's the point?.. only thing that doesnt do is "parallel".. for that use expdp/impdp..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Mar 2008
    Posts
    5
    Quote Originally Posted by abhaysk
    Import will anyway build indexes after dataload.. then what's the point?.. only thing that doesnt do is "parallel".. for that use expdp/impdp..

    Abhay.

    While importing with param index=no indexes will not load in the database
    after data load in the database we can give the param indexfile to create the indexes

    2)If you are really in Hurry give commit=no while importing it will load the data fast but make sure your rollbaack segment must be very huge.

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    Quote Originally Posted by syedfarukhali
    While importing with param index=no indexes will not load in the database
    after data load in the database we can give the param indexfile to create the indexes

    2)If you are really in Hurry give commit=no while importing it will load the data fast but make sure your rollbaack segment must be very huge.

    Be carefull with using no commit, if the table is very large you will blow out your undo and then that table import will rollback and you will have to re-import it, and that sucks.

    I prefer using a buffer and commit to avoid that.

    COMMIT=Y
    BUFFER=12000000

    Something like that helps, you will also avoid the rare instance (IME) where the default buffer is not large enough to swallow the longest column in the table, I've seen that once or twice.
    I remember when this place was cool.

  10. #10
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    you can go for datapump...if you are using the oracle10g.

    Other wise you can follow this steps as the above person said

    COMMIT=Y
    BUFFER=12000000
    just like commit=y you can use nolog=y
    direct=y

    or

    you can use incremental export

    at the time of exporting you can export the indexes in to another file...
    you can get lot of solution in the Google.... :-)
    Last edited by gopu_g; 04-03-2008 at 05:45 AM.

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