|
|
Steve Pritchard created an issue |
|
Issue Type: |
|
|---|---|
|
Affects Versions: |
2.7.0 |
|
Assignee: |
Unassigned |
|
Components: |
WMS |
|
Created: |
08/May/15 11:33 AM |
|
Environment: |
Server: Linux, GlassFish Server Open Source Edition 4.0, Oracle 11.2 |
|
Priority: |
|
|
Reporter: |
|
Usually, when displaying a map, GeoServer makes a query to Oracle like: SELECT BOUNDARY as BOUNDARY FROM OWNER.MYTABLE WHERE SDO_FILTER(BOUNDARY, :1 , ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’ where :1 is set to the extent of the map. However if the extent crosses +180/-180 longitude, GeoServer makes a query like: SELECT BOUNDARY as BOUNDARY FROM OWNER.MYTABLE WHERE (SDO_FILTER(BOUNDARY, :1 , ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’ OR SDO_FILTER(BOUNDARY, :2 , ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’ ) This query takes a long time to execute (1 minute +) and puts a heavy load on Oracle. If several such queries are made concurrently, contention arises in the database slowing things down further. In the first (usual case), Oracle uses the spatial index to satisfy the query, giving good performance. In the second case, it appears that Oracle can’t use the spatial index (because of the OR condition). The second query could be rewritten using UNION as: SELECT BOUNDARY as BOUNDARY FROM OWNER.MYTABLE WHERE SDO_FILTER(BOUNDARY, :1 , ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’) This query is much more performant on Oracle, as the spatial index can be used on both halves of this query, and then the results are combined. Could the existing OR query made by GeoServer be changed to to a UNION. |
|
|
|
This message was sent by Atlassian JIRA (v6.5-OD-03-002#65000-sha1:b8f65f8) |
|
|