[Geoserver-users] Oracle view doesn't usw spatial index

Jukka_Rahkonen · July 27, 2016, 9:41am

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-

________________________________________
fmd85@anonymised.com 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 <fmd85@anonymised.com>
Lähetetty: 27. heinäkuuta 2016 10:07
Vastaanottaja: geoserver-users@lists.sourceforge.net
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
Geoserver-users@lists.sourceforge.net
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

Andrea_Aime4 · July 27, 2016, 10:05am

Hey,
I just stumbled into this interesting article:
https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1

It would seem Oracle could be made smarter in its plan choice, but one has to tell it explicitly to be with a query hint??
I don’t have time/mandate to work on this, but it could be useful to anyone that does.

Cheers
Andrea

···

On Wed, Jul 27, 2016 at 11:41 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

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-

fmd85@anonymised.com 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).

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 <fmd85@anonymised.com>
Lähetetty: 27. heinäkuuta 2016 10:07
Vastaanottaja: geoserver-users@lists.sourceforge.net
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
Geoserver-users@anonymised.comsts.sourceforge.net
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
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Quoted from: [http://osgeo-org.1560.x6.nabble.com/Oracle-view-doesn-t-use-spatial-index-tp5278308p5278311.html](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
Geoserver-users@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime

@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313

fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

[Geoserver-users] Oracle view doesn't usw spatial index

== GeoServer Professional Services from the experts! Visit http://goo.gl/it488V for more information.

==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.