3

I have two tables in 11g oracle db, one with points geometry, one with polygons. Currently I need to enrich the points table daily with the name of the polygon (country) that the point is inside, but what I'm using takes way too long. How can I improve performance? Here's what I'm doing:

    BEGIN
    FOR rec IN (
        SELECT country_name, SHAPE
        FROM territory
        ORDER BY country_name
        )
    LOOP
        UPDATE points_table
        SET Country = rec.country_name
        WHERE (Country is null) AND (SDO_INSIDE(Geometry, rec.SHAPE) = 'TRUE';
    END LOOP;
END
;
1

Try reversing your WHERE clause, to put the spatial constraint first. The optimizer should handle this, but sometimes needs help. You can also add an explicit hint.

Then again, if the number of NULL country values is more restrictive, you might want to index that (and hint appropriately).

0

My first thought would be a missing or outdated spatial index on you points. I don't use Oracle, but a quick search lead me to their help pages, Oracle CREATE INDEX.

  • Thanks, should have mentioned that yes, both geometries have spatial indexes before the join is attempted – user1812591 Oct 21 '13 at 21:02
0

I assume you are dealing with a small number of countries but a large number of points. How many points are you dealing with ? And how many countries ?

When you say it takes "way too long", how long is that ? And how long would be acceptable for you ? Also what exact version of Oracle do you use ?

Your approach is the correct one: you will essentially do one search through the points table for each country and update the matching points. The reverse approach (directly updating the points by searching for the matching territory) will be very slow, since it will require one search through the territory table for each point.

You could try using an SDO_JOIN approach, but if the two tables are very different in size, then it will revert to using essentially the same approach you already use.

Albert

  • You're correct in your first assumption, I have about 200 countries (polygons), but about 200 million points, 100K of which have a null country when I run this statement. By "way too long" I mean in excess of 6 hours, usually timing out at that point. Ideally I'd like to see it finish in less than an hour. – user1812591 Oct 23 '13 at 15:35

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.