一个朋友问个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.