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版本也是一样的模拟情况。
嗯。。非常神奇。