Oracle BUG？反连接中dblink访问远程表导致只能filter无法hash_aj的一种情况
版本11.2.0.4.0
构造环境如下：
有A，B两个库。
A库执行以下操作：
create table a1 as select * from dba_objects; create database link link_b connect to zkm identified by zkm using 'b_DB';
B库执行以下操作：
create table b1 as select * from dba_objects;
现有SQL如下：
select object_name from a1 where owner in (select owner from b1@link_b) and object_id not in (select /*+ unnest hash_aj */ object_id from b1@link_b);
在A库对用用户执行：
可以看到，对于反连接是使用filter，无法使用hash_aj，导致效率可能大大降低。
14:47:41 ZKM@test(172)> explain plan for select object_name from a1 where owner in (select owner from b1@link_b) and object_id not in (select /*+ unnest hash_aj */ object_id from b1@link_b); Explained. Elapsed: 00:00:00.01 14:47:45 ZKM@test(172)> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------ Plan hash value: 208854353 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21293 | 2349K| 100K (1)| 00:20:03 | | | |* 1 | FILTER | | | | | | | | |* 2 | HASH JOIN RIGHT SEMI| | 21325 | 2353K| 230 (1)| 00:00:03 | | | | 3 | REMOTE | B1 | 66651 | 1106K| 151 (1)| 00:00:02 | LINK_B | R->S | | 4 | TABLE ACCESS FULL | A1 | 21325 | 1999K| 78 (0)| 00:00:01 | | | | 5 | REMOTE | B1 | 3 | 39 | 150 (0)| 00:00:02 | LINK_B | R->S | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ HASH_AJ UNNEST */ 0 FROM "B1" WHERE LNNVL("OBJECT_ID"<>:B1))) 2 - access("OWNER"="OWNER") Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT "OWNER" FROM "B1" "B1" (accessing 'LINK_B' ) 5 - SELECT "OBJECT_ID" FROM "B1" "B1" WHERE LNNVL("OBJECT_ID"<>:1) (accessing 'LINK_B' ) Note ----- - dynamic sampling used for this statement (level=2) 32 rows selected. Elapsed: 00:00:00.03
但是如果将反连接中远程表需要的信息固化成本地表，就可以使用hash anti join了。
到此觉得像是BUG了。防偷盗
14:51:08 ZKM@eportdb1(172)> create table b1_local as select object_id from b1@link_b; Table created. Elapsed: 00:00:00.05 14:51:26 ZKM@eportdb1(172)> explain plan for select object_name from a1 where owner in (select owner from b1@link_b) and object_id not in (select /*+ unnest hash_aj */ object_id from b1_local); Explained. Elapsed: 00:00:00.06 14:51:57 ZKM@eportdb1(172)> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2086217084 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 21325 | 2623K| 257 (2)| 00:00:04 | | | |* 1 | HASH JOIN RIGHT SEMI | | 21325 | 2623K| 257 (2)| 00:00:04 | | | | 2 | REMOTE | B1 | 66651 | 1106K| 151 (1)| 00:00:02 | LINK_B | R->S | |* 3 | HASH JOIN RIGHT ANTI NA| | 21325 | 2269K| 105 (1)| 00:00:02 | | | | 4 | TABLE ACCESS FULL | B1_LOCAL | 52226 | 663K| 26 (0)| 00:00:01 | | | | 5 | TABLE ACCESS FULL | A1 | 21325 | 1999K| 78 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OWNER"="OWNER") 3 - access("OBJECT_ID"="OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 2 - SELECT "OWNER" FROM "B1" "B1" (accessing 'LINK_B' ) Note ----- - dynamic sampling used for this statement (level=2) 28 rows selected. Elapsed: 00:00:00.02
另外我发现，即使不固化出b1_local，将where条件反连接换个顺序放在第一位也可以走出hash anti join。
https://www.cnblogs.com/PiscesCanon/p/17587617.html
14:53:52 ZKM@eportdb1(172)> explain plan for select object_name from a1 where object_id not in (select /*+ unnest hash_aj */ object_id from b1@link_b) and owner in (select owner from b1@link_b); Explained. Elapsed: 00:00:00.02 14:53:56 ZKM@eportdb1(172)> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 4108339526 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT| ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 118M| 13G| | 1449 (49)| 00:00:18 | | | |* 1 | HASH JOIN | | 118M| 13G| | 1449 (49)| 00:00:18 | | | | 2 | VIEW | VW_NSO_1 | 66651 | 1106K| | 522 (1)| 00:00:07 | | | | 3 | HASH UNIQUE | | 66651 | 1106K| 1584K| 522 (1)| 00:00:07 | | | | 4 | REMOTE | B1 | 66651 | 1106K| | 151 (1)| 00:00:02 | LINK_B | R->S | |* 5 | HASH JOIN RIGHT ANTI NA| | 21325 | 2269K| | 230 (1)| 00:00:03 | | | | 6 | REMOTE | B1 | 66651 | 846K| | 151 (1)| 00:00:02 | LINK_B | R->S | | 7 | TABLE ACCESS FULL | A1 | 21325 | 1999K| | 78 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OWNER"="OWNER") 5 - access("OBJECT_ID"="OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 4 - SELECT "OWNER" FROM "B1" "B1" (accessing 'LINK_B' ) 6 - SELECT "OBJECT_ID" FROM "B1" "B1" (accessing 'LINK_B' ) Note ----- - dynamic sampling used for this statement (level=2) 32 rows selected. Elapsed: 00:00:00.02
19.19版本也是一样的模拟情况。
嗯。。非常神奇。