2 Replies Latest reply: Feb 14, 2014 5:57 AM by _jum RSS

    bypassing the spatial index

    udadi

      Hi,

        Could someone please answer by questions?:

        I was reading the "Pro Oracle Spatial" book and it has the following about the two stage filtering when using spatial operators:

      "In some cases, however, the optimizer may decide to bypass the spatial index. It then invokes the secondary filter(i.e., the Geometry Engine function) directly on appropriate rows of the table. (This might happen for a variety of reasons....."

      My questions:

      1) Where can I find more information about the "variety of reasons" when this occurs?

      2) Is it possible to force Oracle Spatial to not use primary filter and go directly to secondary filter? The reason why I want to do that is that I do my own pre-filtering which is equivalent to primary filter and I have an additional query condition(along with spatial condition) which selects only those which are pre-filtered.

      3) Or is there any other Oracle Spatial function exactly like SDO_ANYINTERACT, but only applies secondary filter (and so does not require spatial index)?

       

      Any help is greatly appreciated.

       

      Upendra

        • 1. Re: bypassing the spatial index
          Ying Hu-Oracle

          1) For example, select * from tab where c1 = 1 and sdo_anyinteract(...) = 'TRUE';

          If "c1 = 1" is very selective, say only only one row or very few fows are returned,

          sdo_anyinteract(...) may not go through the spatial index.

           

          2) You can always use the /*+ full */ hint like:

          select /*+ full(a) */ * from tab a where sdo_anyinteract(...) = 'TRUE';

           

          3) SDO_GEOM.RELATE() seems to be what you are looking for.

          • 2. Re: bypassing the spatial index
            _jum

            Alternatively you can forbid only the spatial index and use your own index instead:

            select /*+ NO_INDEX(a geom_spix) INDEX (a my_idx) */
            a.*
            from tab a where sdo_anyinteract(...) = 'TRUE';