Database Software

I am in the process of converting my database from a 9i box to a new 10G 64-bit box. But I have found a problem which is causing some reports to be slower on the new box. I have simplified the queries down to having the user_sdo_geom_metadata table joined to use the diminfo in the queries (I know that I am not using them in these queries, but I simplified for testing purposes...)

If I run the following I get and look at the explain plan I get full table scans for both spatial tables and index lookups for the user_sdo_geom_metadata table queries and runs for about 14 seconds.

SELECT ROWNUM
from COUNTIES s,
NOMINATIONS O,
(select diminfo from user_sdo_geom_metadata where table_name='COUNTIES') S_DIM,
(select diminfo from user_sdo_geom_metadata where table_name='NOMINATIONS') O_DIM
where sdo_filter(S.GEOM,o.geom, 'querytype=WINDOW')='TRUE'
and sdo_geom.within_distance(o.geom,0,S.GEOM,.5)='TRUE';


If I just remove the two user_sdo_geom_metadata joins, I get spatial index usage on COUNTIES and the whole thing runs in less that a second.

SELECT ROWNUM
from COUNTIES s,
NOMINATIONS O
where sdo_filter(S.GEOM,o.geom, 'querytype=WINDOW')='TRUE'
and sdo_geom.within_distance(o.geom,0,S.GEOM,.5)='TRUE';

I have rebuilt the indexes, gathered stats, and tried hints to force the first query to use the spatial index. None of which made any change.

Has anyone else seen this?

Gerard Vidrine