Better time to import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Better time to import

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Better time to import

    I have an enormous file of 1.9 GB (more than 650000 records of Host's BD) that I have to import in an Oracle BD and realize calculations with its fields.
    I have tried to do it with two methods:
    1.-Using utl_file to work directly with the file
    2.-Using sql*loader to import the file to a table and work from this table.
    The two works(the second one a bit more rapid than the first one) but turn out to be very slow (1000 policies per minute) being able to be late the whole process more than 10 hours!
    How I can improve the yield of the process?

    I attach the procedure of the first method and the trigger of the second one


    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    Please post your .ctl file for SQL*loader. Any way you got single 2GB file. USe direct path use skip indexes option . I guess if it is taking 10 hours means you are using conventional path method and you may have several triggers and the table.
    Raghu

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi
    i don't hv any clue of ur business requirement.
    when u IMPORT use DIRECT=Y this is faster then the conventional method
    Cheers!
    OraKid.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's not the load itself that is slowing you up, it's the per-row processing method you are using. Switching to direct load will not help you at the moment because it will not fire the trigger.

    Get rid of the trigger, load the data into your table, then process it in bulk. That is the way that databases run best.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by balajiyes
    hi
    i don't hv any clue of ur business requirement.
    when u IMPORT use DIRECT=Y this is faster then the conventional method
    err, there is no such thing for import

  6. #6
    Join Date
    Aug 2001
    Posts
    267
    Pando ,Probably he is thinking script like this

    sqlldr userid=xxx/xxx control=${FS_SQL}/${CNTL1} \
    log=load_log.file direct = true \
    SKIP_UNUSABLE_INDEXES = true \
    SKIP_INDEX_MAINTENANCE= true >>log.file
    Raghu

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pando
    err, there is no such thing for import
    err, I think he substitued the process (IMPORT) for the program SQL*Loader.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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