How to optimize a SQL query that combines INNER JOINs and DISTINCT?

SELECT DISTINCT af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux 
FROM signature sig  
INNER JOIN stockage st ON st.id_flux = sig.id_flux AND st.date_statut_stockage >= sysdate - 100
INNER JOIN allotissement_flux af ON af.ID = sig.id_flux  
WHERE sig.date_statut_signature >= sysdate - 100
AND sig.statut_signature = 'A_SIGNER'
AND st.statut_stockage = 'OUI'
AND sig.nb_appel_service_signature < 4 ;

The query above returns me 5222 rows, and it runs locally in 0.63 seconds. I’m wondering if it is as fast as it can be or if I can tweak it further somehow, as this dataset will grow fast overtime.

Things that I’ve tried without change in speed:

1 – Change the join order, joining from the smallest to the biggest table.

2 – Adding an index to af.ID.

Answer

Move the junction table out of the main query:

---

SELECT af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux 
FROM JOIN stockage st 
INNER JOIN allotissement_flux af 
   ON EXISTS ( SELECT *
        FROM signature sig
        WHERE st.id_flux = sig.id_flux
        AND af.ID = sig.id_flux
        AND sig.statut_signature = 'A_SIGNER'
        AND sig.nb_appel_service_signature < 4 
        AND sig.date_statut_signature >= sysdate - 100
        )
WHERE st.statut_stockage = 'OUI'
AND st.date_statut_stockage >= sysdate - 100
        ;