Home » Server Options » Spatial » Performance Issue with the query (oracle 11g)
Performance Issue with the query [message #571595] Tue, 27 November 2012 16:21
Messages: 117
Registered: April 2005
Senior Member

Iam querying the table which has over 8 million rows with the following query, It takes lot of time to query. Any suggestions to improve the query performance is greatly appreciated.

select out.* from (select r.category, r.subcat, r.lat, r.lon sdo_geom.sdo_distance(r.geom,latlon2geom(38.93486,-77.26026), 0.5, 'UNIT=MILE')dist,id, nvl(source, 'SHIP2010') as source
from (select * from shippers where sdo_within_distance(geom, latlon2geom(38.93486,-77.26026), 'DISTANCE=1 UNIT=MILE') = 'TRUE' and upper(category) = 'FARMING' AND upper(subcat) = 'CATTLE') R order by dist) out

where rownum <= 1;

I have indexes on geom column, function based index on upper(category), upper(subcat) columns.
latlon2geom is a function we have all it does it it converts the input to point geometry type

ex: latlon2geom(38.93486,-77.26026) = (2001, 8307, (-77.26026, 38.93486,),,)

Here is the explain plan for the above query

5 count stopkey

4 view TOM
cost: 766 bytes: 5250 cardinality:14

3 sort order by stopkey
cost: 766 bytes: 2,478 cardinality:14

2 Table access by index rowid TABLE shippers
cost: 765 bytes 2,478 cardinality: 14

1 Index range scan index shippers_subcat_fnct_idx
cost:119 cardinality: 33,832

Note: Unfortunately I can not create a test case as the database is on a different network and my email access is on a different network. The email access network doesn't have any oracle installed.

Thank you very much for your prompt reply.

Previous Topic: sdo_contains help
Next Topic: Oracle Spatial Query help
Goto Forum:

Current Time: Tue Jul 29 12:15:05 CDT 2014

Total time taken to generate the page: 0.08818 seconds