I have about 300 000 records (worksites) in a table (S) with id (id_s), start_date, end_date (and in some cases an authorisation date - AUTH_START). I have a second table containing the id of 500 of these worksites (called id_a), which constitute a subset of these worksites. I have a third table storing the id_s/id_a and the geometry (shape) for all these worksites.
I am looking for all the worksites which will be active during the same dates as my subsets and which are intersection my subset worksites.
I don't have any writing rights on the dB, I can't create temporary tables or views. The original tables in the dB have spatial indexes.
Is there anyway I can speed up my query ? Maybe by getting rid of the GROUP BY function in some way ? I used the query in the with to narrow down the full table to +/- 17 000 worksites instead of 250 000... but of course it's still slow.
WITH selection as ( SELECT (ana.id_S)id_as ,ana.START ,ana.STOP FROM (SELECT (PR.ID_S_FK)ID_S ,NVL(S.AUTHO_START, S.PR_START)START ,NVL(S.AUTHO_STOP, S.PR_STOP)STOP FROM PR LEFT JOIN WORKSITE S ON PR.ID_S_FK = S.ID_S )ana, (SELECT min(NVL(S.AUTH_START, S.PR_START))MIN_START ,max(NVL(S.AUTH_STOP, S.PR_STOP))MAX_STOP FROM extract, WORKSITE S WHERE S.ID_S IN (extract.id) )S WHERE ana.START < S.MAX_STOP AND ana.STOP > S.MIN_START ) SELECT ana.id_as FROM --selection (SELECT selection.id_as ,NVL(S.AUTHO_START, S.PR_START)START ,NVL(S.AUTHO_STOP, S.PR_STOP)STOP ,sh.shape FROM selection LEFT JOIN WORKSITE S ON selection.ID_AS = S.ID_S LEFT JOIN sh ON selection.id_as=sh.id_S )ana ,(SELECT S.ID_S ,NVL(S.AUTHO_START, PR_START)START ,NVL(S.AUTHO_STOP, PR_STOP)STOP ,shape FROM extract e, WORKSITE S ON S.ID_S = PR.ID_S_FK LEFT JOIN sh ON PR.ID_S_FK = SH.ID_S WHERE S.ID_S IN (extract.id) )S WHERE ana.id_as != S.id_S AND ana.START < S.STOP AND ana.STOP > S.START AND SDO_RELATE(S.SHAPE, ANA.SHAPE, 'mask=ANYINTERACT') = 'TRUE' GROUP BY ana.id_as ;