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.

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据