Antwort: [UMN_MAPSERVER-USERS] Oracle spatial performance issue

[Benedikt Rothe]

Hi

> By adding the keyword /*+ ordered */ to the select statement 
considerably
> speeds up the quary. The resulting SQL statement the looks like this:
> SELECT /*+ ordered */OBJECTID, OBJTYPE, SHAPE FROM 
KARTDATA.N50_HoydeKontur 
> ...

What about this?

LAYER
  NAME "N50Hoydekurver"
  ...
  DATA "SHAPE FROM (SELECT /*+ ordered */ OBJECTID, OBJTYPE, SHAPE FROM 
KARTDATA.N50_HoydeKontur) USING UNIQUE OBJECTID VERSION 10g"
  ...


Benedikt Rothe

UMN MapServer Users List <MAPSERVER-USERS at LISTS.UMN.EDU> schrieb am 
04.12.2007 09:33:29:

> Hi!
> 
> I have set up a mapfile with layers that uses oracle spatial data. The
> problem is that the performance is rather bad. This is how I have set up 
the
> connection in the LAYER object.
> 
>    LAYER
>       NAME "N50Hoydekurver"
>       STATUS ON
>       CONNECTION "user/pass at KARTINNS_SKRIVDB06"
>       CONNECTIONTYPE oraclespatial
>       DATA "SHAPE FROM KARTDATA.N50_HoydeKontur USING UNIQUE OBJECTID
> VERSION 10g"
> 
> When this layer is queried the resulting SQL that is submitted to the
> database typically looks like this:
> 
> SELECT OBJECTID, OBJTYPE, SHAPE FROM KARTDATA.N50_HoydeKontur 
> WHERE SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, NULL,
> NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.
> SDO_ORDINATE_ARRAY(-26711.6293,6726071.91,-14068.0091,6736415.72)
> ),'querytype=window') = 'TRUE'
> 
> By adding the keyword /*+ ordered */ to the select statement 
considerably
> speeds up the quary. The resulting SQL statement the looks like this:
> 
> SELECT /*+ ordered */OBJECTID, OBJTYPE, SHAPE FROM 
KARTDATA.N50_HoydeKontur 
> WHERE SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, NULL,
> NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.
> SDO_ORDINATE_ARRAY(-26711.6293,6726071.91,-14068.0091,6736415.72)
> ),'querytype=window') = 'TRUE'
> 
> This test is performed manually because I haven't find anywhere to 
insert
> the keyword in the mapfile. Anyone have any ideas on how I could 
accomplish
> to tell MapServer to insert the keyword before sending the SQL to the
> database?
> 
> regards,
> Pål Kristensen
> -- 
> View this message in context: http://www.nabble.com/Oracle-spatial-
> performance-issue-tf4941783.html#a14146626
> Sent from the Mapserver - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20071205/5c979e6c/attachment.htm>