Ever heard of a BAND join? It’s not actually new, it has been around since 12.2. Jonathan Lewis wrote up a nice post on it was back in 2017 but in a nutshell
- MERGE join done without a band join
SQL> select
2 e1.last_name|| ' has salary roughly between '|| e2.last_name comparison
3 from
4 hr.employees e1,
5 hr.employees e2
6 where
7 e1.salary between e2.salary - 100 and e2.salary + 100;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3014 | 72336 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 3014 | 72336 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 107 | 1284 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1284 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 107 | 1284 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1284 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E1"."SALARY"<="E2"."SALARY"+100)
5 - access(INTERNAL_FUNCTION("E1"."SALARY")>="E2"."SALARY"-100)
filter(INTERNAL_FUNCTION("E1"."SALARY")>="E2"."SALARY"-100)
- MERGE join done with a band join
SQL> select
2 e1.last_name|| ' has salary roughly between '|| e2.last_name comparison
3 from
4 hr.employees e1,
5 hr.employees e2
6 where
7 e1.salary between e2.salary - 100 and e2.salary + 100;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 302 | 6644 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 302 | 6644 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 107 | 1177 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 107 | 1177 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 1177 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(INTERNAL_FUNCTION("E1"."SALARY")>="E2"."SALARY"-100)
filter("E1"."SALARY"<="E2"."SALARY"+100 AND
INTERNAL_FUNCTION("E1"."SALARY")>="E2"."SALARY"-100)
Notice the subtle difference in FILTER placement, but you can read Jonathan’s post for more details.
Once you’ve read the post, you’ll probably be convinced that these band joins are a good thing and hence you might be tempted to add the USE_BAND hint to your inequality joins, just like it describes in the docs
There is only one small problem with that approach. That hint doesn’t exist 🙂 If you try this in 19c, you’ll see the following in the Hint Report
SQL> select /*+ USE_BAND(e1 e2) */
2 e1.last_name|| ' has salary roughly between '|| e2.last_name comparison
3 from
4 hr.employees e1,
5 hr.employees e2
6 where
7 e1.salary between e2.salary - 100 and e2.salary + 100;
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - USE_BAND
and the same for NO_USE_BAND. I initially assumed that these hints existed briefly after the feature was introduced in 12.2 just in case anyone encountered a regression, but I can’t find any evidence of the hint in any version of the database from 12.2 onwards and they’ve become the default mechanism since then.
We’ll get those docs fixed 🙂
Ho Ho Ho! Merry Christmas.
Learning is not a spectator sport 
Got some thoughts? Leave a comment