How can I force an Oracle Heterogeneous query to send the where clause to the ODBC driver?

Question

I am using Oracle 21c XE for testing. I have installed a Progress Openedge ODBC driver, and created everything including a DB link needed for Heterogeneous Services. When I execute a simple "select * from myview" it returns 200 rows relatively quickly even when "MyView" has a few hundred thousand rows.

On the same VM where Oracle database resides I installed Query Tool (using ODBC) to use for "native" query testing and performance comparison.

If I execute;

select * from myview where guid = 1.488657859 

from Query Tool it returns one row in .203 seconds, from Oracle it takes 1.54 seconds to return the same row using;

select * from myview@data_link where guid = 1.488657859

While this slower, it is not totally egregious. However if I execute;

SELECT * 
FROM myview
WHERE POST_ASSESS_TIME IS NULL AND
      mod_date > SYSDATE -1

from Query Tool it returns 25 rows in .390 seconds but Oracle never seems to return when I execute;

SELECT * 
FROM myview@data_link
WHERE POST_ASSESS_TIME IS NULL AND
      mod_date > SYSDATE -1

I have waited over an hour.

After a bit of research I changed the queries to only return the guid, and I turned on tracing in initODBC.ora;

HS_FDS_TRACE_LEVEL=4 

In my trace file I found the following for the where clause looking for a single guid (guid=1.488657859) the trace file shows this query is passed to odbc driver;

SQL text from hgopars, id=1, len=69 ...
     00: 53454C45 43542041 312E2247 55494422  [SELECT A1."GUID"]
     10: 2046524F 4D202246 4C4F5753 48454554  [ FROM "MYVERYOWN]
     20: 5F564945 57222041 31205748 45524520  [_VIEW" A1 WHERE ]
     30: 41312E22 47554944 223D312E 34383836  [A1."GUID"=1.4886]
     40: 35373835 39                          [57859]

This is obviously sending the entire query including the where clause and it returns quickly.

For the query with the more complex where file it sends the following to the odbc driver;

SQL text from hgopars, id=1, len=65 ...
     00: 53454C45 43542022 47554944 222C224D  [SELECT "GUID","M]
     10: 4F445F44 41544522 2C22504F 53545F41  [OD_DATE","POST_A]
     20: 53534553 535F5449 4D452220 46524F4D  [SSESS_TIME" FROM]
     30: 2022464C 4F575348 4545545F 56494557  [ "MYVERYOWN_VIEW]
     40: 22                                   ["]

It does not appear to send the full query with the where clause.

It appears to be retrieving what I requested (guid) as well as everything needed to process the where clause on the Oracle DB which will cause it to retrieve over 200,000 rows and bring them into Oracle where it will perform the filter.

Is there a way of telling Oracle to pass the where clause as is to the ODBC driver?

If so how?

Answer 1

The driver is going to try "its best" to send predicates through, but if there is an incompatibility, which is typically due to some sort of data type mismatch then we will omit the predicate and then apply it locally.

For example, the concept of "SYSDATE" is probably unknown in your remote database. We don't know what timezone the remote database is in, what clock it uses etc etc, so sending over a date means potentially spurious results. In such as case, you could evaluate the value of sysdate locally, and then use the literal value.

There is a mechanism of sending an entire operation to the remote database using a passthrough mechanism. More details here

https://connor-mcdonald.com/2012/07/18/dbms_hs_passthroughthe-magic-package/