Giter Club home page Giter Club logo

Comments (6)

laurenz avatar laurenz commented on August 14, 2024

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.

ckinsey avatar ckinsey commented on August 14, 2024

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_Ais the Oracle table and table_a is the Postgres Foreign Table.

from oracle_fdw.

laurenz avatar laurenz commented on August 14, 2024

Thanks.

Can you also give me the Oracle table definitions and the PostgreSQL foreign table definitions?

from oracle_fdw.

ckinsey avatar ckinsey commented on August 14, 2024

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.

chrullrich avatar chrullrich commented on August 14, 2024

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.

ckinsey avatar ckinsey commented on August 14, 2024

@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)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo 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.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.