Nine Stars

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: Oracle - What is the best way to bulk export using custom SQL?

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!

4 REPLIES
One Star

Re: Oracle - What is the best way to bulk export using custom SQL?

Hi,
Did you receive an answer for this?
Thanks,
B
Nine Stars

Re: Oracle - What is the best way to bulk export using custom SQL?

No, still waiting...
Fifteen Stars

Re: Oracle - What is the best way to bulk export using custom SQL?

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)?
Rilhia Solutions
Nine Stars

Re: Oracle - What is the best way to bulk export using custom SQL?

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!