Hi, You should first test the view directly in Oracle with a bounding box and ANY_INTERACTION query. If such query is fast, change the logging level of your Geoserver into geotools developer, catch the SQL that Geoserver is generating and run queries directly from Oracle. You may find something interesting even the test is not totally reliable because Geoserver is using prepared statements and running the extracted SQL with SQL Plus is not exactly the same thing.
One quick test would be to remove first separately and then both "where year=2016" and "where geom is not null". Oracle optimizer is sometimes making odd decisions. -Jukka Rahkonen- ________________________________________ [email protected] wrote: Hi, no, my Select-Statement in the view is just sth. like 'select column abc as XY, select column bcd as XZ where year=2016 and geom is not null'. The rights don't seem to be a problem, since i have also tried to use the DBA-Account with no success. I'm using OJDBC7.jar as the driver (OJDBC6.jar was also tested). <quote author='Rahkonen Jukka (MML)'> Hi, If the view makes simple "SELECT geometry, other_stuff WHERE..." the spatial index works transparently for us. We do not run 2.9, though. Do you use some more advanced SQL in your views? Also giving GRANT SELECT ON VIEW for the geoserver user may not be enough but your user seems to have good rights because normat tables work fine. -Jukka Rahkonen- ________________________________________ Lähettäjä: fmd.85 <[email protected]> Lähetetty: 27. heinäkuuta 2016 10:07 Vastaanottaja: [email protected] Aihe: [Geoserver-users] Oracle view doesn't usw spatial index Hi, I'm using geoserver 2.9.0 with the oracle plugin and try to publish a layer of a view. I created the View in the database and in Geosever (add new layer -> create view) with the same results. The original table in the database has a spatial index and publishing the table directly works fine. If i use any kind of view, the spatial index of the table seems to be ignored. Even the computation of the Bounding Box takes ~30 minutes (with an index ~15 seconds). Does anyone else have the same problem? Any suggestions how to solve this problem? Creating materialized views for each table is no option for me since i'm running out of table space. Cheers, Max -- View this message in context: http://osgeo-org.1560.x6.nabble.com/Oracle-view-doesn-t-usw-spatial-index-tp5278308.html Sent from the GeoServer - User mailing list archive at Nabble.com. ------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev _______________________________________________ Geoserver-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users ------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev _______________________________________________ Geoserver-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users </quote> Quoted from: http://osgeo-org.1560.x6.nabble.com/Oracle-view-doesn-t-use-spatial-index-tp5278308p5278311.html _____________________________________ Sent from http://osgeo-org.1560.x6.nabble.com ------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev _______________________________________________ Geoserver-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
