Oracle version: 11.2.0.3
When joining local and remote (over DB Link) tables, Oracle has high tendency of downloading all remote records and filter records locally (i.e. HASH join), instead of running the filter in remote DB (with NESTED LOOP join). And I can't get it to filter at remote if UNION is used
Test Setup:
Local DB:
create table union_part1 as
select * from dba_objects
where owner!='SYSTEM';
create index union_part1_idx on union_part1 (object_type);
create table ty
as
select 'DATABASE LINK' ty from dual;
(Note: to reproduce following behaviour, it is needed to use a 'normal' table instead of DUAL)
Remote DB:
create table union_part2
select * from dba_objects
where owner='SYSTEM';
create index union_part2_idx on union_part2 (object_type);
Case 1: No joining, filtered remotely. Can use index of local table [
explain plan for
select * from (
select object_name, object_type, owner from union_part1 a
union
select object_name, object_type, owner from union_part2@REMOT b
) x
where object_type ='DATABASE LINK';
Plan hash value: 1839530724
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1880 | 6 (0)| 00:00:01 | | |
| 1 | VIEW | | 20 | 1880 | 6 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| UNION_PART1 | 7 | 280 | 2 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | UNION_PART1_IDX | 7 | | 1 (0)| 00:00:01 | | |
| 5 | REMOTE | UNION_PART2 | 13 | 1222 | 4 (0)| 00:00:01 | REMOT | R->S |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_TYPE"='DATABASE LINK')
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT "OWNER","OBJECT_NAME","OBJECT_TYPE" FROM "UNION_PART2" "B" WHERE "OBJECT_TYPE"='DATABASE
LINK' (accessing 'REMOT' )
Case 2: Use joins. Download everything then filter, cannot use index of local table
(Note: a SEMI-JOIN has similar result)
explain plan for
select * from (
select object_name, object_type, owner from union_part1 a
union all
select object_name, object_type, owner from union_part2@REMOT b
) x, ty
where object_type =ty;
Plan hash value: 4230951196
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77667 | 8267K| 228 (1)| 00:00:03 | | |
|* 1 | HASH JOIN | | 77667 | 8267K| 228 (1)| 00:00:03 | | |
| 2 | TABLE ACCESS FULL | TY | 1 | 15 | 3 (0)| 00:00:01 | | |
| 3 | VIEW | | 77667 | 7129K| 224 (1)| 00:00:03 | | |
| 4 | UNION-ALL | | | | | | | |
| 5 | TABLE ACCESS FULL| UNION_PART1 | 76358 | 2982K| 220 (1)| 00:00:03 | | |
| 6 | REMOTE | UNION_PART2 | 1309 | 120K| 4 (0)| 00:00:01 | REMOT | R->S |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_TYPE"="TY")
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "OWNER","OBJECT_NAME","OBJECT_TYPE" FROM "UNION_PART2" "B" (accessing 'REMOT'
)
Note
-----
- dynamic sampling used for this statement (level=2)
As a simplified case, if the remote table is not unioned with local table, Oracle still prefer to filter locally, but it is possible to force Oracle to send filter to remote using hints. With the union, I cannot find the correct hint.
Case 3: Join between local and remote table, filter locally without hints
explain plan for
select object_name, object_type, owner
from union_part2@REMOT x, ty
where object_type =ty;
Plan hash value: 2523477457
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1309 | 139K| 8 (13)| 00:00:01 | | |
|* 1 | HASH JOIN | | 1309 | 139K| 8 (13)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL| TY | 1 | 15 | 3 (0)| 00:00:01 | | |
| 3 | REMOTE | UNION_PART2 | 1309 | 120K| 4 (0)| 00:00:01 | REMOT | R->S |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_TYPE"="TY")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OWNER","OBJECT_NAME","OBJECT_TYPE" FROM "UNION_PART2" "X" (accessing
'REMOT' )
Note
-----
- dynamic sampling used for this statement (level=2)
Case 4: Worked around with USE_NL hint
explain plan for
select /*+ use_nl(x ty) */ object_name, object_type, owner
from union_part2@REMOT x, ty
where object_type =ty;
Plan hash value: 2546774085
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1309 | 139K| 11 (37)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1309 | 139K| 11 (37)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL| TY | 1 | 15 | 3 (0)| 00:00:01 | | |
| 3 | REMOTE | UNION_PART2 | 1309 | 120K| 4 (0)| 00:00:01 | REMOT | R->S |
--------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT /*+ USE_NL ("X") */ "OWNER","OBJECT_NAME","OBJECT_TYPE" FROM "UNION_PART2"
"X" WHERE "OBJECT_TYPE"=:1 (accessing 'REMOT' )
Note
-----
- dynamic sampling used for this statement (level=2)