Post reply

DTS to Oracle database

  • April 6, 2004 at 3:05 pm

    #59822

    I have a DTS program to load a flat file to an Oracle database. It ran very slow. Looks like bulkinsert did not work for inserting data to Oracle tables. Did I miss something? Any suggestions?

    Thanks in advance. 

  • April 7, 2004 at 1:46 am

    #501958

    I have had a similar problem where a bulk load of data was taking three to four minutes in Oracle and the equivalent in SQL Server 7 was 10 seconds. So we had a chat with an Oracle after sales guy who said about using hints on the insert statement and it immediately dropped to 2 seconds. Yes we were impressed.

    Now the annoying bit...I can't remember the syntax we used. Try checking out PL-SQL (Oracles SQL) sites or Oracle.com for relevant sites.

    As a helper you are looking for hints to turn off logging on inserts as Oracle will log every record inserted otherwise, which is the bit that is slowly the data load down.

    When you do find it can you post it on this site please? Cheers.

    Keith

  • April 7, 2004 at 1:53 am

    #501959

    Found it.

    Check out the /* APPEND */ hint at

    http://www.dba-oracle.com/oracle_tips_data_load.htm

    Apparantly makes it 100 times faster.

    Keith

  • April 7, 2004 at 3:52 pm

    #502061

    Thank you very much Keith! It works.

  • July 1, 2004 at 9:23 am

    #512960

    I have the same problem. However, where do you apply the APPEND hint when the MSSQL DTS package is handling the data load?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply