On Mon, Sep 20, 2021 at 3:18 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > On Sun, 2021-09-19 at 10:28 +0000, Niels Jespersen wrote: > > We are often using the oracle_fdw to transfer data between Postgres > > (version 11+) and Oracle (version 18+). It works great. > > > > However I have a task at hand that requires inserting a few billion rows in > > an Oracle table from a Postgres query. > > > > insert into t_ora (a,b,c) > > select a,b,c from t_pg; > > > > This is driven from a plpgsql stored procedure, if that matters. > > > > I want to optimize the running time of this. But I am unsure of which, if > > any, possibilities there actually is. > > > > Reducing the number of network roundtrips is usually a good way to increase > > throughput. But, how do I do that? > > > > If I could make the Oracle insert direct load, that would usually also > > increase throughput. > > But, is that possible here. There are no constraints defined on the > > destinaton tables. > > The cause of the bad performance for bulk data modifications is that the FDW > API is built > that way: each row INSERTed means a round trip between PostgreSQL and Oracle. >
Just as a note, with PostgreSQL 14 the FDW APIs allow batch insert. It should be possible to update oracle_fdw to take advantage of that as well, right? -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
