a few weeks ago we upgraded from Oracle10g and ArcSDE 9.3.1 to Oralce11g and ArcSDE 10 SP1.
The new system is installed on new hardware and not connected to the old environment. Now we want to move our projects to the new environment, but we are running into big performance problems with our ST Functions. The main problem is, that the spatial index is not used in our new system enviroment. But why?
One example is ST_CONTAINS(geom1, geom2), if I am right it only uses the spatial index for the first argument. Correct?
This Statment takes 0,07 seconds on our old system and up to 200 seconds on the new system:
select c.kd_id as kd_id from trackpoints p, customer_fencing c WHERE p.deviceid = '262022534211494' AND p.speed <= 10.0 AND sde.st_contains(c.shape, p.shape) = 1
With Explain i can see that it is not using any Index on the new System (Full Table Scan) but it is using the Domain Index on the old System.
Even with:
select /*+ INDEX(c A123_IX1) */ c.kd_id as kd_id from trackpoints p, customer_fencing c WHERE p.deviceid = '262022534211494' AND p.speed <= 10.0 AND sde.st_contains(c.shape, p.shape) = 1
it is not using any index.
In our new System we have some Tablespaces which are using the SDE Tablespace to save all the Meta Data and some which are saving the Meta Data within the same Tablespace (for backup reasons). Could this be a problem?
I already check the hints given in this article but this does not work like excepted:
