topic Re: ST_Geometry query is doing full table scans (not using spatial indexes) in Data Management Questions

ST_Geometry query is doing full table scans (not using spatial indexes)

EllenDean — Thu, 16 May 2013 14:03:06 GMT

Hello -

We are running ArcSDE 10 sp5, Oracle 11.2.0.1

We have a web application that is doing an intersection using an ST_GEOMETRY (st_intersects) query between two polygon feature classes. One of our developers told me it was running very slowly, so I did an execution plan on the query and found out it is doing full table scans of both the feature classes.

I looked at the feature classes in ArcCatalog and saw they both had spatial indexes. I recreated the spatial indexes (in ArcCatalog), but still no luck in having the query use the indexes and is still doing full table scans.

Below is output from the query and execution plan:

select count(1)
from
   gislib.plss_024k_str a,
   pdslib.cup_bnd_all b
where
   offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1
and
   rownum < 2
/

COUNT(1)
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2651061692

--------------------------------------------------------------------------------------
| Id | Operation            | Name          | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     1 |   876 | 2620   (2)| 00:00:32 |
|   1 | SORT AGGREGATE      |               |     1 |   876 |            |          |
|* 2 |   COUNT STOPKEY      |               |       |       |            |          |
|   3 |    NESTED LOOPS      |               | 2182 | 1866K| 2620   (2)| 00:00:32 |
|* 4 |     TABLE ACCESS FULL| CUP_BND_ALL   |     4 | 2468 |   501   (1)| 00:00:07 |
|* 5 |     TABLE ACCESS FULL| PLSS_024K_STR |   564 |   142K|   530   (2)| 00:00:07 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<2)
   4 - filter("OFFCL_PRMT_ID"=38)
   5 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1)

SQL>

Any thoughts or suggestions would be appreciated!

Thanks,

Ellen Dean
St. Johns River Water Management District

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

VinceAngelo — Thu, 16 May 2013 14:25:55 GMT

Have you tried formatting the query with a JOIN clause?

There's no way to use both spatial indexes, but you can at least use
one index (though I forget if the first or second shape is the one that
uses the index).

I'd certainly use a hint to tell the database to use the index on offcl_prmt_id
for whichever table contains that column, and make sure the right column
is in the right spot for an index-based spatial search.

- V

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

EllenDean — Thu, 16 May 2013 15:37:27 GMT

Hi Vince,
Thanks so much for getting back.

A little more info (in case it helps): I'm using the "b" table as the second parameter in the st_intersects, as St_Geometry documentation said this should be the table that is being used as a filter. The gislib.plss_024k_str feature class has 56373 records. The pdslib.cup_bnd_all feature class has 21653 records.

Hopefully I did this correctly - below is a revised query, but it still is doing a full table scan
(note: the pdslib.cup_bnd_all table's layer_id=13540):

select /*+ INDEX(pdslib.cup_bnd_all, A13540_IX1) */ count(1)
from
   gislib.plss_024k_str a JOIN pdslib.cup_bnd_all b ON (b.offcl_prmt_id=38 and sde.st_intersects(a.shape, b.shape)=1)
and
   rownum < 2
/

COUNT(1)
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2651061692

--------------------------------------------------------------------------------------
| Id | Operation            | Name          | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     1 |   876 | 2620   (2)| 00:00:32 |
|   1 | SORT AGGREGATE      |               |     1 |   876 |            |          |
|* 2 |   COUNT STOPKEY      |               |       |       |            |          |
|   3 |    NESTED LOOPS      |               | 2182 | 1866K| 2620   (2)| 00:00:32 |
|* 4 |     TABLE ACCESS FULL| CUP_BND_ALL   |     4 | 2468 |   501   (1)| 00:00:07 |
|* 5 |     TABLE ACCESS FULL| PLSS_024K_STR |   564 |   142K|   530   (2)| 00:00:07 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<2)
   4 - filter("B"."OFFCL_PRMT_ID"=38)
   5 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1)

SQL>

The polygon (offcl_prmt_id=38) is fairly normal shape - I tried running the query using other polygons that are simple rectangles and would intersect with only a few polygons in the other layer. All of these queries also did full table scans.

- Ellen D.

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

VinceAngelo — Sun, 12 Dec 2021 04:44:09 GMT

