Oracle spatial performance issue

[Fernando Simon]

Hi,
    I believe that in the first version of the Oracle Spatial driver for
Mapserver (sometime ago, in 2001/2002) the ordered was present in the
SQL. But unfortunately I didn't remember why this token was remove. I
will check about this token, but can you send the EXPLAIN for the two
queries? How many rows are in your table? We can check the difference
between both.
    You can try to do something like this for your data definition:
        DATA "SHAPE FROM (SELECT /*+ ordered */OBJECTID, OBJTYPE, SHAPE
FROM KARTDATA.N50_HoydeKontur) USING UNIQUE OBJECTID VERSION 10g"
    Best regards.

Fernando Simon

Pål Kristensen wrote:
> 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'
>
> 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
>