Can Oracle be forced to use the spatial index for sdo_filter in combination with an or clause? Diffe

957609

We’re seeing the following issue: sql - Can Oracle be forced to use the spatial index for sdo_filter in combination with an or clause? - Stack Overflow (posted by a colleague of mine) and are curious to know if this behaviour is due to a difference between standard and enterprise, or could we doing something else wrong in our DB config.?

We have also reproduced the issue on the following stacks:

---

Oracle SE One 11.2.0.3 (with Spatial enabled)

Redhat Linux 2.6.32-358.6.2.el6.x86_64 #1 SMP Thu May 16 20:59:36 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

11.2.0.3.0 Standard Edition and 11.2.0.4.0 Standard Edition (both with Spatial enabled)

Microsoft Windows Server 2003R2 Standard x64 Edition

---

However, the SQL works fine if we try it on Oracle 11.2.0.3.0 *Enterprise* Edition.

Any help or advice would be much appreciated.

Kindest Regards,

Kevin

Ying Hu-Oracle

In enterprise edition, bitmap operations are enabled. So it looks like

that the two sdo_filter() operators will take the spatial index plan

and then do "bitmap OR" in enterprise edition?

SBJ

In my experience sdo_filter ALWAYS uses the spatial index, so that's not the problem. Since you did not provide the explain plans, we can't say for sure but I think yhu is right: Standard Edition can't use the bitmap operations, and thus it'll take longer to combine the results of the two queries (because the optimizer will surely split this OR up in two parts, then combine them).

BTW: when asking questions about queries here, it would be nice if you posted the queries here as well, so that we do not have to check another website in order to see what you are doing. Plus it will probably get you more answers, because not everyone can be bothered to click on that link. It would also have been nice if you had posted your own answer on the other post here as well, because my recommendation would have been to use union all - but since you already found that out for yourself my recommendation would have been a little late.