If offcl_prmt_id lives in the b table, you want to drive it from the b table,
and use the a table's spatial index. Something like:

SELECT /* Hint for offcl_prmt_id index on b */ count(1)
FROM pdslib.cup_bnd_all b
INNER JOIN  gislib.plss_024k_str a 
   ON (sde.st_intersects(a.shape, b.shape)=1)
WHERE b.offcl_prmt_id=38 and rownum < 2
/

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

EllenDean — Thu, 16 May 2013 18:07:57 GMT

Thanks Vince - I tried this, and it did use the offcl_prmt_id index (after I created it! - wasn't there originally), but still didn't get the spatial index.

Interesting, though, I tried running the initial query (original query used by our developers for the web app that was getting records, not just a count) and ran it on the first 9 records. This time it uses the spatial index. Next, I tried running it on the first 99 records, and it did not use the spatial index, but did a full table scan on both. Running the original query on all records returns 1114 and this doesn't use the spatial index. I'm wondering - I remember Tom Brown mentioning awhile back at an Esri conference when I was talking to him something about Esri development was working on "opening up the pipe" (my own layman words) and allowing a larger number of records to be passed through at a time on a given st_geometry query. I'm just wondering if maybe we're running into this "pipe" limitation and because of this it is not using the spatial index(?).

Anyway - thanks again for your thoughts and suggestions. We may - on our end - need to revise how our apps are doing these queries, especially if they'll be returning a large number of features. We can (although reluctantly) redo the code and use ArcObjects.

Ellen D.

Here is the output from my queries using different number of returned records:

1. Full query
* returns 1114 rows
* does not use spatial index on either table
* uses index on offcl_prmt_id field

select a.sec, a.twn, a.rng
from
   gislib.plss_024k_str a,
   pdslib.cup_bnd_all b
where
   b.offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1
/
       SEC        TWN        RNG
---------- ---------- ----------
         8         -5         29
        58         -4         29
        66         -5         29
         5         -5         29
        63         -5         29
.
.
.
        41         -4         29
        57         -4         28
        19         -4         29
        13         -4         28
        53         -4         28
        13         -4         28
        24         -4         28
        25         -3         28
        32         -3         29
        41         -4         28
        28         -3         28

1114 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1466874949

-------------------------------------------------------------------------------------------------
| Id | Operation                    | Name             | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  | 2182 | 1866K| 2124   (2)| 00:00:26 |
|   1 | NESTED LOOPS                |                  | 2182 | 1866K| 2124   (2)| 00:00:26 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUP_BND_ALL      |     4 | 2468 |     5   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | IDX_CUP_BND_ALL_ |     4 |       |     1   (0)| 00:00:01 |
|* 4 |   TABLE ACCESS FULL          | PLSS_024K_STR    |   564 |   142K|   530   (2)| 00:00:07 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OFFCL_PRMT_ID"=38)
   4 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1)

Statistics
----------------------------------------------------------
    1973055 recursive calls
          0 db block gets
     355232 consistent gets
          0 physical reads
          0 redo size
      19409 bytes sent via SQL*Net to client
       1178 bytes received via SQL*Net from client
         76 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       1114 rows processed

SQL>

2. Query run on first 9 rows
* returns 9 rows
* uses spatial index from "a" table
* uses index on offcl_prmt_id field

select a.sec, a.twn, a.rng
from
   gislib.plss_024k_str a,
   pdslib.cup_bnd_all b
where
   b.offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1
where
   rownum < 10
/

       SEC        TWN        RNG
---------- ---------- ----------
         8         -5         29
        58         -4         29
        66         -5         29
         5         -5         29
        63         -5         29
        10         -5         29
        35         -3         28
        64         -4         29
        24         -4         28

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 421526145

