1

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

halfer
  • 19,824
  • 17
  • 99
  • 186
seunofk
  • 11
  • 2
  • Welcome to StackOverflow! I have impression that you did not mention some critical parts. First: why postresql and oracle-fdw in tags? Does that means that you're doing query from postgresql thru foreing data wrapper? Second: "querying using comparison operator >=," - which exactly query you're running? And " index cannot be used in this condition." - how did you understood that? Was that from execution plan? Of oracle or postgresql? – Alex Yu Aug 04 '21 at 00:40
  • Thanks for the welcome. The first is right. I ran the query from PostgreSQL to Oracle via FDW. = used the index when performing the interval value of the date, but a FULL scan occurred in >=. I checked with the PostgreSQL plan, and I wonder why it behaves like this. – seunofk Aug 04 '21 at 01:11
  • A. You mean index on Oracle side, right? You're quering Oracle table or Postgresql ? It's still unclear. B. What will become if you diisect `>=` into equivalent `[your value] > to_char(now() - interval '7' day, 'YYYYMMDD') OR [your value] = to_char(now() - interval '7' day, 'YYYYMMDD') ` ? Overall: it would help others to help you if you will add DDL scripts to your question. Even better if you use [db fiddle](https://www.db-fiddle.com/) for that – Alex Yu Aug 04 '21 at 01:46
  • What I mean is to ask the behavior of the comparison operator when executing a query in Oracle via a db link (oracle_fdw) in PostgreSQL. ## oracle index not running select code,suc_dt from sucdt100 where code='S' and suc_dt >= to_char(now() - interval '7' day, 'YYYYMMDD'); ## oracle index running select code,suc_dt from sucdt100 where code='S' and suc_dt = to_char(now() - interval '7' day, 'YYYYMMDD'); – seunofk Aug 04 '21 at 04:24
  • It would be better if you put it in your question. Read about formatting SO posts: https://stackoverflow.com/editing-help. And don't you think that your question is more oracle than postgresql? Did you tried your query directly on Oracle? I mean without FDW from postgresql – Alex Yu Aug 04 '21 at 12:11
  • As you said, the content was not clear, so I edited the content. – seunofk Aug 05 '21 at 05:23
  • 1
    "*When oracle date type is varchar2*" - why, oh why? But I think the Oracle FDW doesn't push down conditions on varchars because of possible collation problems. –  Aug 05 '21 at 08:44

1 Answers1

1

The problem is that you store your dates as strings.

oracle_fdw will push down = and <> comparisons, but not <, <=, > and >=. This is because such comparisons depend on the collation, and collations can be different in Oracle and PostgreSQL, even if they are both English. That could mean that the query result might be different, depending on whether <= is pushed down or not, which is not acceptable.

Use DATE instead of VARCHAR2. Perhaps you can create an Oracle view that converts the string to a DATE.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263