Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: March 25, 2020 - 3:10 pm UTC

Answered by: Chris Saxon - Last updated: March 27, 2020 - 10:26 am UTC

Category: Database Development - Version: 12.2.0.1

Viewed 100+ times

You Asked

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)

and we said...

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)

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.