By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ranges_table as
select
level as id
, level*2000 as from_num
, (level + 1)*2000 - 1 as to_num
from dual
connect by level < 100
99 rows affected
create table other_table as
select
level as id
, lpad(level, 10, '0') as val1
, rpad(level, 10, '0') as val2
from dual
connect by level < 1000000
999999 rows affected
select max(count_) from (
select /*+gather_plan_statistics*/
rt.id as range_id,
count(ot.id) as count_
from ranges_table rt
left join other_table ot
on rt.from_num <= ot.id
and rt.to_num >= ot.id
group by rt.id
)
| MAX(COUNT_) |
|---|
| 2000 |
select *
from dbms_xplan.display_cursor(
format => 'ALLSTATS LAST'
)
| PLAN_TABLE_OUTPUT |
|---|
| SQL_ID 5vq0fx28u7ak0, child number 0 |
| ------------------------------------- |
| select max(count_) from ( select /*+gather_plan_statistics*/ rt.id as |
| range_id, count(ot.id) as count_ from ranges_table rt left join |
| other_table ot on rt.from_num <= ot.id and rt.to_num >= ot.id group |
| by rt.id ) |
| Plan hash value: 1049176303 |
| -------------------------------------------------------------------------------------------------------------------------------------- |
| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | |
| -------------------------------------------------------------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:13.84 | 4429 | 4425 | | | | |
| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:13.84 | 4429 | 4425 | | | | |
| | 2 | VIEW | | 1 | 99 | 99 |00:00:13.84 | 4429 | 4425 | | | | |
| | 3 | HASH GROUP BY | | 1 | 99 | 99 |00:00:13.84 | 4429 | 4425 | 1230K| 1230K| | |
| | 4 | MERGE JOIN OUTER | | 1 | 9088K| 198K|00:03:27.85 | 4429 | 4425 | | | | |
| | 5 | SORT JOIN | | 1 | 99 | 99 |00:00:00.01 | 2 | 1 | 9216 | 9216 | 8192 (0)| |
| | 6 | TABLE ACCESS FULL | RANGES_TABLE | 1 | 99 | 99 |00:00:00.01 | 2 | 1 | | | | |
| |* 7 | FILTER | | 99 | | 198K|00:00:01.90 | 4427 | 4424 | | | | |
| |* 8 | SORT JOIN | | 99 | 999K| 89M|00:00:14.51 | 4427 | 4424 | 21M| 1714K| 19M (0)| |
| | 9 | TABLE ACCESS FULL| OTHER_TABLE | 1 | 999K| 999K|00:00:00.13 | 4427 | 4424 | | | | |
| -------------------------------------------------------------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 7 - filter("RT"."TO_NUM">="OT"."ID") |
| 8 - access("RT"."FROM_NUM"<="OT"."ID") |
| filter("RT"."FROM_NUM"<="OT"."ID") |
with ranges_unpivot as (
/*Transform from_ ... to_... to the event-like structure*/
select
id
, val
, val_type
from ranges_table
unpivot(
val for val_type in (from_num as '01_START', to_num as '03_END')
)
union all
/*Append the rest of the data to the event stream*/
select
null,
id,
/*
This should be ordered between START mark and END mark
to process edge cases correctly
*/
'02_val'
from other_table
where id <= (select max(to_num) from ranges_table)
)
select max(count_) from (
select /*+parallel(4) gather_plan_statistics*/ *
from ranges_unpivot
match_recognize (
order by val asc, val_type asc
measures
start_.id as range_id,
count(values_.val) as count_
pattern (start_ values_* end_)
define
start_ as val_type = '01_START',
| MAX(COUNT_) |
|---|
| 2000 |
select *
from dbms_xplan.display_cursor(
format => 'ALLSTATS LAST'
)
| PLAN_TABLE_OUTPUT |
|---|
| SQL_ID 5mjc0mzb8mwg0, child number 0 |
| ------------------------------------- |
| with ranges_unpivot as ( /*Transform from_ ... to_... to the |
| event-like structure*/ select id , val , val_type from |
| ranges_table unpivot( val for val_type in (from_num as |
| '01_START', to_num as '03_END') ) union all /*Append the rest |
| of the data to the event stream*/ select null, id, /* |
| This should be ordered between START mark and END mark to |
| process edge cases correctly */ '02_val' from other_table |
| where id <= (select max(to_num) from ranges_table) ) select max(count_) |
| from ( select /*+parallel(4) gather_plan_statistics*/ * from |
| ranges_unpivot match_recognize ( order by val asc, val_type asc |
| measures start_.id as range_id, count(values_.val) as count_ |
| pattern (start_ values_* end_) define start_ as val_type = |
| '01_START', values_ as val_type = '02_val', end_ as val_type = |
| '03_END' ) ) |
| Plan hash value: 3946352968 |
| --------------------------------------------------------------------------------------------------------------------------------------- |
| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | |
| --------------------------------------------------------------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.27 | 4431 | 4424 | | | | |
| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.27 | 4431 | 4424 | | | | |
| | 2 | VIEW | | 1 | 50198 | 99 |00:00:00.27 | 4431 | 4424 | | | | |
| | 3 | MATCH RECOGNIZE SORT | | 1 | 50198 | 99 |00:00:00.27 | 4431 | 4424 | 6573K| 1032K| 5842K (0)| |
| | 4 | VIEW | | 1 | 50198 | 200K|00:00:00.14 | 4431 | 4424 | | | | |
| | 5 | UNION-ALL | | 1 | | 200K|00:00:00.05 | 4431 | 4424 | | | | |
| |* 6 | VIEW | | 1 | 198 | 198 |00:00:00.01 | 2 | 0 | | | | |
| | 7 | UNPIVOT | | 1 | | 198 |00:00:00.01 | 2 | 0 | | | | |
| | 8 | TABLE ACCESS FULL| RANGES_TABLE | 1 | 99 | 99 |00:00:00.01 | 2 | 0 | | | | |
| |* 9 | TABLE ACCESS FULL | OTHER_TABLE | 1 | 50000 | 199K|00:00:00.02 | 4429 | 4424 | | | | |
| | 10 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | | |
| | 11 | TABLE ACCESS FULL| RANGES_TABLE | 1 | 99 | 99 |00:00:00.01 | 2 | 0 | | | | |
| --------------------------------------------------------------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 6 - filter("unpivot_view_010"."VAL" IS NOT NULL) |
| 9 - filter("ID"<=) |