In my last project we were working on a requirement where huge data (40 million rows) needs to read and for each row we need to trigger a process. As part of design we used multithreading where each thread fetch data for a given partition using Jdbc Cursor with a configurable fetch size. However when we ran the job in the application in the Prod environment, we observed that it is slow as it is taking more time in querying data from database.
As we had very tight time lines on completion of job execution, we have come up with work around where the data is exported from SQL Developer in csv file format and split in to small files. These files are provided to job. This has improved the job performance significantly and helped completing the job on time.
As mentioned above we have used manual step to export the data to the file. If this need to automate this step, executing exporting step from Java App for instance, which one of the below options (which are suggested on the web) will be faster.
- sqlplus (Java making native call to sqlplus)
- sqlcl parallel spool
- pl/sql procedure with utl_file and dbms_parallel_execute
Below link gives some details on the above but does not have stats.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536328100346697722
Please note that currently I don't have access to this Oracle environment, so could not test from my side. Also I am an application developer and don't have much expertise on DB side. So looking for advise from some one who have worked on similar use case earlier or have relevant expertise.
Thanks in advance.
