We are trying to move the database to a new server and get performance hit. This is a simple query but with OWM we have more than 10 years historical data in LT tables.
1. With just ordered hint, it takes 6:30 mins to run
SELECT /*+ ordered*/ COUNT (a.id)
FROM slimdba.neighbourhoods b,slimdba.zoning_polygons a
WHERE b.id = 170
AND sdo_relate (a.geometry, b.geometry, 'mask=INSIDE+COVEREDBY+EQUAL querytype=WINDOW' ) = 'TRUE';
2. With parallet hint, it takes 1:05 mins to run
SELECT /*+ full(b) parallel(b,8) */ COUNT (a.id)
FROM slimdba.neighbourhoods b,slimdba.zoning_polygons a
WHERE b.id = 170
AND sdo_relate (a.geometry, b.geometry, 'mask=INSIDE+COVEREDBY+EQUAL querytype=WINDOW' ) = 'TRUE';
3. With no_merge(b) hint, it only process the current live data in b, it takes only 4 seconds
SELECT /*+ ordered no_merge(b) */ COUNT (a.id)
FROM slimdba.neighbourhoods b,slimdba.zoning_polygons a
WHERE b.id = 170
AND sdo_relate (a.geometry, b.geometry, 'mask=INSIDE+COVEREDBY+EQUAL querytype=WINDOW' ) = 'TRUE';
In most of the cases our users only care about live data. Is there a system level setting to ask Oracle to filter the live data first instead of using LT table first then filter after the spatial process?
Thanks a lot for any help!!
Sharon