From: legrand legrand <legrand_legrand@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Fwd: increase insert into local table from remote oracle table preformance
Date: Tue, 14 Aug 2018 15:28:34 -0700 (MST)
Message-ID: <1534285714771-0.post@n3.nabble.com> (raw)
In-Reply-To: <CA+t6e1meSMh3AcaPOJumMHUxVr6D=n-76HyFW=tbsumDwrszbg@mail.gmail.com>
References: <CA+t6e1m7HJ=2p6kK=M2JrJpUQek3zW6gGt5cOT9-qdDvqa_Qpw@mail.gmail.com>
<1534194238024-0.post@n3.nabble.com>
<CA+t6e1meSMh3AcaPOJumMHUxVr6D=n-76HyFW=tbsumDwrszbg@mail.gmail.com>
main ideas are:
- inserting directly to the right partition:
perform as many inserts as pg partitions found in main_table_hist, like
INSERT INTO 14/08/2018_value1 select * from remote_oracle_hist where
day=to_date('14/08/2018','DD/MM/YYYY') and value='value1'
please check execution plan (in Oracle db) using EXPLAIN ANALYZE
- all those inserts should be executed in // (with 4 or 8 sql scripts)
- wal archiving should be disabled during hist data recovery only (not
during day to day operations)
- for prefetch see
https://github.com/laurenz/oracle_fdw
prefetch (optional, defaults to "200")
Sets the number of rows that will be fetched with a single round-trip
between PostgreSQL and Oracle during a foreign table scan. This is
implemented using Oracle row prefetching. The value must be between 0 and
10240, where a value of zero disables prefetching.
Higher values can speed up performance, but will use more memory on the
PostgreSQL server.
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: legrand_legrand@hotmail.com
Subject: Re: Fwd: increase insert into local table from remote oracle table preformance
In-Reply-To: <1534285714771-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox