6

Initially I was trying to find out why it's so slow to do a spatial query with multiple SDO_REALTE in a single SELECT statement like this one:

SELECT * FROM geom_table a 
WHERE SDO_RELATE(a.geom_column, SDO_GEOMETRY(...), 'mask=inside')='TRUE' AND
SDO_RELATE(a.geom_column, SDO_GEOMETRY(...), 'mask=anyinteract')='TRUE';

Note the two SDO_GEOMETRY may not be necessary the same. So it's a bit different from SDO_GEOMETRY(a.geom_column, the_same_geometry, 'mask=inside+anyinteract')='TRUE'

Then I found this paragraph from oracle documentation for SDO_RELATE:

Although multiple masks can be combined using the logical Boolean operator OR, for example, 'mask=touch+coveredby', better performance may result if the spatial query specifies each mask individually and uses the UNION ALL syntax to combine the results. This is due to internal optimizations that Spatial can apply under certain conditions when masks are specified singly rather than grouped within the same SDO_RELATE operator call. (There are two exceptions, inside+coveredby and contains+covers, where the combination performs better than the UNION ALL alternative.) For example, consider the following query using the logical Boolean operator OR to group multiple masks:

SELECT a.gid   FROM polygons a, query_polys B   WHERE B.gid = 1   AND
SDO_RELATE(A.Geometry, B.Geometry,
                   'mask=touch+coveredby') = 'TRUE';

The preceding query may result in better performance if it is expressed as follows, using UNION ALL to combine results of multiple SDO_RELATE operator calls, each with a single mask:

SELECT a.gid
      FROM polygons a, query_polys B
      WHERE B.gid = 1
      AND SDO_RELATE(A.Geometry, B.Geometry,
                   'mask=touch') = 'TRUE' UNION ALL SELECT a.gid
      FROM polygons a, query_polys B
      WHERE B.gid = 1
      AND SDO_RELATE(A.Geometry, B.Geometry,
                   'mask=coveredby') = 'TRUE';

It somehow gives the answer for my question, but still it only says: "due to internal optimizations that Spatial can apply under certain conditions". So I have two questions:

  1. What does it mean with "internal optimization", is it something to do with spatial index? (I'm not sure if I'm too demanding on this question, maybe only developers in oracle know about it.)

  2. The oracle documentation doesn't say anything about my original problem, i.e. SDO_RELATE(..., 'mask=inside') AND SDO_RELATE(..., 'maks=anyinteract') in a single SELECT. Why does it also have very bad performance? Does it work similarly to SDO_RELATE(..., 'mask=inside+anyinteract')?

EDIT:

After some research, I've got some additional information. When explaining the execute plan for ANDed SDO_RELATE case, it shows that only one of predicate has been applied with spatial index, the image of the plan is enclosed.

enter image description here

One way to improve the performance I just found is to put one of the SDO_RELATE into sub-query, and that will make both predicate utilize the spatial index. That is:

SELECT * FROM geom_table a 
WHERE SDO_RELATE(a.geom_column, SDO_GEOMETRY(...), 'mask=inside')='TRUE'
AND a.id IN (SELECT b.id FROM geom_table b WHERE SDO_RELATE(b.geom_column, SDO_GEOMETRY(...), 'mask=anyinteract')='TRUE');

And the execute plan is: enter image description here

But I wonder if there is better options, because here the HASH JOIN seems to be an extra cost.

4

The UNION ALL does not perform the same as what you want to achieve. Like the manual says, it is really an alternative to a combined mask. For example, the "+" in expression "MASK=INSIDE+COVEREDBY" stands for an inclusive OR, and so can be replaced by a UNION ALL, that also represents an inclusive OR.

But you want to do something else: you want to find out objects that relate to both of two other objects (i.e. a AND). What's more you want to compare them to two DIFFERENT objects.

I am guessing that you are running a Standard Edition database. With an Enterprise Edition database, the optimizer would most likely use the spatial index for both predicates and combine their results using bitmap techniques. Those do not exist on Standard Edition, so the optimizer has to decide on using one index only.

Using the IN approach turns the query into a join. I am a little surprised that the optimizer choose a HASH JOIN approach: those are typically used only when predicates are not very selective or with full table scans, i.e. when many rows need joining. Then again, that should be fine: if both spatial predicates return only few rows, then the hash join will not be an issue, and if you run it in cases where both return many rows, then the hash join will be more efficient than a nested loop join.

What version of Oracle are you using ?

  • Thank you for your answer, it's very good and professional. Yes, we are using a standard edition. But what do you think we can do to improve the performance in such use cases that we want to apply multiple spatial predicates? Or there's nothing we can do with an SE license? – mfdev Aug 26 '13 at 18:10
  • And the oracle version is 10.2 – mfdev Aug 27 '13 at 5:40
  • What you are getting is probably as good as it can be: the hash join/subquery approach uses the spatial index for both predicates then combines the results. The EE/bitmap approach does the same, maybe a little more efficiently. Then again much depends on the actual purpose of the query and what problem it tries to answer. If one of the two predicates is always going to be the most selective, then you may rewrite the query in such a way that it is applied first. Without knowing what data you are dealing with and what you are trying to achieve, it is hard to propose soutions. – Albert Godfrind Aug 28 '13 at 13:40
  • 1
    ...three years later...to avoid the hash join, use the optimizer hint /*+ USE_NL */ which fill force a nested loop as opposed to the hash join. – JasonInVegas Sep 14 '16 at 15:58

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.