Team,
Was reading about Band join from the below link
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/joins.html#GUID-24F34188-110F-4245-9DE7-43954092AFE0 https://jonathanlewis.wordpress.com/2017/02/13/band-join-12c/ and tried this below test case in my local database, could you please help me to understand, what i am missing here and why the optimizer doesn't pick for band join here.
demo@PDB1> create table t1 as select * from dba_objects;
Table created.
demo@PDB1> create table t2 as select * from dba_objects;
Table created.
demo@PDB1> set autotrace traceonly explain
demo@PDB1> select *
2 from t1, t2
3 where t1.object_id between t2.object_id+5 and t2.object_id+10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1030928244
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1340M| 331G| | 16218 (69)| 00:00:01 |
| 1 | MERGE JOIN | | 1340M| 331G| | 16218 (69)| 00:00:01 |
| 2 | SORT JOIN | | 73222 | 9510K| 27M| 2576 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 73222 | 9510K| | 399 (1)| 00:00:01 |
|* 4 | FILTER | | | | | | |
|* 5 | SORT JOIN | | 73223 | 9510K| 27M| 2576 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T2 | 73223 | 9510K| | 399 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+10)
5 - access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"+5)
filter(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"+5)
Currently band joins only happen if the lower bound uses subtraction and the upper bound is an addition:
set serveroutput off
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
explain plan for
select *
from t1, t2
where t1.object_id between t2.object_id+5 and t2.object_id+10;
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL | T1 |
|* 4 | FILTER | |
|* 5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL| T2 |
-------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+10)
5 - access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"+5)
filter(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"+5)
select *
from table(dbms_xplan.display(null, null, 'BASIC LAST +PREDICATE'));
explain plan for
select *
from t1, t2
where t1.object_id between t2.object_id-5 and t2.object_id+5;
select *
from table(dbms_xplan.display(null, null, 'BASIC LAST +PREDICATE'));
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| T1 |
|* 4 | SORT JOIN | |
| 5 | TABLE ACCESS FULL| T2 |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)
filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+5 AND
INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)