[mapserver-users] Oracle Spatial: SDO_SRID is bad for performance

[Bjarni Pjetursson]

Hi group,
after experimenting with the Oracle Spatial support for mapserver, I've
noticed poor performance on Oracle 9i when querying large data sets. The
reason seems to be, that the query is sent to Oracle like this:

SELECT rownum, shape FROM my_table my_table_alias
WHERE sdo_filter(shape,
  mdsys.sdo_geometry(
    2003,
    my_table_alias.shape.sdo_srid,
    null,
    mdsys.sdo_elem_info_array(1,1003,3),
    mdsys.sdo_ordinate_array(31,19,52,61)
 ),'querytype=window') = 'TRUE';

By using the "my_table_alias.shape.sdo_srid", Oracle cannot optimize the
query, since each row must go through the sdo_filter function separately.

I know all my data to be of the same SRID, so I can replace
"my_table_alias.shape.sdo_srid" whith "8307" in the above query. The
response time drops from 60 seconds to 3 seconds! Unfortunately there is no
way I can tell mapserver this in the map-file...

Has anyone else experienced the same problem or know of a solution?
I use MapServer 3.5 on Windows.

Regards
Bjarni Thor Pjetursson
TietoEnator Consulting A/S
Denmark