Oracle 21c

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',
    values_ as val_type = '02_val',
    end_ as val_type = '03_END'
)
)

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"<=)