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

Improve this question
7
  • 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? Commented Aug 4, 2021 at 0: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. Commented Aug 4, 2021 at 1: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 for that Commented Aug 4, 2021 at 1: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'); Commented Aug 4, 2021 at 4:24
  • 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. Commented Aug 5, 2021 at 8:44

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.