with
ranges as
(
select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual
)
select t1.*, t2.*
from ranges t1, ranges t2
where t1.external_key = t2.external_key
and t1.range_id != t2.range_id
and t1.val_to > t2.val_from
and t1.val_from < t2.val_to

with
ranges as
(
select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual
)
select t1.*, t2.*
from ranges t1, ranges t2
where t1.external_key = t2.external_key
and t1.range_id != t2.range_id
and (sysdate+t1.val_from,sysdate+t1.val_to) overLaps
(sysdate+t2.val_from,sysdate+t2.val_to);

SQL> with
t as
(
select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual
)
select range_id, external_key, val_from, val_to
from (select range_id,
external_key,
val_from, val_to,
lead (val_from) over (partition by external_key order by val_from) lead_val_from,
lag (val_to) over (partition by external_key order by val_to) lag_val_to
from t)
where lead_val_from between val_from and val_to
or lag_val_to between val_from and val_to
RANGE_ID E VAL_FROM VAL_TO
---------- - ---------- ----------
1 A 1 5
4 A 3 9
2 A 8 10

Yes I've seen this before in one of your investigations about the differences in arguments of the count function, but I am not getting the point you are making here. Is it that sysdate always produces the same output in one query, not because of read consistency, but because of something else?

I don't believe SYSDATE is truly read-consistent, if it was then references to SYSDATE within a serializable (or read-only) transaction would return the same value, it is trivial to show that they do not.

However It appears that SYSDATE has been made to behave as if it is read-consistent at the statement level and this implementation appears to involve re-write as previously shown. Possibly (and this is pure speculation) it is persisted somewhere at the start of the statement and this value is referenced by SYSDATE@!. This might give benefits in terms of both performance and statement level read-consistent behaviour (you might see the latter as a side-effect). I notice a similar rewrite is used with the USER function.

Of course the implementation is largely irrelevant as long as we clearly understand the behaviour.

it was then references to SYSDATE within a
serializable (or read-only) transaction would return
the same value, it is trivial to show that they do not.

Good point. I just checked it to be really sure and they show different times indeed.

> However It appears that SYSDATE has been made to

behave as if it is read-consistent at the statement
level and this implementation appears to involve
re-write as previously shown. Possibly (and this is
pure speculation) it is persisted somewhere at the
start of the statement and this value is referenced
by SYSDATE@!. This might give benefits in terms of
both performance and statement level read-consistent
behaviour (you might see the latter as a
side-effect).

This sounds very plausible. All evidences support this theory. And, like you said, the important part to remember here is that sysdate behaves as read consistent at statement level.