0

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
    ;
  • Your problem mentions shapefiles, but not tables, then queries tables. Are you using "shp" and "shape file" to refer to tables? Please Edit the question to clarify, and to replace "repercute" with the word you intended to use. – Vince Jan 9 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.