如何强制 Oracle 异构查询将 where 子句发送到 ODBC 驱动程序？
我正在使用Oracle 21C XE进行测试。我已经安装了Progress OpenEDGE ODBC驱动程序，并创建了所有内容，包括异质服务所需的数据库链接。当我执行一个简单的“从myview”中执行一个简单的“选择 *”时，即使“ myview”有数十万行，它也相对较快地返回200行。
在相同的VM上，Oracle数据库所在的位置我安装了查询工具（使用ODBC）用于“本机”查询测试和性能比较。
如果我执行；
select * from myview where guid = 1.488657859
从查询工具中，它在.203秒内返回一行，从Oracle使用1.54秒才能使用同一行返回同一行；
select * from myview@data_link where guid = 1.488657859
虽然这个较慢，但并不是完全令人毛骨悚然。但是，如果我执行；
SELECT *
FROM myview
WHERE POST_ASSESS_TIME IS NULL AND
mod_date > SYSDATE -1
从查询工具中，它在.390秒内返回25行，但执行时Oracle似乎永远不会返回。
SELECT *
FROM myview@data_link
WHERE POST_ASSESS_TIME IS NULL AND
mod_date > SYSDATE -1
我已经等了一个多小时。
经过一番研究，我将查询更改为仅返回GUID，然后打开了initodbc.ora的追踪。
HS_FDS_TRACE_LEVEL=4
在我的跟踪文件中，我找到了以下内容的Where子句寻找单个GUID（GUID = 1.488657859），跟踪文件显示此查询已传递给ODBC驱动程序；
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]
显然，这是发送整个查询，包括Whey子句，并且它很快返回。
对于更复杂的查询，该文件将以下文件发送给ODBC驱动程序；
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 ["]
它似乎没有将完整查询发送给Where子句。
它似乎正在检索我要求的内容（GUID）以及处理Oracle DB上的Where子句所需的所有内容，这将导致其检索超过200,000行，并将其带入Oracle将执行过滤器。
有没有办法告诉Oracle将其按照ODBC驱动程序的方式传递？
如果是这样？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
驱动程序将尝试“最佳”发送谓词，但是如果存在不相容性，这通常是由于某种数据类型的不匹配而引起的，那么我们将省略谓词，然后在本地应用。
例如，远程数据库中“ sysdate”的概念可能是未知的。我们不知道远程数据库在哪个时区，它使用的时钟等等等，因此发送日期意味着潜在的虚假结果。在这种情况下，您可以在本地评估Sysdate的价值，然后使用文字值。
有一种机制，可以使用传递机制将整个操作发送到远程数据库。更多详细信息在这里
https://connor-mcdonald.com/2012/ 07/18/dbms_hs_passthroughththe-magic-package/
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/