oracle_fdw select from specific partition

[aymanadou]

Hello, i try to copy data from oracle to postgres via oracle_fdw, i need to accelerate copy by using more than a session i want to isolate each copy from specific partition

From postgres INSERT INTO datavalue SELECT * FROM orclfdw."datavalue" PARTITION (P_20240918);

from oracle SELECT /3911cdf8f9664372c79efa0cb5582fa7/ r3."DATA_TYPE", r3."ID", r3."CONTEXT_ID", r3."KEY", r3."VALUE", r3."VALUEDATE", r3."EXECUTIONDATE", r3."USERPROFILEID", r3."VALUECLOB", r3."VALUEBLOB" FROM "XXXX"."DATAVALUE" r3

why the oracle_fdw did not preserve the partition macro?

[laurenz]

This is a bit confusing.

If you run the following statement in PostgreSQL:

SELECT * FROM orclfdw."datavalue" PARTITION (P_20240918);

the string PARTITION (P_20240918) will be interpreted as a table alias and the alias for the first table column. It will not select data from a single partition. See here:

test=> CREATE TABLE tab (col1 integer, col2 integer);
CREATE TABLE
test=> SELECT * FROM tab PARTITION (P_20240918);
 p_20240918 │ col2 
════════════╪══════
(0 rows)

There is no way to select only from a single partition of the Oracle table.

Perhaps you can define a foreign table based on an SQL statement that uses the Oracle PARTITION clause. Or you could use a WHERE condition that corresponds to the partition boundaries.

[aymanadou]

hello , yes it is confusing , actually, i thought that the query written on postgres to select from oracle should be valide to oracle SQL grammar. Thats why i tried this way:

i am ok with you that we can use the where clause to create more than a foreign table but for hash partition it will be difficult.

[laurenz]

I meant either create a table like

CREATE FOREIGN TABLE (...)
SERVER ... OPTIONS (table '(SELECT ... FROM oratab PARTITION (...))');

or be explicit about the boundaries:

SELECT ... FROM foreign_table
WHERE ts >= '2024-10-16' AND ts < '2024-10-17';

Is there any value in selecting data from a single hash partition?

[aymanadou]

Ok i will create foreign table as many as i have partitions.

my aim is to reduce data copy time from oracle to postgres,to reduce down time when migrating from oracle to postgres. i am actually using ora2Pg (with oracle_fdw), i tuned the prefetch parallelize some tables , but still not satisfied (12h of copy (8h only in this table 80% database size)).

I belive that using partition pruning to isolate data from oracle and to copy it to postgres may help me reducing the copy time if i succed to // copy of partitions

[laurenz]

Copying in parallel has a good chance of being faster. I'd probably go with a single foreign table and query it along the partition boundaries.

Can we close the issue?