--------------------------------------------------------------------------------------------------
| Id | Operation                     | Name             | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |     9 | 7884 |    13   (8)| 00:00:01 |
|* 1 | COUNT STOPKEY                |                  |       |       |            |          |
|   2 |   NESTED LOOPS                |                  |    15 | 13140 |    13   (8)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUP_BND_ALL      |     4 | 2468 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | IDX_CUP_BND_ALL_ |     4 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| PLSS_024K_STR    |    10 | 2590 |    13   (8)| 00:00:01 |
|* 6 |     DOMAIN INDEX (Sel: 1)     | A10943_IX1       |       |       |    10 (10)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   4 - access("OFFCL_PRMT_ID"=38)
   6 - access("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1)

Statistics
----------------------------------------------------------
        699 recursive calls
         40 db block gets
        640 consistent gets
        207 physical reads
          0 redo size
        584 bytes sent via SQL*Net to client
        364 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          7 sorts (memory)
          0 sorts (disk)
          9 rows processed

SQL>

3. Query run on first 99 rows
* returns 99 rows
* does not use spatial index on either table
* uses index on offcl_prmt_id field

select a.sec, a.twn, a.rng
from
   gislib.plss_024k_str a,
   pdslib.cup_bnd_all b
where
   b.offcl_prmt_id = 38 and sde.st_intersects(a.shape, b.shape) = 1
where
   rownum < 100
/

       SEC        TWN        RNG
---------- ---------- ----------
         8         -5         29
        58         -4         29
        66         -5         29
         5         -5         29
        63         -5         29
        10         -5         29
        35         -3         28
        64         -4         29
        24         -4         28
.
.
.
        52         -4         28
        19         -3         29
         8         -4         29
         5         -4         28
         0         -5         29
        28         -4         29
         7         -5         29
        25         -4         28
        30         -3         29
         5         -5         29
         5         -5         29
        68         -4         29
        17         -4         28
         4         -4         28

99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4040828753

--------------------------------------------------------------------------------------------------
| Id | Operation                     | Name             | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |    99 | 86724 |    98   (2)| 00:00:02 |
|* 1 | COUNT STOPKEY                |                  |       |       |            |          |
|   2 |   NESTED LOOPS                |                  |   146 |   124K|    98   (2)| 00:00:02 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CUP_BND_ALL      |     4 | 2468 |     3   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | IDX_CUP_BND_ALL_ |     4 |       |     1   (0)| 00:00:01 |
|* 5 |    TABLE ACCESS FULL          | PLSS_024K_STR    |   100 | 25900 |    95   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<100)
   4 - access("OFFCL_PRMT_ID"=38)
   5 - filter("SDE"."ST_INTERSECTS"("A"."SHAPE","B"."SHAPE")=1)

Statistics
----------------------------------------------------------
     143992 recursive calls
          0 db block gets
      30375 consistent gets
          0 physical reads
          0 redo size
       2121 bytes sent via SQL*Net to client
        430 bytes received via SQL*Net from client
          8 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         99 rows processed

SQL>

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

VinceAngelo — Thu, 16 May 2013 18:32:33 GMT

Did you try swapping the a.shape and b.shape positions?

If the attribute test didn't have an index, the optimizer wouldn't have much choice.

Have you done anything non-standard to your Oracle instance, like disassociating
the operator functions?

I'm not sure what it would look like, but you could also try an optimizer hint within
the join clause, telling it to use the spatial index.

- V

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

EllenDean — Fri, 17 May 2013 14:25:29 GMT

Thanks Vince,

I tried swapping the a.shape and b.shape positions, which resulted in the spatial index not being selected even with only returning the first 10 rows. Which in some ways made sense as the St_Geometry documentation says to use the second parameter as the one from the table with a filter.

I also tried using the optimizer hint with the spatial index in the join clause - still didn't use the spatial index (when returning > 10 rows).

But, another interesting thing - I tried this same query on a copy of these features on our test instance (which is at 11.2.0.2, also ArcSDE 10 sp5), and it worked correctly and the spatial index was selected in the original query with all rows returned (1114 records). I'm not sure (I'm thinking not) that it is related to the Oracle version, but maybe more along the lines of some setup on our production instance (as you suggested). I'll check with our DBA's on this. At least this gives some path to explore.

Thanks again for your thoughts and suggestions - if we find (hopefully) anything I'll post these on this thread.

Ellen D.

Re: ST_Geometry query is doing full table scans (not using spatial indexes)

VinceAngelo — Fri, 17 May 2013 15:27:06 GMT

This has always been an optimizer issue, so it's not surprising that Oracle 11.2.0.1
and 11.2.0.2 could behave differently. You could probably pursue that possibility
with Tech Support (giving them a copy of the data, and have them try queries with
11.2.0.1, 11.2.0.2, and 11.2.0.3 instances).

- V