Forum Stats

  • 3,770,714 Users
  • 2,253,158 Discussions
  • 7,875,556 Comments

Discussions

Oracle Data Integrator (ODI) - Transfer data from Oracle to PostGreSQL Database

user12033760
user12033760 Member Posts: 5 Red Ribbon
edited Jun 10, 2020 10:04AM in Data Integrator

We would like to transfer around 4 millions rows of data from Oracle Database to PostGreSQL using Oracle Database Integrator (ODI).

At the moment we have around 4 million rows on 600 columns (columns will potential increase in the future) in Oracle and when we transfer them in PostGreSQL it takes around 4 hours. For the transfer of the data we use JYTHON knowledge module in ODI.

4 hours is quite a long time and it is not an acceptable option for what we are doing. We would like someone to provide us with alternative options on how to move this amount of data, using ODI ONLY, from Oracle to PostgreSQL but with reduced time

Thank you

Tagged:

Answers

  • SH_INT
    SH_INT Member Posts: 3,188 Bronze Crown
    edited Jun 10, 2020 10:04AM

    Not sure what the JYTHON knowledge module you are using in ODI does but generally the quickest way to copy large volumes of data into PostGreSQL is to use the copy command (note this requires a csv file as the source). There is a good article on PostGrSQL load performance here which shows how optimising the load of 32,500 records reduced the load time from just over 2 mins for a row by row insert to around 0.5 seconds when using the copy command. Take a look here:

    https://hakibenita.com/fast-load-data-python-postgresql

    All of the techniques could be implemented in ODI