Christian Maul created GEOS-5086:
------------------------------------
Summary: getFeatureInfo causes full-table scans to return feature information with ArcSDE/Oracle
Key: GEOS-5086
URL: https://jira.codehaus.org/browse/GEOS-5086
Project: GeoServer
Issue Type: Bug
Components: ArcSDE
Affects Versions: 2.1.3
Environment: Database
Oracle 10g ArcSDE jars version 9.2, 9.3, 9.3.1
Geoserver2.1.3/Tomcat6.0.35/Java 1.6.31/Apache2.2.24/Windows2008 64-bit
Reporter: Christian Maul
Assignee: Andrea Aime
Fix For: 2.1.4
According to database logs Oracle does a full table scan and does not use the resultset or indices to find the feature for the getFeatureInfo request.
The spatial index is up-to-date and the tables are not versioned.
The SDE version is 9.2, we have a trial version of 9.3.1 both on top of Oracle 10g and both display the following behaviour:
tables with small amount of features (100 - 5000) return a getFeatureInfo in less than 500msec.
tables with up to 50000 features need 10 seconds
tables with 200000 features return getFeatureInfo in 30 - 60 seconds.
with 3 mio features 9.2 does not return a response using the jsde-sdk92.jar, using the jsde-sdk93.jar it returned a response after 70 minutes.
Answers when raised @ the geoserver-users group from Gabriel Roldan and Andrea Aimee:
Andrea Aimee's answer
Geoserver does two queries, one to determine how many features are in the
pixel you queried and one to determine the actual features. Normally that is
pretty fast since the area queried is so small, I guess either the SDE
connector is missing some optimization or the underlying database is missing
either a spatial index or has outdates statistics and it is not using the
index. Hard to tellwithout access to the actual data.
Gabriel Roldans answer
The ArcSDE connector can't do optimized count on ArcSDE + Oracle
_only_ if the table is registered as versioned in ArcSDE. The
proprietary arcsde jar produces an error if you try to compute table
statistics on versioned tables. That said, by looking at the code [1]
it looks to me like there's actually a bug in there. I think the query
scan should only be done if that's the case, but the control statement
is not enclosing it. But I am not sure, would need to re-test it and
document what's the actual behavior with oracle. Can't do that right
now though due to other priorities. Chances are you create a jira
issue and assign it to me so I don't forget, or if you have some
coding skills you can send a patch.
link
this is which Gabriel considers the offending bit:
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://jira.codehaus.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira