Thread: Any optimizations using oracle_fdw with oracle tables that haveclob columns?
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,
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
Re: Any optimizations using oracle_fdw with oracle tables that haveclob columns?
From
Laurenz Albe
Date:
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
Thanks for the explanation. We will make the best of what we have given the constraints.
Regards,
Craig
Regards,
Craig
On Jun 19, 2020, 1:00 AM -0600, Laurenz Albe <laurenz.albe@cybertec.at>, 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