Filtering on oracle layers using mapserver 4.6

[Robert oude Lohuis]

Hi,

Currently we are trying to use different datasets through a single layer
definition in a mapfile. Mapserver runs on a Linux platform against a oracle
spatial database using OGR. The layer always accesses the same table, but we
want to use a filter. But then we got problems. The following two scenario's
are possible:

1. Use a complete Oracle select statement for the DATA clause
Advantage:    Oracles query power is used to get the specific dataset.
Disadvantage: No spatial queries are used by Mapserver when zooming in, so
all data from that set is transferred from database to mapserver. When
Zooming in the application does not get faster.

2. Use the full table for the DATA clause and use FILTER and FILTERITEM
clauses (this is also the proposed option by the mapserver documentation)
Advantage:    perfect spatial where clauses are produced by Mapserver, so
when zooming in performance is getting better because less objects are selected
Disadvantage: Filtering is NOT performed in the Oracle query. We assume it
is carried out in OGR/Mapserver, so too much data is transferred from
database to mapserver. With each dataset added to the database, the
performance gets worse in a lineair way.

We very much like to use option 2, is there any other suggestion/option for
our problem ?

greetz and thanks in advance.