oracle driving site,ORACLE 的DRIVING_SITE HINT 在INSERT AS SELECT中无效

一个朋友问个SQL在本地表连接执行的部分非常快，当执行到最后通过DBLINK连接远程DB表时就很慢(本地执行后记录小于100条然后和远程DB连接。lot@pkgmes lt表1000W 并且有连接字段的PK)。后来在select部分添加hint /*+  DRIVING_SITE (lt) */  后，会把本地表传到远程去比对，子查询速度在1s以内。但是加上前面的insert hint好像是失效了，又变得很慢表和索引的统计信息都是最新的。

其实不光是INSERT AS SELECT中无效CREATE TABLE AS SELECT之类的也会无效

原因：

What happened?  That’s actually expected behaviour, a distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this. DRIVING_SITE hint means that entire cursor (not a subquey) is supposed to be mapped remotely. That also means CREATE TABLE cannot be executed remotely (which is also the reason why you get ORA-2021 when you try to accomplish this with an Create Table table_name@remote_database).

So keep in mind when using the DRIVING_SITE hint this is merely for query optimization and not intended for DML or DDL.

解决办法

Create a view on the remote database (A) and then issue the insert

query by selecting from the view@link_name.

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！