One of many optimizer enhancements that
appeared in Oracle 12cR2 for sql is the “Band join” that makes certain type of
“merge join” much more efficient.
demo@ORA12C> create table t1
2 as
3 select a.*, rownum as id
4 from all_objects a
5 where rownum <= 10000;
Table created.
demo@ORA12C> create table t2
2 as
3 select a.*, rownum as id
4 from all_objects a
5 where rownum <= 10000;
Table created.
Now consider the following query that
joins two tables of 10,000 rows each using a range predicates on a column which
happen to be a unique with sequential values – though we have no
constraint/index in place.
select *
from t1, t2
where t1.id between t2.id -1
and t2.id + 2;
Here are the two execution plans from 11g
(11.2.0.4) and 12c (12.2.0.1) database.
demo@ORA11G> select *
2 from t1, t2
3 where t1.id between t2.id -1
4 and t2.id + 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1030928244
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
25M| 4390M| |
890 (43)| 00:00:11 |
| 1 | MERGE JOIN |
| 25M| 4390M|
| 890 (43)| 00:00:11 |
| 2 | SORT JOIN |
| 10000 | 898K| 2552K|
258 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | T1 | 10000 |
898K| | 42
(0)| 00:00:01 |
|* 4 | FILTER | |
| | | |
|
|* 5 | SORT JOIN |
| 10000 | 898K| 2552K|
258 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL| T2 | 10000 |
898K| | 42
(0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -
filter("T1"."ID"<="T2"."ID"+2)
5 -
access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
filter(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
demo@ORA12C> select *
2 from t1, t2
3 where t1.id between t2.id -1
4 and t2.id + 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 40000 |
9687K| | 677
(1)| 00:00:01 |
| 1 | MERGE JOIN |
| 40000 | 9687K| |
677 (1)| 00:00:01 |
| 2 | SORT JOIN |
| 10000 | 1210K| 3656K|
338 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 1210K|
| 57 (2)| 00:00:01 |
|* 4 | SORT JOIN |
| 10000 | 1210K| 3656K|
338 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T2 | 10000 |
1210K| | 57
(2)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -
access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
filter("T1"."ID"<="T2"."ID"+2
AND
INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
The query returns 40000 rows in the
output, expect for the values at the extreme ends of the range each, 10K rows
in T1 will join with 4 rows (avg) in T2.
Notice how the FILTER that appeared in
the setp#4 in Oracle 11g database has disappeared in Oracle 12cR2 plans, and
the filter predicate that it used to hold is now part of the filter predicate
of the SORT JOIN that has been promoted to the setp#4 in the new plan.
The merge join operates like this – for each
row returned by the SORT JOIN at step#2 it calls step#4 – in 11g example will
then call step#5 so the SORT JOIN there happens 10K times (probing the sorted
result set in local memory 10K times)
So in 11g database, the setp#5 probes the
local in-memory dataset starting at the point where T1.ID
>= T2.ID-1 – having found the
first point in the in-memory set where the access predicate is true, Oracle
will walk through the list passing each row back to the FILTER operation as
long as the access predicate is still true and it will be true until the end of
the list. As each row arrives the FILTER operation oracle checks to see if the
filter predicate there is true and passes the row up to the MERGE JOIN
operation if it is. We know that each cycle of FILTER operation will start
returning false after returning 4 rows from SORT JOIN operation – but Oracle
doesn’t. It sends a total of 50M to the FILTER operation and discarded.
In 12cR2 database, Oracle has
re-engineered the code to eliminate the FILTER operation and test both part of
the range predicates (between clauses) in the same subroutine it uses to probe
and scan the row source. In 12.2 the SORT JOIN clause will pass only four rows
up to the MERGE JOIN operation and stop scanning on the fifth row it reaches.
In our example that was an enormous (CPU) savings in subroutine calls and
tests.
demo@ORA11G> @script.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID as368c5f9cgxc, child number 0
-------------------------------------
select * from
t1, t2 where t1.id between t2.id -1 and
t2.id + 2
Plan hash
value: 1030928244
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
39996 |00:00:25.92 | 278 |
| 1 |
MERGE JOIN |
| 1 | 25M|
39996 |00:00:25.92 | 278 |
| 2 |
SORT JOIN | |
1 | 10000 | 10000 |00:00:00.02 | 139 |
| 3 |
TABLE ACCESS FULL | T1 | 1 |
10000 | 10000 |00:00:00.01 | 139 |
|* 4 |
FILTER |
| 10000 | |
39996 |00:00:25.89 | 139 |
|* 5 |
SORT JOIN | |
10000 | 10000 | 50M|00:00:19.83 |
139 |
| 6 |
TABLE ACCESS FULL| T2 | 1 |
10000 | 10000 |00:00:00.01 | 139 |
---------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
filter("T1"."ID"<="T2"."ID"+2)
5 -
access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
filter(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
25 rows
selected.
demo@ORA12C> @script.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID as368c5f9cgxc, child number 0
-------------------------------------
select * from
t1, t2 where t1.id between t2.id -1 and
t2.id + 2
Plan hash
value: 412793182
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
39996 |00:00:00.06 | 410 |
| 1 |
MERGE JOIN | |
1 | 40000 | 39996 |00:00:00.06 | 410 |
| 2 |
SORT JOIN | |
1 | 10000 | 10000 |00:00:00.02 | 205 |
| 3 |
TABLE ACCESS FULL| T1 | 1 |
10000 | 10000 |00:00:00.01 | 205 |
|* 4 |
SORT JOIN | | 10000 | 10000
| 39996
|00:00:00.04 | 205 |
| 5 |
TABLE ACCESS FULL| T2 | 1 |
10000 | 10000 |00:00:00.01 | 205 |
--------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)
filter(("T1"."ID"<="T2"."ID"+2
AND
INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1))
23 rows selected.
And the Tkprof from 11g and 12c database
shows this.
11g database:
select *
from t1, t2
where t1.id
between t2.id -1
and t2.id + 2
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.00 0.00 0 0 0 0
Execute 1
0.00 0.00 0 0 0 0
Fetch 268
24.89 25.03 0
278 0 39996
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 270
24.89
25.03 0 278 0 39996
Misses in
library cache during parse: 0
Optimizer mode:
ALL_ROWS
Parsing user
id: 91
Number of plan
statistics captured: 1
Rows (max) Row Source Operation
----------
---------------------------------------------------
39996
MERGE JOIN (cr=278 pr=0 pw=0
time=890 us starts=24986311 cost=890 size=4603680368 card=25020002)
10000
SORT JOIN (cr=139 pr=0 pw=0 time=258 us starts=25996 cost=258
size=920000 card=10000)
10000
TABLE ACCESS FULL T1 (cr=139 pr=0 pw=0 time=42 us starts=4157 cost=42
size=920000 card=10000)
39996
FILTER (cr=139 pr=0 pw=0 time=0
us starts=24949817)
50014999
SORT JOIN (cr=139 pr=0 pw=0 time=258 us starts=19168333 cost=258
size=920000 card=10000)
10000
TABLE ACCESS FULL T2 (cr=139 pr=0 pw=0 time=42 us starts=3500 cost=42 size=920000
card=10000)
12c database:
select *
from t1, t2
where t1.id
between t2.id -1
and t2.id + 2
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.00 0.00 0 0 0 0
Execute 1
0.00 0.00 0 0 0 0
Fetch 268
0.03 0.11 0 402 8 39996
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 270
0.03
0.11 0 402 8 39996
Misses in
library cache during parse: 0
Optimizer mode:
ALL_ROWS
Parsing user
id: 108
Number of plan
statistics captured: 1
Rows (max) Row Source Operation
----------
---------------------------------------------------
39996
MERGE JOIN (cr=402 pr=0 pw=0
time=65577 us starts=1 cost=677 size=9920000 card=40000)
10000
SORT JOIN (cr=201 pr=0 pw=0 time=19356 us starts=1 cost=338 size=1240000
card=10000)
10000
TABLE ACCESS FULL T1 (cr=201 pr=0 pw=0 time=3630 us starts=1 cost=57
size=1240000 card=10000)
39996
SORT JOIN (cr=201 pr=0 pw=0 time=42567 us starts=10000 cost=338
size=1240000 card=10000)
10000
TABLE ACCESS FULL T2 (cr=201 pr=0 pw=0 time=3285 us starts=1 cost=57
size=1240000 card=10000)
One the other note, this band join is
applicable only for range predicates involving constants (using binds or
literals) – but not for expression involving column names something like this.
demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select *
2 from
t1, t2
3
where t1.id between t2.id - t2.object_id
4
and t2.id + t2.object_id ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 1030928244
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 250K| 59M|
| 713 (6)| 00:00:01 |
| 1 |
MERGE JOIN | |
250K| 59M| |
713 (6)| 00:00:01 |
| 2 |
SORT JOIN | | 10000 |
1210K| 3656K| 338
(1)| 00:00:01 |
| 3 |
TABLE ACCESS FULL | T1 | 10000
| 1210K| |
57 (2)| 00:00:01 |
|* 4 | FILTER |
| | |
| | |
|* 5 |
SORT JOIN | | 10000 |
1210K| 3656K| 338
(1)| 00:00:01 |
| 6 |
TABLE ACCESS FULL| T2 | 10000
| 1210K| |
57 (2)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
filter("T1"."ID"<="T2"."ID"+"T2"."OBJECT_ID")
5 - access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-"T2"."OBJECT_ID")
filter(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-"T2"."OBJECT_ID")
demo@ORA12C>
set autotrace off
demo@ORA12C>
Addendum: added on 3/31/2020
Currently the band join is only possible if the lower bound uses
subtraction and the upper bound is an addition
c##rajesh@QES1>
select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------------------------------
Oracle Database
19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
c##rajesh@QES1>
create table t1 as select * from all_objects;
Table created.
c##rajesh@QES1>
create table t2 as select * from all_objects;
Table created.
c##rajesh@QES1>
set autotrace traceonly explain
c##rajesh@QES1>
select *
2
from t1, t2
3
where t1.object_id between
4
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 |
| 1129M| 281G| |
12389 (62)| 00:00:01 |
| 1
| MERGE JOIN
| | 1129M|
281G| | 12389 (62)| 00:00:01 |
| 2
| SORT JOIN
| | 67219 | 8796K|
25M| 2380 (1)| 00:00:01 |
| 3
| TABLE ACCESS FULL | T1 | 67219 |
8796K| | 369 (1)|
00:00:01 |
|* 4
|
FILTER
| |
| | |
| |
|* 5
| SORT JOIN
| | 67220 | 8796K|
25M| 2380 (1)| 00:00:01 |
| 6
| TABLE ACCESS FULL| T2 | 67220 |
8796K| | 369 (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)
c##rajesh@QES1>
select *
2
from t1, t2
3
where t1.object_id between
4
t2.object_id - 5 and t2.object_id + 10;
Execution Plan
----------------------------------------------------------
Plan hash
value: 412793182
------------------------------------------------------------------------------------
| Id |
Operation | Name |
Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0
| SELECT STATEMENT |
| 972K| 248M|
| 4762 (1)| 00:00:01 |
| 1
| MERGE JOIN
| | 972K|
248M| | 4762 (1)|
00:00:01 |
| 2
| SORT JOIN
| | 67219 | 8796K|
25M| 2380 (1)| 00:00:01 |
| 3
| TABLE ACCESS FULL| T1 | 67219 |
8796K| | 369 (1)|
00:00:01 |
|* 4
| SORT JOIN
| | 67220 | 8796K|
25M| 2380 (1)| 00:00:01 |
| 5
| TABLE ACCESS FULL| T2 | 67220 |
8796K| | 369 (1)|
00:00:01 |
------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4
-
access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)
filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+10
AND
INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)
c##rajesh@QES1>
set autotrace off
No comments:
Post a Comment