Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (KEY, TYPE, DATE_FROM, DATE_TO, VALID_FROM, VALID_TO) AS
SELECT 123456, 'A', DATE '2022-10-31', DATE '2099-12-31', DATE '2011-12-31', DATE '2025-02-28' FROM DUAL UNION ALL
SELECT 123456, 'B', DATE '2022-03-09', DATE '2099-12-31', DATE '2022-10-31', DATE '2025-02-28' FROM DUAL UNION ALL
SELECT 123456, 'C', DATE '2022-10-31', DATE '2099-12-31', DATE '2025-02-28', DATE '2035-08-31' FROM DUAL;
3 rows affected

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY valid_from, valid_to DESC
  ALL ROWS PER MATCH
  PATTERN ( overlapping {- overlapping* not_overlapping -} | not_overlapping )
  DEFINE
    overlapping AS MAX(valid_to) > NEXT(valid_from)
)


VALID_FROM
VALID_TO
KEY
TYPE
DATE_FROM
DATE_TO


2011-12-31 00:00:00
2025-02-28 00:00:00
123456
A
2022-10-31 00:00:00
2099-12-31 00:00:00

2025-02-28 00:00:00
2035-08-31 00:00:00
123456
C
2022-10-31 00:00:00
2099-12-31 00:00:00

VALID_FROM	VALID_TO	KEY	TYPE	DATE_FROM	DATE_TO
2011-12-31 00:00:00	2025-02-28 00:00:00	123456	A	2022-10-31 00:00:00	2099-12-31 00:00:00
2025-02-28 00:00:00	2035-08-31 00:00:00	123456	C	2022-10-31 00:00:00	2099-12-31 00:00:00