Comments (6)
Strange, I cannot immediately see why that might happen.
Can you tell me the execution plan you get with
EXPLAIN (ANALYZE, VERBOSE) SELECT COUNT(*) FROM table_a LEFT JOIN table_b ON table_a.char_key = table_b.char_key WHERE table_b.data IS NOT NULL;
You need SELECT privileges on V$SQL
and V$SQL_PLAN
for that.
If that is a problem, omit the VERBOSE
.
from oracle_fdw.
Execution plan is as follows:
Aggregate (cost=20192.16..20192.17 rows=1 width=0) (actual time=5462.986..5462.986 rows=1 loops=1)
Output: count(*)
-> Merge Join (cost=20099.66..20179.66 rows=5000 width=0) (actual time=5462.971..5462.971 rows=0 loops=1)
Merge Cond: ((table_a.char_key)::text = (table_b.char_key)::text)
-> Sort (cost=10049.83..10052.33 rows=1000 width=54) (actual time=2303.251..2357.638 rows=140903 loops=1)
Output: table_a.char_key
Sort Key: table_a.char_key
Sort Method: external merge Disk: 2880kB
-> Foreign Scan on public.table_a (cost=10000.00..10000.00 rows=1000 width=54) (actual time=16.870..870.473 rows=140903 loops=1)
Output: table_a.char_key
Oracle query: SELECT /*4cf9caa95b92d7f20e5e94f87b23e6fa*/ "CBTA_PROPERTY_ID" FROM "SYSADM"."TABLE_A"
Oracle plan: SELECT STATEMENT
Oracle plan: INDEX STORAGE FAST FULL SCAN TABLE_A
-> Sort (cost=10049.83..10052.33 rows=1000 width=54) (actual time=2993.286..3018.576 rows=201788 loops=1)
Output: table_b.char_key
Sort Key: table_b.char_key
Sort Method: external sort Disk: 3800kB
-> Foreign Scan on public.table_b (cost=10000.00..10000.00 rows=1000 width=54) (actual time=51.710..1466.675 rows=201788 loops=1)
Output: table_b.char_key
Oracle query: SELECT /*6a78ab8d19af030693e6008583982e1d*/ "EXECTIME", "DATA" FROM "SYSADM"."TABLE_B" WHERE ("DATA" IS NOT NULL)
Oracle plan: SELECT STATEMENT
Oracle plan: TABLE ACCESS STORAGE FULL TABLE_B
Planning time: 78.171 ms
Execution time: 5464.724 ms
Where TABLE_A
is the Oracle table and table_a
is the Postgres Foreign Table.
from oracle_fdw.
Thanks.
Can you also give me the Oracle table definitions and the PostgreSQL foreign table definitions?
from oracle_fdw.
I stripped it down mostly to the concerned columns. Apologies for having to obscure it--the data is confidential to a client of mine.
table_a, Postgres
CREATE FOREIGN TABLE table_a (
CHAR_KEY character varying(18) NOT NULL,
COL_B character varying(15) NOT NULL,
COL_C character varying(100) NOT NULL,
-- ...
) SERVER xxxx OPTIONS (schema 'xxxx', table 'TABLE_A');
TABLE_A, Oracle
CHAR_KEY VARCHAR2 (18 Byte) N
COL_B VARCHAR2 (15 Byte) N
COL_C VARCHAR2 (100 Byte) N
table_b, Postgres
CREATE FOREIGN TABLE table_b (
CHAR_KEY character varying(18) NOT NULL,
DATA float8 NOT NULL,
-- ...
) SERVER xxxx OPTIONS (schema 'xxxx', table 'TABLE_B');
TABLE_B, Oracle
DATA NUMBER (9,6) N
CHAR_KEY VARCHAR2 (18 Byte) N
from oracle_fdw.
According to the (helpfully incompletely anonymized) plan above, you are joining on CBTA_PROPERTY_ID and EXECTIME, did you mean to do that? In the schema extract the column order of table_b vs. TABLE_B is clearly different, too, so it makes sense that oracle_fdw would pick the wrong column to query for.
I'm not sure how there are no "type mismatch"-style errors here, but if oracle_fdw either gets the values from Oracle in string form (because it's an arbitrary-precision numeric type?), or transparently converts the NUMBER to VARCHAR (it always fits into 18 characters), it would "work" except that there are clearly no matches (except accidental ones) for the join.
from oracle_fdw.
@chrullrich, you are correct. The order of columns in the Oracle schema differed from the order in the foreign table definition on the Postgres side.
from oracle_fdw.
Related Issues (20)
- Cannot connect to foreign Oracle server HOT 6
- oracle_fdw cannnot load clobs larger than 21kbytes HOT 66
- cannot make oracle_fdw HOT 12
- Could not open extension control file HOT 7
- Encountered make issue related to missing oci.h file HOT 5
- Speed issue HOT 5
- make command raise error while installing oracle_fdw HOT 24
- Unable to compile oracle_fdw HOT 3
- create extension HOT 8
- Oracle client library (oci.dll) not found - Oracle Fdw HOT 29
- Crash fetching CLOB columns with Oracle Client 21 HOT 9
- QUESTION: Extension runtime dependencies HOT 3
- Import Schema invalid memory alloc HOT 3
- `IMPORT FOREIFN SCHEMA` new `import_views` option HOT 2
- What operational system are supported by Oracle FDW ? Error when compiling in Oracle Linux 9.3 HOT 3
- Oracle_fdw installation issue HOT 11
- `IMPORT FOREIFN SCHEMA don't transfer comments from Oracle HOT 21
- Conversion between Oracle DATE and PostgreSQL TIMESTAMP HOT 9
- Query to Oracle when there are geometry conditions implies that all table are loaded in PostgreSQL. HOT 3
- Not able to create extension oracle_fdw HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from oracle_fdw.