I have encountered an interesting issue related to SQL query performance. When I include an extra space after the DISTINCT keyword in my query, the performance significantly degrades, and sometimes the query fails to return results altogether. However, when there is only a single space after DISTINCT, the query executes quickly.

Details:

The problematic query goes like:

select DISTINCT  nsql.site_num, nsql.candidate_key, nsql.candidate_type, nsql.root_key, nsql.root_type   from ( select osql.site_num,osql.candidate_key, osql.candidate_type,  osql.root_key as root_key, osql.root_type as root_type,  nvl( MARKER_CANDIDATE.candidate_key, -1 ) as notmarker from (SELECT unit.site_num, unit.unit_key candidate_key, 'Unit' candidate_type, t.candidate_key as root_key, 'Lot' root_type FROM UNIT unit, (  SELECT status.site_num site_num,lot.lot_key candidate_key, 'Lot' candidate_type, lot.lot_key root_key, 'Lot' root_type FROM LOT lot, TRACKED_OBJECT_STATUS status, LOT_FLOW_TRACKING track WHERE lot.pd_xfr_update_pid < 2 AND lot.pd_xfr_update_pid > 0 AND lot.purged is null  and lot.order_key=-1 AND lot.order_item_key=-1 AND lot.site_num=1 AND  lot.site_num=status.site_num AND lot.site_num=track.site_num AND lot.lot_key=track.lot_key  AND track.lot_type!=5 AND track.lot_type!=4 AND track.tobj_status_key=status.tobj_status_key AND status in ('Closed') ) t WHERE unit.lot_key=t.candidate_key AND unit.site_num=t.site_num AND unit.lot_key>0 AND unit.unit_key>0  and unit.purged is null) osql left join MARKER_CANDIDATE_1 MARKER_CANDIDATE on  osql.candidate_key = MARKER_CANDIDATE.candidate_key and MARKER_CANDIDATE.candidate_type=osql.candidate_type and osql.site_num=MARKER_CANDIDATE.site_num ) nsql where nsql.notmarker=-1;

The issue occurs specifically when there are two spaces after DISTINCT.
I suspect that this might be related to character encoding or parsing.
The database system I’m using is Oracle10g.

This statement was generated by a
proprietary product from a certain supplier , so I was only able to get this statement from the log it printed, but unable to adjust the generation logic (add or remove spaces) at this time.

I would like to understand why this behavior occurs and find a solution to ensure consistent query performance regardless of the number of spaces.