Any optimizations using oracle_fdw with oracle tables that have clob columns?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Any optimizations using oracle_fdw with oracle tables that have clob columns?

Hotmail
Hi All,

We are moving a table (with almost a billion rows)  to Postgres from Oracle using oracle_fdw. We have discovered that there is a single row fetch limitation in oracle when pulling a row that contains a clob column into Postgres. 

My question is, other than running multiple oracle_fdw parallel threads to speed up the data load are there any other optimizations that we may be unaware for oracle_fdw when working with Oracle clob columns?

Any suggestions would be appreciated,

Craig Jackson
Reply | Threaded
Open this post in threaded view
|

Re: Any optimizations using oracle_fdw with oracle tables that have clob columns?

Laurenz Albe
On Thu, 2020-06-18 at 09:54 -0600, Hotmail wrote:
> We are moving a table (with almost a billion rows)  to Postgres from Oracle using oracle_fdw.
> We have discovered that there is a single row fetch limitation in oracle when pulling a row
> that contains a clob column into Postgres.
>
> My question is, other than running multiple oracle_fdw parallel threads to speed up the data
> load are there any other optimizations that we may be unaware for oracle_fdw when working
> with Oracle clob columns?

No, this is a limitation of Oracle:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/using-sql_statements-in-oci.html#GUID-7AE9DBE2-5316-4802-99D1-969B72823F02
"Prefetching is not in effect if LONG, LOB or Opaque Type columns (such as XMLType) are part of the query."

I could change the code to *not* use automatic prefetching and rather explicitly fetch
rows in bundles, but so far I have been too lazy to complicate the code with that.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



Reply | Threaded
Open this post in threaded view
|

Re: Any optimizations using oracle_fdw with oracle tables that have clob columns?

Hotmail
Thanks for the explanation. We will make the best of what we have given the constraints.

Regards,

Craig
On Jun 19, 2020, 1:00 AM -0600, Laurenz Albe <[hidden email]>, wrote:
On Thu, 2020-06-18 at 09:54 -0600, Hotmail wrote:
We are moving a table (with almost a billion rows) to Postgres from Oracle using oracle_fdw.
We have discovered that there is a single row fetch limitation in oracle when pulling a row
that contains a clob column into Postgres.

My question is, other than running multiple oracle_fdw parallel threads to speed up the data
load are there any other optimizations that we may be unaware for oracle_fdw when working
with Oracle clob columns?

No, this is a limitation of Oracle:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/using-sql_statements-in-oci.html#GUID-7AE9DBE2-5316-4802-99D1-969B72823F02
"Prefetching is not in effect if LONG, LOB or Opaque Type columns (such as XMLType) are part of the query."

I could change the code to *not* use automatic prefetching and rather explicitly fetch
rows in bundles, but so far I have been too lazy to complicate the code with that.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com