Poor performance of spatial query in view with multiple geometry type unioned

kkkk1 — Fri, 10 Dec 2021 21:36:39 GMT

Hi:

I am using Arcgis SDE 10 to export our geographic data to the Oracle 11g database, and the spatial column are saved using the `St_Geometry` type.

And we have almost 10 + tables with points, lines, and polygons.

However we have to query the data despite of their geometry type, so I create a view which union all the required table and columns to the view, like this:

create or replace view MyView as
  select objectid,shape,name,address, 'FeaturePY' as table_name from FeaturePY
union all
  select objectid,shape,name,address, 'FeatureLN' as table_name from FeatureLN
union all
  select objectid,shape,name,address, 'FeaturePY' as table_name from FeaturePY
........

Everything works well unless I try to query the data by some spatial , for example, I want to query data inside the bounding box of

(20.22774247419824,30.168182996158205,20.29657868635644 30.21127000177344)

and make a pagination, I use the following two sqls:

1) for data

select * from ( select name,st_astext(shape) as shape from MyView h where ST_Intersects(h.shape, st_geometry('POLYGON ((20.22774247419824 30.168182996158205, 20.29657868635644 30.168182996158205, 20.29657868635644 30.21127000177344, 20.22774247419824 30.21127000177344, 20.22774247419824 30.168182996158205))',3)) = 1 ) where rownum <= 10

2) for total count:

select count(shape) from MyView h where ST_Intersects(h.shape, st_geometry('POLYGON ((20.22774247419824 30.168182996158205, 20.29657868635644 30.168182996158205, 20.29657868635644 30.21127000177344, 20.22774247419824 30.21127000177344, 20.22774247419824 30.168182996158205))',3)) = 1

However I found that the first sql will cost almost 30 seconds to get the data, and the second sql will take almost 6 minutes to get the count which is unacceptable.

And I have tried to use a different spatial function `ST_ENVINTERSECTS` to replace the `ST_Intersects`, but I can not get any improvement.

This is my current environment:

Dbserver: Windows server 2008 x64
Arcgis SDE 10 x64 with service pack5 installed.
Oracle 11gR2 x64
Records in the view: almost 150,000

I am not sure what's the problem, any suggestion to fix this?

topic Poor performance of spatial query in view with multiple geometry type unioned in Developers Questions

Poor performance of spatial query in view with multiple geometry type unioned