I am using ESRI ST_Geometry in my application, and I found that the spatial query is rather slow:
The following sql is used for query pois inside a certain bounding_box:
select * from (
select name, st_astext(shape) as shape from POIView h where 1 = 1 and ST_Intersects(h.shape, st_geometry('POLYGON ((20.227742
30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1
) where rownum <= 10
Count the total nums:
select count(shape) from POIView h where 1 = 1 and ST_Intersects(h.shape, st_geometry('POLYGON ((20.227742
30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1
And the first sql will cost almost 3 seconds, and the second will cost 4+ minutes which is unacceptable.
What' more, the bounding_box is a rather simple polygon with a small area, I can not image how long it will cost once I put a complex polygon as the argument.
Is it possible to fix this?
BTW, all the tables are exported to Oracle 11g by ArcMap 10.0 with the sde connection, and we have only 120k records in the table.
Update: the view:
CREATE OR REPLACE VIEW POIView (objectid, shape, id, name, address, table_name) AS
SELECT objectid,shape,hotel_id as id,hotel_name as name,address,'HotelPoint' AS table_name from HotelPoint
union ALL
SELECT objectid,shape,school_id as id,school_name as name,address,'SchoolPoint' AS table_name from SchoolPoint
union ALL
SELECT objectid,shape,id,name,address,'ImportFeaturePoint' AS table_name from ImportFeaturePoint
union ALL
SELECT objectid,shape,id,name,address,'ImportFeatureLine' AS table_name from ImportFeatureLine
union ALL
SELECT objectid,shape,id,name,address,'ImportFeaturePolygon' AS table_name from ImportFeaturePolygon
union ALL
.....
And once I followed the comment from @Vince, tried to change the sql to:
select * from (
select name, st_astext(shape) as shape from POIView h where ST_ENVINTERSECTS(h.shape, st_geometry('POLYGON ((20.227742
30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1
) where rownum <= 10
It take 6 seconds now. And the count sql:
select count(shape) from POIView h where ST_ENVINTERSECTS(h.shape, st_geometry('POLYGON ((20.227742
30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1
takes 240 seconds.
I am downloading the sp5, I will post the new result after I install the patch.
Update after I installed the SP5, and I found that both the query will take longer than before with 24 seconds for the basic query, and 2 minutes for the count query.
1 =1
is that I have to build the where cause for the sql by different query condition, and condition may be null. I have not applied the patches atm, I will have a try.