Sans les fonctions analytiques, on ferait une jointure de la table avec elle-même pour voir les lignes adjacentes. Ici on utilise LEAD() et LAG() pour les voir en une seule passe avec une bien meilleur performance.

Sur une table CONTRACTS avec un contract_id et les plages de validité start_validity,end_validity la requête suivante donne les plages de dates qui se recoupent.

select contract_id,start_validity,end_validity from (
	    select
		   contract_id,start_validity,end_validity
		   ,lag  (end_validity)  over (partition by contract_id order by start_validity,end_validity) lag_end_validity
		   ,lead (start_validity)over (partition by contract_id order by start_validity,end_validity) lead_start_validity
	    from CONTRACTS
) where start_validity <= lag_end_validity or end_validity >>= lead_start_validity
order by 1,2,3
;

Le select imbriqué rajoute les dates de de validité suivantes (LEAD) et précédentes (LAG) pour chaque contrat (PARTITION BY) dans l’ordre chronologique (ORDER BY) et le select principal peut les comparer.

Démo avec plans d’exécution: demo

Sur un gros volume, le FULL TABLE SCAN unique et le WINDOW SORT sont des opérations optimales, faisant des i/o larges, possibilité de Parallel Query, de SmartScan sur Exadata,… alors que la solution jointure fait un accès par index très peu scalable.