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

[John Newton]

try setting in your map file:

DATA shape my_table USING SRID 8307

I believe this works in 3.6, I haven't check 3.5.

-john


----- Original Message -----
From: "Bjarni Pjetursson" <Bjarni.Pjetursson at tietoenator.com>
To: <mapserver-users at lists.gis.umn.edu>
Sent: Tuesday, July 16, 2002 6:57 AM
Subject: [mapserver-users] Oracle Spatial: SDO_SRID is bad for performance


> 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
>