Oracle spatial SDO_RELATE: why better performance result from UNION
ALL/INTERSECT combined individual specified mask
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:
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.)
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')?
No comments:
Post a Comment