From: | legrand legrand <legrand_legrand(at)hotmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Fwd: increase insert into local table from remote oracle table preformance |
Date: | 2018-08-14 22:28:34 |
Message-ID: | 1534285714771-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | dangal | 2018-08-15 01:00:00 | Re: How to revoke privileged from PostgreSQL's superuser |
Previous Message | Bruce Momjian | 2018-08-14 20:24:03 | Re: pg_upgrade failing with error pg_resetxlog no such file |
From | Date | Subject | |
---|---|---|---|
Next Message | Mariel Cherkassky | 2018-08-15 08:43:11 | Re: Fwd: increase insert into local table from remote oracle table preformance |
Previous Message | Andres Freund | 2018-08-14 19:31:18 | Re: Calculating how much redo log space has been used |