p as (
select c, x x1, lead(x) over (order by x ) x2 from (
  select c, x from (
    select c, lag(c) over (order by x) cp, x from (
      select chr(trunc(log(10,1+sum(c) over (order by x)))) c, x from (
        select  power(10,ascii(lvl)) c, x1 x from t union all
        select -power(10,ascii(lvl)) c, x2 x from t
      )
    )
  ) where nvl(c,'-') != nvl(cp,'-')
)
)
select * from p where x1 < x2 order by x1

with ranges (name, range_start, range_end) as
(
          select 'a', 0, 7 from dual
union all select 'b', 2, 4 from dual
union all select 'c', 1, 3 from dual
union all select 'd', 4, 6 from dual
union all select 'e', 3, 5 from dual
),
p (r,c,a,b) as (
          select dense_rank() over (order by name), name, range_start, range_end   from ranges
union all select dense_rank() over (order by name), null, -100500,     range_start from ranges
union all select dense_rank() over (order by name), null, range_end,   +100500     from ranges
),
cte (n, clr, x, y) as (
select max(r)+1, null, -100500, +100500 from p union all
select n-1, c, greatest(x,a), least(y,b)
  from cte e, p 
 where n > 1 and r=n-1 and clr is null and x < y
)
select * from cte where x < y and clr is not null order by x

-- без агрегации групп, формат вывода как у xtender
SQL> select x, (select max(color) from test_ranges where range_start <= x and range_end > x) from
  2  (select range_start x from test_ranges union select range_end from test_ranges) order by x;

20000 rows selected.

Elapsed: 00:02:59.52

Execution Plan
----------------------------------------------------------

| Id  | Operation                         | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                  |  1999K|    24M|     | 28455   (2)|
|   1 |  SORT AGGREGATE                   |                  |     1 |    49 |     |            |
|   2 |   TABLE ACCESS BY INDEX ROWID     | TEST_RANGES      |  2500 |   119K|     |   981   (1)|
|   3 |    BITMAP CONVERSION TO ROWIDS    |                  |       |       |     |            |
|   4 |     BITMAP AND                    |                  |       |       |     |            |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                  |       |       |     |            |
|   6 |       SORT ORDER BY               |                  |       |       | 800K|            |
|   7 |        INDEX RANGE SCAN           | TEST_RANGES_IDX1 |       |       |     |    43   (0)|
|   8 |      BITMAP CONVERSION FROM ROWIDS|                  |       |       |     |            |
|   9 |       SORT ORDER BY               |                  |       |       | 800K|            |
|  10 |        INDEX RANGE SCAN           | TEST_RANGES_IDX2 |       |       |     |    44   (0)|
|  11 |  SORT ORDER BY                    |                  |  1999K|    24M|  38M| 28455   (2)|
|  12 |   VIEW                            |                  |  1999K|    24M|     | 18871   (1)|
|  13 |    SORT UNIQUE                    |                  |  1999K|    41M|  53M| 18871  (51)|
|  14 |     UNION-ALL                     |                  |       |       |     |            |
|  15 |      TABLE ACCESS FULL            | TEST_RANGES      |   999K|    20M|     |  2753   (1)|
|  16 |      TABLE ACCESS FULL            | TEST_RANGES      |   999K|    20M|     |  2754   (1)|

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   45481209  consistent gets
          0  physical reads
          0  redo size
     653151  bytes sent via SQL*Net to client
      15023  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
      40002  sorts (memory)
          0  sorts (disk)
      20000  rows processed

SQL> with p (r,c,a,b) as (
  2            select color, color, range_start, range_end from test_ranges
  3  union all select color, null, null, range_start       from test_ranges
  4  union all select color, null, range_end, null         from test_ranges
  5  ),
  6  cte (n, clr, x, y) as (
  7  select max(color)+1, null, min(range_start), max(range_end) from test_ranges union all
  8  select n-1, c, nvl2(a,greatest(x,a),x), nvl2(b,least(y,b),y)
  9    from cte e, p
 10   where n > 1 and r=n-1 and clr is null and x < y
 11  )
 12  select * from cte where x < y and clr is not null order by x;

31 rows selected.

Elapsed: 00:00:46.25

Execution Plan
----------------------------------------------------------

| Id  | Operation                                  | Name        | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |             |     4 |   208 | 13793   (1)|
|   1 |  SORT ORDER BY                             |             |     4 |   208 | 13793   (1)|
|   2 |   VIEW                                     |             |     4 |   208 | 13792   (1)|
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|             |       | |            |
|   4 |     SORT AGGREGATE                         |             |     1 |    49 |            |
|   5 |      TABLE ACCESS FULL                     | TEST_RANGES |   999K|    46M|  2754   (1)|
|   6 |     HASH JOIN                              |             |     3 |   312 | 11038   (1)|
|   7 |      RECURSIVE WITH PUMP                   |             |       | |            |
|   8 |      VIEW                                  |             |  2999K|   148M|  8262   (1)|
|   9 |       UNION-ALL                            |             |       | |            |
|  10 |        TABLE ACCESS FULL                   | TEST_RANGES |   999K|    46M|  2754   (1)|
|  11 |        TABLE ACCESS FULL                   | TEST_RANGES |   999K|    25M|  2753   (1)|
|  12 |        TABLE ACCESS FULL                   | TEST_RANGES |   999K|    25M|  2754   (1)|

--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          2  recursive calls
      60582  db block gets
    2389761  consistent gets
          0  physical reads
          0  redo size
       2341  bytes sent via SQL*Net to client
        382  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       8049  sorts (memory)
          0  sorts (disk)
         31  rows processed

SQL> insert into test_ranges select level, dbms_random.value(0,10), null from dual connect by level <= 10000;
10000 rows inserted
Executed in 0,156 seconds

SQL> update test_ranges set range_end=dbms_random.value(range_start,10);
10000 rows updated
Executed in 0,578 seconds

SQL> alter index test_ranges_idx1 rebuild online;

Index altered.

Elapsed: 00:00:00.09
SQL> alter index test_ranges_idx2 rebuild online;

Index altered.

Elapsed: 00:00:00.11