search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
477 stars 155 forks source link

Too slow when using comparison operations in oracle's queries. #485

Closed seunofk closed 2 years ago

seunofk commented 2 years ago

Hi.

When oracle date type is varchar2,

When executing a query in PostgreSQL through oracle_fdw

querying using comparison operator >=, index cannot be used in this condition.

index cannot be used

>= to_char(now() - interval '7' day, 'YYYYMMDD')
Output: a.date, a.cusno
Filter: ((a.date)::date >= ((now() - '07 days'::interval day))::date)
                    Oracle query: SELECT /*a05802d21e2d4cec93da21f1abf9ffbb*/ r1."DATE", r1."CUSNO" FROM "ORACLEDB"."CXXXINFO" r1 WHERE (r1."CUSNO" = '12345')
                    Oracle plan: SELECT STATEMENT
                    Oracle plan:   TABLE ACCESS FULL CXXXINFO  (filter "R1"."CUSNO"='12345')

But = in this condition, index is used.

index be used.

= to_char(now() - interval '7' day, 'YYYYMMDD')
Output: a.date, a.cusno
Oracle query: SELECT /*b7552642e64dd971ba5293a42d581661*/ r1."DATE", r1."CUSNO"
WHERE (r1."CUSNO" = '12345') AND (r1."DATE" = to_char(((CAST (:now AS
TIMESTAMP WITH TIME ZONE)) - INTERVAL '07 00:00:00.000000' DAY(9) TO
SECOND(6)), 'YYYYMMDD'))
Oracle plan: SELECT STATEMENT
Oracle plan:   TABLE ACCESS BY INDEX ROWID CXXXINFO
Oracle plan:     INDEX SKIP SCAN CXXXINFO_01 (condition "R1"."CUSNO"='12345' AND "R1"."DATE"=TO_CHAR(CAST(:NOW AS
TIMESTAMP WITH TIME ZONE)-INTERVAL'+000000007 00:00:00.000000' DAY(9)
TO SECOND(6),'YYYYMMDD'))(filter "R1"."DATE"=TO_CHAR(CAST(:NOW AS TIMESTAMP WITH TIME > ZONE)-INTERVAL'+000000007 00:00:00.000000' DAY(9) TO > SECOND(6),'YYYYMMDD'))

May I know what is the difference?

I am currently using version oracle 19.0.2

PostgreSQl oracle_fdw 2.2

and PostgreSQL 12.7

Wait for your reply.

thank you.

laurenz commented 2 years ago

Sorry for the late response, I was on vacation.

The reason is that a.date is a string data type (text or varchar), and "greater" or "less" conditions like >= are not pushed down for such data types. This is because there is no guarantee that the comparison will do the same thing in Oracle and in PostgreSQL, since the collations used are different. This could lead to wrong results.

If you cannot use the proper data type DATE in Oracle, perhaps you could use to_date in the query and use a functional index in Oracle (I didn't test that).

laurenz commented 2 years ago

Is there anything still unclear? Can I close the issue?