Oracle - What is the best way to bulk export using custom SQL?
Normally I work in Teradata, I am trying to find the Oracle equivilent of a tTerdataFastExport I have multiple Oracle tables I need to join to get the data I want to bulk export. Is tOracleInput the only component I can use to send the SQL statement? Is there a faster component that I should use?
So what do you want to do with it? I want to dump the data to a .csv delimited file.
Also, why is the tOracleInput not fast enough? It's slower than when I export from a Teradata database and slower than when I export from Oracle SQL Developer
What sort of speeds are you getting? It took over 4.5 hours
How many rows are you dealing with? 9.4 million rows with a single column
Are you using the "Use Cursor" option (which actually allows you to set the fetch size of the data...this can aid in performance)? Yes, but I didn't increase the cursor size enough. I increased the cursor size to 1 million and the export is done in 17 seconds!
The tOracleInput should be OK for this. You could use the tOracleRow to make use of the Oracle bulk export functionality, but that would bring further complexity depending on what you want to do with the data. So what do you want to do with it? Also, why is the tOracleInput not fast enough? What sort of speeds are you getting? How many rows are you dealing with? Are you using the "Use Cursor" option (which actually allows you to set the fetch size of the data...this can aid in performance)?
So what do you want to do with it? I want to dump the data to a .csv delimited file.
Also, why is the tOracleInput not fast enough? It's slower than when I export from a Teradata database and slower than when I export from Oracle SQL Developer
What sort of speeds are you getting? It took over 4.5 hours
How many rows are you dealing with? 9.4 million rows with a single column
Are you using the "Use Cursor" option (which actually allows you to set the fetch size of the data...this can aid in performance)? Yes, but I didn't increase the cursor size enough. I increased the cursor size to 1 million and the export is done in 17 seconds!
