Oracle spatial SDO_RELATE: why better performance result from UNION ALL/INTERSECT combined individual specified mask

Question

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.

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:

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

Answer 1

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 ?