SQL & PL/SQL

Hi all,

I am trying to find out some information about the Oracle overlaps function.What other function can be used in place of Overlaps that would be faster and will give the same result.

Here's the query from our procedure which uses the overlaps function:

Here v_date_time_start and v_date_time_stop are date time variables

SELECT COUNT(*) INTO V_RESULT
FROM table_name
WHERE site = 'Network'
AND (((EFF_DATE_TIME_START, EFF_DATE_TIME_STOP)
OVERLAPS
(V_DATE_TIME_START, V_DATE_TIME_STOP))
OR (EFF_DATE_TIME_START BETWEEN V_DATE_TIME_START AND V_DATE_TIME_STOP)
OR (EFF_DATE_TIME_STOP BETWEEN V_DATE_TIME_START AND V_DATE_TIME_STOP))
AND ((TO_NUMBER(ABS(EFF_DATE_TIME_START - EFF_DATE_TIME_STOP)) * 1440) > 9) --PT MUST BE IN ROOM AT LEAST 10 MINUTES
AND ((TO_NUMBER(ABS(EFF_DATE_TIME_START - V_DATE_TIME_STOP)) * 1440) > 9)
AND (TO_NUMBER(ABS(EFF_DATE_TIME_STOP - V_DATE_TIME_START)) * 1440 > 9);


Need to fix this quickly as its causing performance problem.I am not familiar with Overlaps so any help would be great.

Thanks,
PB