[mapserver-users] Oracle Spatial

[Rahkonen Jukka]

Hi,

It is impossible to know how the Oracle optimiser selects to do the final job in the database only by looking at the SQL. Capture the SQL and send it directly with SQL*Plus by giving first "set autotrace on".  By doing that Oracle will print the execution plan which will give more information.
Oracle is usually willing to utilise spatial index very soon in the plan, but with complicated views with many odd joins it is not necessary so.

-Jukka Rahkonen-


Stuart.Harlan wrote:

> I am querying an Oracle Spatial table.  I am restricting the query using 
> an attribute in the table (road type).  In examining the query sent to 
> Oracle, it seems to query by attribute first, then by spatial envelope. 


> For datasets that only return a few features in the attribute query, this 
>  seems fairly efficient.  However, for datasets that return many features 
> in the attribute query that lie over a large spatial area, it would seem 
> more efficient to limit by spatial envelope first, then by attributes, 
> especially if the query area is relatively small compared to the spatial 
> extent of the dataset.  Can the user be allowed to set the 
> spatial/attribute query order?

> Example query - attribute query is executed first, then spatial.  For 
> small spatial areas, the query might be faster if the spatial filter were 
> executed first.

> DATA "SHAPE FROM (SELECT SHAPE FROM ROAD_TABLE WHERE DESG IN 
> ('CST','CRD')) USING SRID 8307"

> Stuart Harlan
> Senior Information Systems Technologist
> GIS Web Services
> Transportation Planning Division
> Missouri Department of Transportation