Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Cancel
- Home
- :
- All Communities
- :
- Products
- :
- Data Management
- :
- Data Management Questions
- :
- ST_Geometry query is doing full table scans (not u...
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
ST_Geometry query is doing full table scans (not using spatial indexes)
Subscribe
1992
7
05-16-2013 07:03 AM
05-16-2013
07:03 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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
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
7 Replies
05-16-2013
07:25 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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
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
05-16-2013
08:37 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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.
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.
05-16-2013
08:51 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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:
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 /
05-16-2013
11:07 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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>
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>
05-16-2013
11:32 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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
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
05-17-2013
07:25 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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.
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.
05-17-2013
08:27 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
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
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
